google-bigquery - BigQuery GROUP BY 前 n 个类别和组其余部分位于 "other"

Question

我经常遇到相同的任务 - 按分类变量中的前 X 值聚合数据，并将其他所有内容滚动到“其他”中。

到目前为止，我一直在使用这个技巧:

SELECT
year,
if(tt.state is null, "other", t.state) as state_filtered,
count(1) as children
FROM [publicdata:samples.natality] as t
LEFT OUTER JOIN (
  SELECT state, count(1) as children FROM [publicdata:samples.natality]
  WHERE state is not null
  GROUP BY state
  ORDER BY children DESC
  LIMIT 5
) as tt ON tt.state=t.state
GROUP BY year, state_filtered
ORDER BY year, state_filtered

但它不是很干净，因为我两次查询同一个表，并且在现实生活中的示例代码变得过于复杂。我一直在寻找使用 ROLLUP 或 TOP 的解决方案，但没有找到更好的解决方案。

有人知道更好的方法吗？

Answer 1

您可以在子查询中使用 Row_Number。

SELECT
  IF (RNB<=5, state, "Other") AS state,
  SUM(children) AS Children
FROM (
  SELECT
    state,
    children,
    ROW_NUMBER() OVER (ORDER BY children DESC) AS RNB
  FROM (
    SELECT
      state,
      COUNT(1) AS children,
    FROM
      [publicdata:samples.natality]
    WHERE
      state IS NOT NULL
    GROUP BY
      state))
GROUP EACH BY
  state