如何查询首次出现后从连续列表中消失的tradeid

模拟测试数据 #

CREATE TABLE #table(cobid INT,tradeid VARCHAR(10))

INSERT INTO #table
SELECT cobid, tradeid
FROM (VALUES
    (20240923,'123456'),
    (20240924,'123456'),
    (20240925,'123456'),
    (20240926,'123456'),
    (20240924,'123457'),
    (20240925,'123457'),
    (20240926,'123457'),
    (20240924,'123458'),
    (20240925,'123458'),
    (20240923,'123459'),
    (20240924,'123459')
) t(cobid, tradeid)

需求说明 #

需要编写SQL查询，标记那些首次出现后从连续cobid列表中消失的tradeid：

• 示例：tradeid 123458在20240926缺失，需要标记
• 无需关注tradeid首次出现前的cobid缺失情况（比如123458在20240923不存在，不用处理）

现有可行实现 #

已通过生成全组合的方式实现需求，但代码较为繁琐，现有代码如下：

-- 生成所有可能的cobid与tradeid组合
WITH cte AS (
    SELECT *
    FROM (
        SELECT DISTINCT cobid 
        FROM #table
    ) x
    CROSS JOIN (
        SELECT DISTINCT tradeid 
        FROM #table
    ) y
), 
ctf AS (
    SELECT c.tradeid AS tradeidmaster,
        c.cobid AS cobidmaster,
        t.cobid,
        t.tradeid,
-- 累计统计tradeid已出现的次数，过滤首次出现前的行
        SUM(CASE WHEN t.tradeid IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY c.tradeid ORDER BY c.cobid) AS checkval
    FROM cte c 
    LEFT JOIN #table t ON t.cobid = c.cobid AND c.tradeid = t.tradeid
)
SELECT *
FROM ctf
-- 过滤tradeid首次出现前的行
WHERE ctf.checkval <> 0
-- 筛选首次出现后缺失的记录
AND ctf.tradeid IS NULL

更简洁的优化实现 #

可以通过窗口函数先锁定每个tradeid的有效检查区间，再生成必要的日期组合，最后匹配原表找出缺失项：

WITH trade_valid_range AS (
    -- 获取每个tradeid的首次出现日期，以及全局最大cobid
    SELECT 
        tradeid,
        MIN(cobid) AS first_cobid,
        (SELECT MAX(cobid) FROM #table) AS max_global_cobid
    FROM #table
    GROUP BY tradeid
),
required_cobids AS (
    -- 生成每个tradeid从首次出现到全局最大cobid的所有日期
    SELECT 
        t.tradeid,
        c.cobid
    FROM trade_valid_range t
    JOIN (SELECT DISTINCT cobid FROM #table) c 
        ON c.cobid BETWEEN t.first_cobid AND t.max_global_cobid
)
-- 左连接原表，筛选缺失记录
SELECT 
    arc.tradeid,
    arc.cobid AS missing_cobid,
    '缺失' AS flag
FROM required_cobids arc
LEFT JOIN #table t 
    ON arc.tradeid = t.tradeid AND arc.cobid = t.cobid
WHERE t.tradeid IS NULL

优化亮点 #

• 避免全量笛卡尔积，只生成每个tradeid首次出现后的必要日期组合，减少计算开销
• 逻辑更直观，直接聚焦需要检查的有效区间
• 结果输出更简洁，直接返回缺失的tradeid和对应cobid

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