Oracle中如何计算会员每日活动参与率？

核心需求逻辑：每日活动参与率 = 当日活动参与会员数 ÷ 当日有效会员数，其中有效会员需满足：

• 入会日期JOIN_DT ≤ 统计日期
• 退会日期SSMB_DT为NULL 或 SSMB_DT > 统计日期
• 会员状态ST_CD = 'Normal'

实现SQL语句 #

WITH date_range AS (
    -- 生成统计日期集合，这里取TB_EVT中所有活动日期，也可替换为固定连续日期区间
    SELECT DISTINCT EVT_DT AS stat_date
    FROM TB_EVT
),
daily_valid_members AS (
    -- 统计每日有效会员总数
    SELECT
        dr.stat_date,
        COUNT(m.MBR_NO) AS valid_member_count
    FROM date_range dr
    LEFT JOIN TB_MBR m
        ON m.JOIN_DT <= dr.stat_date
        AND (m.SSMB_DT IS NULL OR m.SSMB_DT > dr.stat_date)
        AND m.ST_CD = 'Normal'
    GROUP BY dr.stat_date
),
daily_participants AS (
    -- 统计每日参与活动的会员数（去重，避免同一会员当日多次参与重复计数）
    SELECT
        EVT_DT AS stat_date,
        COUNT(DISTINCT MBR_NO) AS participant_count
    FROM TB_EVT
    GROUP BY EVT_DT
)
SELECT
    dr.stat_date,
    COALESCE(dp.participant_count, 0) AS participant_count,
    dv.valid_member_count,
    -- 计算参与率，保留两位小数，处理除数为0的异常
    CASE
        WHEN dv.valid_member_count = 0 THEN 0
        ELSE ROUND(COALESCE(dp.participant_count, 0) / dv.valid_member_count * 100, 2)
    END AS participation_rate
FROM date_range dr
LEFT JOIN daily_valid_members dv ON dr.stat_date = dv.stat_date
LEFT JOIN daily_participants dp ON dr.stat_date = dp.stat_date
ORDER BY dr.stat_date;

关键细节说明 #

• date_range：如果需要统计无活动的日期，可替换为生成连续日期的逻辑，比如近30天：SELECT TRUNC(SYSDATE) - LEVEL + 1 AS stat_date FROM dual CONNECT BY LEVEL <= 30。
• daily_valid_members：通过左连接确保每个统计日期都能匹配到对应的有效会员数，避免遗漏无活动日期的统计。
• daily_participants：用COUNT(DISTINCT MBR_NO)确保同一会员当日多次参与活动只算一次。
• 参与率计算：用COALESCE处理无参与人数的情况，用CASE避免除数为0时的报错，最终输出百分比格式的参与率。

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