1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.