按分组列值的变化对数据进行分组
发布于 2021-05-10 20:32:31
带有以下数据
create table #ph (product int, [date] date, price int)
insert into #ph select 1, '20120101', 1
insert into #ph select 1, '20120102', 1
insert into #ph select 1, '20120103', 1
insert into #ph select 1, '20120104', 1
insert into #ph select 1, '20120105', 2
insert into #ph select 1, '20120106', 2
insert into #ph select 1, '20120107', 2
insert into #ph select 1, '20120108', 2
insert into #ph select 1, '20120109', 1
insert into #ph select 1, '20120110', 1
insert into #ph select 1, '20120111', 1
insert into #ph select 1, '20120112', 1
我想产生以下输出:
product | date_from | date_to | price
1 | 20120101 | 20120105 | 1
1 | 20120105 | 20120109 | 2
1 | 20120109 | 20120112 | 1
如果我按价格分组并显示最大日期和最小日期,那么我将得到以下不是我想要的内容(请参见重叠的日期)。
product | date_from | date_to | price
1 | 20120101 | 20120112 | 1
1 | 20120105 | 20120108 | 2
因此,基本上我想做的是根据组列产品和价格对数据进行逐步更改。
什么是最干净的方法来做到这一点?
关注者
0
被浏览
113
1 个回答
-
有一种(或多或少)解决此类问题的已知技术,涉及两个
ROW_NUMBER()
调用,如下所示:WITH marked AS ( SELECT *, grp = ROW_NUMBER() OVER (PARTITION BY product ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY product, price ORDER BY date) FROM #ph ) SELECT product, date_from = MIN(date), date_to = MAX(date), price FROM marked GROUP BY product, price, grp ORDER BY product, MIN(date)
输出:
product date_from date_to price ------- ---------- ------------- ----- 1 2012-01-01 2012-01-04 1 1 2012-01-05 2012-01-08 2 1 2012-01-09 2012-01-12 1