Google SQL：按两列分组获取对应列的最频繁字符串值

问题分析 #

你当前的代码错误在于仅按ColumnA分组统计总数，没有关联ColumnB的具体值，因此无法定位到每个ColumnA分组下ColumnB的高频值。要实现需求，需要先统计每个ColumnA-ColumnB组合的出现次数，再通过窗口函数为每个分组内的ColumnB值按频次排序，最终取排名第一的结果。

解决方案代码 #

WITH value_counts AS (
  -- 统计每个ColumnA分组下，不同ColumnB值的出现次数
  SELECT
    ColumnA,
    ColumnB,
    COUNT(*) AS occurrence
  FROM your_table
  GROUP BY ColumnA, ColumnB
),
ranked_values AS (
  -- 为每个ColumnA分组内的ColumnB值排序：优先非null值，再按出现次数降序
  SELECT
    ColumnA,
    ColumnB,
    ROW_NUMBER() OVER (
      PARTITION BY ColumnA
      ORDER BY 
        CASE WHEN ColumnB IS NOT NULL THEN 1 ELSE 0 END DESC,
        occurrence DESC
    ) AS rank_num
  FROM value_counts
)
-- 取每个分组内排名第一的结果
SELECT ColumnA, ColumnB
FROM ranked_values
WHERE rank_num = 1;

关键说明 #

1. 统计组合频次：value_counts CTE按ColumnA和ColumnB联合分组，得到每个分组下不同ColumnB值的出现次数，这是定位众数的基础。
2. 处理null值优先级：在窗口函数的ORDER BY中，通过CASE语句将非null的ColumnB值排在前面，确保当最频繁值为null时，自动取次高频的非null值。
3. 排序规则：使用ROW_NUMBER()会为每个分组内的ColumnB值生成唯一排名，若存在多个频次相同的众数，仅取其中一个；如果需要保留所有并列众数，可将ROW_NUMBER()替换为RANK()。

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