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.
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.
0