1.row_number() over() (生成全局递增唯一ID)
select
row_number() over() id,
a
from test1
此方法会产生一个reduce。数据量比较大会产生数据倾斜。
2.生成全局唯一数字ID(非递增连续)
2.1先将原始数据随机分成101份(可以理解为步长)
create table test2
as
select
a,
cast(rand()*100 as bigint) num
from test1
2.2 每份内排序后生成一个唯一ID
select
a,
num+(rn-1)*101
from
(
select
a,
num,
ROW_NUMBER() OVER(PARTITION BY num) AS rn
from test2
) a
3.生成全局唯一数字ID(递增连续)
3.1 先将原始数据随机分成101份写入test2表中
3.2 利用开窗函数求每个分组的累积值
select
num,
cnt,
sum(cnt) over(order by num ) amt
from
(
select
num,
count(*) cnt
from test2
group by num
) a
3.3 每组组内排序rn+上一组的累积值
select
a.a,
a.num,
a.rn,
b.num,
b.cnt,
b.amt,
nvl(b.amt,0)+rn
from
(
select
a,
num,
ROW_NUMBER() OVER(PARTITION BY num) AS rn
from test2
) a left outer JOIN
(
select
num,
cnt,
sum(cnt) over(order by num ) amt
from
(
select
num,
count(*) cnt
from test2
group by num
) a
) b on a.num-1=b.num