SqlServer 2005 T-SQL Query 学习笔记(10)
作者在这篇文章里再次展示了分组因数的使用技巧,上次是在NUM数组里取存在的范围,现在这个例子则显得更加贴近实际。
有下面的数据集:
dt | price |
2006-08-01 00:00:00.000 | 13 |
2006-08-02 00:00:00.000 | 14 |
2006-08-03 00:00:00.000 | 17 |
… | … |
2006-08-15 00:00:00.000 | 60 |
2006-08-16 00:00:00.000 | 60 |
2006-08-17 00:00:00.000 | 55 |
… | … |
2006-08-31 00:00:00.000 | 10 |
现在想把数据集的数据进行分组,按price=50作为中线,统计大于50的连续天数,开始日期,结束日期,和最大价格。
那么,统计后的结果应该如下所示:
start_range | end_range | numberdays | maxprice |
2006-08-06 00:00:00.000 | 2006-08-09 00:00:00.000 | 4 | 70 |
2006-08-15 00:00:00.000 | 2006-08-19 00:00:00.000 | 5 | 60 |
2006-08-25 00:00:00.000 | 2006-08-28 00:00:00.000 | 4 | 70 |
按照分组因数的思想,我们应该这样引入分组因数,步骤见下:
1.首先筛选出 price > 50 的结果:
2006-08-06 00:00:00.000 52
2006-08-07 00:00:00.000 56
2006-08-08 00:00:00.000 60
2006-08-09 00:00:00.000 70
2006-08-15 00:00:00.000 60
2006-08-16 00:00:00.000 60
2006-08-17 00:00:00.000 55
2006-08-18 00:00:00.000 60
2006-08-19 00:00:00.000 60
2006-08-25 00:00:00.000 60
2006-08-26 00:00:00.000 60
2006-08-27 00:00:00.000 70
2006-08-28 00:00:00.000 70
2.加入分组因数:
2006-08-06 00:00:00.000 2006-08-09 00:00:00.000 52
2006-08-07 00:00:00.000 2006-08-09 00:00:00.000 56
2006-08-08 00:00:00.000 2006-08-09 00:00:00.000 60
2006-08-09 00:00:00.000 2006-08-09 00:00:00.000 70
2006-08-15 00:00:00.000 2006-08-19 00:00:00.000 60
2006-08-16 00:00:00.000 2006-08-19 00:00:00.000 60
2006-08-17 00:00:00.000 2006-08-19 00:00:00.000 55
2006-08-18 00:00:00.000 2006-08-19 00:00:00.000 60
2006-08-19 00:00:00.000 2006-08-19 00:00:00.000 60
2006-08-25 00:00:00.000 2006-08-28 00:00:00.000 60
2006-08-26 00:00:00.000 2006-08-28 00:00:00.000 60
2006-08-27 00:00:00.000 2006-08-28 00:00:00.000 70
2006-08-28 00:00:00.000 2006-08-28 00:00:00.000 70
3.对带有分组因数的结果集进行分组,选取每组的最大,最小日期,最大的价格,目的达成。
源码如下:
with T1 AS ( select dt, -- (select min(dt) from stocks as b where b.dt >= a.dt and b.price > 50 --找到间断的日期组的最大的那个值,即中断点。 and not exists ( --找到不间断的日期组。 select * from stocks as c where b.dt = c.dt - 1 and c.price > 50 ) ) as factor , price from stocks as a where price > 50 ) select min(dt) as startrange,max(dt) as endrange,max(price),datediff(day,min(dt),max(dt))+1 from T1 group by factor ;
上为方法一。
在方法一中,可以看见在筛选临界点的时候非常麻烦,用了个Not exists来进行筛选。但是,因为用price<50作为断点条件,所以,只要在每条记录里,查询出所有大于dt且price<50的日期,再取这些日期的最小值,即为这个dt的分组因数。 即为方法二。
方法二代码如下:
SELECT MIN(dt) AS startrange, MAX(dt) AS endrange, DATEDIFF(day, MIN(dt), MAX(dt)) + 1 AS numdays, MAX(price) AS maxprice FROM (SELECT dt, price, (SELECT MIN(dt) FROM dbo.Stocks AS S2 WHERE S2.dt > S1.dt AND price < 50) AS grp FROM dbo.Stocks AS S1 WHERE price >= 50) AS D GROUP BY grp;
SELECT MIN(dt) AS startrange, MAX(dt) AS endrange, DATEDIFF(day, MIN(dt), MAX(dt)) + 1 AS numdays, MAX(price) AS maxprice FROM (SELECT dt, price, dt - ROW_NUMBER() OVER(ORDER BY dt) AS grp FROM dbo.Stocks AS S1 WHERE price >= 50) AS D GROUP BY grp;
文章告诉了我们一个常用的技巧:当遇见分类,差异统计等问题时,我们可以考虑人为的引入一个分组因数来简化计算。
同时,作者还透露了一种思考的方法,即从一个事物的正反2方面进行连接思考,通过正,反的一个组合连接,得到了我们想要的正面结果,正如方法2。