我有一个表填充了在当天(基本上)随机点插入的带时间戳的行.
我需要生成每分钟1行的运行总计(因此,对于一天,总会有正好24 * 60行),例如
Date Quantity Running Total 2009-10-29 06:30 1 1 2009-10-29 06:31 5 6 2009-10-29 06:32 10 16 2009-10-29 06:33 11 27 2009-10-29 06:34 22 49
…
…
有关最佳方法的任何想法吗？
一个SQL查询是理想的但不是必需的,性能相当重要(在包含500k行的表上5秒以下,其中70k对此查询很有意义)
我的最终解决方案(或多或少).
实际情况是这样的.我有两个表,其中包含与Fills表有1：n关系的Orders.
我需要在交易日显示每分钟的运行平均价格和累计总额
DECLARE @StartDate AS DATETIME,@EndDate AS DATETIME
SET @StartDate = '2009-10-28';
SET @EndDate = '2009-10-29';
-- Generate a Temp Table containing all the dates I'm interested in
WITH DateIntervalsCTE AS
(
SELECT 0 i,@StartDate AS Date
UNION ALL
SELECT i + 1,DATEADD(minute,i,@StartDate )
FROM DateIntervalsCTE
WHERE DATEADD(minute,@StartDate ) < @EndDate
)
SELECT DISTINCT Date
INTO #Dates
FROM DateIntervalsCTE
OPTION (MAXRECURSION 32767);
SELECT
d.Date,mo3.symbol,ISNULL(SUM(mf.Quantity),0) AS CumulativeTotal,ROUND(ISNULL(SUM(mf.Quantity * mf.Price)/SUM(mf.Quantity),0),4) AS AveragePrice
FROM
#Dates AS d
CROSS JOIN (
SELECT DISTINCT mo2.Symbol,mo2.OrderID
FROM
Orders AS mo2
INNER JOIN Fills AS mf2 ON mo2.OrderID = mf2.OrderID
WHERE CONVERT(DATETIME,CONVERT(CHAR(10),mf2.FillDate,101)) = @StartDate
) AS mo3
LEFT JOIN Fills AS mf ON mo3.OrderID = mf.OrderID AND CONVERT(DATETIME,CONVERT(CHAR(16),mf.FillDate,120)) < = d.Date
WHERE
d.Date >= DATEADD(mi,390,@StartDate) -- 06:30
AND d.Date <= DATEADD(mi,780,@StartDate) -- 13:00
GROUP BY d.Date,mo3.symbol
ORDER BY mo3.Symbol,d.Date
我还没有完成我的所有测试,但这看起来像诀窍,谢谢你的帮助！
解决方法
确保日期列上有索引,性能应该合理.
SELECT t.Date,COUNT(*) AS Quantity,(SELECT COUNT(*) FROM Table WHERE Date < t.Date) AS RunningTotal FROM Table t GROUP BY t.Date
DECLARE @StartDate smalldatetime DECLARE @EndDate smalldatetime SET @StartDate = '1 jan 2009' --MIN(TimeStamp) FROM Table SET @EndDate = '2 jan 2009' --MAX(TimeStamp) FROM Table SET @StartDate = DATEADD(minute,-DATEPART(minute,@StartDate),@StartDate) SET @EndDate = DATEADD(minute,@EndDate),@EndDate) ; WITH DateIntervalsCTE AS ( SELECT 0 i,@startdate AS Date UNION ALL SELECT i + 1,@startdate ) FROM DateIntervalsCTE WHERE DATEADD(minute,@startdate ) <= @enddate ) SELECT DISTINCT Date FROM DateIntervalsCTE OPTION (MAXRECURSION 32767);
只要你需要<由于递归限制,大约22天的数据. 现在你需要的是将两者合并,使用临时表来保存数据似乎是最快的
DECLARE @StartDate smalldatetime DECLARE @EndDate smalldatetime DECLARE @t TABLE (Date smalldatetime,Quantity int,RunningTotal int) SET @StartDate = '1 jan 2009' --MIN(TimeStamp) FROM Table SET @EndDate = '2 jan 2009' --MAX(TimeStamp) FROM Table SET @StartDate = DATEADD(minute,@startdate ) <= @enddate ) INSERT INTO @t (Date) SELECT DISTINCT Date FROM DateIntervalsCTE OPTION (MAXRECURSION 32767); UPDATE t SET Quantity = (SELECT COUNT(d.TimeStamp) FROM Table d WHERE Date = t.date) from @t t update t2 set runningtotal = (SELECT SUM(Quantity) FROM @t WHERE date <= t2.date) from @t t2 select * from @t
