Oracle SQL：如何获取最近3个月的滚动SUM？

ergxz8rk 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(98)

我有以下数据集，其中我想检查一个部分是否有过去3个月的数量，并总结它们。例如，对于部件ABC，对于01-MAY-23的月份，vdate列中的最近3个月为01-MAY-23, 01-APR-23, 01-MAR-23。由于这3个月存在，我想将数量添加为50 + 40 + 30 = 120。对于DEF部分则不存在相同的情况，因此该部分不会出现在输出中。
data

| 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  |

Output

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

sql

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
)

SELECT *
FROM data;

oracle

来源：https://stackoverflow.com/questions/77217577/oracle-sql-how-to-get-rolling-sum-of-only-last-3-months

2条答案

按热度按时间

svmlkihl1#

你发布的数据

SQL> WITH data AS (
  2      SELECT 'ABC' part, TO_DATE('01-JAN-23') vdate, TO_DATE('01-MAY-23') month, 10 qty FROM dual UNION
  3      SELECT 'ABC' part, TO_DATE('01-FEB-23') vdate, TO_DATE('01-MAY-23') month, 20 qty FROM dual UNION
  4      SELECT 'ABC' part, TO_DATE('01-MAR-23') vdate, TO_DATE('01-MAY-23') month, 30 qty FROM dual UNION
  5      SELECT 'ABC' part, TO_DATE('01-APR-23') vdate, TO_DATE('01-MAY-23') month, 40 qty FROM dual UNION
  6      SELECT 'ABC' part, TO_DATE('01-MAY-23') vdate, TO_DATE('01-MAY-23') month, 50 qty FROM dual UNION
  7      SELECT 'ABC' part, TO_DATE('01-MAR-23') vdate, TO_DATE('01-JUN-23') month, 25 qty FROM dual UNION
  8      SELECT 'ABC' part, TO_DATE('01-APR-23') vdate, TO_DATE('01-JUN-23') month, 35 qty FROM dual UNION
  9      SELECT 'ABC' part, TO_DATE('01-MAY-23') vdate, TO_DATE('01-JUN-23') month, 45 qty FROM dual UNION
 10      SELECT 'ABC' part, TO_DATE('01-JUN-23') vdate, TO_DATE('01-JUN-23') month, 55 qty FROM dual UNION
 11      SELECT 'DEF' part, TO_DATE('01-APR-23') vdate, TO_DATE('01-MAY-23') month, 25 qty FROM dual UNION
 12      SELECT 'DEF' part, TO_DATE('01-MAY-23') vdate, TO_DATE('01-MAY-23') month, 35 qty FROM dual
 13  )

这可能是一种选择：

14  select part, month, sum(qty) qty
 15  from data
 16  where vdate between add_months(month, -2) and month
 17  group by part, month
 18  having count(*) = 3;

PAR MONTH            QTY
--- --------- ----------
ABC 01-MAY-23        120
ABC 01-JUN-23        135

SQL>

赞(0）回复(0）举报 2023-10-16

klsxnrf12#

在Oracle 12中，您可以使用MATCH_RECOGNIZE进行逐行模式匹配：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY part, month
  ORDER BY vdate
  MEASURES
    SUM(qty) AS qty
  PATTERN (consecutive_months{2} last_month)
  DEFINE 
    consecutive_months AS ADD_MONTHS(vdate, 1) = NEXT(vdate),
    last_month AS vdate = month
)

其中，对于样本数据：

CREATE TABLE table_name (part, vdate, month, qty) AS
  SELECT 'ABC', DATE '2023-01-01', DATE '2023-05-01', 10 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-02-01', DATE '2023-05-01', 20 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-03-01', DATE '2023-05-01', 30 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-04-01', DATE '2023-05-01', 40 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-05-01', DATE '2023-05-01', 50 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-03-01', DATE '2023-06-01', 25 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-04-01', DATE '2023-06-01', 35 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-05-01', DATE '2023-06-01', 45 FROM DUAL UNION
  SELECT 'ABC', DATE '2023-06-01', DATE '2023-06-01', 55 FROM DUAL UNION
  SELECT 'DEF', DATE '2023-04-01', DATE '2023-05-01', 25 FROM DUAL UNION
  SELECT 'DEF', DATE '2023-05-01', DATE '2023-05-01', 35 FROM DUAL;

输出：
| 部分|月|数量|
| --|--|--|
| ABC| 2023-05-01 00：00：00| 120 |
| ABC| 2023-06-01 00：00：00| 135 |
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

Oracle SQL：如何获取最近3个月的滚动SUM？

2条答案

相关问题

热门标签

最新问答