从人口稀少的数据库表中每分钟生成一行 [英] Generate a row per minute of the day from a sparsely populated database table

问题描述

我有一个表格，其中包含在当天(基本上)随机点插入的时间戳行.

I have a table populated with time stamped rows inserted at (essentially) random point in the day.

我需要以每分钟 1 行的方式生成运行总计(因此对于一天而言，总会有 24 * 60 行)例如

I need to generate running totals with 1 row per minute (so for a single day there will always be exactly 24 * 60 rows) e.g.

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 对这个查询感兴趣)

Any thoughts on the best way to do this? One SQL query would be ideal but not essential, performance is fairly important (sub 5 seconds on a table containg 500k rows of which 70k are interesting to this query)

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

My Final Solution (more or less).

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

The actual scenario was this. I have two tables one containing Orders with a 1:n relationship to a Fills table.

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

I needed to show the running Average Price and Cumulative Total for each minute in the trading day

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, i, @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

我还没有完成我所有的测试，但这看起来确实有效，感谢您的帮助！

I still haven't completed all my testing but this looks like it does the trick, thanks for the assistance!

推荐答案

确保日期列有索引并且性能应该合理.

Make sure the date column has an index on it and performance should be reasonable.

SELECT t.Date,
COUNT(*) AS Quantity,
(SELECT COUNT(*) FROM Table WHERE Date < t.Date) AS RunningTotal
FROM Table t
GROUP BY t.Date

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

Getting a table populated with one row per minute can be done extremely quickly as follows:

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,-DATEPART(minute,@EndDate),@EndDate)


; WITH DateIntervalsCTE AS
(
SELECT 0 i, @startdate AS Date
UNION ALL
SELECT i + 1, DATEADD(minute, i, @startdate )
FROM DateIntervalsCTE 
WHERE DATEADD(minute, i, @startdate ) <= @enddate
)
SELECT DISTINCT Date FROM DateIntervalsCTE
OPTION (MAXRECURSION 32767);

只要你只需要 <由于递归限制，大约需要 22 天的数据.

Provided you only need < ~22 days worth of data due to recursion restrictions.

您现在需要的只是合并两者，使用临时表来保存数据似乎最快

All you need now is to merge the two, using a temp table to hold data seems to be quickest

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,-DATEPART(minute,@StartDate),@StartDate)
SET @EndDate = DATEADD(minute,-DATEPART(minute,@EndDate),@EndDate)


; WITH DateIntervalsCTE AS
(
SELECT 0 i, @startdate AS Date
UNION ALL
SELECT i + 1, DATEADD(minute, i, @startdate )
FROM DateIntervalsCTE 
WHERE DATEADD(minute, i, @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

这篇关于从人口稀少的数据库表中每分钟生成一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！