如何在GROUP BY查询中获取众数(MODE)对应的出现次数/频率？

完整可运行查询示例 #

WITH subtable AS (
SELECT
    member_casual,
    seasons,
    start_station_name || ' to ' || end_station_name AS route
FROM
    bike_data
),
-- 存储原有分组查询的结果
grouped_result AS (
SELECT
    member_casual,
    seasons,
    MODE() WITHIN GROUP (ORDER BY route) AS most_frequent_route, 
    COUNT(*) AS total_service_used_count
FROM
    subtable
GROUP BY
    member_casual,
    seasons
)
-- 外层查询补充最频繁路线的独立出现次数
SELECT
    g.*,
    (SELECT COUNT(*) 
     FROM subtable s 
     WHERE s.member_casual = g.member_casual 
       AND s.seasons = g.seasons 
       AND s.route = g.most_frequent_route) AS most_frequent_route_count
FROM grouped_result g;

逻辑说明 #

• 第一层subtable和原有逻辑完全一致，负责生成拼接后的路线字段
• 第二层grouped_result存储原有查询的分组结果，包含每个会员类型+季节组合的最频繁路线、总服务使用次数
• 新增的most_frequent_route_count列通过标量子查询实现：匹配当前行的会员类型、季节、最频繁路线三个维度，统计该路线在对应分组内的实际出现次数。只要你的原有查询可以正常运行，这个修改后的查询无需额外调整即可直接执行。

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