有以下数据
- create table #ph (product int,[date] date,price int)
- insert into #ph select 1,'20120101',1
- insert into #ph select 1,'20120102','20120103','20120104','20120105',2
- insert into #ph select 1,'20120106','20120107','20120108','20120109','20120110','20120111','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
因此,基本上我希望做的是根据组列产品和价格对数据进行分组更改.
实现这一目标的最简洁方法是什么？
解决方法
有一种(或多或少)已知的解决此类问题的技术,涉及两个
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
