代码之家 › 专栏 › 技术社区 › CuriousDeveloper

密级更改时按顺序断列

dense-rank tsql sql-server sql

CuriousDeveloper · 技术社区 · 6 年前

这个 grp GroupId, RouteId 并下令 LengthStart 如果locationId保持不变 玻璃钢 是一样的。序列中的中断将创建一个新的 玻璃钢

+---------+----------+--------------+------------+-------------+------+
| GROUPID |  ROUTEID |  LENGTHSTART |  LENGTHEND |  LOCATIONID |  GRP |
+---------+----------+--------------+------------+-------------+------+
|     1   | A        | 0            | 1          | 1           | 1    |
|     1   | A        | 1            | 2          | 1           | 1    |
|     1   | A        | 2            | 3          | 2           | 2    |
|     1   | A        | 3            | 4          | 1           | 3    |
|     2   | A        | 2            | 3          | 2           | 4    |
|     1   | B        | 2            | 3          | 2           | 5    |
|     1   | A        | 4            | 5          | 1           | 3    |
+---------+----------+--------------+------------+-------------+------+

我对这个问题的研究使我找到了这个解决方案: DENSE_RANK according to particular order

玻璃钢 计算:

SELECT *, ROW_NUMBER() OVER (ORDER BY GroupId, RouteId, LengthStart) - ROW_NUMBER() OVER (PARTITION BY GroupId, RouteId, LocationId ORDER BY GroupId, RouteId, LengthStart) AS grp
FROM mytable

我尝试采用这种解决方案,这样我就可以有更多级别的分区,并且它可以在真正基本的用例中工作(就像上面展示的那样,但是在复杂的场景中它失败了)。

我真的不完全理解为什么两行数()是相减的,以及它是如何工作的,但是在这个简单的例子中它工作得非常好。

LAG 但就是不懂得如何运用逻辑。

下面是一个更复杂的场景:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3704dfe8583b0dd020b189184d149cb7

你可以看到我在这里看到的许多错误之一:

2 回复 | 直到 6 年前

MatBailie 6 年前

这看起来是一个空白和岛屿的问题,你已经错误地理解了解决方案。

在这种情况下 grp 列本身并不能识别一个缺口或孤岛,它只是给你 额外的 识别他们所需的信息。

(locationId, grp) 形成新的“子组”标识符。

我也会稍微改变你的问题,让你更容易理解发生了什么。。。

SELECT
  *,
    ROW_NUMBER() OVER (PARTITION BY GroupID, RouteID             ORDER BY LengthStart)
  - ROW_NUMBER() OVER (PARTITION BY GroupId, RouteId, LocationId ORDER BY LengthStart) AS grp
FROM
  mytable
ORDER BY
  GroupId, RouteId, LengthStart

GroupID, RouteID 有 (分组) .

然后我可以举一个简单的例子。。。

  LocationID, Start, SetRowNum, LocRowNum, Difference, GroupID

      1        000       1       1          0          (1,0)
      1        100       2       2          0          (1,0)

      2        200       3         1          2        (2,2)
      2        300       4         2          2        (2,2)

      1        400       5       3          2          (1,2)

      2        500       6         3          3        (2,3)

      3        600       7           1          6      (3,6)

      2        700       8         4          4        (2,4)
      2        800       9         5          4        (2,4)

island 为每个 LocationID 分别地 .

A gap 只是任何一排其他的 .

其中的“诀窍”是,在每个岛上,两个行数同时增加。两者加在一起,差别保持不变。这种差异就是 island_id .

岛id **为了这个 .

岛id 对于 location_1 所有其他位置 作为 gaps 地点1的岛屿之间。

对于位置1,我们在第二排有岛 1,2 和 5 IslandID 0 和 2 分别。

  LocationID, Start, SetRowNum, LocRowNum, Difference, GROUP_ID

      1        000       1       1          0          (1,0)
      1        100       2       2          0          (1,0)

      GAP

      1        400       5       3          2          (1,2)

3,4 , 6 和 8,9 属于 2 , 3 4 分别。

  LocationID, Start, SetRowNum, LocRowNum, Difference

     GAP

      2        200       3         1          2        (2,2)
      2        300       4         2          2        (2,2)

     GAP

      2        500       6         3          3        (2,3)

     GAP

      2        700       8         4          4        (2,4)
      2        800       9         5          4        (2,4)

对于位置3,我们在世界其他地区有岛屿 7 具有 岛 属于 6 .

  LocationID, Start, SetRowNum, LocRowNum, Difference

     GAP

      3        600       7           1          6      (3,6)

     GAP

总之,这些岛屿都有不同的身份证。

(locationID, IslandID) 在一起
复合密钥对于该分区是唯一的

Adrian Maxwell 6 年前

通过改变over子句的参数会产生不同的结果。注意,不需要按顺序重复用于分区的列。这种技术的概念是计算一个公共值(在列中) grp

SELECT
    *
  , ROW_NUMBER() OVER (ORDER BY GroupId, RouteId, LengthStart) 
  - ROW_NUMBER() OVER (PARTITION BY GroupId, RouteId ORDER BY LengthStart ) AS grp
FROM mytable
ORDER BY
    GroupId
  , RouteId
  , LengthStart

输出两行数字计算结果非常有用,这样您就可以看到它是如何工作的:

    Id      GroupId   RouteId   LengthStart   LengthEnd   LocationId   rn1   rn2   grp  
 --------- --------- --------- ------------- ----------- ------------ ----- ----- ----- 
  2651246         3   AAA         0.0000000   0.0920000      1884268     1     1     0  
  2651247         3   AAA         0.0920000   0.5800000      1855305     2     2     0  
  2651248         3   AAA         0.5800000   1.3610000      1884268     3     3     0  
  2651249         3   AAA         1.3610000   1.6170000      1884268     4     4     0  
  2651250         3   AAA         1.6170000   2.3750000      1884268     5     5     0  
  2681493         3   BBB         0.0000000   1.5600000      1864963     6     1     5  
  2681494         3   BBB         1.5600000   2.7100000      1864963     7     2     5  
  2681495         3   BBB         2.7100000   3.3900000      1864963     8     3     5  
  2954915         3   CCC         0.0000000   0.0500000      1883382     9     1     8  
  2954916         3   CCC         0.0500000   0.1400000      1846300    10     2     8  
  â¦

这个 rn1 rn2 从 rn1号 你会得到一个“常数”的结果对于rn2中使用的每个分区