mysql - 向上指望积极变化，向下指望负面变化

Question

我有一个更改值的列。我想通过在每次向上变化时加法和在每次向下变化时减法来计数。假设 x[] 是我的值，Delta 是 x 元素变化的符号，y[] 是我的目标结果或计数。

我们一直向上计数，直到下一个 delta -1 开始向下计数，然后当 delta 变回 +1 时我们继续向上计数。总而言之，我们正常加法，直到 delta 为 -1 时我们开始减法，然后在下一个 +1 delta 处继续加法。

x:     1, 3, 4, 4, 4, 5, 5,  3, 3, 4, 5, 5,  6,  5,  4, 4, 4,  3, 4, 5, 6, 7,  8
Delta: 0, 1, 1, 0, 0, 1, 0, -1, 0, 1, 1, 0,  1, -1, -1, 0, 0, -1, 1, 1, 1, 1,  1
y:     1, 2, 3, 4, 5, 6, 7,  6, 5, 6, 7, 8, 9,  8,  7, 6, 5,  4, 5, 6, 7, 8, 9

我的数组长度为数百万行，效率很重要。不确定是否应该在 SQL 中完成此类操作，或者我是否最好从数据库中检索数据并在外部执行此类计算。

Answer 1

您可以在 SQL-Server 中使用此查询，假定用于排序的 PK 列:

WITH CTE AS
(
    SELECT t.ID, t.Value, 
           LastValue = Prev.Value,
           Delta = CASE WHEN Prev.Value IS NULL 
                          OR t.Value > Prev.Value THEN 1
                        WHEN t.Value = Prev.Value THEN 0 
                        WHEN t.Value < Prev.Value THEN -1 END
    FROM dbo.TableName t
    OUTER APPLY (SELECT TOP 1 t2.ID, t2.Value
                 FROM dbo.TableName t2
                 WHERE t2.ID < t.ID
                 ORDER BY t2.ID DESC) Prev
)
, Changes AS
(
    SELECT CTE.ID, CTE.Value, CTE.LastValue, CTE.Delta, 
           Change = CASE WHEN CTE.Delta <> 0 THEN CTE.Delta
                    ELSE (SELECT TOP 1 CTE2.Delta
                          FROM CTE CTE2
                          WHERE CTE2.ID < CTE.ID
                          AND  CTE2.Delta <> 0
                          ORDER BY CTE2.ID DESC) END
    FROM CTE
)
SELECT SUM(Change) FROM Changes c

结果是预期的 9:

• complete result set
• only Sum

OUTER APPLY将当前记录与上一条记录链接起来，上一条记录的 ID 最高 < current.ID .它的工作方式类似于 LEFT OUTER JOIN .

主要挑战是上一个 CTE 中的子查询。这是找到最后一个增量即 <> 0 所必需的。以确定当前增量是正数还是负数。