Beginning Data Mining

Question

For simplicity, my database has entities and transactions. Each transaction has a DateCreated and a foreign key to the entity. I'd like to have the ability to find entities that have had a recurring number of transactions within a close time proximity of other entities.

Simply, if entity A has had a transaction within 10 minutes of entity B, 5 different times.

The more I research on this topic, the more confused I'm becoming.

I ran through the Data Mining Algorithms and their AdventureWorks data mining tutorial. I think I need a clustering approach.

Mostly I'm looking for guidance on how this type of problem is typically approached. In the end, my goal is to allow the user to select X minutes and Y occurrences, and display the result data back to them. If I can offer any more information please let me know.

• gnat: recommended reading: Where to start

Answer 1

I would treat the DateCreated column from entity A and entity B as a foreign key to another table that stores counts of A and B for those dates. You can drop the seconds for that foreign key.

You can then use that table to find all entity A records that have entity B records within X minutes of each other. That would be done by using a JOIN from the entity A table to the foreign date counts table.

Difficult to explain further without actually seeing the schema of the tables.