Oracle如何通过窗口函数查询指定分组下两列组合数最大的行

你原来的SQL存在几处逻辑和语法错误，以下是可直接运行的正确实现：

WITH publid_uniq_cnt AS (
    -- 统计每个clusterid、相同issuedate+operdate分组下，各publid对应的publid+inn唯一组合数
    SELECT 
        clusterid,
        issuedate,
        operdate,
        publid,
        COUNT(DISTINCT inn) AS uniq_cnt
    FROM your_table
    WHERE clusterid IS NOT NULL
    GROUP BY clusterid, issuedate, operdate, publid
),
ranked_publid AS (
    -- 同分组内按唯一组合数倒序排名，取排名第一的publid
    SELECT 
        *,
        RANK() OVER(PARTITION BY clusterid, issuedate, operdate ORDER BY uniq_cnt DESC) AS rn
    FROM publid_uniq_cnt
)
-- 关联回原表获取对应publid的所有行
SELECT t.*
FROM your_table t
INNER JOIN ranked_publid r
    ON t.clusterid = r.clusterid
    AND t.issuedate = r.issuedate
    AND t.operdate = r.operdate
    AND t.publid = r.publid
WHERE r.rn = 1;

原有SQL问题说明 #

• WHERE子句位置错误：SQL执行顺序为WHERE > GROUP BY，你将WHERE放在GROUP BY后会直接触发语法错误
• 分组维度不全：仅按publid分组，其余非聚合字段未在GROUP BY中声明，除开启非严格模式的MySQL外，所有数据库都会报错，也无法正确保留业务所需的关联字段
• 统计逻辑错误：需求要求统计publid+inn的唯一组合数，需使用COUNT(DISTINCT inn)实现，而非普通COUNT(inn)
• 窗口分区维度缺失：需求要求在同一clusterid、相同issuedate和operdate的范围内比较publid的组合数，因此PARTITION BY需要同时包含clusterid、issuedate、operdate三个字段

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