如何用DB2 SQL计算指定12个月的每日活跃ID月均数

核心思路 #

先清洗原始数据，合并同一ID的重叠/连续活跃区间，避免单日重复计数；再生成目标周期内的所有日期，关联活跃区间得到每日唯一活跃ID；最后按月统计总活跃数并除以当月天数，得到日均。

1. 清洗活跃区间（合并同一ID的重叠/连续记录） #

使用递归CTE合并同一ID的重叠或连续活跃时间段，确保每个ID的活跃区间无重叠：

WITH cleaned_active_periods AS (
    -- 初始步骤：按ID和START_DATE排序，标记初始区间
    SELECT 
        id,
        start_date,
        end_date,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY start_date) AS rn
    FROM your_table_name
    UNION ALL
    -- 递归步骤：合并重叠/连续区间
    SELECT 
        cap.id,
        cap.start_date,
        CASE WHEN tap.end_date > cap.end_date THEN tap.end_date ELSE cap.end_date END AS end_date,
        tap.rn
    FROM cleaned_active_periods cap
    JOIN (
        SELECT 
            id,
            start_date,
            end_date,
            ROW_NUMBER() OVER(PARTITION BY id ORDER BY start_date) AS rn
        FROM your_table_name
    ) tap ON cap.id = tap.id AND tap.rn = cap.rn + 1
        AND tap.start_date <= cap.end_date + 1 DAY -- 连续或重叠的区间合并
),
-- 去重，保留每个ID的最终合并区间
final_active_periods AS (
    SELECT 
        id,
        start_date,
        MAX(end_date) AS end_date
    FROM cleaned_active_periods
    GROUP BY id, start_date
)

2. 生成目标12个月周期的日期序列 #

替换起止日期为你需要统计的12个月周期的首尾日（格式如'2023-01-01'）：

, date_range AS (
    SELECT DATE('2023-01-01') AS calendar_date
    FROM sysibm.sysdummy1
    UNION ALL
    SELECT calendar_date + 1 DAY
    FROM date_range
    WHERE calendar_date < DATE('2023-12-31')
)

3. 统计每日活跃ID数并计算月度日均 #

关联清洗后的活跃区间与日期序列，统计每日唯一活跃ID，再按月计算日均：

SELECT 
    YEAR(calendar_date) AS stat_year,
    MONTH(calendar_date) AS stat_month,
    -- 计算当月天数
    DAYS(LAST_DAY(calendar_date)) - DAYS(DATE(YEAR(calendar_date), MONTH(calendar_date), 1)) + 1 AS month_days,
    -- 日均活跃ID数：总活跃数 / 当月天数
    COUNT(DISTINCT fap.id) * 1.0 / (DAYS(LAST_DAY(calendar_date)) - DAYS(DATE(YEAR(calendar_date), MONTH(calendar_date), 1)) + 1) AS avg_daily_active_ids
FROM date_range dr
LEFT JOIN final_active_periods fap 
    ON dr.calendar_date BETWEEN fap.start_date AND fap.end_date
GROUP BY YEAR(calendar_date), MONTH(calendar_date)
ORDER BY stat_year, stat_month;

完整整合SQL #

替换your_table_name和起止日期即可运行：

WITH cleaned_active_periods AS (
    SELECT 
        id,
        start_date,
        end_date,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY start_date) AS rn
    FROM your_table_name
    UNION ALL
    SELECT 
        cap.id,
        cap.start_date,
        CASE WHEN tap.end_date > cap.end_date THEN tap.end_date ELSE cap.end_date END AS end_date,
        tap.rn
    FROM cleaned_active_periods cap
    JOIN (
        SELECT 
            id,
            start_date,
            end_date,
            ROW_NUMBER() OVER(PARTITION BY id ORDER BY start_date) AS rn
        FROM your_table_name
    ) tap ON cap.id = tap.id AND tap.rn = cap.rn + 1
        AND tap.start_date <= cap.end_date + 1 DAY
),
final_active_periods AS (
    SELECT 
        id,
        start_date,
        MAX(end_date) AS end_date
    FROM cleaned_active_periods
    GROUP BY id, start_date
),
date_range AS (
    SELECT DATE('2023-01-01') AS calendar_date -- 替换为起始月份第一天
    FROM sysibm.sysdummy1
    UNION ALL
    SELECT calendar_date + 1 DAY
    FROM date_range
    WHERE calendar_date < DATE('2023-12-31') -- 替换为结束月份最后一天
)
SELECT 
    YEAR(calendar_date) AS stat_year,
    MONTH(calendar_date) AS stat_month,
    DAYS(LAST_DAY(calendar_date)) - DAYS(DATE(YEAR(calendar_date), MONTH(calendar_date), 1)) + 1 AS month_days,
    COUNT(DISTINCT fap.id) * 1.0 / (DAYS(LAST_DAY(calendar_date)) - DAYS(DATE(YEAR(calendar_date), MONTH(calendar_date), 1)) + 1) AS avg_daily_active_ids
FROM date_range dr
LEFT JOIN final_active_periods fap 
    ON dr.calendar_date BETWEEN fap.start_date AND fap.end_date
GROUP BY YEAR(calendar_date), MONTH(calendar_date)
ORDER BY stat_year, stat_month;

注意事项 #

• 确保表中日期字段为DATE类型，若为字符串需先转换（如DATE(start_date)）。
• 若统计最近12个月，可将date_range的起止日期改为CURRENT_DATE - 365 DAYS和CURRENT_DATE。

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