SQL. Найдите первое и последнее значение за интервал времени, а затем работайте с ними

#sql-server #tsql

#sql-server #tsql

Вопрос:

Сегодня я начал оптимизировать различные процедуры базы данных в SQL Server.

Я хотел бы знать, можете ли вы помочь мне оптимизировать следующий запрос:

 DECLARE @fkTipoDato INTEGER = 8;
DECLARE @fkPlanta INTEGER = 1;
DECLARE @FechaInicio DATETIME = CAST('2020-27-10 00:00:00' AS DATETIME);
DECLARE @FechaFin DATETIME = CAST('2020-28-10 00:00:00' AS DATETIME);
DECLARE @FechaWhile DATETIME = @FechaInicio;
DECLARE @valorMaximoMedidor REAL = 2999999;
DECLARE @TablaTemp TABLE (
    tempFecha VARCHAR(20)
    ,cero REAL
    ,uno REAL
    ,dos REAL
    ,tres REAL
    ,cuatro REAL
    ,cinco REAL
    ,seis REAL
    ,siete REAL
    ,ocho REAL
    ,nueve REAL
    ,diez REAL
    ,ONCE REAL
    ,doce REAL
    ,trece REAL
    ,catorce REAL
    ,quince REAL
    ,dieciseis REAL
    ,diecisiete REAL
    ,dieciocho REAL
    ,diecinueve REAL
    ,veinte REAL
    ,veintiuno REAL
    ,veintidos REAL
    ,veintitres REAL
    );

WHILE @FechaWhile <= @FechaFin
BEGIN
    SET NOCOUNT ON;

    DECLARE @ValorFinal0 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= CAST(@FechaWhile AS DATETIME)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 1, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial0 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= CAST(@FechaWhile AS DATETIME)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 1, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal1 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 1, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 2, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial1 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 1, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 2, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal2 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 2, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 3, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial2 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 2, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 3, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal3 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 3, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 4, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial3 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 3, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 4, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal4 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 4, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 5, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial4 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 4, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 5, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal5 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 5, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 6, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial5 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 5, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 6, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal6 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 6, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 7, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial6 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 6, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 7, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal7 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 7, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 8, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial7 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 7, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 8, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal8 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 8, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 9, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial8 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 8, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 9, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal9 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 9, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 10, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial9 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 9, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 10, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal10 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 10, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 11, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial10 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 10, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 11, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal11 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 11, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 12, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial11 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 11, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 12, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal12 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 12, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 13, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial12 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 12, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 13, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal13 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 13, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 14, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial13 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 13, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 14, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal14 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 14, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 15, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial14 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 14, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 15, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal15 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 15, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 16, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial15 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 15, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 16, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal16 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 16, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 17, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial16 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 16, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 17, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal17 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 17, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 18, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial17 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 17, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 18, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal18 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 18, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 19, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial18 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 18, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 19, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal19 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 19, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 20, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial19 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 19, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 20, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal20 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 20, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 21, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial20 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 20, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 21, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal21 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 21, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 22, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial21 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 21, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 22, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal22 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 22, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 23, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial22 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 22, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 23, @FechaWhile))
            ORDER BY FechaHora ASC
            );
    DECLARE @ValorFinal23 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 23, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 24, @FechaWhile))
            ORDER BY FechaHora DESC
            );
    DECLARE @ValorInicial23 REAL = (
            SELECT TOP 1 valor
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND fk_Planta = @fkPlanta
                AND FechaHora >= DATEADD(HOUR, 23, @FechaWhile)
                AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 24, @FechaWhile))
            ORDER BY FechaHora ASC
            );

    IF EXISTS (
            SELECT TOP 1 FechaHora
            FROM RegistroDatos
            WHERE fk_TipoDato = @fkTipoDato
                AND FechaHora >= @FechaWhile
                AND FechaHora <= DATEADD(day, 1, @FechaWhile)
                AND fk_Planta = @fkPlanta
            )
    BEGIN
        INSERT INTO @TablaTemp
        SELECT TOP 1 CONVERT(VARCHAR(10), FechaHora, 103) AS [Fecha]
            ,(
                CASE 
                    WHEN @ValorFinal0 >= @ValorInicial0
                        THEN (
                                SELECT (@ValorFinal0 - @ValorInicial0)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial0)   @ValorFinal0)
                            )
                    END
                ) AS [00:00]
            ,(
                CASE 
                    WHEN @ValorFinal1 >= @ValorInicial1
                        THEN (
                                SELECT (@ValorFinal1 - @ValorInicial1)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial1)   @ValorFinal1)
                            )
                    END
                ) AS [01:00]
            ,(
                CASE 
                    WHEN @ValorFinal2 >= @ValorInicial2
                        THEN (
                                SELECT (@ValorFinal2 - @ValorInicial2)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial2)   @ValorFinal2)
                            )
                    END
                ) AS [02:00]
            ,(
                CASE 
                    WHEN @ValorFinal3 >= @ValorInicial3
                        THEN (
                                SELECT (@ValorFinal3 - @ValorInicial3)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial3)   @ValorFinal3)
                            )
                    END
                ) AS [03:00]
            ,(
                CASE 
                    WHEN @ValorFinal4 >= @ValorInicial4
                        THEN (
                                SELECT (@ValorFinal4 - @ValorInicial4)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial4)   @ValorFinal4)
                            )
                    END
                ) AS [04:00]
            ,(
                CASE 
                    WHEN @ValorFinal5 >= @ValorInicial5
                        THEN (
                                SELECT (@ValorFinal5 - @ValorInicial5)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial5)   @ValorFinal5)
                            )
                    END
                ) AS [05:00]
            ,(
                CASE 
                    WHEN @ValorFinal6 >= @ValorInicial6
                        THEN (
                                SELECT (@ValorFinal6 - @ValorInicial6)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial6)   @ValorFinal6)
                            )
                    END
                ) AS [06:00]
            ,(
                CASE 
                    WHEN @ValorFinal7 >= @ValorInicial7
                        THEN (
                                SELECT (@ValorFinal7 - @ValorInicial7)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial7)   @ValorFinal7)
                            )
                    END
                ) AS [07:00]
            ,(
                CASE 
                    WHEN @ValorFinal8 >= @ValorInicial8
                        THEN (
                                SELECT (@ValorFinal8 - @ValorInicial8)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial8)   @ValorFinal8)
                            )
                    END
                ) AS [08:00]
            ,(
                CASE 
                    WHEN @ValorFinal9 >= @ValorInicial9
                        THEN (
                                SELECT (@ValorFinal9 - @ValorInicial9)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial9)   @ValorFinal9)
                            )
                    END
                ) AS [09:00]
            ,(
                CASE 
                    WHEN @ValorFinal10 >= @ValorInicial10
                        THEN (
                                SELECT (@ValorFinal10 - @ValorInicial10)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial10)   @ValorFinal10)
                            )
                    END
                ) AS [10:00]
            ,(
                CASE 
                    WHEN @ValorFinal11 >= @ValorInicial11
                        THEN (
                                SELECT (@ValorFinal11 - @ValorInicial11)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial11)   @ValorFinal11)
                            )
                    END
                ) AS [11:00]
            ,(
                CASE 
                    WHEN @ValorFinal12 >= @ValorInicial12
                        THEN (
                                SELECT (@ValorFinal12 - @ValorInicial12)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial12)   @ValorFinal12)
                            )
                    END
                ) AS [12:00]
            ,(
                CASE 
                    WHEN @ValorFinal13 >= @ValorInicial13
                        THEN (
                                SELECT (@ValorFinal13 - @ValorInicial13)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial13)   @ValorFinal13)
                            )
                    END
                ) AS [13:00]
            ,(
                CASE 
                    WHEN @ValorFinal14 >= @ValorInicial14
                        THEN (
                                SELECT (@ValorFinal14 - @ValorInicial14)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial14)   @ValorFinal14)
                            )
                    END
                ) AS [14:00]
            ,(
                CASE 
                    WHEN @ValorFinal15 >= @ValorInicial15
                        THEN (
                                SELECT (@ValorFinal15 - @ValorInicial15)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial15)   @ValorFinal15)
                            )
                    END
                ) AS [15:00]
            ,(
                CASE 
                    WHEN @ValorFinal16 >= @ValorInicial16
                        THEN (
                                SELECT (@ValorFinal16 - @ValorInicial16)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial16)   @ValorFinal16)
                            )
                    END
                ) AS [16:00]
            ,(
                CASE 
                    WHEN @ValorFinal17 >= @ValorInicial17
                        THEN (
                                SELECT (@ValorFinal17 - @ValorInicial17)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial17)   @ValorFinal17)
                            )
                    END
                ) AS [17:00]
            ,(
                CASE 
                    WHEN @ValorFinal18 >= @ValorInicial18
                        THEN (
                                SELECT (@ValorFinal18 - @ValorInicial18)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial18)   @ValorFinal18)
                            )
                    END
                ) AS [18:00]
            ,(
                CASE 
                    WHEN @ValorFinal19 >= @ValorInicial19
                        THEN (
                                SELECT (@ValorFinal19 - @ValorInicial19)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial19)   @ValorFinal19)
                            )
                    END
                ) AS [19:00]
            ,(
                CASE 
                    WHEN @ValorFinal20 >= @ValorInicial20
                        THEN (
                                SELECT (@ValorFinal20 - @ValorInicial20)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial20)   @ValorFinal20)
                            )
                    END
                ) AS [20:00]
            ,(
                CASE 
                    WHEN @ValorFinal21 >= @ValorInicial21
                        THEN (
                                SELECT (@ValorFinal21 - @ValorInicial21)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial21)   @ValorFinal21)
                            )
                    END
                ) AS [21:00]
            ,(
                CASE 
                    WHEN @ValorFinal22 >= @ValorInicial22
                        THEN (
                                SELECT (@ValorFinal22 - @ValorInicial22)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial22)   @ValorFinal22)
                            )
                    END
                ) AS [22:00]
            ,(
                CASE 
                    WHEN @ValorFinal23 >= @ValorInicial23
                        THEN (
                                SELECT (@ValorFinal23 - @ValorInicial23)
                                )
                    ELSE (
                            SELECT ((@valorMaximoMedidor - @ValorInicial23)   @ValorFinal23)
                            )
                    END
                ) AS [23:00]
        FROM RegistroDatos
        WHERE fk_TipoDato = @fkTipoDato
            AND FechaHora >= @FechaWhile
            AND FechaHora <= DATEADD(day, 1, @FechaWhile)
            AND fk_Planta = @fkPlanta
    END

    SET @FechaWhile = DATEADD(day, 1, @FechaWhile);
END

SELECT tempFecha AS [Fecha]
    ,cero AS [00:00]
    ,uno AS [01:00]
    ,dos AS [02:00]
    ,tres AS [03:00]
    ,cuatro AS [04:00]
    ,cinco AS [05:00]
    ,seis AS [06:00]
    ,siete AS [07:00]
    ,ocho AS [08:00]
    ,nueve AS [09:00]
    ,diez AS [10:00]
    ,ONCE AS [11:00]
    ,doce AS [12:00]
    ,trece AS [13:00]
    ,catorce AS [14:00]
    ,quince AS [15:00]
    ,dieciseis AS [16:00]
    ,diecisiete AS [17:00]
    ,dieciocho AS [18:00]
    ,diecinueve AS [19:00]
    ,veinte AS [20:00]
    ,veintiuno AS [21:00]
    ,veintidos AS [22:00]
    ,veintitres AS [23:00]
FROM @TablaTemp
  

Он возвращает потребление (разницу между максимальным и минимальным значением), разделенное часами и днями (что было бы легко сделать), но проблема возникает, поскольку при сохранении данных и достижении значения = 2999999 счетчик сбрасывается. Поэтому вы должны проверять первое и последнее значения вместо минимального и максимального, а затем работать с ними.

А также прикрепите код, он работает, но медленно.

ВЕРСИЯ: Microsoft SQL Server 2008 R2 (RTM)

Таблица RegistroDatos имеет следующую структуру: DDL
Дизайн RegistroDatos

RegistroDatos

Есть предложения?

Комментарии:

1. С первого взгляда избавьтесь от всего кода -1ms и замените оператор сравнения <= на < . И избавьтесь от всех ложных select s. (select(@ValorFinal7-@ValorInicial7)) просто @ValorFinal7-@ValorInicial7 .

2. итак, я уверен, что для этого есть один оператор select, использующий windowing, но я понятия не имею, что вы делаете — можете ли вы объяснить, что вы делаете, и привести пример начальных данных и результатов?

3. Пожалуйста, добавьте DDL, включая любые индексы, для RegistroDatos вашего сообщения.

4. @HABO Готово, добавьте в соответствии с запросом

5. Я удалил все -1 мс и select (@ValorFinalx — @ValorInicialx), но задержка запроса остается прежней

Ответ №1:

Я не тестировал код, потому что вы разместили свой DDL как изображение, а не как текст. Но я думаю, что я понял, чего вы пытаетесь достичь. Вы выполняете один и тот же запрос несколько раз, поэтому выполнение всей работы за один проход должно увеличить время выполнения.

 WITH timeParts AS (
    /* Get the date and hour parts as independent columns
        to simplify expressions down the line */
    SELECT
        FechaHora, Valor,
        CAST(FechaHora AS DATE) AS [date],      --Truncate time part
        DATEPART([HOUR], FechaHora) AS [hour]
    FROM
        dbo.RegistroDatos
    WHERE
        fk_TipoDato   = @fkTipoDato
        AND fk_Planta = @fkPlanta
        AND FechaHora >= CAST(@FechaInicio AS DATETIME)
        AND FechaHora < DATEADD([DAY], 1, CAST(@FechaFin AS DATETIME))
),
firstAndLast AS (
    /* generate sequences to identify the first and last "valor" values */
    SELECT
        [date], [hour],
        ROW_NUMBER() OVER(PARTITION BY [date], [hour] ORDER BY FechaHora) AS firstR,
        ROW_NUMBER() OVER(PARTITION BY [date], [hour] ORDER BY FechaHora DESC) AS lastR,
        Valor
    FROM
        timeParts
),
firstPass AS (
    /*  Put initial and last values side by side to simplify the comsumption calculation,
        basically a pivot operation */
SELECT
    [date], [hour],
    MIN(CASE WHEN firstR = 1 THEN Valor END) AS initialValue,
    MIN(CASE WHEN lastR  = 1 THEN Valor END) AS finalValue
FROM
    firstAndLast
GROUP BY
    [date], [hour]
),
consumptions AS (
    /*  Having every value needed side by side simplifies the code:
        you don't need to repeat 24 times the same code... */
    SELECT
        [date], [hour],
        CASE
            WHEN finalValue >= initialValue THEN finalValue - initialValue
            ELSE @valorMaximoMedidor - initialValue   finalValue
        END AS consumption
    FROM
        firstPass
)
SELECT *
FROM
    consumptions
PIVOT(MIN(consumption) FOR [hour] IN (
     [0], [1], [2], [3], [4], [5], [6], [7], [8], [9],[10],[11],
    [12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) pvt;

  

Соответствующий индекс для вашего запроса еще больше увеличит время выполнения, если он еще не существует:

 CREATE INDEX index_name
ON dbo.RegistroDatos(FechaHora, fk_Planta, fk_TipoDato)
INCLUDE(Valor);
  

Комментарии:

1. Большое спасибо!!! Это решило мою проблему. Низкое время запроса от 2 минут до 2 секунд!

Ответ №2:

Возможно, существует решение, основанное на наборе. Я бы начал с ГРУППИРОВАНИЯ по СОСТАВУ (FechaHora как дата), DATEPART(ЧАС, FechaHora). При необходимости группируйте в других столбцах. МИН и МАКС — это просто. Первое и последнее сложнее. Если у вас есть монотонно увеличивающееся или уменьшающееся значение, которое уникально, его можно использовать для получения «ключа» к первой и последней записям в каждой группе. FechaHora может подойти для этого, если нет ничего другого. В противном случае я бы попробовал решение, используя функции Windows FIRST() и LAST() .

     SELECT CAST(FechaHora as date) as [FechaHora_date], 
        DATEPART(HOUR, FechaHora) as [FechaHora_hour],
        MIN(valor) as [valor_min],
        MAX(valor) as [valor_max],
        MAX(valor) - MIN(valor) as [Consumption],
        MIN(id) as [id_min], -- is there a min value the identifies the first record?
        MAX(id) as [id_max], -- is there a max value the identifies the last record?
    FROM RegistroDatos
    WHERE FechaHora BETWEEN @FechaInicio AND @FechaFin
    GROUP BY CAST(FechaHora as date), DATEPART(HOUR, FechaHora)
  

Если вам нужно использовать первую и последнюю записи, затем присоединитесь к RegistroDatosto по id_min, а затем снова по id_max, чтобы получить значение valor для обеих.

Если вам нужны записи для тех мест, где нет данных, вы можете сгенерировать таблицу чисел, чтобы создать таблицу со всеми необходимыми датами и часами. Затем присоединитесь к нему с данными, при необходимости заменив нули на ноль.

Комментарии:

1. В сообщении добавьте формат таблицы RegistroDatos