在Oracle SQL中按月生成日期

发布于09月30日

这是数据

CREATE TABLE table_name (a, b, c, ym) AS
SELECT 1, 2,  1, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 1, 2,  7, DATE '2024-09-01' FROM DUAL UNION ALL
SELECT 2, 2,  8, DATE '2024-04-01' FROM DUAL;

我需要生成所有丢失的日期在每月的水平与列c为零值. 在至少有一个月具有值的年份中，为A和B列的每个唯一组合生成缺少的月份.例如，组合2和2的数据在2024年，所以不需要在2023年生成月份，而只需要在2024年生成月份！

输出将如下所示

a     b      c   ym
1     2      1   2023-01-01
1     2      0   2023-02-01
.
.
.
.
1     2      0   2023-12-01
1     2      0   2024-01-01
.
.
.
1     2      7   2024-09-01
.
.
.
1     2      0   2024-12-01
2     2      0   2024-01-01
2     2      0   2024-02-01
2     2      0   2024-03-01
2     2      8   2024-04-01
.
.
.
2     2      0   2024-12-01

推荐答案

将日期拆分为年和月组件，然后使用PARTITIONed OUTER JOIN:

SELECT t.a,
       t.b,
       COALESCE(t.c, 0) AS c,
       ADD_MONTHS(t.year, c.month - 1) AS ym
FROM   (
         SELECT LEVEL AS month
         FROM   DUAL
         CONNECT BY LEVEL <= 12
       ) c
       LEFT OUTER JOIN (
         SELECT a, b, c, TRUNC(ym, 'YY') AS year, EXTRACT(MONTH FROM ym) AS month
         FROM   table_name
       )t
       PARTITION BY (t.a, t.b, t.year)
       ON (t.month = c.month)

其中，对于样本数据:

CREATE TABLE table_name (a, b, c, ym) AS
SELECT 1, 2,  1, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 1, 2,  7, DATE '2024-09-01' FROM DUAL UNION ALL
SELECT 2, 2,  8, DATE '2024-04-01' FROM DUAL;

输出:

A	B	C	YM
1	2	1	2023-01-01 00:00:00
1	2	0	2023-02-01 00:00:00
1	2	0	2023-03-01 00:00:00
1	2	0	2023-04-01 00:00:00
1	2	0	2023-05-01 00:00:00
1	2	0	2023-06-01 00:00:00
1	2	0	2023-07-01 00:00:00
1	2	0	2023-08-01 00:00:00
1	2	0	2023-09-01 00:00:00
1	2	0	2023-10-01 00:00:00
1	2	0	2023-11-01 00:00:00
1	2	0	2023-12-01 00:00:00
1	2	0	2024-01-01 00:00:00
1	2	0	2024-02-01 00:00:00
1	2	0	2024-03-01 00:00:00
1	2	0	2024-04-01 00:00:00
1	2	0	2024-05-01 00:00:00
1	2	0	2024-06-01 00:00:00
1	2	0	2024-07-01 00:00:00
1	2	0	2024-08-01 00:00:00
1	2	7	2024-09-01 00:00:00
1	2	0	2024-10-01 00:00:00
1	2	0	2024-11-01 00:00:00
1	2	0	2024-12-01 00:00:00
2	2	0	2024-01-01 00:00:00
2	2	0	2024-02-01 00:00:00
2	2	0	2024-03-01 00:00:00
2	2	8	2024-04-01 00:00:00
2	2	0	2024-05-01 00:00:00
2	2	0	2024-06-01 00:00:00
2	2	0	2024-07-01 00:00:00
2	2	0	2024-08-01 00:00:00
2	2	0	2024-09-01 00:00:00
2	2	0	2024-10-01 00:00:00
2	2	0	2024-11-01 00:00:00
2	2	0	2024-12-01 00:00:00