如何基于Snowflake SQL分区统计结果获取最大行数记录？

方法1：用窗口函数筛选排名第一的记录 #

通过在外层嵌套RANK()窗口函数，给所有记录按ROWCOUNT降序排名，直接取排名第一的结果（若有多个记录的ROWCOUNT同为最大值，会返回所有并列记录）：

WITH ranked_records AS (
    SELECT 
        sub.*,
        RANK() OVER (ORDER BY sub.ROWCOUNT DESC) AS rank_num
    FROM (
        SELECT DISTINCT 
            A, B, C, D, E, 
            COUNT(*) OVER (PARTITION BY A, B) AS ROWCOUNT 
        FROM TABLE
    ) sub
)
SELECT A, B, C, D, E, ROWCOUNT
FROM ranked_records
WHERE rank_num = 1;

如果只想从并列最大值里取单条记录，可把RANK()换成ROW_NUMBER()，同时在OVER()里补充额外排序字段（比如ORDER BY sub.ROWCOUNT DESC, A, B）来保证结果稳定。

方法2：先找最大ROWCOUNT再关联筛选 #

先计算出全局最大的ROWCOUNT值，再用这个值过滤初始子查询的结果：

SELECT sub.*
FROM (
    SELECT DISTINCT 
        A, B, C, D, E, 
        COUNT(*) OVER (PARTITION BY A, B) AS ROWCOUNT 
    FROM TABLE
) sub
WHERE sub.ROWCOUNT = (
    SELECT MAX(group_count)
    FROM (
        SELECT COUNT(*) AS group_count
        FROM TABLE
        GROUP BY A, B
    ) max_group
);

不同数据库的最大值取法可简化：比如MySQL用LIMIT 1，SQL Server用TOP 1，Oracle用FETCH FIRST 1 ROW ONLY，直接取分组行数最大的那个值。

方法3：用CTE简化结构 #

把初始的分组统计逻辑定义为CTE，后续操作更直观：

WITH base_stats AS (
    SELECT DISTINCT 
        A, B, C, D, E, 
        COUNT(*) OVER (PARTITION BY A, B) AS ROWCOUNT 
    FROM TABLE
)
SELECT *
FROM base_stats
WHERE ROWCOUNT = (SELECT MAX(ROWCOUNT) FROM base_stats);

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