这个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
