sql 连续分组判断 partition by

partition by 会根据分类字段进行排序 加上rownum 可以形成 每组从1开始重新排序

举个例子, 我要根据时间为依据,连续出现合并为一组,统计每组在区间里的次数

---------------------------------------------------

2010-07-18   2010-07-25   359
2010-06-13   2010-07-11   358
2010-06-06   2010-06-06   359
2010-05-16   2010-05-30   360

---------------------------------------------------

 

可以用以下代码实现

模拟数据

create table x (weekEndDate char(10), storeCount int);
insert into x values
('2010-07-25',359),
('2010-07-18',359),
('2010-07-11',358),
('2010-07-04',358),
('2010-06-27',358),
('2010-06-20',358),
('2010-06-13',358),
('2010-06-06',359),
('2010-05-30',360),
('2010-05-23',360),
('2010-05-16',360);

排序分组语句

select min(weekenddate) as startdate, max(weekenddate) as enddate, min(storecount) as storecount 
from 
(select weekenddate, storecount, concat(row_number() over (order by weekenddate) -row_number() over (partition by storecount order by weekenddate),'|',storecount) as groupkey from x) w
group by groupkey order by startdate desc;

 根据普通排序 order by 与 分区排序 partition by 做排序相减 就可以得到 新的分组列,我们就知道按照这个列去得到我们要的结果了