SQLite查询补全缺失数据：为费用类型月度统计补0

要解决Chart.js因缺失月份导致的图表异常问题，我们需要通过CTE生成所有费用类型+年月的全量组合，再与原统计结果左连接，对无消费记录的行自动补0。以下是完整实现方案：

完整SQL语句 #

WITH 
-- 生成1-12月的两位格式月份字符串（匹配strftime("%m")的输出格式）
months AS (
    SELECT '01' AS month
    UNION ALL
    SELECT printf('%02d', CAST(month AS INTEGER) + 1) AS month
    FROM months
    WHERE CAST(month AS INTEGER) < 12
),
-- 从Transactions表中提取所有不重复的年份
years AS (
    SELECT DISTINCT strftime('%Y', valuationdate) AS year
    FROM Transactions
),
-- 生成所有可能的年月组合
year_months AS (
    SELECT y.year, m.month
    FROM years y
    CROSS JOIN months m
),
-- 提取所有不重复的费用类型
expense_types AS (
    SELECT DISTINCT expense
    FROM Transactions
),
-- 生成「费用类型+年月」的全量统计维度
all_combinations AS (
    SELECT et.expense, ym.year, ym.month
    FROM expense_types et
    CROSS JOIN year_months ym
),
-- 原有的分组统计逻辑（仅保留有消费记录的行）
expense_stats AS (
    SELECT 
        expense,
        strftime('%Y', valuationdate) AS year,
        strftime('%m', valuationdate) AS month,
        SUM(value) AS balance
    FROM Transactions
    GROUP BY expense, year, month
)
-- 左连接全量维度与统计结果，无记录的行补0
SELECT 
    ac.month,
    ac.year,
    ac.expense,
    COALESCE(es.balance, 0) AS balance
FROM all_combinations ac
LEFT JOIN expense_stats es 
    ON ac.expense = es.expense 
    AND ac.year = es.year 
    AND ac.month = es.month
ORDER BY ac.expense, ac.year, ac.month;

关键逻辑说明 #

1. 生成全量年月组合：通过递归CTE生成两位格式的月份，再与表中所有年份交叉连接，确保覆盖所有需要统计的年月。
2. 生成全量统计维度：将每个费用类型与所有年月组合配对，得到无遗漏的统计维度。
3. 补0处理：使用COALESCE(es.balance, 0)将左连接后无匹配记录的balance字段替换为0。
4. 格式一致性：所有年月格式与原查询保持一致，确保Chart.js能正常识别并渲染。

可选调整：固定年份范围 #

如果不需要统计表中所有年份，可修改years CTE指定固定范围，例如仅统计最近2年：

years AS (
    SELECT strftime('%Y', 'now') AS year
    UNION ALL
    SELECT strftime('%Y', 'now', '-1 year') AS year
)

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