时间戳

首页 > 解决方案 > 密集排名，按 A 列分区，按 B 列更改递增，但按 C 列排序

sql - 密集排名，按 A 列分区，按 B 列更改递增，但按 C 列排序

问题描述

我有一张这样的桌子

name|subtitle|date
ABC|excel|2018-07-07
ABC|excel|2018-08-08
ABC|ppt|2018-09-09
ABC|ppt|2018-10-10
ABC|excel|2018-11-11
ABC|ppt|2018-12-12
DEF|ppt|2018-12-31

我想添加一个在字幕发生变化时递增的列，如下所示：

name|subtitle|date|Group_Number
ABC|excel|2018-07-07|1
ABC|excel|2018-08-08|1
ABC|ppt|2018-09-09|2
ABC|ppt|2018-10-10|2
ABC|excel|2018-11-11|3
ABC|ppt|2018-12-12|4
DEF|ppt|2018-12-31|1

问题是如果我做 Dense_rank() over(partition by name order by subtitle) 那么不仅会将所有字幕分组到一个组中，而且还会删除日期排序。我也尝试过使用 lag 函数，但是当您尝试增加列时，这似乎不是很有用。

有没有一种简单的方法可以实现这一目标？

请记住，我使用的表有数百个不同的名称。

标签： sqlsql-serverssmsgaps-and-islands

解决方案

快速回答

declare @table table (name varchar(20),subtitle varchar(20),[date] date )

insert into @table (name,subtitle,date)
values
('ABC','excel','2018-07-07'),
('ABC','excel','2018-08-08'),
('ABC','ppt','2018-09-09'),
('ABC','ppt','2018-10-10'),
('ABC','excel','2018-11-11'),
('ABC','ppt','2018-12-12'),
('DEF','ppt','2018-12-31');

with nums as (

    select *,  
         case when subtitle != lag(subtitle,1) over (partition by name order by date) 
              then 1 
              else 0 end as num
    from @table
)
select *,
    1+sum(num) over (partition by name order by date) AS Group_Number
from nums

解释

你问的并不完全是排名。您正试图在严格按日期排序的序列中检测名称和副标题相同的“岛屿” 。

为此，您可以将当前行的值与前一行进行比较。如果它们匹配，那么您就在同一个“岛”中。如果没有，有一个开关。您可以使用它来发出例如1每次检测到更改时。

就是这样：

CASE WHEN subtitle != LAG(subtitle,1) OVER (PARTITION BY name ORDER BY date) 
     THEN 1

一旦你有了它，你就可以计算出变化的总数：

sum(num) over (partition by name order by date) AS Group_Number

这将生成从 0 开始的值。要获取从 1 开始的数字，只需加 1：

1+sum(num) over (partition by name order by date) AS Group_Number

更新

正如 T. Clausen 在评论中解释的那样，颠倒比较将摆脱+1：

with nums as (

    select *,  
         case when subtitle = lag(subtitle,1) over (partition by name order by date) 
              then 0 
              else 1 end as num
    from @table
)
select *,
    sum(num) over (partition by name order by date) AS Group_Number
from nums

这也是检测岛屿的更好方法，即使这种情况下的结果是相同的。第一个查询会产生这个结果：

name    subtitle    date    num Group_Number
ABC     excel   2018-07-07  0   1
ABC     excel   2018-08-08  0   1
ABC     ppt     2018-09-09  1   2
ABC     ppt     2018-10-10  0   2
ABC     excel   2018-11-11  1   3
ABC     ppt     2018-12-12  1   4
DEF     ppt     2018-12-31  0   1

当检测到除边界以外1的字幕中断时，将发出查询。

第二个查询返回：

name    subtitle    date    num Group_Number
ABC     excel   2018-07-07  1   1
ABC     excel   2018-08-08  0   1
ABC     ppt     2018-09-09  1   2
ABC     ppt     2018-10-10  0   2
ABC     excel   2018-11-11  1   3
ABC     ppt     2018-12-12  1   4
DEF     ppt     2018-12-31  1   1

在这种情况下1，每次更改都会发出，包括边界

推荐阅读