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 做排序相减就可以得到新的分组列,我们就知道按照这个列去得到我们要的结果了

巴特西

sql 连续分组判断 partition by

最新文章

热门文章