sql 2000 数据汇总 例1

----问题

下单表字段(数据很多):
自动生成(ID) 名称(Product) 时间(dealDateTime) 价格(Price) 数量(Quantity)

要求返回某一产品的在指定时间间隔(如1分钟)内的平均价,数量交易差
参数:名称,时间间隔(单位分钟)
要法返回结果如下
名称(Product) 时间 平均价 数量交易差

平均价的算法是 符合某种产中的时间间隔内的价格(Price)平均值
数量交易差算法是 时间间隔内最后的一笔单减去第一笔单

1MDJ2007-02-01 23:35:35.0001.001
2MDJ2007-02-01 23:35:03.0003.007
3MDJ2007-02-01 23:36:07.0001.003
4MDJ2007-02-01 23:38:07.0005.002
5MDJ2007-02-01 23:40:08.0001.002
6MDJ2007-02-01 23:40:08.0001.004

型号为MDJ的产品在 23:35 到 23:40 之间有6条数据,现在假设以每一分钟(时间间隔为1分钟)计算一次平均值和数量差,指定时间从23:35开始,23:39结束,那么第一分钟从23:35开始计算,符合记录的有两条,平均值是2 ((1+3)/2) ,数量差是6 (时间间隔内最后一条数量:7-第一条数量:1),然后加上时间间隔计算下一个结果,那么下一分钟就是要计算36到37分钟之间的数据......一直计算到指定的结束时间,如果在某一分钟没有数据,根据业务,则应该显示0,如果在某一分钟内只有一条记录，则数量差为该记录的数量

最后的返回结果应该是
 
 MDJ  2007-02-01 23:35:00  2  6
 MDJ  2007-02-01 23:36:00  1  3
 MDJ  2007-02-01 23:37:00  0  0
 MDJ  2007-02-01 23:38:00  5  2 

----------------------------------------------------------------------------------------------------------------------------------------

--建测试环境
IF OBJECT_ID(N'Test') > 0
  DROP TABLE Test
GO

CREATE TABLE Test(ID int identity(1,1),Product Varchar(100),dealDateTime datetime,Price INT,Quantity INT)
INSERT INTO Test(Product,dealDateTime,Price,Quantity)
SELECT 'MDJ','2007-02-01 23:35:35',0001,001
UNION All SELECT 'MDJ','2007-02-01 23:35:03',0003,007
UNION All SELECT 'MDJ','2007-02-01 23:36:07',0001,003
UNION All SELECT 'MDJ','2007-02-01 23:38:07',0005,002
UNION All SELECT 'MDJ','2007-02-01 23:40:08',0001,002
UNION All SELECT 'MDJ','2007-02-01 23:40:08',0001,004
GO

--建函数
IF OBJECT_ID(N'f_Test') > 0
  DROP FUNCTION f_Test
GO

CREATE FUNCTION f_Test(@sProduct VARCHAR(100),@bDATETIME DATETIME,@eDATETIME DATETIME)
RETURNS @tbTestLIST TABLE(Product VARCHAR(100),dealDateTime CHAR(16),Price INT,Quantity INT)
AS
BEGIN

  DECLARE @sFlag DATETIME

  SELECT @sFlag = @bDATETIME
  WHILE @sFlag < @eDATETIME BEGIN
    INSERT @tbTestLIST(Product,dealDateTime,Price,Quantity) SELECT @sProduct,convert(CHAR(16),@sFlag,120),0,0
    SELECT @sFlag = dateadd(MINUTE,1,@sFlag)
  END;

  UPDATE a set a.Product = c.Product,a.Price = c.Price,a.Quantity = c.Quantity
  FROM @tbTestLIST a LEFT OUTER JOIN
  (SELECT Product = MAX(Product),dealDateTime = convert(CHAR(16),dealDateTime,120),Price = avg(Price),
     Quantity =
     CASE WHEN COUNT(1) >1 THEN (SELECT Quantity FROM Test WHERE ID = MAX(a.ID))-(SELECT Quantity FROM Test WHERE ID = MIN(a.id))
       ELSE (SELECT Quantity FROM Test WHERE ID = MAX(a.ID)) END
   FROM Test a
   WHERE Product = @sProduct AND dealDateTime BETWEEN @bDATETIME AND @eDATETIME
   GROUP BY convert(CHAR(16),dealDateTime,120)) c ON a.dealDateTime = c.dealDateTime

  RETURN

END

GO
--执行
SELECT Product = 'MDJ',dealDateTime,Price = ISNULL(Price,0),Quantity = ISNULL(Quantity,0)
from f_Test('MDJ','2007-02-01 23:35:00','2007-02-01 23:39:00')

--删除测试环境
DROP TABLE Test
DROP FUNCTION f_Test