请求编写SQL：按重叠日期合并两表并统计PROD_TABLE记录数

现有PROD_TABLE与STAGING_TABLE两张表，主键为Assignment_ID、Effective_Start_Date、Effective_END_Date。需编写SQL，按日期范围重叠规则关联两表，统计每个STAGING_TABLE日期区间对应的PROD_TABLE记录数量。

PROD_TABLE 数据 #

分配ID   生效开始日期    生效结束日期
record 1:   60001           2001/1/1                2020/2/1
record 2:   60001           2020/2/2                2021/2/2
record 3:   60001           2021/2/3                2021/3/19
record 4:   60001           2023/3/20               4712/12/31

STAGING_TABLE 数据 #

分配ID   生效开始日期    生效结束日期
record 1:   60001           2001/1/1                2021/2/2
record 2:   60001           2021/2/3                2021/3/19
record 3:   60001           2023/3/20               4712/12/31

预期输出 #

分配ID   生效开始日期    生效结束日期  **数量**
60001           2001/1/1                2021/2/2            **2**
60001           2021/2/3                2021/3/19           **1**
60001           2023/3/20               4712/12/31          **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/2001', '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'));

解决方案SQL #

核心逻辑是通过日期区间重叠条件关联两表，再按STAGING_TABLE的分组字段统计数量：

SELECT 
    st.ASSIGNMENT_ID AS "分配ID",
    st.EFFECTIVE_START_DATE AS "生效开始日期",
    st.EFFECTIVE_END_DATE AS "生效结束日期",
    COUNT(pt.ASSIGNMENT_ID) AS "数量"
FROM STAGING_TABLE st
LEFT JOIN PROD_TABLE pt 
    ON st.ASSIGNMENT_ID = pt.ASSIGNMENT_ID
    AND pt.EFFECTIVE_START_DATE <= st.EFFECTIVE_END_DATE
    AND pt.EFFECTIVE_END_DATE >= st.EFFECTIVE_START_DATE
GROUP BY 
    st.ASSIGNMENT_ID,
    st.EFFECTIVE_START_DATE,
    st.EFFECTIVE_END_DATE
ORDER BY 
    st.EFFECTIVE_START_DATE;

逻辑说明 #

• 关联条件中，pt.EFFECTIVE_START_DATE <= st.EFFECTIVE_END_DATE 且 pt.EFFECTIVE_END_DATE >= st.EFFECTIVE_START_DATE 是判断两个日期区间存在重叠的标准规则
• 使用LEFT JOIN确保STAGING_TABLE的所有区间都被统计，即使没有匹配的PROD_TABLE记录（此时数量为0）
• 按STAGING_TABLE的主键字段分组，保证每个区间对应一条统计结果

内容的提问来源于stack exchange，提问作者IMDUMB