按组排名的SQL Server

Question

这个问题看起来很简单，但我无法理解它。 这是针对SQL Server的。

 what I have in a table :            What I need as a output .
 cksum          id                      cksum       id
-2162514679     204                    -2162514679  204    1
-2162514679     207                    -2162514679  207    1
-2162514679     215                    -2162514679  215    1
-2162514679     218                    -2162514679  218    1
-2162514679     221                    -2162514679  221    1
-2160286363     257                    -2160286363  257    2
-2160286363     260                    -2160286363  260    2
-2160286363     332                    -2160286363  332    2
-2162514679     335                    -2162514679  335    3
-2162514679     338                    -2162514679  338    3
-2126731931     348                    -2126731931  348    4
-2126731931     387                    -2126731931  387    4

表格按id排序，我需要一个紧随id列但分组cksum的排名，注意，由于ID的存在，cksum可能返回到先前的值，但仍具有其排名（对于值2162514679而言，它在开头出现了5次，下面又出现了一次，它们构成了两个不同的排名）。我已经花了几个小时去做这件事情，使用row_number进行分区或使用CTE似乎很愚蠢，但是没有找到实现此操作的逻辑...有人知道答案吗？

- Flying Turtle

---

你正在使用哪个 SQL Server 版本？ - Kamran Shahid

我正在使用 SQL SERVER 2008 R2。 - Flying Turtle

Answer 1

这有点棘手。您可以使用一个技巧来获取ID的分组--行号之间的差异。然后，您需要获取每个组的最小ID，以确保最终排名是正确的顺序。然后，您可以使用密集排名：

select cksum, id, dense_rank() over (order by minid)
from (select t.*, min(id) over (partition by cksum, grp) as minid
      from (select t.*,
                   (row_number() over (order by id) -
                    row_number() over (partition by cksum order by id)
                   ) as grp
            from table t
           ) t
     ) t;

- Gordon Linoff

---

我认为你第二次调用 row_number() 函数时缺少了 order by。 - DrCopyPaste

这个解决方案非常有前途，但我遇到的问题是这一行row_number() over (partition by cksum Order by id)它不会重置计数器，而是从上一个值结束的地方继续，因此有时会按顺序切换，从而不遵循id顺序列。如果我能够为每个分区将其重置为零，那么它就可以工作了。 - Flying Turtle

@FlyingTurtle……我不理解你的评论。（请注意：我刚刚用正确的“order by”修复了查询。）开关是无关紧要的，因为差异 - 以及cksum - 唯一地标识了每个组。然后使用min(id)进行最终排名。 - Gordon Linoff

当您分解 grp 列的计算时，这是我得到的结果：cksum 1RN 2RN grp -1 1 1 0 -1 2 2 0 -1 3 3 0 -2 4 1 3 -2 5 2 3 -1 6 4 2 -1 7 5 2 -1 8 6 2 如您所见，第一组的第二个 RN 值从第一个 RN 值停止的地方（在 4 处）开始，这会导致 grp 错误（同一 cksum 的风险相同 grp 和错误排名）。 - Flying Turtle

@FlyingTurtle . . . 中间子查询中的 partition by 使用了 cksum。 对于不同的 cksum 值，等效的 grp 值是无关紧要的。 该解决方案假定 id 是唯一的。 - Gordon Linoff

抱歉，一开始我没有理解你的方法，但是应用你的解决方案时，同一组或组排名并不重要。因为你是第一个给出解决方案的，并且你的解决方案似乎更加优化，所以我投了你一票。 - Flying Turtle

Answer 2

这是一种不同的方法，涉及模拟SQL Server 2008 R2中不可用的LAG窗口函数：

;WITH CTE_RN AS (
   SELECT cksum, id, ROW_NUMBER() OVER(ORDER BY id) AS rn
   FROM Checksums
), CTE_LAG AS (
   SELECT c1.cksum, c1.id, c1.rn,
          (CASE WHEN c2.cksum IS NULL OR c1.cksum = c2.cksum THEN 0
                ELSE 1
           END) AS flag 
   FROM CTE_RN AS c1
   LEFT JOIN CTE_RN AS c2 ON c1.rn = c2.rn+1
)
SELECT cksum, id,  (SELECT SUM(flag)
                    FROM CTE_LAG AS t2        
                    WHERE t2.rn <= t1.rn) + 1 AS [rank]     
FROM CTE_LAG AS t1

< p > CTE_LAG 返回以下结果集（基于 OP 的样本数据）：

cksum       id  rn  flag
-------------------------
-2162514679 204 1   0
-2162514679 207 2   0
-2162514679 215 3   0
-2162514679 218 4   0
-2162514679 221 5   0
-2160286363 257 6   1
-2160286363 260 7   0
-2160286363 332 8   0
-2162514679 335 9   1
-2162514679 338 10  0
-2126731931 348 11  1
-2126731931 387 12  0

如果当前的cksum与之前的cksum不相等，则字段flag等于1，否则flag等于0。

字段rank是flag的累计总和。

- Giorgos Betsos

---

它有效了，真的很喜欢找到解决方案的思路，非常感谢！ - Flying Turtle