我有一个表，其中包含用户ID和log_date等日志数据列，我想检索过去7小时的唯一用户计数，
test_table
----------------
userid logdate
1 04/02/2020 02:12:00 PM
1 04/02/2020 02:00:00 PM
2 04/02/2020 01:50:00 PM
1 04/02/2020 01:10:00 PM
2 04/02/2020 01:11:00 PM
3 04/02/2020 01:12:00 PM
1 04/02/2020 10:12:00 AM
4 04/02/2020 10:14:00 AM
假设 sysdate 是 04/02/2020 02：12：14 PM，因此如果记录存在，则输出应如下所示，则唯一用户计数为 0。
unique_user_count time
2 NOW(04/02/2020 02:12:14 PM)
3 04/02/2020 01:12:14 PM
0 04/02/2020 12:12:14 PM
2 04/02/2020 11:12:14 AM
0 04/02/2020 10:12:14 AM
0 04/02/2020 09:12:14 AM
0 04/02/2020 08:12:14 AM
我是甲骨文的新手，所以请在这里需要专家的帮助。
使用示例数据，这样的东西可能会起作用。请参阅代码中的注释。
SQL> with
2 right_now (datum) as
3 -- it is used instead of SYSDATE, because of sample data
4 (select to_date('02.04.2020 14:12', 'dd.mm.yyyy hh24:Mi') from dual),
5 test (userid, logdate) as
6 (select 1, to_date('02.04.2020 14:12', 'dd.mm.yyyy hh24:mi') from dual union all
7 select 1, to_date('02.04.2020 14:00', 'dd.mm.yyyy hh24:mi') from dual union all
8 select 2, to_date('02.04.2020 13:50', 'dd.mm.yyyy hh24:mi') from dual union all
9 select 1, to_date('02.04.2020 13:10', 'dd.mm.yyyy hh24:mi') from dual union all
10 select 2, to_date('02.04.2020 13:11', 'dd.mm.yyyy hh24:mi') from dual union all
11 select 3, to_date('02.04.2020 13:12', 'dd.mm.yyyy hh24:mi') from dual union all
12 select 1, to_date('02.04.2020 10:12', 'dd.mm.yyyy hh24:mi') from dual union all
13 select 4, to_date('02.04.2020 10:14', 'dd.mm.yyyy hh24:mi') from dual
14 ),
15 calendar as
16 -- in real situation, you'd use SYSDATE instead of DATUM and DUAL instead of RIGHT_NOW
17 (select r.datum - (level - 1) / 24 datum,
18 lag(r.datum - (level - 1) / 24) over (order by level desc) prev_datum
19 from right_now r
20 connect by level <= 8
21 )
22 select count(distinct t.userid) distinct_users,
23 to_char(c.prev_datum, 'hh24:mi') ||' - '||
24 to_char(c.datum , 'hh24:mi') period
25 from calendar c left join test t on t.logdate between c.prev_datum and c.datum
26 where c.prev_datum is not null
27 group by to_char(c.prev_datum, 'hh24:mi') ||' - '||
28 to_char(c.datum , 'hh24:mi')
29 order by 2;
DISTINCT_USERS PERIOD
-------------- -------------
0 07:12 - 08:12
0 08:12 - 09:12
1 09:12 - 10:12
2 10:12 - 11:12
0 11:12 - 12:12
3 12:12 - 13:12
3 13:12 - 14:12
7 rows selected.
SQL>
你需要某种数字/计数表。 但这个想法是一个left join：
with n as (
select 0 as n from dual union all
select 1 as n from dual union all
select 2 as n from dual union all
select 3 as n from dual union all
select 4 as n from dual
)
select sysdate - (n + 1) * interval '1' hour,
count(distinct t.userid)
from n left join
test_table t
on t.logdate >= sysdate - (n + 1) * interval '1' hour and
t.logdate < sysdate - n * interval '1' hour
group by n
order by n;