Hej,
Jag har en lösning med en CTE och lite ranking funktioner.
Först så ska man skapa det här indexet.
CREATE NONCLUSTERED INDEX IX_VehicleName_StartTime ON dbo.MeterReadings (VehicleName ASC, StartTime ASC) INCLUDE (MeterValue)
Sedan så är scriptet här.
WITH MeterReadingsRowNumber AS (
SELECT VehicleName,
ROW_NUMBER() OVER (PARTITION BY VehicleName ORDER BY StartTime ASC) AS RowNumber,
StartTime,
MeterValue
FROM MeterReadings
),
Periods AS (
SELECT MeterReadingsRowNumber1.VehicleName,
MeterReadingsRowNumber1.StartTime StartTime,
MeterReadingsRowNumber2.StartTime EndTime,
CAST(MeterReadingsRowNumber2.MeterValue - MeterReadingsRowNumber1.MeterValue AS float) AS Consumption,
CAST(DATEDIFF(ms,MeterReadingsRowNumber1.StartTime,MeterReadingsRowNumber2.StartTime) AS float) / 1000 / 60 AS TimeInMinutes,
CAST(MeterReadingsRowNumber2.MeterValue - MeterReadingsRowNumber1.MeterValue AS float) / DATEDIFF(ms,MeterReadingsRowNumber1.StartTime,MeterReadingsRowNumber2.StartTime) * 1000 * 60 AS ConsumptionPerMinute
FROM MeterReadingsRowNumber MeterReadingsRowNumber1
INNER JOIN MeterReadingsRowNumber MeterReadingsRowNumber2
ON MeterReadingsRowNumber1.VehicleName = MeterReadingsRowNumber2.VehicleName
AND MeterReadingsRowNumber1.RowNumber = MeterReadingsRowNumber2.RowNumber -1
),
PeriodsPercentile AS (
SELECT VehicleName,
StartTime,
EndTime,
Consumption,
TimeInMinutes,
ConsumptionPerMinute,
NTILE(100) OVER (PARTITION BY VehicleName ORDER BY ConsumptionPerMinute DESC, StartTime DESC) AS Percentile
FROM Periods
)
MERGE INTO ConsumptionSummary AS TARGET
USING
(
SELECT VehicleName, SUM(Consumption) / SUM(TimeInMinutes) AS Top20PctAveragePerMinute
FROM PeriodsPercentile
WHERE Percentile <= 20
GROUP BY VehicleName
) AS SOURCE
ON TARGET.VehicleName = SOURCE.VehicleName
WHEN MATCHED THEN UPDATE SET Top20PctAveragePerMinute = SOURCE.Top20PctAveragePerMinute
WHEN NOT MATCHED BY TARGET THEN INSERT (VehicleName, Top20PctAveragePerMinute) VALUES (VehicleName, Top20PctAveragePerMinute)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
Det tar ca 3 sekunder på min dator.
M.v.h.
Ola
|