Oracle SQL中统计Offer活跃天数问题求助

需求 #

统计每个Offer的活跃天数。

表结构 #

CREATE TABLE myTable(u_id, offer, status, status_date) as
SELECT 1,  'Offer_1', 'Active', TO_DATE('2021/12/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')
  FROM dual
UNION ALL
SELECT 1, 'Offer_1', 'Deactive', TO_DATE('2022/02/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss') FROM dual
UNION ALL
SELECT 1, 'Offer_1','Active', TO_DATE('2022/03/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')  FROM dual
UNION ALL
SELECT 1, 'Offer_1','Deactive', TO_DATE('2022/04/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')  FROM dual
UNION ALL
SELECT 1, 'Offer_2','Active', TO_DATE('2022/12/30 21:02:44', 'yyyy/mm/dd hh24:mi:ss') FROM dual

注：原表创建语句存在语法错误（多余逗号），已修正。

原错误SQL #

select distinct u_id,offer_id,
trunc(nvl(case when status = 'Deactive' then status_date end),sysdate) - 
trunc(case when status = 'Active' then status_date end) date_diff 
from myTable

期望输出 #

问题分析 #

原SQL存在多处问题：

• 引用了不存在的字段offer_id，实际表字段为offer；
• nvl函数使用错误，第二个参数位置误用sysdate，且未正确关联Active与对应的Deactive记录；
• 未处理多次活跃周期的累加逻辑，直接按行计算会得到错误的差值。

修正后的SQL #

使用窗口函数LEAD关联每个Active状态对应的下一个状态日期（无后续状态则用当前日期），再累加每个活跃周期的天数：

SELECT u_id,
       offer,
       SUM(TRUNC(NEXT_STATUS_DATE) - TRUNC(status_date)) AS total_day
FROM (
    SELECT u_id,
           offer,
           status,
           status_date,
           -- 获取下一条记录的状态日期，若为最后一条Active记录则用当前日期
           LEAD(status_date, 1, SYSDATE) OVER (PARTITION BY u_id, offer ORDER BY status_date) AS NEXT_STATUS_DATE
    FROM myTable
)
WHERE status = 'Active' -- 仅计算从Active到下一个状态的时长
GROUP BY u_id, offer;

结果验证 #

• Offer_1的两个活跃周期：
  • 2021/12/01 至 2022/02/01：TRUNC('2022/02/01') - TRUNC('2021/12/01') = 62天
  • 2022/03/01 至 2022/04/01：TRUNC('2022/04/01') - TRUNC('2022/03/01') = 31天
  • 总计：62+31=93天，符合预期。
• Offer_2从2022/12/30到当前日期（假设当前为2023/01/30）：TRUNC(SYSDATE) - TRUNC('2022/12/30') = 31天，符合预期。

内容的提问来源于stack exchange，提问作者user18552635