为有多列重复的行按顺序赋值

Group rows by multiple columns and number sequentially for each group, at the same time, keep their relative order.

问题描述

这里有个很奇特的需求:

有一张表,前两列可以组成一个元组(tuple),现在想要给表的每一行都添加序号,要求

  1. 序号是从1开始递增的
  2. 序号整体是连续的。也就是 dense_rank() 而非 rank() 的感觉
  3. 如果有两行前两列相同,则它们的序号也相同,
  4. 序号从表第一行开始计,按表原始顺序处理

文字说明有点难懂,我们来看个例子

1
2
3
4
5
6
7
8
9
create table table1 (
id_1 char(1),
id_2 int,
some_value int
);

insert into table1 (id_1, id_2, some_value)
values ('a', 1, 36), ('a', 2, 36), ('b', 3, 45), ('a', 4, 65)
, ('a', 1, 75), ('b', 2, 35), ('b', 5, 35), ('b', 2, 76);

具体值是这样的

id_1 id_2 some_value
a 1 36
a 2 36
b 3 45
a 4 65
a 1 75
b 2 35
b 5 35
b 2 76

也就是需要这样的结果

id_1 id_2 some_value ordered_number
a 1 36 1
a 2 36 2
b 3 45 3
a 4 65 4
a 1 75 1
b 2 35 5
b 5 35 6
b 2 76 5
  • 注意这里的第1行和第5行的(a, 1),它们的序号都是1;
  • 第3行(b, 3)是在原位上的
  • 倒数第1行和倒数第3行的(b, 2) 沿用倒数第3行的序号
  • 倒数第2行的序号6的意思是:从头开始数,第6对互不相同的元组

可以把id_1和id_2理解成年份和月份,想要给每个年月按出现顺序标上序号的意思。

分析思路

其实这个需求上的说明文字已经把算法过程写明了。

第n行序号是a,第n+1行如果在之前没出现过 则它的序号是a+1,不然就是之前相同那行的序号。

可是要用SQL语言怎么实现呢,同时还要考虑每个SQL方言对语句的不同实现

解题过程

这里提供一种解法,它在SQL Server 2017上测试通过

给每一行添加「行号」这个列

有了原始行号,不论如何打乱,也能通过对该列排序来还原原始顺序。
主要目的还是为了解决SQL方言对distinctgroup by等语句实现不一致的问题

可以用row_number()函数快速完成。

ROW_NUMBER() 在SQL Server中的这个函数简单用法如下

ROW_NUMBER ( )
  OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

比如:
row_number() over (partition by column_a, column_b order by column_c)

意思是按照A列进行分组,然后按B列排序。
结果便是A列具有相同值的会在同一组,它们从1开始计数,同一组里的按照B排序。

这个order by在SQL Server里是必须有的,不然会报错。
如果partition by写了两个列,则意思是这两列同时做计算,两列中有不相同的都算不同的组.

Query:

1
select row_number() over (order by (select 0)) as rn, * from table1

order by可以给一个空的值,0可以 1也可以 null也可以,这样就不会排序,维持顺序了。

Results:

rn id_1 id_2 some_value
1 a 1 36
2 a 2 36
3 b 3 45
4 a 4 65
5 a 1 75
6 b 2 35
7 b 5 35
8 b 2 76

计算互相独立的列

使用SQL语言提供的distinct关键字,可以很容易地排除相同的行。

Query:

1
select distinct id_1, id_2 from table1

Results:

id_1 id_2
a 1
a 2
a 4
b 2
b 3
b 5

可是也能看到几个不足

  1. SQL Server擅自给结果排序了,这是我们不想要的。

    通过其他额外对比还能发现不同SQL方言对distinct的实现略有不同,将会在🐛段继续讨论

  2. 无法通过在select上添加新的列名来给这个查询添加新的结果列。

    如果那么做的话,distinct的结果将不是我们想要的。将在下一段讨论解决方案

给不重复的行添加编号

这其实是个简单的查询工作,对每一个唯一行,找出最小的原始行号。
如果distinct出来的结果没有被额外排序,我们只需要添加row_number即可,可是现实并没有这么丰满🌚

可以使用outer apply语句

Query:

1
2
3
4
5
6
7
8
9
10
11
12
with row_order_tbl as ( select row_number() over (order by ( select 1 )) as original_row_order
, id_1, id_2, some_value
from table1 )
, distinct_tbl as ( select distinct id_1, id_2
from table1 )
select t2.original_row_order, t1.id_1, t1.id_2, t2.some_value
from distinct_tbl t1
outer apply ( select top 1 *
from row_order_tbl t2
where t1.id_1 = t2.id_1
and t1.id_2 = t2.id_2 ) t2
order by t2.original_row_order

这里使用了with语句引用前面计算的临时表。

outer apply 里的意思很直白,从带有行号的表中 找出第一个(top 1)两列相同的行 合并进去。

如果方言不支持这种语法,也可以用left join完成…

注意: top 1 后面的 *,它的意思是取t2中的所有列,写成select top 1 id_1, id_2 from t2就好理解了

Results:

original_row_order id_1 id_2 some_value
1 a 1 36
2 a 2 36
3 b 3 45
4 a 4 65
6 b 2 35
7 b 5 35

有了这个就快接近我们期望的结果啦,可是注意到了没有,这里需要中少了个5呢。

原因是第5行和第一行重复,数字就没了。

那咋办啊,大佬你到你会不会呀.jpg

把序号摆正

稍微有点常识的人都知道🌚,这时候只需要再来一个row_number完事。

就酱做……

Query:

1
2
3
4
5
6
7
8
9
10
11
12
13
with row_order_tbl as ( select row_number() over (order by ( select 1 )) as original_row_order, id_1, id_2, some_value
from table1 )
, distinct_tbl as ( select distinct id_1, id_2
from table1 )
, new_order_tbl as ( select t2.original_row_order, t1.id_1, t1.id_2, t2.some_value
from distinct_tbl t1
outer apply ( select top 1 *
from row_order_tbl t2
where t1.id_1 = t2.id_1
and t1.id_2 = t2.id_2 ) t2 )
select row_number() over (order by original_row_order) as ordered_number
, id_1, id_2, some_value
from new_order_tbl

因为在的后面有个order by了上面一层的排序就不需要了…

Results:

ordered_number id_1 id_2 some_value
1 a 1 36
2 a 2 36
3 b 3 45
4 a 4 65
5 b 2 35
6 b 5 35

很好😆参照表已经做好啦,接下来一行行map即可

大功告成

把原始行对应的新值加上即可啦

Query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with row_order_tbl as ( select row_number() over (order by ( select 1 )) as original_row_order, id_1, id_2, some_value
from table1 )
, distinct_tbl as ( select distinct id_1, id_2
from table1 )
, new_order_tbl as ( select t2.original_row_order, t1.id_1, t1.id_2, t2.some_value
from distinct_tbl t1
outer apply ( select top 1 *
from row_order_tbl t2
where t1.id_1 = t2.id_1
and t1.id_2 = t2.id_2 ) t2 )
, final_tbl as ( select row_number() over (order by original_row_order) as ordered_number, id_1, id_2, some_value
from new_order_tbl )
select final_tbl.ordered_number, table1.id_1, table1.id_2, table1.some_value
from table1
left join final_tbl on table1.id_1 = final_tbl.id_1 and table1.id_2 = final_tbl.id_2;

Results:

ordered_number id_1 id_2 some_value
1 a 1 36
2 a 2 36
3 b 3 45
4 a 4 65
1 a 1 75
5 b 2 35
6 b 5 35
5 b 2 76

👏めでたしめでたし

恼人的🐛

Hive中的row_number()函数

Hive分析函数row_number() Hive中的这个函数简单用法如下

ROW_NUMBER() OVER (PARTITION BY COLUMN_A ORDER BY COLUMN_B ASC/DESC)

意思是按照A列进行分组,然后按B列排序。
结果便是A列具有相同值的会在同一组,它们从1开始计数,同一组里的按照B排序。

这个order by在SQL Server里是必须有的,不然会报错。
如果partition by写了两个列,则意思是这两列同时做计算,两列中有不相同的都算不同的组.

Query:

1
2
select row_number() over (partition by id_1, id_2 order by (select 0)) as rn, *
from table1;

order by可以给一个空的值,0可以 1也可以 null也可以,这样就不会排序,维持顺序了。

Results:

rn id_1 id_2 some_value
1 a 1 36
2 a 1 75
1 a 2 36
1 a 4 65
1 b 2 76
2 b 2 35
1 b 3 45
1 b 5 35

不一样的Distinct

不同SQL方言对 distinct 的实现还会有不同。
我测试的主要是SQL Server和Hive,它们在返回顺序上不同。

SQL Server会擅自排序,而Hive不会(至少在小数据量上没出现顺序的改变)。

同时不要弄混 distinctunique

  • distinct 是在获取数据的时候,期望获得不重复的行
  • unique 是在添加数据的时候,确保数据库中的行总是唯一的,给表添加了限制(constraint)
  • select distinct 是标准SQL语法,被普遍支持。
  • select unique 是老旧的语法,Oracle还在使用。

Hive 2.3 中奇怪的倒序

在Hive2.3中,不论是row_number还是dense_rank,返回结果的编号总是倒序的…
不管加没加asc/desc

这不是一个order by能override掉的…

很迷 没能解决🤷

题外话

其实这是在果果弄错了一个需求,在错误的基础上费劲做了两天找到的解决方案。🤦还都得怪自己的半吊子英语

需求原文是这样的

Numbering sequentially if columns id_1 and id_2 are the same

也就是说当id_1和id_2相同的时候,顺序编号

结果就是这样:

ordered_number id_1 id_2 some_value
1 a 1 36
1 a 2 36
1 b 3 45
1 a 4 65
2 a 1 75
1 b 2 35
1 b 5 35
2 b 2 76

其实就是个rank()能解决的🌚

Reference

Hive分析函数row_number()

What is the difference between distinct and unique in terms of SQL?