时间戳

首页 > 解决方案 > 为 BigQuery 中的值的分位数创建列

sql - 为 BigQuery 中的值的分位数创建列

问题描述

我有一个有两列的表：id和score. 我想创建第三列，该列等于个人所属的分位数score。我想在 BigQuery 的标准 SQL 中执行此操作。

这是my_table：

+----+--------+
| id | score  |
+----+--------+
|  1 |      2 |
|  2 |     13 |
|  3 |     -2 |
|  4 |      7 |
+----+--------+

然后我想要下表（以四分位数显示的示例，但我对四分位数/五分位数/十分位数感兴趣）

+----+--------+----------+
| id | score  | quaRtile |
+----+--------+----------+
|  1 |      2 |        2 |
|  2 |     13 |        4 |
|  3 |     -2 |        1 |
|  4 |      7 |        3 |
+----+--------+----------+

如果这适用于 1 亿行，那就太好了。我环顾四周，看到一些似乎使用旧版 sql的解决方案，而使用 RANK()函数的解决方案似乎不适用于非常大的数据集。谢谢！

标签： sqlgoogle-bigquerybigquery-standard-sql

解决方案

如果我理解正确，您可以使用ntile(). 例如，如果你想要一个 1-4 的值，你可以这样做：

select t.*, ntile(4) over (order by score) as tile
from t;

如果要枚举值，请使用rank()or dense_rank()：

select t.*, rank() over (order by score) as tile
from t;

我明白了，您的问题是让代码正常工作，因为 BigQuery 往往会在没有partition by. 一种方法是将分数分成不同的组。我认为这个逻辑可以满足您的要求：

select *, 
       ( (count(*) over (partition by cast(score / 1000 as int64) order by cast(score / 1000 as int64)) -
          count(*) over (partition by cast(score / 1000 as int64))
         ) +
         rank() over (partition by cast(score / 1000 as int64) order by regi_id)
      ) as therank,
      -- rank() over (order by score) as therank
from t;

这会将分数分成 1000 组（对于整数来说可能太多了）。然后重构排名。

如果您的分数具有相对较低的基数，那么join聚合有效：

select t.*, (running_cnt - cnt + 1) as therank
from t join
     (select score, count(*) as cnt, sum(count(*)) over (order by score) as running_cnt
      from t
      group by score
     ) s
     on t.score = s.score;

一旦有了rank()（或row_number()），您就可以轻松地自己计算图块（提示：除法）。

推荐阅读