时间戳

首页 > 解决方案 > 为每个组生成一个随机数并将其分配给组中的所有行

sql - 为每个组生成一个随机数并将其分配给组中的所有行

问题描述

我有一张表格

ID     | DOC 
-------------
id1    | d1  
-------------
id1    | d2  
-------------
id2    | d3 
-------------
id2    | d4
-------------
id3    | d5
-------------

目标是按 ID 对表进行分组，并且对于每个组，从组数中选择一个随机数（在本例中，从 [1, 3] 中选择一个随机数）并为每个组的所有行分配一个数字。一种可能的配置是

ID     | DOC | GROUP_NUM
--------------------------
id1    | d1  | 2
--------------------------
id1    | d2  | 2
--------------------------
id2    | d3  | 1
--------------------------
id2    | d4  | 1
--------------------------
id3    | d5  | 3
--------------------------

我正在考虑使用 ROW_NUMBER() 和 PARTITION() 函数。考虑到 Bigquery 中的表很大，有什么更好的方法呢？

标签： sqlgoogle-bigquery

解决方案

如果随机数可以是连续的，您可以使用dense_rank()：

select t.*, dense_rank() over (order by id) as group_num
from t;

或者更多的随机性：

select t.*,
       dense_rank() over (order by farm_fingerprint(cast(id as string)), id) as group_num
from t;

或者，单独计算id可能是最简单的：

select *
from t join
     (select id,
             dense_rank() over (order by rand()) as group_num
      from t
      group by id
     ) tt
     using (id)

推荐阅读