在特定的最后12个月内缺少特定周期时，如何动态添加与最后12个月数据值的行动行？

Question

这是输入表：

项目	QTY	DATEPERIOD
A	2	1/1/2020 0:00
A	3	2/1/2020 0:00
A	4	3/1/2020 0:00
A	1	4/1/2020 0:00
A	2	5/1/2020 0:00
A	2	6/1/2020 0:00
A	2	8/1/2020
0:00 A	2	10/1/2020
0:00 A	2	12/1/2020 0:00
A	2	1/1/2021 0:00
A	3	2/1/2021 0:00
A	4	3/1/2021 0:00
A	2	5/1/2021
0:00 A	2	6/1/2021 0:00
A	2	8/1/2021 0:00
A	1	9/1/2021 0:00
A	2	10/1/2021 0:00
A	1	11/1/2021
A	1	12/1/2021 0:00

0:00 输入表没有2021年7月的数据，当我必须计算每个行的最后12个月的数据时，我将能够从2021年12月至2021年8月获得最后12个月的数据。

但是，由于输入表确实确实没有2021年7月的数据，使用通常的查询

SUM(qty) OVER (
           PARTITION BY item
           ORDER BY dateperiod
           RANGE BETWEEN INTERVAL '11' MONTH PRECEDING
                 AND     INTERVAL '0' MONTH FOLLOWING
         ) AS total,

将生成2021年6月的最后12个月数据。但是，即使数据在7月至2021年不可用，预期的输出也可以在过去12个月内动态生成一行2021年7月至2021年7月至2020年8月的数据。QTY的结果是：19

类似地，输入表丢失了2021年4月的数据。然后，查询与上一个12个月的数据一起生成了一行， 结果应

为

至	月	。
该数量的	年	2020
2021	年	4
5	月 2020	9
A	4/1/2020	10
A	5/1/2020	12
A	6/1/2020	14
A	7/1/2020	14
A	8/1/2020	16
A	9/1/2020	16
A	10/1/2020	18
A	11/1/2020	18
A	12/1/2020	20
A	1/1/2021	20
A	2/1/2021	20
A	3/1/2021	20
A	4/1/2021	19
A	5/1/2021	19
A	6/1/2021	19
A	7/1/2021	19
A	8/1/2021	19
A	9/1/2021	20
A	10/1/2021	20
A	11/1/2021	21
A	12/1/2021/2021	20

请让我知道这是否可能

Answer 1

您可以使用层次查询来生成日历，并使用ofter Join将其连接到数据（但是，由于您是为每个item进行操作，然后您可能可能想要一个分区 ed 外部加入）：

WITH calendar (month) AS (
  SELECT ADD_MONTHS(min_dp, LEVEL - 1) AS month
  FROM   (
    SELECT MIN(dateperiod) AS min_dp,
           MAX(dateperiod) AS max_dp
    FROM   table_name
  )
  CONNECT BY ADD_MONTHS(min_dp, LEVEL - 1) <= max_dp
)
SELECT item,
       c.month AS dateperiod,
       COALESCE(t.qty, 0) AS qty,
       SUM(t.qty) OVER (
           PARTITION BY t.item
           ORDER BY c.month
           RANGE BETWEEN INTERVAL '11' MONTH PRECEDING
                 AND     INTERVAL '0' MONTH FOLLOWING
         ) AS total
FROM   calendar c
       LEFT OUTER JOIN table_name t
       PARTITION BY (t.item)
       ON (c.month = t.dateperiod);

对于您的示例数据：

CREATE TABLE table_name (ITEM, QTY, DATEPERIOD) AS
SELECT 'A', 2, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 'A', 3, DATE '2020-02-01' FROM DUAL UNION ALL
SELECT 'A', 4, DATE '2020-03-01' FROM DUAL UNION ALL
SELECT 'A', 1, DATE '2020-04-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2020-05-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2020-06-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2020-08-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2020-10-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2020-12-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 'A', 3, DATE '2021-02-01' FROM DUAL UNION ALL
SELECT 'A', 4, DATE '2021-03-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2021-05-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2021-06-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2021-08-01' FROM DUAL UNION ALL
SELECT 'A', 1, DATE '2021-09-01' FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2021-10-01' FROM DUAL UNION ALL
SELECT 'A', 1, DATE '2021-11-01' FROM DUAL UNION ALL
SELECT 'A', 1, DATE '2021-12-01' FROM DUAL;

输出：

item	dateperiod	qty	total
a	2020-01-01 00:00:00	2	2
a	2020-02-01 00:00:00	3	5
a	2020-03-01 00:00:00	4	9
a	2020-04-01 00:00:00	1	10
a	2020-05-01 00:00:00	2	12
a	2020-06-01 00:00:00	2	14
a	2020-07-01 00:00:00	0	14
a	2020-08-01 00:00:00	2	16
a	2020-09-01 00:00:00	0	16
a	2020-10-01 00:00:00	2	18
a	2020-11-01 00:00:00	0	18
a	2020-12-01 00:00:00	2	20
a	2021-01-01 00:00:00	2	20
a	2021-02-01 00:00:00	3	20
a	2021-03-01 00:00:00	4	20
a	2021-04-01 00:00:00	0	19
a	2021-05-01 00:00:00	2	19
a	2021-06-01 00:00:00	2	19
a	2021-07-01 00:00:00	0	19
a	2021-08-01 00:00:00	2	19
a	2021-09-01 00:00:00	1	20
a	2021-10-01 00:00:00	2	20
a	2021-11-01 00:00:00	1	21
a	2021-12-01 00:00:00	1	20

db＆lt; “ nofollow noreferrer”>在这里