Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: http://sqlfiddle.com/#!4/7cd69/6

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: http://sqlfiddle.com/#!4/7cd69/7

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

share|improve this question
    
What determines the start date of the window? –  FreshPhilOfSO Jan 29 '13 at 19:53
    
for the first window for example, max(dt) is the upper limit and max(dt) - [WindowSize] + 1 is the lower limit. for the second window, max(dt) - [WindowSize] for the upper, max(dt) - [2 x WindowSize] + 1 lower and so forth. –  filippo Jan 29 '13 at 20:05
add comment

2 Answers

up vote 2 down vote accepted

Here is another option. It still doesn't use your CONNECT BY (probably a good thing), but does have fewer table scans. For the SQL Fiddle I used Phil's modified data.

SELECT usr, usrmax - (slice-1)*7 WindowEnd, count(*) FROM 
(
  SELECT usr, max(dt) OVER (PARTITION BY Usr) usrmax
     , TRUNC((max(dt) OVER (PARTITION BY Usr) - dt)/7) + 1 Slice
  FROM Sales s
) a
GROUP BY usr, usrmax, slice
ORDER BY usr, WindowEnd;

+1 For an excellent and interesting question.

share|improve this answer
    
Nice one ... :) –  FreshPhilOfSO Jan 30 '13 at 18:44
add comment

I've done it using a conversion into a Julian date, plus a hacky division, truncate, then multiply.

Source data to show the upper window is correct, and different for each user:

with data as
    (
      select usr,TO_NUMBER(TO_CHAR(max(dt), 'J')) as maxwindow
      from sales
      group by usr
    )
select dt, TO_DATE((trunc((TO_NUMBER(TO_CHAR(dt, 'J'))-maxwindow)/7)*7)+maxwindow,'J') as grouping_date_upper_window, sales.usr,cmp
from data,sales
where data.usr=sales.usr

Finished query:

with data as
    (
      select usr,TO_NUMBER(TO_CHAR(max(dt), 'J')) as maxwindow
      from sales
      group by usr
    )
select usr,GROUPING_DATE_UPPER_WINDOW,count(*)
from (
select sales.dt, TO_DATE((trunc((TO_NUMBER(TO_CHAR(dt, 'J'))-maxwindow)/7)*7)+maxwindow,'J') as grouping_date_upper_window, sales.usr,sales.cmp
from data,sales
where data.usr=sales.usr
  )
group by usr, GROUPING_DATE_UPPER_WINDOW
order by 1,2;

Just plug the window size into where the two "7" values currently are.

SQL Fiddle here - new fiddle with slightly altered data so you can see that the windows are different for each usr.

share|improve this answer
    
Thanks for the answer, but is kind of that though. Maybe I didn't exactly get your question on the comments before, but, from what you did seems like we have the same "global" maximun as before. At that point my query would work too. I was wondering on how to use only the relevant maximun for each user (although, as I said, I believe the results wont change in this case). To that end, I figure your data query would have to look something like select TO_NUMBER(TO_CHAR(max(dt), 'J')) as maxwindow, usr from sales group by usr, isn't it? –  filippo Jan 30 '13 at 0:31
    
thanks for the nice trick on how to create a window with only one value anyway :) –  filippo Jan 30 '13 at 0:33
    
answer edited to reflect requirements –  FreshPhilOfSO Jan 30 '13 at 0:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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