BigQuery查询最高频值 频次并列时按日期取最新值去重

你原有逻辑的问题有两处：一是外层分组漏了date字段会触发SQL语法报错，二是ARRAY_AGG的排序规则没有配置频次打平后的兜底判断规则，才会出现同频次下结果重复的问题。

只需要调整排序规则，补全分组逻辑即可实现「优先取使用频次最高的语言，频次相同时取最新使用的语言」的需求。

按用户维度统计（和你最初的查询粒度一致） #

如果不需要按日期拆分结果，仅需要给每个用户匹配唯一的最常用语言，用以下语句：

#standardSQL
SELECT
  User_ID,
  ARRAY_AGG(
    Language 
    ORDER BY cnt DESC, latest_use_date DESC 
    LIMIT 1
  )[OFFSET(0)] most_frequent_language
FROM (
  SELECT 
    User_ID,
    Language,
    COUNT(*) AS cnt,
    MAX(date) AS latest_use_date -- 统计每个用户对应每种语言的最近一次使用时间
  FROM `project.dataset.language`
  WHERE Language IS NOT NULL
  GROUP BY User_ID, Language
)
GROUP BY User_ID

按日期+用户维度统计 #

如果需要按日期拆分，输出每天每个用户的最常用语言，用以下语句：

#standardSQL
SELECT
  date,
  User_ID,
  ARRAY_AGG(
    Language 
    ORDER BY cnt DESC, latest_record_time DESC 
    LIMIT 1
  )[OFFSET(0)] most_frequent_language
FROM (
  SELECT 
    date,
    User_ID,
    Language,
    COUNT(*) AS cnt,
    MAX(date) AS latest_record_time -- 若date字段为天粒度可直接写date，若为精确时间戳保留MAX()即可
  FROM `project.dataset.language`
  WHERE Language IS NOT NULL
  GROUP BY 1,2,3
)
GROUP BY 1,2

逻辑说明 #

• 内层子查询除了统计不同维度下各语言的使用次数，同时计算对应语言的最新使用时间作为兜底排序依据
• ARRAY_AGG排序时优先按使用频次倒序，频次一致时自动按最新使用时间倒序，固定取排序后的第一个值，从根源避免同频次下的重复结果问题
• 外层GROUP BY包含所有非聚合字段，符合BigQuery的语法校验规则，不会报字段未聚合的错误

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