我有一个表填充了在当天(基本上)随机点插入的带时间戳的行.
我需要生成每分钟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