PostgreSQL队列分析SQL查询：月度有效用户快照统计需求

问题分析 #

你需要统计每个雇主（organization_id）和服务类型（service_type）组合，从合作起始月到当前月的每月有效用户快照，但现有SQL仅能统计用户加入当月的数量，核心缺失是没有生成完整的月份序列，也未按月判断用户有效性。

用户有效性规则：end_date为空或晚于start_date时用户有效，同时需要处理end_date为空、早于start_date等异常数据。

解决方案SQL #

WITH org_service_periods AS (
    -- 获取每个雇主-服务类型的合作起始月和当前月作为时间范围
    SELECT 
        o.id AS organization_id,
        o.name AS employer,
        e.service_type,
        date_trunc('month', o.service_start) AS start_month,
        date_trunc('month', CURRENT_DATE) AS end_month
    FROM organizations o
    JOIN eligibility e ON o.id = e.organization_id
    GROUP BY o.id, o.name, e.service_type, o.service_start
),
month_series AS (
    -- 生成每个雇主-服务类型组合的所有月份序列
    SELECT 
        osp.organization_id,
        osp.employer,
        osp.service_type,
        generate_series(osp.start_month, osp.end_month, INTERVAL '1 month') AS snapshot_month
    FROM org_service_periods osp
),
valid_eligibilities AS (
    -- 修正eligibility表中的异常日期，统一有效性判断逻辑
    SELECT 
        e.id,
        e.organization_id,
        e.service_type,
        e.start_date::DATE,
        -- 处理异常end_date：空值或早于start_date时，视为有效到当前日期
        CASE 
            WHEN e.end_date IS NULL OR e.end_date::DATE <= e.start_date::DATE THEN CURRENT_DATE
            ELSE e.end_date::DATE
        END AS end_date
    FROM eligibility e
    -- 过滤掉未来才生效的用户（如果不需要可删除此条件）
    WHERE e.start_date::DATE <= CURRENT_DATE
)
-- 统计每个月份的有效用户快照（月末状态）
SELECT 
    ms.employer,
    ms.service_type,
    ms.snapshot_month::DATE AS snapshot_month,
    COUNT(ve.id) AS active_users
FROM month_series ms
LEFT JOIN valid_eligibilities ve 
    ON ms.organization_id = ve.organization_id
    AND ms.service_type = ve.service_type
    -- 判断用户在当月最后一天是否有效：入职早于等于月末，且未失效（或失效晚于月末）
    AND ve.start_date <= (ms.snapshot_month + INTERVAL '1 month - 1 day')::DATE
    AND ve.end_date >= (ms.snapshot_month + INTERVAL '1 month - 1 day')::DATE
GROUP BY ms.employer, ms.service_type, ms.snapshot_month
ORDER BY ms.employer, ms.service_type, ms.snapshot_month;

代码说明 #

1. org_service_periods：

   • 关联organizations和eligibility表，提取每个雇主-服务类型组合的合作起始月（service_start截断到当月）和当前月作为时间范围。
   • 用GROUP BY去重，避免重复生成同一组合的月份序列。

2. month_series：

   • 使用PostgreSQL的generate_series函数，为每个雇主-服务类型组合生成从起始月到当前月的所有月份，确保每个月都有一条记录，解决原SQL缺少月份序列的问题。

3. valid_eligibilities：

   • 修正end_date的异常数据：如果end_date为空或早于start_date，将其替换为当前日期，保证有效性判断的准确性。
   • 可选过滤未来生效的用户，避免统计还未入职的用户。

4. 最终统计：

   • 通过LEFT JOIN关联月份序列和修正后的用户数据，确保即使当月没有有效用户也会显示0。
   • 判断逻辑为当月最后一天用户是否有效：用户入职日期≤当月最后一天，且失效日期≥当月最后一天（或未失效），符合快照统计的常见需求。

原SQL问题说明 #

你的现有SQL仅按用户的start_date分组统计，没有生成完整的月份序列，因此只能得到用户加入当月的数量，无法覆盖后续每个月的快照需求；同时未处理end_date的异常数据，可能导致统计结果不准确。

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