如何判断纵向数据中Event 'T'是新发生还是已存在并提取eventdate

我有按年度分表存储的纵向数据，需要验证Event 'T'是新发生（首次出现）还是已存在，并提取对应的EventDate。判断规则：

• 如果ID在首次出现Event 'T'之前已有其他事件记录，则该'T'属于新发生（标记为1）
• 如果ID首次出现时就带有Event 'T'，或之前已有'T'记录，则属于已存在（标记为0）

示例数据 #

CREATE TABLE Table_2010 (
    ID INT,
    EventDate DATE,
    Event CHAR(1)
);

CREATE TABLE Table_2011 (
    ID INT,
    EventDate DATE,
    Event CHAR(1)
);

CREATE TABLE Table_2012 (
    ID INT,
    EventDate DATE,
    Event CHAR(1)
);

CREATE TABLE Table_2013 (
    ID INT,
    EventDate DATE,
    Event CHAR(1)
);

CREATE TABLE Table_2014 (
    ID INT,
    EventDate DATE,
    Event CHAR(1)
);

INSERT INTO Table_2010 (ID, EventDate, Event) VALUES
    (1, '2010-01-01', 'U'),
    (1, '2010-02-01', 'U'),
    (2, '2010-01-15', 'T'),
    (2, '2010-02-15', 'V');

INSERT INTO Table_2011 (ID, EventDate, Event) VALUES
    (1, '2011-01-01', 'T'),
    (1, '2011-02-01', 'V'),
    (2, '2011-01-15', 'X'),
    (2, '2011-02-15', 'Z'),
    (2, '2011-03-01', 'T'),
    (3, '2011-02-20', 'T'),
    (3, '2011-03-30', 'Z');

INSERT INTO Table_2012 (ID, EventDate, Event) VALUES
    (1, '2012-01-01', 'U'),
    (1, '2012-02-01', 'T'),
    (2, '2012-01-15', 'T'),
    (2, '2012-02-15', 'Z'),
    (2, '2012-03-01', 'Z');

INSERT INTO Table_2013 (ID, EventDate, Event) VALUES
    (1, '2013-01-01', 'T'),
    (1, '2013-02-01', 'Z'),
    (2, '2013-01-15', 'T'),
    (2, '2013-02-15', 'Y');

INSERT INTO Table_2014 (ID, EventDate, Event) VALUES
    (1, '2014-01-01', 'Z'),
    (1, '2014-02-01', 'T'),
    (2, '2014-01-15', 'T'),
    (2, '2014-02-15', 'X'),
    (2, '2014-03-01', 'Z');

初始方案的问题 #

我最初的SQL只能提取每个ID首次出现'T'的日期，但无法判断该ID在首次出现'T'之前是否已有其他事件记录：

SELECT ID, 
    MIN(CASE WHEN Event = 'T' THEN EventDate END) AS T_StartDate
FROM (
    SELECT ID, EventDate, Event
    FROM Table_2010
    WHERE Event IN ('T')
    UNION ALL
    SELECT ID, EventDate, Event
    FROM Table_2011
    WHERE Event IN ('T')
    UNION ALL
    SELECT ID, EventDate, Event
    FROM Table_2012
    WHERE Event IN ('T')
    UNION ALL
    SELECT ID, EventDate, Event
    FROM Table_2013
    WHERE Event IN ('T')
    UNION ALL
    SELECT ID, EventDate, Event
    FROM Table_2014
    WHERE Event IN ('T')
) AS AllEvents
GROUP BY ID;

比如：

• ID 1在2010年已有非'T'事件，2011年首次出现'T'，应标记为新发生（1）
• ID 2首次出现就有'T'，应标记为已存在（0）
• ID 3首次出现就有'T'，应标记为已存在（0）

解决方案 #

要实现逻辑，需要先获取每个ID的最早记录日期，再对比其首次出现'T'的日期：

WITH AllRecords AS (
    -- 合并所有年度表的所有记录
    SELECT ID, EventDate, Event FROM Table_2010
    UNION ALL
    SELECT ID, EventDate, Event FROM Table_2011
    UNION ALL
    SELECT ID, EventDate, Event FROM Table_2012
    UNION ALL
    SELECT ID, EventDate, Event FROM Table_2013
    UNION ALL
    SELECT ID, EventDate, Event FROM Table_2014
),
ID_Metrics AS (
    SELECT 
        ID,
        -- 获取ID的最早记录日期
        MIN(EventDate) AS First_Record_Date,
        -- 获取ID首次出现'T'的日期
        MIN(CASE WHEN Event = 'T' THEN EventDate END) AS T_First_Date
    FROM AllRecords
    GROUP BY ID
)
SELECT 
    ID,
    T_First_Date AS Eventdate,
    -- 判断：如果最早记录日期 < 首次'T'日期，说明之前有非'T'记录，标记为1；否则为0
    CASE WHEN First_Record_Date < T_First_Date THEN 1 ELSE 0 END AS Newly_occured
FROM ID_Metrics
WHERE T_First_Date IS NOT NULL; -- 过滤从未出现'T'的ID

预期输出 #

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