sql - Oracle：根据每行的过去 6 个月间隔计算 count()
问题描述
我有以下数据（数据可从 2017 年至今）
SELECT * FROM TABLE1 WHERE DATE > TO_DATE('01/01/2019','MM/DD/YYYY')
Emp_ID Date Vehicle_ID Working_Hours
1005 01/01/2019 X500 7
1005 01/02/2019 X500 6
1005 01/03/2019 X700 7
1005 01/04/2019 X500 5
1005 01/05/2019 X700 7
1005 01/06/2019 X500 7
1006 01/01/2019 X500 7
1006 01/02/2019 X500 6
1006 01/03/2019 X700 7
1006 01/04/2019 X500 5
1006 01/05/2019 X700 7
1006 01/06/2019 X500 7
我需要计算两列。LAST_6M_UNIQ_Vehicle_Count ==> 该员工在过去（过去）6 个月内的唯一车辆 ID 计数 LAST_6M_Vehicle_Count ==> 该员工在过去 6 个月内的所有车辆 ID 计数 注意：从日期列开始的过去 6 个月
预期输出：
Emp_ID Date Vehicle_ID Working_Hours LAST_6M_UNIQ_Vehicle_Count LAST_6M_Vehicle_Count
1005 01/01/2019 X500 7 6 66
1005 01/02/2019 X500 6 7 62
1005 01/03/2019 X700 7 6 63
1005 01/04/2019 X500 5 7 67
1005 01/05/2019 X700 7 7 66
1005 01/06/2019 X500 7 7 67
. . . .
. . . .
. . . .
1005 03/20/2019 X600 6 12 75
1006 01/01/2019 X500 7 11 74
1006 01/02/2019 X500 6 10 66
1006 01/03/2019 X700 7 11 72
1006 01/04/2019 X500 5 13 67
1006 01/05/2019 X700 7 12 64
1006 01/06/2019 X500 7 12 63
例如，在第一行中，LAST_6M_UNIQ_Vehicle_Count 的值为 6，因为对于员工 id 1005，车辆 id 在 ((01/01/2019) - 6 个月) 和 01/01/2019 之间的唯一计数有 6 辆不同的车辆id 在其中。
我尝试了 Over and Partition by 但缺少 6 个月的间隔
SELECT t.*, COUNT(DISTINCT t.VEHICLE_ID) OVER (PARTITION BY t.EMP_ID ORDER BY t.DATE)
AS LAST_6M_UNIQ_Vehicle_Count
FROM TABLE1 t
我无法根据每行的 6 个月间隔计算值。
非常感谢您的帮助。
解决方案
Oracle 不喜欢COUNT( DISTINCT ... ) OVER ( ... )在具有范围的窗口分析函数中使用，并且会引发ORA-30487: ORDER BY not allowed here异常（否则，这将是解决方案）。它可以在没有DISTINCT关键字的情况下工作，但不能在使用它的情况下工作。
相反，您可以使用相关的子查询：
SELECT t.*,
( SELECT COUNT( DISTINCT vehicle_id )
FROM table_name c
WHERE c.emp_id = t.emp_id
AND c."DATE" <= t."DATE"
AND ADD_MONTHS( t."DATE", -6 ) <= c."DATE"
) AS last_6m_uniq_vehicle_count,
COUNT(t.vehicle_id) OVER (
PARTITION BY t.emp_id
ORDER BY t."DATE"
RANGE BETWEEN INTERVAL '6' MONTH PRECEDING
AND CURRENT ROW
) AS last_6m_vehicle_count
FROM table_name t
对于样本数据：
CREATE TABLE table_name ( vehicle_id, emp_id, "DATE" ) AS
SELECT 1, 1, DATE '2020-08-31' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-07-31' FROM DUAL UNION ALL
SELECT 1, 1, DATE '2020-06-30' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-05-31' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-04-30' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-03-31' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-02-29' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-01-31' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2020-01-31' FROM DUAL;
输出：
VEHICLE_ID | EMP_ID | 日期 | LAST_6M_UNIQ_VEHICLE_COUNT | LAST_6M_VEHICLE_COUNT
---------: | -----: | :-------- | -------------------------: | --------------------:
2 | 1 | 20 年 1 月 31 日 | 2 | 2
3 | 1 | 20 年 1 月 31 日 | 2 | 2
2 | 1 | 20 年 2 月 29 日 | 2 | 3
2 | 1 | 20 年 3 月 31 日 | 2 | 4
2 | 1 | 20 年 4 月 30 日 | 2 | 5
2 | 1 | 20 年 5 月 31 日 | 2 | 6
1 | 1 | 20 年 6 月 30 日 | 3 | 7
2 | 1 | 20 年 7 月 31 日 | 3 | 8
1 | 1 | 20 年 8 月 31 日 | 2 | 7
db<>在这里摆弄