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;

 

方法三，采用row_number()函数作为解决方案。

思想：把每行数据按顺序进行编号，每行用dt减去row_number()的编号生成新的一列，这一新的列就正好是分组因数列。这个方法在原先的“存在的范围”的例子也介绍过，算温故一遍吧。

 

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。

 

Technorati 标签: sql2005,t-sql,query