sql|sql 连续分组判断 partition by sql连续分组判断partitionby

【sql|sql 连续分组判断 partition by】partition by 会根据分类字段进行排序加上rownum 可以形成每组从1开始重新排序
举个例子, 我要根据时间为依据,连续出现合并为一组,统计每组在区间里的次数
---------------------------------------------------
2010-07-182010-07-25359
2010-06-132010-07-11358
2010-06-062010-06-06359
2010-05-162010-05-30360
---------------------------------------------------

可以用以下代码实现
模拟数据

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 做排序相减就可以得到新的分组列,我们就知道按照这个列去得到我们要的结果了
转载于:https://www.cnblogs.com/linyijia/p/11027694.html