So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be clear in the code examples.
I could fix on the
min(sale_date) of my data and calculate the windows form there, counting each event:
but that would not be a sliding window. However if I fix on the
max(sale_date), as this date increases the windows are brought along:
In these examples, I am using the following mechanism to obtain the time windows:
SELECT DISTINCT ma - (level - mod(level, 7)) - 7 + 1 dt_lim_bot, ma - (level - mod(level, 7)) dt_lim_up, level - mod(level, 7) slc_id FROM (select max(dt) ma, min(dt) mi from sales) CONNECT BY LEVEL <= ma - mi order by 1
(as mentioned, the
7s here could be replaced by any number of days)
Mind the detail:
select max(dt) ma, min(dt) mi from sales. Max and min are found regardless if these dates are relevantes for all of my users, which wouldn't be true in a example like: http://sqlfiddle.com/#!4/74f1b/4
I know it will probably not change the result of my initial examples and the problem is probably solved already, though I was curious.
If I try to use
select max(dt) ma, min(dt) mi, usr from sales group by usr instead (http://sqlfiddle.com/#!4/74f1b/3), it breaks (infinite loop?). So I figured I should change my
connect by too, but couldn't come up with a quick solution.
What would be the correct way to generate these time slices, per user basis?
I am working on Oracle 11r2