Oracle按OBS_TYPEID优先级查询每个OBJECTID唯一记录实现问题

解决方案 #

你可以通过窗口函数ROW_NUMBER()实现分组取优先级最高的唯一记录，修改后的SQL如下：

SELECT OBJECTID, DATETIME_UTC, OBS_TYPEID
FROM (
    SELECT 
        r.OBJECTID AS OBJECTID,
        r.DATETIMEUTC as DATETIME_UTC,
        r.OBS_TYPEID,
        -- 按OBJECTID+小时级时间分组，组内按OBS_TYPEID优先级排序
        ROW_NUMBER() OVER (
            PARTITION BY r.OBJECTID, TRUNC(r.DATETIMEUTC, 'HH')
            ORDER BY DECODE(r.OBS_TYPEID, '7', 1, '4', 2, '6', 3, 4)
        ) AS rn
    FROM PROD.WX_RAW r 
    WHERE r.REPORTTYPE ='FM-15' 
      AND OBS_TYPEID IN('7','4','6')
      AND r.DATETIMEUTC BETWEEN v_start_date AND v_end_date 
) t
WHERE t.rn = 1

逻辑说明 #

1. 分组规则：PARTITION BY 后指定两个分组维度，分别是OBJECTID和精确到小时的DATETIMEUTC，刚好匹配你提到的重复记录出现的维度（同一OBJECTID、同一小时的时间）。
2. 组内排序：和你原有优先级逻辑完全一致，OBS_TYPEID=7排最前，其次是4，最后是6。
3. 结果过滤：外层查询仅保留每个分组内排序第一的记录，保证每个分组只返回一条符合优先级要求的结果。

如果你的运行环境不是Oracle（原SQL用了Oracle独有的DECODE函数），只需要对应调整两个函数即可：

• 小时级时间截断：MySQL用DATE_FORMAT(r.DATETIMEUTC, '%Y-%m-%d %H:00:00')，PostgreSQL用DATE_TRUNC('hour', r.DATETIMEUTC)
• 优先级映射：把DECODE替换为标准SQL的CASE WHEN语句即可。

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