BigQuery按用户列值分组计算20th百分位并生成结果表的实现方案

你可以使用BigQuery内置的百分位计算函数实现需求，两个常用函数的差异如下：

• PERCENTILE_CONT：连续百分位函数，计算时会做插值处理，返回结果可能不在原表的Value取值范围内
• PERCENTILE_DISC：离散百分位函数，返回结果为原表Value列中存在的、最接近对应百分位的取值

两种实现方式可选：

方式1：GROUP BY 聚合写法（更简洁，推荐） #

SELECT
  User AS Name,
  -- 需要用离散百分位的话将PERCENTILE_CONT替换为PERCENTILE_DISC即可
  PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY Value) AS Percentile
FROM
  `你的项目ID.你的数据集名.你的表名`
GROUP BY
  User

方式2：窗口函数写法 #

SELECT
  User AS Name,
  PERCENTILE_CONT(0.2) OVER (PARTITION BY User) AS Percentile
FROM
  `你的项目ID.你的数据集名.你的表名`
-- 按用户去重，每个用户仅返回1条结果
QUALIFY ROW_NUMBER() OVER (PARTITION BY User ORDER BY 1) = 1

两种写法返回的计算结果完全一致，可根据使用习惯选择。

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