按组排序sql server [英] Rank by groups sql server

问题描述

这个问题看起来很简单，但我无法得到我的头，
这是用于sql服务器

 我在桌上有什么：我需要什么作为输出。 
 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进行分组，注意到ck sum可以返回到先前的值，但由于ID（这是2162514679的值，它在开始时是5倍，而第二次是更多的波纹管，它们是两个不同的等级）。我已经在它几个小时了，它似乎很愚蠢像使用分区的row_number或使用CTE，但没有... ....找不到逻辑peice做这个......任何人都有答案？

解决方案

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

pre $ 选择cksum，id，dense_rank（）over（order by minid）
from（select t。*，
（row_number（）over（order by id））（select t。*，min（id）over（partition by cksum，grp）
row_number（）over（由cksum按id划分的分区）
）as grp
from table t
）t
）t;


The problem seems simple but I can't get my head around it, this is for 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

The table is ordered by id, I need a rank that follows the id column but that groups the cksum, notice that the cksum can return to a previous value yet still have its rank because of the ID (this is the case for the value 2162514679, it apears 5 times at the begining and a second time more bellow and they constitude two different ranks). I've been at it for a couple of hours and it seems really stupid like using a row_number with a partition or using a CTE but nope.... can't find the peice of logic to do this... anyone has the answer?

解决方案

This is a bit tricky. You can get the grouping for the ids with a trick -- a difference of row numbers. Then you need to get the minimum id for each group, to ensure that the final rank is in the right order. Then you can use then you can use dense rank:

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;

这篇关于按组排序sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！