Interval analysis

View previous topic View next topic Go down

Interval analysis

Post  winny on Wed Feb 03, 2016 10:10 pm

Hi,

We have "party transaction table", for which lets say grain is one row per party/customer/trans id/trans amt/ trans time stamp.

My user req is to flag to all those parties on daily basis, where there 5 or more transaction at a party location with in 30 minutes of interval.

I am thinking to store all such parties in a separate fact table with a grain of party/customer/trans id/Day/trans amt/ trans time stamp..

But I am kind of stuck on how to derive a logic to get only such parties who fall in above 30 min interval criteria.

Did any of you folks face similar scenario? could some one throw some light on this?

Please suggest.

Thanks,
Winny

winny

Posts : 4
Join date : 2011-03-23

View user profile

Back to top Go down

Re: Interval analysis

Post  ngalemmo on Thu Feb 04, 2016 3:37 am

You are trying to count how many events occurred within 30 minutes of the one you are looking at.

Basically it is a correlated sub query. Given an event, join back to the events and count how many their are where the time is <= 30 minutes from the time of the current event. This will give you a row for each event and how many other events occurred in the half hour. You can then use this list to build the facts.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum