sql 差值_SQL 去掉分割符并重排序

这是群友在工作中碰到的一个需求。有一张表 x (ID,NAME)，数据如下图中的左半部分，他希望通过 SQL 获取到图中右半部分呈现的结果。

左表变换成右表的规则如下：

1. 去掉 NAME 列中的“/”，保持 NAME 列中的数据的相对顺序不变；
2. 重新生成序号（即 ID 列），序号从 1 开始递增；
3. 生成 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() 生成了组号。