BigQuery：获取最接近指定百分位的数值

我来帮你搞定这个需求！你已经用PERCENT_RANK()生成了包含百分位数据的表，现在要提取分别最接近100th、90th…10th的10行数据，这个可以通过结合CTE（公共表表达式）和窗口函数来实现，具体思路和代码如下：

核心思路 #

1. 先定义我们要匹配的目标百分位值（对应10th到100th，即0.1到1.0）；
2. 计算表中每一行的百分位值与每个目标值的绝对差值；
3. 针对每个目标值，筛选出差值最小的那一行（如果有多个行差值相同，可选择保留所有匹配行）。

具体SQL代码 #

假设你的表名为your_table，存储PERCENT_RANK()结果的列名为percent_rank_value，其他需要保留的列用your_columns代替，代码如下：

WITH target_percentiles AS (
  -- 生成我们要找的10个目标百分位值
  SELECT target
  FROM UNNEST([0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0]) AS target
),
ranked_diffs AS (
  SELECT
    t.target,
    y.*,
    -- 计算当前行百分位与目标值的绝对差
    ABS(y.percent_rank_value - t.target) AS diff,
    -- 按目标分组，给差值最小的行标记为1
    ROW_NUMBER() OVER (PARTITION BY t.target ORDER BY ABS(y.percent_rank_value - t.target) ASC) AS rn
  FROM your_table y
  -- 交叉连接，让每个行都和10个目标值做匹配计算
  CROSS JOIN target_percentiles t
)
SELECT
  -- 把目标值转换成10th、20th这样的可读性格式
  target * 100 AS percentile,
  percent_rank_value AS closest_percent_rank,
  -- 这里替换成你需要保留的其他列
  your_columns
FROM ranked_diffs
-- 只保留每个目标下差值最小的行
WHERE rn = 1
-- 按百分位从高到低排序
ORDER BY percentile DESC;

特殊情况处理 #

如果你的表中存在多个行与某个目标百分位的差值完全相同（比如两个行的percent_rank_value都是0.80001213，和0.8的差值一致），上面的ROW_NUMBER()只会随机保留其中一行。如果你想保留所有这类匹配行，可以把ROW_NUMBER()换成RANK()，修改后的窗口函数部分如下：

RANK() OVER (PARTITION BY t.target ORDER BY ABS(y.percent_rank_value - t.target) ASC) AS rnk

然后把筛选条件改成WHERE rnk = 1即可。

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