google-bigquery - 在 BigQuery 中查找模式

Question

众数是一个集合中最常出现的值。

我想要类似的东西:

SELECT
    t.id as t_id,
    GROUP_CONCAT(t.value) as value_list,
    MODE(t.value) AS value_mode
FROM dataset.table as t
GROUP BY t_id

例如:

t_id    value_list     value_mode
1       2,2,2,3,6,6    2

这是怎么做的？

编辑 : value_list 只是为了说明目的。只需要模式

Answer 1

select id, value as value_list, v as value_mode
from (
  select 
    id, value, v, 
    count(1) as c, 
    row_number() over(partition by id order by c desc) as top
  from (
    select id, value, split(value) as v 
    from dataset.table 
  )
  group by id, value, v
)
where top = 1