sql - 计数表中值的连续出现次数,尽管重复行,但具有新分区计数
问题描述
create table #t_Jenas (Id int, Name char)
insert into #t_Jenas values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B')
对于行号分区,如果重复的行,数字将继续,使得减号列不升序。知道如何使分区号从 1 开始,尽管重复行作为上面的数据?
select name,row_number() over (order by id) as cont ,row_number() over (partition by name order by id) as newcount,( row_number() over (order by id)-row_number() over (partition by name order by id)) as rowminusnewcount --, count(*) as cnt
from #t_Jenas
实际的:
name cont newcount rowminusnewcount
A 1 1 0
A 2 2 0
B 3 1 2
B 4 2 2
B 5 3 2
B 6 4 2
C 7 1 6
B 8 5 3
B 9 6 3
预期的:
name cont newcount rowminusnewcount
A 1 1 0
A 2 2 0
B 3 1 2
B 4 2 2
B 5 3 2
B 6 4 2
C 7 1 6
B 8 1 7
B 9 2 7
解决方案
我的猜测是cont
,newcount
而且rowminusnewcount
都是 row_numbers。我修改id
以展示这个想法。
create table #t_Jenas (Id int, Name char);
insert into #t_Jenas values
(10, 'A'),
(20, 'A'),
(30, 'B'),
(40, 'B'),
(50, 'B'),
(60, 'B'),
(70, 'C'),
(80, 'B'),
(90, 'B');
select name, cont,
row_number() over (partition by grp, name order by id) as new_count,
cont - row_number() over (partition by grp, name order by id) rowminusnewcount
from (
select t.*, row_number() over (order by id) as cont
, row_number() over (order by id) - row_number() over (partition by name order by id) as grp
from #t_Jenas t
) t
order by id
结果
name cont new_count rowminusnewcount
A 1 1 0
A 2 2 0
B 3 1 2
B 4 2 2
B 5 3 2
B 6 4 2
C 7 1 6
B 8 1 7
B 9 2 7
推荐阅读
- c - 如何从 USB 设备名称中获取 USB 端口?
- calculator - 如何在 python 中显示下面的整个根多项式?
- c# - 任何一个键都可以为空的多键字典
- c# - 停止 VSCode 格式化在保存时执行特定的格式化(换行符)
- python - 如何验证电话号码前缀
- python - Dataframe groupby 对不同的列使用不同的函数
- postgresql - PostgresSQL 中的参数嗅探/绑定窥视
- python - /create/ NOT NULL 约束处的 IntegrityError 失败:main_post.author_id
- android - 未从 recyclerview 获取总项目数
- python - 如何将 ANTsR 转换为 ANTsPy 图像查询命令?