基于排名与最新时间戳提取关联记录的SQL实现求助

原表结构 #

---------------------------------------------------------------------------------
id  |   ref         |   type    |   status  |   update_dt
---------------------------------------------------------------------------------
id1 |   m1123       |   10      |   1       | 03-NOV-22 10.44.64.104000000 AM
id1 |   m2123       |   10      |   2       | 03-NOV-22 10.44.64.104000000 AM
id1 |   s1123       |   20      |           | 03-NOV-22 10.44.64.104000000 AM
id1 |   s2123       |   20      |           | 03-NOV-22 10.44.54.104000000 AM
id1 |   p1123       |   30      |           | 03-NOV-22 10.44.54.104000000 AM

id2 |   m1234       |   10      |           | 02-NOV-22 10.44.64.104000000 AM
id2 |   s1234       |   20      |           | 02-NOV-22 10.44.54.104000000 AM
id2 |   s2234       |   20      |           | 02-NOV-22 10.44.54.104000000 AM

id3 |   m1345       |   10      |   1       | 01-NOV-22 10.44.64.104000000 AM
id3 |   s1345       |   20      |           | 01-NOV-22 10.44.64.104000000 AM
id3 |   s2345       |   20      |           | 01-NOV-22 10.44.54.104000000 AM
---------------------------------------------------------------------------------

需求说明 #

• 仅提取type为10和20的记录，其中type10的status为null或1；
• 将type10的update_dt转换为epoch时间，筛选出大于指定值（示例为1667300400，对应2022年11月1日11点）的记录；
• type10与type20记录通过id关联；
• 为每条符合条件的type10记录，匹配对应id下update_dt最新的type20记录；
• 若多条type20记录update_dt相同，任选其一即可。

期望结果 #

-----------------------------------------------------------------------------------------------
ref1    |   ref2    |   ref1_update_dt                  |   ref2_update_dt
-----------------------------------------------------------------------------------------------
m1123   |   s1123   |   03-NOV-22 10.44.64.104000000 AM |   03-NOV-22 10.44.64.104000000 AM
m1234   |   s2234   |   02-NOV-22 10.44.64.104000000 AM |   02-NOV-22 10.44.54.104000000 AM
-----------------------------------------------------------------------------------------------

你的SQL问题分析 #

当前SQL存在两个核心问题：

1. 窗口函数未按id分区：RANK() OVER(ORDER BY t2.update_dt DESC)是全局排序，会把所有type20记录统一排名，最终只取排名第一的一条，无法实现每个id下取最新type20的需求；
2. epoch转换丢失时间精度：CAST(t1.update_dt AS DATE)会丢掉时分秒信息，导致转换后的epoch不准确，而且比较时把数字1667300400加了引号，变成字符串比较，逻辑错误。

修正后的SQL #

WITH filtered_type10 AS (
    -- 筛选符合条件的type10记录
    SELECT 
        id,
        ref AS ref1,
        update_dt AS ref1_update_dt
    FROM myTable
    WHERE type = 10
      AND (status IS NULL OR status = 1)
      -- 直接将timestamp转换为epoch，保留时间精度，比较时用数字类型
      AND EXTRACT(EPOCH FROM update_dt) > 1667300400
),
latest_type20 AS (
    -- 按id分区，获取每个id下update_dt最新的type20记录
    SELECT 
        id,
        ref AS ref2,
        update_dt AS ref2_update_dt,
        -- 按id分区，update_dt降序排名，相同时间任选其一用ROW_NUMBER更合适
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY update_dt DESC) AS rn
    FROM myTable
    WHERE type = 20
)
-- 关联两个CTE，得到最终结果
SELECT 
    ft10.ref1,
    lt20.ref2,
    ft10.ref1_update_dt,
    lt20.ref2_update_dt
FROM filtered_type10 ft10
JOIN latest_type20 lt20 ON ft10.id = lt20.id
WHERE lt20.rn = 1
ORDER BY ft10.ref1_update_dt;

逻辑说明 #

1. filtered_type10：先筛选出符合status条件、且update_dt转epoch大于指定值的type10记录，同时保留id和原始时间字段；
2. latest_type20：对type20记录按id分区，用ROW_NUMBER()按update_dt降序排名，每个id下排名1的就是最新的记录（如果时间相同，ROW_NUMBER()会随机分配排名，满足"任选其一"的需求）；
3. 最后将两个CTE通过id关联，筛选出每个id下排名1的type20记录，得到期望结果。

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