Oracle SQL:基于固定的时间框架(例如15或30分钟)统计记录

Question

我有一个类似于

的表

Start time            |       End Time                 | User |
09/02/2021 03:01:13   |       09/02/2021 03:45:15      | ABC  |
09/02/2021 03:15:20   |       09/02/2021 05:03:20      | XYZ  |
09/02/2021 06:03:12   |       09/02/2021 06:15:30      | DEF  |

期待输出:

StDt                  |       EndDt                    | Count(1) 
09/02/2021 00:00:00   |       09/02/2021 01:00:00      | 0
09/02/2021 01:00:00   |       09/02/2021 02:00:00      | 0
09/02/2021 02:00:00   |       09/02/2021 03:00:00      | 0
09/02/2021 03:00:00   |       09/02/2021 04:00:00      | 2
09/02/2021 04:00:00   |       09/02/2021 05:00:00      | 1
09/02/2021 05:00:00   |       09/02/2021 06:00:00      | 0
09/02/2021 06:00:00   |       09/02/2021 07:00:00      | 1

这个例子中的间隔是每小时，但我想保持它的灵活性，为10分钟/15分钟/30分钟。我希望这是写在一个单一的sql。到目前为止，我所能解决的就是如何生成范围。

select t1.StartDt, t1.EndDt from 
(
select 
(to_char(timestamp '2021-02-09 00:00:00' + numtodsinterval(rownum*60,'MINUTE') - numtodsinterval(60,'MINUTE'),'DD-MM-YYYY hh24:mi')) as StartDt,
(to_char(timestamp '2021-02-09 00:00:00' + numtodsinterval(rownum*60,'MINUTE'),'DD-MM-YYYY hh24:mi')) as EndDt
from dual connect by level <= 24
) t1;

我不知道如何链接到上面提到的表格，以获得我需要的格式的数据。

Answer 1

你有这样一个很好的启动，除了保持时间值在子查询中的时间标记格式，并在结果显示阶段将TO_CHAR格式移动到主查询，同时使用具有明显计数聚合的重叠间隔的相关子查询，并使用绑定变量作为时间部分值(60,30,15)的占位符，如

SQL> var min number
SQL> exec :min := 60

PL/SQL procedure successfully completed
min
---------
60

SQL> SELECT TO_CHAR(t.StartDt,'DD-MM-YYYY HH24:MI') AS StartDt,
2         TO_CHAR(t.EndDt,'DD-MM-YYYY HH24:MI') AS EndDt,
3         ( SELECT COUNT(DISTINCT "User") 
FROM tab 
WHERE t.EndDt >= Start_Time 
AND t.StartDt <= End_Time ) AS Count
4    FROM
5    (
6       SELECT timestamp '2021-02-09 00:00:00' +
7                        numtodsinterval(rownum * :min, 'MINUTE') -
8                        numtodsinterval(:min, 'MINUTE') AS StartDt,
9              timestamp '2021-02-09 00:00:00' +
10                        numtodsinterval(rownum * :min, 'MINUTE') AS EndDt
11         FROM dual
12      CONNECT BY level <= 24
13    ) t
14   ORDER BY StartDt;

STARTDT          ENDDT                 COUNT
---------------- ---------------- ----------
09-02-2021 00:00 09-02-2021 01:00          0
09-02-2021 01:00 09-02-2021 02:00          0
09-02-2021 02:00 09-02-2021 03:00          0
09-02-2021 03:00 09-02-2021 04:00          2
09-02-2021 04:00 09-02-2021 05:00          1
09-02-2021 05:00 09-02-2021 06:00          1
09-02-2021 06:00 09-02-2021 07:00          1
09-02-2021 07:00 09-02-2021 08:00          0
.....
.....