这是群友在工作中碰到的一个需求。有一张表 x (ID,NAME),数据如下图中的左半部分,他希望通过 SQL 获取到图中右半部分呈现的结果。
![3d4076b4c4c605f7b4ef5010a890ed18.png](https://i-blog.csdnimg.cn/blog_migrate/d59994f313e386636ad6b12c2adb59d3.png)
左表变换成右表的规则如下:
- 去掉 NAME 列中的“/”,保持 NAME 列中的数据的相对顺序不变;
- 重新生成序号(即 ID 列),序号从 1 开始递增;
- 生成 NAME 列中每个值对应的组号,被“/”分隔的且序号连续的为一组,组号也是从 1 开始。
右表的 ID 列和 NAME 列的结果很容易转换得到。
SELECT
row_number() over() AS ID,
NAME
FROM
X
WHERE NAME <> '/'
ORDER BY ID
执行结果 >>>
ID name
------ --------
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
怎么知道哪些数据属于同一组呢?去掉“/”后原始序号连续的就是同一组。那怎么用 SQL 来表示呢?
我们先来看原始序号和新序号之间的联系 >>>
name 原始序号 新序号 差值
------ ------ ------ --------
A 2 1 1
B 3 2 1
C 4 3 1
D 6 4 2
E 7 5 2
F 9 6 3
G 10 7 3
H 11 8 3
可以看出,有一批数据的【原始序号-新序号】的结果相同,而这批数据正好是同一组数据。因此,我们可以通过此方法找出每组数据。
需要注意的是,计算出来的差值并不能作为组号,而是要依据差值生成组号。
WITH x1 AS
(SELECT
*,
row_number() over() AS new_id
FROM
x
WHERE NAME <> '/'),
x2 AS
(SELECT
b.new_id AS id,
a.name,
a.id - b.new_id AS margin
FROM
x a
INNER JOIN x1 b
ON b.id = a.id)
SELECT
id,
NAME,
dense_rank() over (ORDER BY margin)
AS `group`
FROM
x2
我们使用窗口函数 dense_rank()
生成了组号。