Genere una fila por minuto del día a partir de una tabla de base de datos escasamente poblada
Tengo una tabla rellena con filas de marca de tiempo insertadas en (punto) punto aleatorio en el día.
Necesito generar totales acumulados con 1 fila por minuto (por lo que para un solo día siempre habrá exactamente 24 * 60 filas), por ejemplo.
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
...
¿Alguna idea sobre la mejor manera de hacer esto? Una consulta SQL sería ideal pero no esencial, el rendimiento es bastante importante (menos de 5 segundos en una tabla con 500k filas de las cuales 70k son interesantes para esta consulta)
Mi solución final (más o menos).
El escenario real fue este. Tengo dos tablas, una que contiene Órdenes con una relación 1: n con una tabla de Rellenos.
Necesitaba mostrar el Precio promedio y el Total acumulativo en ejecución por cada minuto del día de negociación
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
Todavía no he completado todas mis pruebas, pero parece que funciona, ¡gracias por la ayuda!