sql-server-2005 – 从稀疏填充的数据库表中生成每天一分钟的行数

前端之家收集整理的这篇文章主要介绍了sql-server-2005 – 从稀疏填充的数据库表中生成每天一分钟的行数前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个表填充了在当天(基本上)随机点插入的带时间戳的行.

我需要生成每分钟1行的运行总计(因此,对于一天,总会有正好24 * 60行),例如

  1. Date Quantity Running Total
  2. 2009-10-29 06:30 1 1
  3. 2009-10-29 06:31 5 6
  4. 2009-10-29 06:32 10 16
  5. 2009-10-29 06:33 11 27
  6. 2009-10-29 06:34 22 49


有关最佳方法的任何想法吗?
一个SQL查询是理想的但不是必需的,性能相当重要(在包含500k行的表上5秒以下,其中70k对此查询很有意义)

我的最终解决方案(或多或少).

实际情况是这样的.我有两个表,其中包含与Fills表有1:n关系的Orders.

我需要在交易日显示每分钟的运行平均价格和累计总额

  1. DECLARE @StartDate AS DATETIME,@EndDate AS DATETIME
  2. SET @StartDate = '2009-10-28';
  3. SET @EndDate = '2009-10-29';
  4.  
  5. -- Generate a Temp Table containing all the dates I'm interested in
  6. WITH DateIntervalsCTE AS
  7. (
  8. SELECT 0 i,@StartDate AS Date
  9. UNION ALL
  10. SELECT i + 1,DATEADD(minute,i,@StartDate )
  11. FROM DateIntervalsCTE
  12. WHERE DATEADD(minute,@StartDate ) < @EndDate
  13. )
  14. SELECT DISTINCT Date
  15. INTO #Dates
  16. FROM DateIntervalsCTE
  17. OPTION (MAXRECURSION 32767);
  18.  
  19. SELECT
  20. 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
  21. FROM
  22. #Dates AS d
  23. CROSS JOIN (
  24. SELECT DISTINCT mo2.Symbol,mo2.OrderID
  25. FROM
  26. Orders AS mo2
  27. INNER JOIN Fills AS mf2 ON mo2.OrderID = mf2.OrderID
  28. WHERE CONVERT(DATETIME,CONVERT(CHAR(10),mf2.FillDate,101)) = @StartDate
  29. ) AS mo3
  30. LEFT JOIN Fills AS mf ON mo3.OrderID = mf.OrderID AND CONVERT(DATETIME,CONVERT(CHAR(16),mf.FillDate,120)) < = d.Date
  31. WHERE
  32. d.Date >= DATEADD(mi,390,@StartDate) -- 06:30
  33. AND d.Date <= DATEADD(mi,780,@StartDate) -- 13:00
  34. GROUP BY d.Date,mo3.symbol
  35. ORDER BY mo3.Symbol,d.Date

我还没有完成我的所有测试,但这看起来像诀窍,谢谢你的帮助!

解决方法

确保日期列上有索引,性能应该合理.
  1. SELECT t.Date,COUNT(*) AS Quantity,(SELECT COUNT(*) FROM Table WHERE Date < t.Date) AS RunningTotal
  2. FROM Table t
  3. GROUP BY t.Date

获取每分钟填充一行的表可以非常快速地完成,如下所示:

  1. DECLARE @StartDate smalldatetime
  2. DECLARE @EndDate smalldatetime
  3.  
  4. SET @StartDate = '1 jan 2009' --MIN(TimeStamp) FROM Table
  5. SET @EndDate = '2 jan 2009' --MAX(TimeStamp) FROM Table
  6.  
  7. SET @StartDate = DATEADD(minute,-DATEPART(minute,@StartDate),@StartDate)
  8. SET @EndDate = DATEADD(minute,@EndDate),@EndDate)
  9.  
  10.  
  11. ; WITH DateIntervalsCTE AS
  12. (
  13. SELECT 0 i,@startdate AS Date
  14. UNION ALL
  15. SELECT i + 1,@startdate )
  16. FROM DateIntervalsCTE
  17. WHERE DATEADD(minute,@startdate ) <= @enddate
  18. )
  19. SELECT DISTINCT Date FROM DateIntervalsCTE
  20. OPTION (MAXRECURSION 32767);

只要你需要<由于递归限制,大约22天的数据. 现在你需要的是将两者合并,使用临时表来保存数据似乎是最快的

  1. DECLARE @StartDate smalldatetime
  2. DECLARE @EndDate smalldatetime
  3. DECLARE @t TABLE (Date smalldatetime,Quantity int,RunningTotal int)
  4. SET @StartDate = '1 jan 2009' --MIN(TimeStamp) FROM Table
  5. SET @EndDate = '2 jan 2009' --MAX(TimeStamp) FROM Table
  6.  
  7. SET @StartDate = DATEADD(minute,@startdate ) <= @enddate
  8. )
  9. INSERT INTO @t (Date)
  10. SELECT DISTINCT Date FROM DateIntervalsCTE
  11. OPTION (MAXRECURSION 32767);
  12.  
  13. UPDATE t SET Quantity = (SELECT COUNT(d.TimeStamp) FROM Table d WHERE Date = t.date)
  14. from @t t
  15.  
  16. update t2 set runningtotal = (SELECT SUM(Quantity) FROM @t WHERE date <= t2.date)
  17. from @t t2
  18.  
  19. select * from @t

猜你在找的MsSQL相关文章