Oracle SQL：如何计算仅最近3个月的滚动求和（需有3月数据）

现有如下数据集，需检查零件是否存在对应MONTH的最近3个月的QTY记录，并对这3个月的数量求和：

• 零件ABC在MONTH为01-MAY-23时，VDATE列中最近3个月为01-MAY-23、01-APR-23、01-MAR-23，这3个月均有数据，因此求和为50+40+30=120；
• 零件DEF不满足该条件（缺少01-MAR-23的记录），故不会出现在输出结果中。

原始数据集 #

| PART | VDATE     | MONTH     | QTY |
|------|-----------|-----------|-----|
| ABC  | 01-JAN-23 | 01-MAY-23 | 10  |
| ABC  | 01-FEB-23 | 01-MAY-23 | 20  |
| ABC  | 01-MAR-23 | 01-MAY-23 | 30  |
| ABC  | 01-APR-23 | 01-MAY-23 | 40  |
| ABC  | 01-MAY-23 | 01-MAY-23 | 50  |
| ABC  | 01-MAR-23 | 01-JUN-23 | 25  |
| ABC  | 01-APR-23 | 01-JUN-23 | 35  |
| ABC  | 01-MAY-23 | 01-JUN-23 | 45  |
| ABC  | 01-JUN-23 | 01-JUN-23 | 55  |
| DEF  | 01-APR-23 | 01-MAY-23 | 25  |
| DEF  | 01-MAY-23 | 01-MAY-23 | 35  |

预期输出 #

| PART | MONTH     | QTY |
|------|-----------|-----|
| ABC  | 01-MAY-23 | 120 |
| ABC  | 01-JUN-23 | 135 |

实现SQL（Oracle） #

WITH data AS (
    SELECT 'ABC' part, TO_DATE('01-JAN-23') vdate, TO_DATE('01-MAY-23') month, 10 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-FEB-23') vdate, TO_DATE('01-MAY-23') month, 20 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-MAR-23') vdate, TO_DATE('01-MAY-23') month, 30 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-APR-23') vdate, TO_DATE('01-MAY-23') month, 40 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-MAY-23') vdate, TO_DATE('01-MAY-23') month, 50 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-MAR-23') vdate, TO_DATE('01-JUN-23') month, 25 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-APR-23') vdate, TO_DATE('01-JUN-23') month, 35 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-MAY-23') vdate, TO_DATE('01-JUN-23') month, 45 qty FROM dual UNION
    SELECT 'ABC' part, TO_DATE('01-JUN-23') vdate, TO_DATE('01-JUN-23') month, 55 qty FROM dual UNION
    SELECT 'DEF' part, TO_DATE('01-APR-23') vdate, TO_DATE('01-MAY-23') month, 25 qty FROM dual UNION
    SELECT 'DEF' part, TO_DATE('01-MAY-23') vdate, TO_DATE('01-MAY-23') month, 35 qty FROM dual
),
filtered_data AS (
    SELECT 
        part,
        month,
        SUM(qty) AS total_qty,
        COUNT(DISTINCT TRUNC(vdate, 'MONTH')) AS month_count
    FROM data
    WHERE vdate BETWEEN ADD_MONTHS(month, -2) AND month
    GROUP BY part, month
)
SELECT part, month, total_qty AS qty
FROM filtered_data
WHERE month_count = 3
ORDER BY part, month;

逻辑说明 #

1. 通过ADD_MONTHS(month, -2)计算最近3个月的起始日期，筛选出该区间内的VDATE记录；
2. 按PART和MONTH分组，求和QTY并统计分组内的有效月份数量；
3. 仅保留月份数量为3的分组，确保最近3个月均有完整记录。

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