时间戳

首页 > 解决方案 > 避免使用while循环来检查行“状态变化”

sql-server - 避免使用while循环来检查行“状态变化”

问题描述

我有一个存储 Id、日期时间和 int 新月值的表。该值会增加，直到它“中断”并返回到接近 0 的值。例如：...1000、1200、1350、8、10、25...我需要计算这种“溢出”发生了多少次，但是...我说的是每天存储 200k 行的表！我已经解决了！但是使用带有游标的过程，该游标通过while循环对其进行迭代。但我知道这不是更快的方法。

有人可以帮我找到另一种方法吗？谢谢！

->

表结构：Id Bigint Primary Key、CreatedAt DateTime、Value Not Null Int。

问题：如果两个连续行之间的 Delta-Value < 0，则增加一个计数器。表每天有 200k 新行。不允许触发。

[第一次编辑]

Table has the actual structure:
    CREATE TABLE ValuesLog (
    Id BIGINT PRIMARY KEY, 
    Machine BIGINT, 
    CreatedAt DATETIME,
    Value INT
)

我需要：检查某些[Machine]的[Value]何时突然降低。

一些用户说使用了 LEAD/LAG。但它有一个问题......如果我选择了很多机器，LEAD/LAG 功能并不关心“它是什么机器”。因此，如果我找到机器 1 和机器 2，如果机器 1 增加但机器 2 减少，则 LEAD/LAG 会给我一个误报。

所以，我的表实际上看起来如何： 实际表的许多行

（上面的图像选择了 3 或 4 台机器。但是，在这个例子中，机器没有搞砸。但是可能会发生！在这种情况下，LEAD/LAG 不关心上面的行是 machine-1 还是机器-2)

我想要什么：在第 85 行，[value] 中断并重新启动。我想计算每一次发生时，选定的机器。所以：“机器 1 重启了 6 次……机器 9 重启了 10 次……”

我做过这样的事情：

CREATE PROCEDURE CountProduction @Machine INT_ARRAY READONLY, @Start DATETIME, @End DATETIME AS
    SET NOCOUNT ON

    -- Declare counter and insert start values
    DECLARE @Counter TABLE(
        machine INT PRIMARY KEY, 
        lastValue BIGINT DEFAULT 0, 
        count BIGINT DEFAULT 0
    )
    INSERT INTO @Counter(machine) SELECT n FROM @Machine

    -- Declare cursor to iteract over results of values log
    DECLARE valueCursor CURSOR LOCAL FOR 
        SELECT
            Value,
            Aux.LastValue,
            Aux.count
        FROM
            ValueLog,
            @Machine AS Machine,
            @Counter AS Counter
        WHERE
            ValueLog.Machine = Machine.n
            AND Counter.machine = ValueLog.Machine
            AND ValueLog.DateCreate BETWEEN @Start AND @End;

    -- Start iteration
    OPEN valueCursor
    DECLARE @RowMachine INT
    DECLARE @RowValue BIGINT
    DECLARE @RowLastValue BIGINT
    DECLARE @RowCount BIGINT
    FETCH NEXT FROM valueCursor INTO @RowMachine, @RowValue, @RowLastValue, @RowCount

    -- Iteration
    DECLARE @increment INT
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @RowValue < @RowLastValue
            SET @increment = 1
        ELSE
            SET @increment = 0

        -- Update counters
        UPDATE 
            @Counter
        SET
            lastValue = @RowValue,
            count = count + @increment
        WHERE
            inj = @RowMachine

        -- Proceed to iteration
        FETCH NEXT FROM valueCursor INTO @RowMachine, @RowValue, @RowLastValue, @RowCount
    END

    -- Closing iteration
    CLOSE valueCursor
    DEALLOCATE valueCursor

    SELECT machine, count FROM @Counter

标签： sql-serverdatabase

解决方案

建议使用@jeroen-mostert 解决

DECLARE @Start DATETIME
DECLARE @End DATETIME

SET @Start = '2019-01-01'
SET @End = GETDATE()

SELECT 
    Machine, 
    COUNT(DeltaValue) AS Prod
FROM
    (SELECT
        Log.Machine,
        Log.Value - LAG(Log.Value) OVER (PARTITION BY Log.Machine ORDER BY Log.Id) AS DeltaValue
    FROM
        ValueLog AS Log,
        (SELECT
            Id,
            Machine,
            Value
        FROM
            ValueLog
        ) AS AuxLog
    WHERE
        AuxLog.Id = Log.Id
        AND Proto.DateCreate BETWEEN @Start AND @End
        AND Proto.Machine IN (1, 9, 10)
        ) as TB1
WHERE
    DeltaValue < 0
GROUP BY
    Machine
ORDER BY
    Machine

在这种情况下，内部 LAG/LEAD 函数并没有弄乱内容（当我创建视图时出于某种原因发生了什么......我稍后会尝试理解）。

谢谢大家！我是 DB 的新手，这个问题让我疯狂了一整天。

推荐阅读