如何用SQL比较XX与YY表中同类型事件的发生先后顺序

数据表结构与数据 #

XX表 #

CREATE TABLE XX 
(
    name VARCHAR(50),
    date DATE,
    a INT,
    b INT,
    c INT
);

INSERT INTO XX (name, date, a, b, c) 
VALUES 
('john', '2010-11-01', 1, 0, 0),
('john', '2010-10-01', 0, 1, 0),
('sara', '1999-02-01', 1, 0, 0),
('julie', '2015-09-01', 1, 0, 0),
('julie', '2015-09-01', 0, 1, 0);

YY表 #

CREATE TABLE YY 
(
    name VARCHAR(50),
    yy_date DATE,
    yy CHAR(1)
);

INSERT INTO YY (name, yy_date, yy) 
VALUES 
('john', '2015-01-01', 'A'),
('john', '2016-01-01', 'A'),
('john', '2000-02-01', 'B'),
('john', '2010-03-01', 'C'),
('julie', '2017-09-01', 'A'),
('julie', '2010-09-01', 'B'),
('tom', '2010-09-01', 'B');

查询需求 #

为YY表每条记录新增一列，标注对应（name,类型）下XX事件与YY事件的发生先后关系，规则如下：

• 若XX中无对应同类型记录，标注「Not applicable」
• 若XX事件早于YY事件，标注「XX happened before YY」
• 若XX事件晚于YY事件，标注「YY happened before XX」
• 若日期相同，标注「XX and YY happened on the same date」

测试SQL语句 #

WITH xx_mapped AS (
    SELECT name, date, 
           CASE 
               WHEN a = 1 THEN 'A'
               WHEN b = 1 THEN 'B'
               WHEN c = 1 THEN 'C'
           END AS xx_type
    FROM XX
),
earliest_xx AS (
    SELECT name, xx_type, MIN(date) as earliest_xx_date
    FROM xx_mapped
    GROUP BY name, xx_type
),
yy_with_xx AS (
    SELECT 
        y.name, 
        y.yy_date, 
        y.yy, 
        e.earliest_xx_date
    FROM YY y
    LEFT JOIN earliest_xx e ON y.name = e.name AND y.yy = e.xx_type
),
comparison_result AS (
    SELECT 
        name, 
        yy_date, 
        yy,
        CASE
            WHEN earliest_xx_date IS NULL THEN 'Not applicable'
            WHEN earliest_xx_date < yy_date THEN 'XX happened before YY'
            WHEN earliest_xx_date > yy_date THEN 'YY happened before XX'
            ELSE 'XX and YY happened on the same date'
        END as xx_vs_yy
    FROM yy_with_xx
)
SELECT *
FROM comparison_result
ORDER BY name, yy_date;

验证分析 #

这条SQL核心逻辑符合需求，但需注意：它取XX表中对应（name,xx_type）的最早事件日期与YY事件日期做比较，而非所有同类型XX事件的日期。若需求是基于「最早XX事件」判断先后，逻辑完全成立；若需求是判断「是否存在同类型XX事件早于/晚于YY事件」，则需要调整逻辑。

逐条匹配需求规则：

1. 无对应同类型记录：通过LEFT JOIN后earliest_xx_date IS NULL判断，正确标注「Not applicable」，比如YY表中tom的B类型记录，XX表无对应，标注正确。
2. XX事件早于YY事件：用最早XX日期小于YY日期判断，比如john的2015-01-01的A类型，XX中john的A最早日期是2010-11-01，早于2015-01-01，标注正确。
3. XX事件晚于YY事件：用最早XX日期大于YY日期判断，比如john的2000-02-01的B类型，XX中john的B最早日期是2010-10-01，晚于2000-02-01，标注「YY happened before XX」，正确。
4. 日期相同：当最早XX日期等于YY日期时自动匹配标注逻辑，若存在对应场景可正确识别。

执行结果示例 #

执行上述SQL后，返回结果如下：

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