Postgres SQL实现按Bus分组后取每组Top2热门路线并统计数量

你的现有SQL已经完成了每个Bus下各线路的次数统计和排序，只需要补充按Bus分组后取每组前2条的逻辑即可，核心通过窗口函数的分区排名实现，以下是兼容主流支持窗口函数数据库（MySQL 8.0+、PostgreSQL、SQL Server、Hive等）的标准写法：

WITH route_stat AS (
    -- 复用原有统计逻辑，统计每个Bus+Route的出现次数
    SELECT 
        Bus, 
        Route, 
        COUNT(Route) AS Count 
    FROM my_table
    GROUP BY Bus, Route
),
route_rank AS (
    -- 按Bus分区，按线路次数倒序排名
    SELECT 
        Bus,
        Route,
        Count,
        ROW_NUMBER() OVER(PARTITION BY Bus ORDER BY Count DESC) AS rk
    FROM route_stat
)
-- 筛选每个Bus排名前2的线路
SELECT Bus, Route, Count
FROM route_rank
WHERE rk <= 2
ORDER BY Bus, Count DESC;

适配说明 #

• 如果你需要保留并列排名的结果（比如某Bus下有2条线路并列第2，需要都返回），将ROW_NUMBER()替换为RANK()即可。
• 如果你使用的是不支持窗口函数的低版本数据库（如MySQL 5.x），可以用变量实现相同逻辑：

SELECT Bus, Route, Count
FROM (
    SELECT 
        Bus,
        Route,
        Count,
        @rk := IF(@cur_bus = Bus, @rk + 1, 1) AS rk,
        @cur_bus := Bus
    FROM (
        SELECT Bus, Route, COUNT(Route) AS Count
        FROM my_table
        GROUP BY Bus, Route
        ORDER BY Bus, Count DESC
    ) AS t1,
    (SELECT @rk := 0, @cur_bus := '') AS init_var
) AS t2
WHERE rk <= 2;

以上两种写法返回的结果都完全符合预期：Slowcoach返回SC555（4次）、SC123（3次），SpeedyTram返回ST222（4次）、ST111（2次）。

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