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

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

¶问题描述

这里有个很奇特的需求：

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

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

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

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方言对distinct和group 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

可是也能看到几个不足

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

通过其他额外对比还能发现不同SQL方言对distinct的实现略有不同，将会在🐛段继续讨论。
无法通过在select上添加新的列名来给这个查询添加新的结果列。

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

¶给不重复的行添加编号

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

可以使用outer apply语句

Query:

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:

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:

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不会（至少在小数据量上没出现顺序的改变）。

同时不要弄混 distinct 和 unique 呀

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?

¶问题描述

¶分析思路

¶解题过程

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

¶计算互相独立的列

¶给不重复的行添加编号

¶把序号摆正

¶大功告成

¶恼人的🐛

¶Hive中的row_number()函数

¶不一样的Distinct

¶Hive 2.3 中奇怪的倒序

¶题外话

¶Reference

¶Hive中的`row_number()`函数