SQLite数据分析：订阅型企业月度订阅营收统计SQL实现咨询

需求背景 #

我正在为一家订阅型企业处理月度销售统计需求，需按月份汇总所有活跃订阅的营收。现有合同数据表Combined，包含以下字段：

• ContractStartDate：订阅开始日期
• ContractEndDate：订阅结束日期（NULL表示订阅仍在活跃状态）
• PropositionReference：订阅方案类型（仅分为Standard和Discount两种）
• PropositionPrice：订阅价格

目标是从最早的ContractStartDate开始，统计每个月两种方案的对应营收，输出格式为包含Month、RevenueStandard、RevenueDiscount的表格。

尝试的错误SQL代码 #

SELECT 
MonthYear, PropositionReference, 
SUM(CASE WHEN STRFTIME("%m %Y", ContractStartDate) <= MonthYear 
AND (ContractEndDate IS NULL OR STRFTIME("%m %Y", ContractEndDate) >= MonthYear) 
AND PropositionReference = "Standard" 
THEN PropositionPrice ELSE 0 END) AS RevenueStandard,
SUM(CASE WHEN STRFTIME("%m %Y", ContractStartDate) <= MonthYear 
AND (ContractEndDate IS NULL OR STRFTIME("%m %Y", ContractEndDate) >= MonthYear) 
AND PropositionReference = "Discount" 
THEN PropositionPrice ELSE 0 END) AS RevenueDiscount
FROM (SELECT *, STRFTIME("%m %Y", ContractStartDate) AS MonthYear FROM Combined)
GROUP BY MonthYear, PropositionReference
ORDER BY MonthYear, PropositionReference

问题分析 #

你的SQL有三个核心问题：

1. 月份覆盖不全：只基于ContractStartDate生成月份，会漏掉那些没有新订阅但有活跃订阅的月份
2. 分组逻辑不符合要求：按MonthYear和PropositionReference分组后，每个月份会输出两行数据（分别对应两种方案），无法达到一行展示两种营收的需求
3. 日期比较逻辑有缺陷：用STRFTIME("%m %Y")生成的字符串做比较，会出现排序错误（比如"02 2024"会被认为大于"12 2023"）

正确SQL实现 #

完整代码 #

WITH RECURSIVE Months AS (
    -- 获取最早订阅开始日期的当月第一天
    SELECT DATE(MIN(ContractStartDate), 'start of month') AS MonthDate
    FROM Combined
    UNION ALL
    -- 递归生成后续月份，直到覆盖到最晚订阅结束日期或当前日期
    SELECT DATE(MonthDate, '+1 month')
    FROM Months
    WHERE MonthDate < DATE(COALESCE(MAX(ContractEndDate), DATE('now')), 'start of month')
),
FormattedMonths AS (
    -- 将月份格式化为YYYY-MM，方便排序和展示
    SELECT STRFTIME('%Y-%m', MonthDate) AS Month
    FROM Months
)
SELECT
    fm.Month,
    -- 汇总Standard方案的月度营收
    SUM(CASE WHEN c.PropositionReference = 'Standard' THEN c.PropositionPrice ELSE 0 END) AS RevenueStandard,
    -- 汇总Discount方案的月度营收
    SUM(CASE WHEN c.PropositionReference = 'Discount' THEN c.PropositionPrice ELSE 0 END) AS RevenueDiscount
FROM FormattedMonths fm
LEFT JOIN Combined c
    -- 判断订阅在当前月份是否活跃：开始日期不晚于当月最后一天，结束日期不早于当月第一天（或未结束）
    ON DATE(c.ContractStartDate) <= DATE(fm.Month, '+1 month', '-1 day')
    AND (c.ContractEndDate IS NULL OR DATE(c.ContractEndDate) >= DATE(fm.Month, 'start of month'))
GROUP BY fm.Month
ORDER BY fm.Month;

核心逻辑说明 #

1. 生成连续月份序列：用递归CTE生成从最早订阅开始月到最晚订阅结束月（或当前月）的所有月份，确保没有遗漏任何需要统计的月份
2. 精确判断活跃订阅：使用日期函数做精确的时间范围判断，避免字符串比较带来的错误
3. 按需聚合数据：按月份单独分组，通过CASE语句分别统计两种方案的营收，直接输出符合要求的一行式结果

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