如何使用SQL窗口函数按多字段组合分区筛选符合要求的记录

原有代码错误点 #

• 语法顺序错误：SQL执行顺序中WHERE子句优先级高于GROUP BY，你写的WHERE放在GROUP BY之后会直接报语法错误
• 分组逻辑不符合需求：仅按publid分组无法匹配「先按相同非空clusterid、相同issuedate、相同operdate分组」的前提
• 窗口函数分区规则错误：PARTITION BY仅设置了clusterid，没有加入issuedate、operdate两个分组维度，分区范围不符合要求
• 聚合统计逻辑错误：直接在窗口函数的ORDER BY里写count(inn)，和GROUP BY的组合逻辑混乱，无法正确统计每个publid对应的唯一publid+inn组合数量

正确实现代码（窗口函数实现） #

SELECT inn, publid, clusterid, issuedate, operdate
FROM (
    SELECT 
        t.*,
        -- 按分组规则分区，按每个publid对应的组合数倒序排名
        RANK() OVER (
            PARTITION BY clusterid, issuedate, operdate 
            ORDER BY publid_cnt DESC
        ) AS rn
    FROM (
        SELECT 
            m.*,
            -- 统计每个分组内同一publid对应的唯一publid+inn组合数
            COUNT(*) OVER (
                PARTITION BY clusterid, issuedate, operdate, publid
            ) AS publid_cnt
        FROM `table` m
        WHERE clusterid IS NOT NULL
    ) t
) res
WHERE res.rn = 1

逻辑说明 #

1. 最内层子句先过滤掉clusterid为空的行，用窗口函数按clusterid、issuedate、operdate、publid分区，统计每个publid在对应大分组下的行数，也就是需求要求的「publid + inn」唯一组合数量
2. 第二层用RANK()窗口函数，按clusterid、issuedate、operdate分区，按统计出来的组合数倒序排名
3. 最外层取排名为1的所有行，就是组合数最多的publid对应的全部行

验证结果 #

对你提供的样例数据，publid为--1--的组合数是2，publid为--2--的组合数是3，所以排名第一的是--2--对应的3行，和你给出的期望输出完全匹配。

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