Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Interval analysis

2 posters

Go down

Interval analysis Empty Interval analysis

Post  winny Wed Feb 03, 2016 10:10 pm


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.



Posts : 4
Join date : 2011-03-23

Back to top Go down

Interval analysis Empty Re: Interval analysis

Post  ngalemmo 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.

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

Back to top Go down

Back to top

- Similar topics

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