你有答案,将工作的一些选项,下面是一个使用Oracle的“Windowing Functions with Logical Offset”功能,而不是连接或相关子查询。
首先,测试表:
Wrote file afiedt.buf
1 create table t pctfree 0 nologging as
2 select date '2011-09-15' + level/(24 * 4) as date_page_requested
3 from dual
4* connect by level <= (24 * 4)
SQL>/
Table created.
SQL> insert into t values (to_date('2011-09-15 11:11:11', 'YYYY-MM-DD HH24:Mi:SS'));
1 row created.
SQL> commit;
Commit complete.
牛逼现在包含在上午11点11分11秒排每刻钟为一个附加行的日子。该查询先行三步。第1步是,对于每一行,让前来接下来的一个小时内,该行的时间后的行数:
1 with x as (select date_page_requested
2 , count(*) over (order by date_page_requested
3 range between current row
4 and interval '1' hour following) as hour_count
5 from t)
然后通过HOUR_COUNT分配顺序:
6 , y as (select date_page_requested
7 , hour_count
8 , row_number() over (order by hour_count desc, date_page_requested asc) as rn
9 from x)
最后选择具有最多后续行的最早行。
10 select to_char(date_page_requested, 'YYYY-MM-DD HH24:Mi:SS')
11 , hour_count
12 from y
13* where rn = 1
如果多个60分钟的窗口数小时计数,上面只会给你第一个窗口。