Oracle SQL数据库中数据范围的填补和重叠修复查询

hs1rzwqc 于 2023-04-29 发布在 Oracle

关注(0)|答案(1)|浏览(143)

如何查询：在Oracle SQL中填充空白并修复重叠的日期范围

CREATE TABLE PROD_TABLE(Assignment_ID   number, Effective_START_Date date,  Effective_END_Date date, DEPARTMENT VARCHAR2(100));
INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('1/1/2020', 'MM/DD/YYYY'),  TO_DATE('2/1/2020', 'MM/DD/YYYY'), 'DEPT1');
INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('2/2/2020', 'MM/DD/YYYY'),  TO_DATE('2/2/2021', 'MM/DD/YYYY'), 'DEPT1');
INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('2/3/2021', 'MM/DD/YYYY'),  TO_DATE('3/19/2021', 'MM/DD/YYYY'), 'DEPT1');
INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('3/20/2023', 'MM/DD/YYYY'), TO_DATE('12/31/4712', 'MM/DD/YYYY'), 'DEPT1');

CREATE TABLE STAGING_TABLE(Assignment_ID    number, Effective_START_Date date,  Effective_END_Date date, DEPARTMENT VARCHAR2(100));
INSERT INTO STAGING_TABLE VALUES (30001,    TO_DATE('2/2/2020', 'MM/DD/YYYY'),  TO_DATE('5/4/2020', 'MM/DD/YYYY'), 'DEPT1');
INSERT INTO STAGING_TABLE VALUES (30001,    TO_DATE('5/5/2020', 'MM/DD/YYYY'),  TO_DATE('2/2/2021', 'MM/DD/YYYY'), 'DEPT1');

在STAGING_TABLE中，有两条记录与PROD_TABLE中的记录重叠。分期记录：1.开始日期：2/2/2020结束日期：5/4/2020
2.开始日期：2020年5月5日结束日期：2/2/2021
PROD_TABLE：上面的记录与记录1重叠。开始日期：2/2/2020结束日期：2/2/2021
因此，希望将STAGING_TABLE记录合并到PROD_TABLE记录中。合并后，预期记录应为1。开始日期：2/2/2020结束日期：5/4/2020 2.开始日期：2020年5月5日结束日期：2/2/2021
这是我到目前为止创建的查询。有没有简单的方法可以做到这一点？

WITH query1 AS ( SELECT prod_table.Assignment_ID,
       GREATEST(staging_table.effective_start_date, prod_table.effective_start_date) AS effective_START_date,
       LEAST(staging_table.effective_end_date, prod_table.effective_end_date) AS effective_END_date,
       staging_table.DEPARTMENT   FROM PROD_TABLE prod_table,
       STAGING_TABLE staging_table  WHERE prod_table.Assignment_ID = staging_table.Assignment_ID    AND prod_table.effective_start_date <
staging_table.effective_end_date    AND prod_table.effective_end_date
> staging_table.effective_start_date )     SELECT * FROM query1 UNION SELECT prod_table.Assignment_ID,
       prod_table.effective_start_date,
       prod_table.effective_end_date,
       prod_table.DEPARTMENT   FROM PROD_TABLE prod_table  WHERE NOT EXISTS (SELECT 1 
                     FROM query1 qry
                    WHERE prod_table.Assignment_ID = qry.Assignment_ID
                      AND prod_table.effective_start_date BETWEEN qry.effective_start_date AND qry.effective_end_date) ORDER BY  1, 2;

oracle

来源：https://stackoverflow.com/questions/76089593/query-for-filling-gaps-and-fix-overlapping-date-ranges-in-oracle-sql

1条答案

按热度按时间

balp4ylt1#

如果要替换PROD_TABLE中完全包含STAGING_TABLE中的行的行，则可以使用MERGE语句：

MERGE INTO prod_table dst
USING (
  SELECT p.ROWID AS rid,
         s.*,
         ROW_NUMBER() OVER (PARTITION BY p.ROWID ORDER BY s.effective_start_date) AS rn
  FROM   prod_table p
         INNER JOIN staging_table s
         ON     p.assignment_id        =  s.assignment_id
            AND p.effective_start_date <= s.effective_start_date
            AND s.effective_end_date   <= p.effective_end_date
            AND p.department           = s.department
) src
ON (dst.ROWID = src.rid AND src.rn = 1)
WHEN MATCHED THEN
  UPDATE
  SET    effective_start_date = src.effective_start_date,
         effective_end_date   = src.effective_end_date
WHEN NOT MATCHED THEN
  INSERT (assignment_id, effective_start_date, effective_end_date, department)
  VALUES (src.assignment_id, src.effective_start_date, src.effective_end_date, src.department);

其中，对于样本数据，在MERGE之后，然后prod_table包含：
| 分配ID|生效日期|生效结束日期|联系方式|
| --------------|--------------|--------------|--------------|
| 30001|2020-01-01 00：00：00|2020-02-01 00：00：00|DEPT1|
| 30001|2020-02-02 00：00：00|2020-05-04 00：00：00|DEPT1|
| 30001|2019 -02-03 00：00：00|2021-03-19 00：00：00|DEPT1|
| 30001|2019 -03-20 00：00：00|2019 - 01 -12 00：00：00|DEPT1|
| 30001|2020-05-05 00：00：00|2019 -02- 22 00：00：00|DEPT1|

更新：

如果你想要SELECT（而不是MERGE）来自staging_table的所有数据和prod_table的数据没有完全包围staging_table的行，那么使用UNION ALL和NOT EXISTS：

SELECT *
FROM   prod_table p
WHERE  NOT EXISTS (SELECT 1 
                   FROM staging_table s
                   WHERE p.assignment_id        =  s.assignment_id
                   AND   p.effective_start_date <= s.effective_start_date
                   AND   s.effective_end_date   <= p.effective_end_date
                   AND   p.department           = s.department)
UNION ALL
SELECT *
FROM   staging_table;

其输出：
| 分配ID|生效日期|生效结束日期|联系方式|
| --------------|--------------|--------------|--------------|
| 30001|2020-01-01 00：00：00|2020-02-01 00：00：00|DEPT1|
| 30001|2019 -02-03 00：00：00|2021-03-19 00：00：00|DEPT1|
| 30001|2019 -03-20 00：00：00|2019 - 01 -12 00：00：00|DEPT1|
| 30001|2020-02-02 00：00：00|2020-05-04 00：00：00|DEPT1|
| 30001|2020-05-05 00：00：00|2019 -02- 22 00：00：00|DEPT1|
fiddle

赞(0）回复(0）举报 2023-04-29

我来回答

Oracle SQL数据库中数据范围的填补和重叠修复查询

1条答案

更新：

相关问题

热门标签

最新问答