oracle SQL根据重叠的日期合并两个表并获取计数

6g8kf2rb 于 2023-04-29 发布在 Oracle

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

请在这件事上帮助我。下面有两个表（PROD_TABLE，STAGING_TABLE），具有3个主键（Assignment_ID，Effective_Start_Date & Effective_END_Date）。

*PROD_TABLE

Assignment ID   Effective_Start_Date    Effective_END_Date
record 1:   60001           1/1/2001                2/1/2020
record 2:   60001           2/2/2020                2/2/2021
record 3:   60001           2/3/2021                3/19/2021
record 4:   60001           3/20/2023               12/31/4712

STAGING_TABLE

Assignment ID   Effective_Start_Date    Effective_END_Date
record 1:   60001           1/1/2001                2/2/2021
record 2:   60001           2/3/2021                3/19/2021
record 3:   60001           3/20/2023               12/31/4712

我想进行一个查询，根据重叠的日期（Effective_Start_Date、Effective_END_Date）合并两个表，并从PROD_TABLE获取记录计数。
我想得到如下输出（预期输出：）：1/1/2001 - 2/2/2021的COUNT为2，因为在PROD_TABLE中，1/1/2001 - 2/1/2020和2/2/2020 - 2/2/2021福尔斯STAGING_TABLE的1/1/2001 - 2/2/2021范围。

预期输出：

Assignment ID   Effective_Start_Date    Effective_END_Date  **COUNT**
60001           1/1/2001                2/2/2021            **2**
60001           2/3/2021                3/19/2021           **1**
60001           3/20/2023               12/31/4712          **1**

CREATE TABLE PROD_TABLE(ASSIGNMENT_ID   NUMBER, EFFECTIVE_START_DATE DATE,  EFFECTIVE_END_DATE DATE);
INSERT INTO prod_table VALUES (60001,   TO_DATE('1/1/2001', 'MM/DD/YYYY'),  TO_DATE('2/1/2020', 'MM/DD/YYYY'));
INSERT INTO prod_table VALUES (60001,   TO_DATE('2/2/2020', 'MM/DD/YYYY'),  TO_DATE('2/2/2021', 'MM/DD/YYYY'));
INSERT INTO prod_table VALUES (60001,   TO_DATE('2/3/2021', 'MM/DD/YYYY'),  TO_DATE('3/19/2021', 'MM/DD/YYYY'));
INSERT INTO prod_table VALUES (60001,   TO_DATE('3/20/2023', 'MM/DD/YYYY'), TO_DATE('12/31/4712', 'MM/DD/YYYY'));

CREATE TABLE STAGING_TABLE(ASSIGNMENT_ID NUMBER, EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE);
INSERT INTO STAGING_TABLE VALUES (60001,    TO_DATE('1/1/2020', 'MM/DD/YYYY'),  TO_DATE('2/2/2021', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (60001,    TO_DATE('2/3/2021', 'MM/DD/YYYY'),  TO_DATE('3/19/2021', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (60001,    TO_DATE('3/20/2023', 'MM/DD/YYYY'), TO_DATE('12/31/4712', 'MM/DD/YYYY'));

oracle

来源：https://stackoverflow.com/questions/76119045/sql-to-merge-the-two-tables-according-to-the-date-where-it-overlaps-and-get-the

2条答案

按热度按时间

unhi4e5o1#

期望输出由下式给出：

select 
  ASSIGNMENT_ID,    
  EFFECTIVE_START_DATE, 
  EFFECTIVE_END_DATE,
  (select count(*) 
   FROM PROD_TABLE p
   WHERE s.EFFECTIVE_START_DATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
      or s.EFFECTIVE_END_DATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
     ) as Count
from STAGING_TABLE s

参见：DBFIDDLE
但我不认为这在所有情况下都是正确的解决方案。在这种情况下，它恰好给予了正确的答案。😉
编辑：

-- This query converted to a query without subquery
select 
  s.ASSIGNMENT_ID,  
  s.EFFECTIVE_START_DATE,   
  s.EFFECTIVE_END_DATE,
  count(*) as Count
from STAGING_TABLE s
left join PROD_TABLE p ON s.ASSIGNMENT_ID = p.ASSIGNMENT_ID and (
    s.EFFECTIVE_START_DATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
    or s.EFFECTIVE_END_DATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE)
GROUP BY 
  s.ASSIGNMENT_ID,  
  s.EFFECTIVE_START_DATE,   
  s.EFFECTIVE_END_DATE

参见：DBFIDDLE

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

wlzqhblo2#

从临时表中进行选择，并使用相关子查询计算与生产表重叠的行数：

SELECT s.*,
       ( SELECT COUNT(*)
         FROM   prod_table p
         WHERE  s.assignment_id = p.assignment_id
         AND    s.effective_start_date <= p.effective_end_date
         AND    p.effective_start_date <= s.effective_end_date ) AS cnt
FROM   staging_table s

或者，在Oracle 12中，使用LATERAL连接：

SELECT s.*,
       p.cnt
FROM   staging_table s
       CROSS JOIN LATERAL (
         SELECT COUNT(*) AS cnt
         FROM   prod_table p
         WHERE  s.assignment_id = p.assignment_id
         AND    s.effective_start_date <= p.effective_end_date
         AND    p.effective_start_date <= s.effective_end_date
       ) p

对于样本数据，两者都输出：
| 分配ID|生效日期|生效结束日期|CNT|
| --------------|--------------|--------------|--------------|
| 60001|2020-01-01 00：00：00|2019 -02- 22 00：00：00|二|
| 六零零一|2019 -02-03 00：00：00|2021-03-19 00：00：00|1|
| 60001|2019 -03-20 00：00：00|2019 - 01 -12 00：00：00|1|
fiddle

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

我来回答

oracle SQL根据重叠的日期合并两个表并获取计数

2条答案

相关问题

热门标签

最新问答