如何简化SQL查询：获取Top1000高频<minute,id>的type分布

可以通过两种方式简化你的查询，避免多层嵌套CTE的冗余：

方案1：合并CTE并使用JOIN关联 #

先通过单个CTE计算所有<minute,id>组合的总次数并筛选Top1000，再直接关联原表统计各type的次数，写法更紧凑：

WITH TopMinuteIds AS (
    SELECT minute, id, COUNT(*) AS total_count
    FROM state_table
    GROUP BY minute, id
    ORDER BY total_count DESC
    LIMIT 1000
)
SELECT 
    st.minute, 
    st.id, 
    COUNT(*) AS count, 
    st.type
FROM state_table st
JOIN TopMinuteIds tmi 
    ON st.minute = tmi.minute 
    AND st.id = tmi.id
GROUP BY st.minute, st.id, st.type
ORDER BY tmi.total_count DESC, st.type;

方案2：使用窗口函数直接筛选Top1000组合 #

利用窗口函数ROW_NUMBER()先给每个<minute,id>组合按总次数排序，筛选出前1000后再统计type分布，逻辑更连贯：

WITH RankedMinuteIds AS (
    SELECT 
        minute, 
        id, 
        type,
        COUNT(*) OVER (PARTITION BY minute, id) AS total_count,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) OVER (PARTITION BY minute, id) DESC) AS rn
    FROM state_table
)
SELECT 
    minute, 
    id, 
    COUNT(*) AS count, 
    type
FROM RankedMinuteIds
WHERE rn <= 1000
GROUP BY minute, id, type, total_count
ORDER BY total_count DESC, type;

说明 #

• 方案1逻辑直观，适配大多数数据库，执行效率取决于数据库对LIMIT和JOIN的优化策略；
• 方案2通过窗口函数减少了一次表扫描（部分数据库优化器会自动完成此优化），ROW_NUMBER()会给同一<minute,id>组合的所有行标记相同排名，筛选后分组统计即可得到目标结果。

内容的提问来源于stack exchange，提问作者d.mc2