sql-server-2005 – 从稀少添补的数据库表中天生天天一分钟的行

我有一个表添补了在当天(根基上)随机点插入的带时刻戳的行.

我必要天生每分钟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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!