时间戳

首页 > 解决方案 > 简短的 SQL Server 排名/分组脚本

sql - 简短的 SQL Server 排名/分组脚本

问题描述

我有一个简单的客户表。

我需要为这些客户分配 groupID，但组中的最大记录数是@groupMaxVariable.

GroupId 是按顺序分配的。如果没有客户有超过 5 条记录，那么客户组只会获得顺序 ID。

寻找简单的东西，而不是一些疯狂的嵌套游标。

create table #tempcust 
(
    id int,
    name nvarchar(50),
    amount nvarchar(50),
    groupid int
)

insert into #tempcust (id,name,amount)    
values
    (1, 'Bob Smith',    '$50.12'),
    (2, 'Bob Smith',    '$12.33' ),
    (3, 'Bob Smith',    '$33.12' ),
    (4, 'Bob Smith',    '$14.86' ),
    (5, 'Bob Smith',    '$6.36' ),
    (6, 'Bob Smith',    '$2.14'),
    (7, 'Bob Smith',    '$10.64'),
    (8, 'Bob Smith',    '$19.14'),
    (9, 'Bob Smith',    '$27.64'),
    (10,    'Bob Smith',    '$36.14'),
    (11,    'Bob Smith',    '$44.64'),
    (12,    'Bob Smith',    '$53.14'),
    (13,    'Jane Doe', '$14.86' ),
    (14,    'Jane Doe', '$6.36'),
    (15,    'Jane Doe', '$2.14'),
    (16,    'Jane Doe', '$10.64'),
    (17,    'Jane Doe', '$19.14'),
    (18,    'Jane Doe','$27.64'),
    (19,    'Jane Doe', '$36.14'),
    (20,    'Kylie Robinson',   '$5.00') ,
    (21,    'Kylie Robinson',   '$6.00' ),
    (22,    'Kylie Robinson',   '$7.00' ),
    (23,    'Kylie Robinson',   '$8.00' ),
    (24,    'Kylie Robinson',   '$9.00' );

查询：

select 
    id, name, amount, groupid,
    dense_rank() over (order by i.name) as Rank  
from 
    #tempcust i

drop table #tempcust

结果：

id          name                                               amount                                             groupid     Rank
----------- -------------------------------------------------- -------------------------------------------------- ----------- --------------------
1           Bob Smith                                          $50.12                                             NULL        1
2           Bob Smith                                          $12.33                                             NULL        1
3           Bob Smith                                          $33.12                                             NULL        1
4           Bob Smith                                          $14.86                                             NULL        1
5           Bob Smith                                          $6.36                                              NULL        1
6           Bob Smith                                          $2.14                                              NULL        1
7           Bob Smith                                          $10.64                                             NULL        1
8           Bob Smith                                          $19.14                                             NULL        1
9           Bob Smith                                          $27.64                                             NULL        1
10          Bob Smith                                          $36.14                                             NULL        1
11          Bob Smith                                          $44.64                                             NULL        1
12          Bob Smith                                          $53.14                                             NULL        1
13          Jane Doe                                           $14.86                                             NULL        2
14          Jane Doe                                           $6.36                                              NULL        2
15          Jane Doe                                           $2.14                                              NULL        2
16          Jane Doe                                           $10.64                                             NULL        2
17          Jane Doe                                           $19.14                                             NULL        2
18          Jane Doe                                           $27.64                                             NULL        2
19          Jane Doe                                           $36.14                                             NULL        2
20          Kylie Robinson                                     $5.00                                              NULL        3
21          Kylie Robinson                                     $6.00                                              NULL        3
22          Kylie Robinson                                     $7.00                                              NULL        3
23          Kylie Robinson                                     $8.00                                              NULL        3
24          Kylie Robinson                                     $9.00                                              NULL        3

(24 row(s) affected)

预期结果

id  Name    Amount  GroupID
1   Bob Smith   $50.12  1
2   Bob Smith   $12.33  1
3   Bob Smith   $33.12  1
4   Bob Smith   $14.86  1
5   Bob Smith   $6.36   1
6   Bob Smith   ($2.14) 2
7   Bob Smith   ($10.64)    2
8   Bob Smith   ($19.14)    2
9   Bob Smith   ($27.64)    2
10  Bob Smith   ($36.14)    2
11  Bob Smith   ($44.64)    3
12  Bob Smith   ($53.14)    3
13  Jane Doe    $14.86  4
14  Jane Doe    $6.36   4
15  Jane Doe    ($2.14) 4
16  Jane Doe    ($10.64)    4
17  Jane Doe    ($19.14)    4
18  Jane Doe    ($27.64)    5
19  Jane Doe    ($36.14)    5
20  Kylie Robinson  $5.00   6
21  Kylie Robinson  $6.00   6
22  Kylie Robinson  $7.00   6
23  Kylie Robinson  $8.00   6
24  Kylie Robinson  $9.00   6

标签： sqlsql-server

解决方案

row_number()在每个名称中，您可以使用和一些算术轻松枚举组。

然后，对于每个组，找到最小 id 并运行dense_rank()在最小 id 上：

select c.*, dense_rank() over (order by min_grp_id) as groupid
from (select c.*, min(id) over (partition by name, within_name_groupId) as min_grp_id
      from (select c.*,
                   lag(name) over (order by name) as prev_name,
                   (1 + (row_number() over (partition by name order by id) - 1) / 5
                   ) as within_name_groupId
            from tempcust c
           ) c
     ) c;

这是一个 db<>fiddle。

推荐阅读