这个sp该怎么写效率才高
下单表字段(数据很多):
自动生成(ID)   名称(Product)   时间(dealDateTime)   价格(Price)   数量(Quantity)


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

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

不知道是否能描述清楚,谢谢帮忙........




------解决方案--------------------
Create Table Test(ID int identity(1,1),Product Varchar(100),dealDateTime Smalldatetime,Price Dec(9,4),Quantity Dec(9,2))
Insert Into Test(Product,dealDateTime,Price,Quantity)
select 'aa ', '2007-03-09 11:30:00 ',1.00,2
Union All Select 'aa ', '2007-03-09 11:30:00 ',2.00,2
Union All Select 'aa ', '2007-03-09 11:30:00 ',3.00,3
Union All Select 'aa ', '2007-03-09 11:30:00 ',2.00,4
Union All Select 'bb ', '2007-03-09 11:30:00 ',15.00,10
Union All Select 'bb ', '2007-03-09 11:30:00 ',10.00,20


Select Product,dealDateTime,Price=avg(Price),
Quantity=(Select Quantity From Test Where ID = Max(a.ID))-(Select Quantity From Test Where ID = Min(a.id))
From Test a Group By Product,dealDateTime


Drop Table Test
------解决方案--------------------

--建测试环境
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