如何统计各月用户活跃天数 计算年度月均活跃占比

核心逻辑说明 #

统计的本质是计算每个自然月内，用户处于Active状态的天数，再除以当月总天数得到单月活跃占比，最终对统计周期内12个月的占比取平均值，即可得到你需要的0.8722的结果。
单月活跃天数的计算规则为两个时间区间的交集长度：

单月活跃天数 = max(0, min(Active状态结束日期, 当月最后1天) - max(Active状态开始日期, 当月第1天) + 1)
注：公式最后+1是因为日期计算需要包含起止当日，比如1号到2号共2天，直接做日期差会得到1，需要补1修正。

用你提供的样例数据逐月累算（统计周期2021年1月-2021年12月），结果和参考值完全匹配：

• 2021-01：完全在Active区间，活跃31天，占比31/31=1
• 2021-02：完全在Active区间，活跃28天，占比28/28=1
• 2021-03：完全在Active区间，活跃31天，占比31/31=1
• 2021-04：Active状态到4月24日，活跃24天，占比24/30=0.8
• 2021-05：整月处于Inactive状态，活跃0天，占比0
• 2021-06：6月11日恢复Active，到月末共20天，占比20/30≈0.6667
• 2021-07至2021-12：全部处于Active区间，每个月占比均为1
  所有月份占比求和为10.4667，除以12后结果≈0.8722，和参考计算结果一致（你提供的参考公式漏写了5月的0项，不影响逻辑）。

具体实现步骤 #

1. 基础数据预处理 #

你原始表中1Jan2021格式的日期是字符串类型，需要先转换为标准日期格式才能做区间计算；另外需要准备覆盖统计周期的自然月维度表，记录每个月的第一天、最后一天、当月总天数，没有现成日历表的话可以用SQL内置函数生成。

2. SQL实现示例（适配Hive/Spark SQL） #

-- 生成统计周期内的自然月维度，此处统计2021年全年12个月
WITH month_dim AS (
    SELECT 
        date_trunc('month', dt) AS month_start,
        last_day(dt) AS month_end,
        day(last_day(dt)) AS month_total_days
    FROM (
        SELECT explode(sequence(to_date('2021-01-01'), to_date('2021-12-01'), interval 1 month)) AS dt
    ) t
),
-- 清洗原始状态表，转换日期格式，仅保留Active状态的区间
active_interval AS (
    SELECT 
        to_date(Start_Dt, 'dMMMyyyy') AS active_start,
        to_date(End_Dt, 'dMMMyyyy') AS active_end
    FROM your_business_table
    WHERE Status = 'Active'
)
-- 关联计算每个月的活跃天数和占比
SELECT 
    date_format(m.month_start, 'yyyy-MM') AS stat_month,
    m.month_total_days,
    -- 计算区间交集天数，无交集时返回0
    coalesce(sum(
        datediff(
            least(m.month_end, a.active_end),
            greatest(m.month_start, a.active_start)
        ) + 1
    ), 0) AS active_days,
    coalesce(sum(
        datediff(
            least(m.month_end, a.active_end),
            greatest(m.month_start, a.active_start)
        ) + 1
    ), 0) / m.month_total_days AS active_ratio
FROM month_dim m
LEFT JOIN active_interval a
-- 区间重叠判断条件：Active区间和当月区间有交集才关联
ON a.active_start <= m.month_end AND a.active_end >= m.month_start
GROUP BY m.month_start, m.month_end, m.month_total_days
ORDER BY m.month_start;

执行上述SQL后会直接返回每个自然月的总天数、活跃天数、活跃占比，对结果中的active_ratio字段取平均值即可得到全年的平均活跃占比。

注意事项 #

• 日期格式转换的格式符需要和你实际存储的格式匹配，dMMMyyyy可以兼容1位或2位日期+3位英文月份+4位年份的格式
• 不要用等值日期关联，必须用区间重叠的判断条件，否则会漏掉跨多个月的状态记录
• 计算结果如果需要更高精度，可以把日期差的计算替换为对应引擎的高精度日期函数，避免整数除法的精度损失

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