Oracle按月统计未完成记录累计值的SQL查询求助

样本数据 #

查询需求 #

按月返回累计未完成记录数，逻辑如下：

• 6月创建3条，当月完成0条，累计剩余3条
• 7月创建4条，完成1条6月记录+1条7月记录，累计剩余3+4-2=5条
• 8月创建3条，完成2条7月记录+1条8月记录，累计剩余5+3-3=5条

期望输出：

错误的SQL及结果 #

原尝试的SQL：

WITH record_creations AS (
    SELECT
        TO_CHAR(startdate, 'YYYY-MM') AS month,
        COUNT(id) AS created_records
    FROM
        table1
    WHERE id in (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
    GROUP BY
        TO_CHAR(startdate, 'YYYY-MM')
),
records_incomplete AS (
    SELECT
        TO_CHAR(compdate, 'YYYY-MM') AS month,
        COUNT(id) AS remaining_records
    FROM
        table1
    WHERE
        compdate IS  NULL
        and id in (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
    GROUP BY
        TO_CHAR(compdate, 'YYYY-MM')
)
SELECT
    NVL(c.month, i.month) AS ColumnName,
    NVL(created_records, 0) - NVL(remaining_records, 0) AS records
FROM
    record_creations c
    FULL OUTER JOIN records_incomplete i ON c.month = i.month
ORDER BY
    NVL(c.month, i.month)

返回的错误结果：

问题分析 #

原SQL的核心错误：

1. records_incomplete逻辑完全错误：未完成记录的compdate是空值，无法按compdate分组到对应月份，等于没统计到任何完成量
2. 没有计算累计值，只是简单做了当月创建数的减法，没有延续之前月份的剩余记录量

修正后的SQL #

WITH all_months AS (
    -- 提取所有有记录创建的月份，确保结果覆盖所有需要统计的月份
    SELECT DISTINCT TO_CHAR(startdate, 'YYYY-MM') AS month
    FROM table1
    WHERE id IN (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
),
monthly_changes AS (
    -- 统计每个月的新增记录数，以及当月完成的所有记录数（不管记录是哪个月创建的）
    SELECT
        am.month,
        COALESCE(rc.created, 0) AS added,
        COALESCE(fc.finished, 0) AS finished
    FROM all_months am
    LEFT JOIN (
        SELECT TO_CHAR(startdate, 'YYYY-MM') AS month, COUNT(id) AS created
        FROM table1
        WHERE id IN (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
        GROUP BY TO_CHAR(startdate, 'YYYY-MM')
    ) rc ON am.month = rc.month
    LEFT JOIN (
        SELECT TO_CHAR(compdate, 'YYYY-MM') AS month, COUNT(id) AS finished
        FROM table1
        WHERE id IN (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
          AND compdate IS NOT NULL
        GROUP BY TO_CHAR(compdate, 'YYYY-MM')
    ) fc ON am.month = fc.month
)
-- 用累计窗口函数计算每月的累计未完成记录数
SELECT
    month AS ColumnName,
    SUM(added - finished) OVER (ORDER BY month) AS Records
FROM monthly_changes
ORDER BY month;

逻辑说明 #

1. all_months：先获取所有存在记录创建的月份，保证结果不会遗漏需要统计的月份
2. monthly_changes：分别计算每个月的新增记录数，以及当月完成的所有记录数（不管这条记录是哪个月创建的）
3. 最后通过SUM() OVER (ORDER BY month)计算累计值：从第一个月开始，每月的剩余未完成数 = 之前累计剩余 + 当月新增 - 当月完成

运行该SQL后即可得到符合需求的结果。

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