sql-server - 通过为每个 ID 更改参数来计算系列的增加

Question

我有一个数据集，它有 12 个不同的 ID 值，还有开始值和结束值。我想要初始化的是将起始值作为循环的开始参数，将结束值作为最后一个参数。根据它们搜索谷值，并找到第一个连续增加的值。

下面我发布了一个示例数据集:

create table #sample_data(
ID  VARCHAR(10), val1 INT, val2 INT, val3 INT, val4 INT, val5 INT, val6 INT, val7 INT, val8 INT, val9 INT, val10 INT, val11 INT, val12 INT, startValue INT, endValue INT  
);

insert into #sample_data values
    (1001,3,2,1,0,1,2,3,0,0,0,0,0,1,7),
    (1002,1,2,3,4,0,0,0,1,2,3,0,0,1,12),
    (1003,0,3,2,1,0,0,0,0,0,0,0,0,1,12),
    (1004,0,1,2,3,4,0,0,0,0,0,0,0,3,9),
    (1005,1,1,1,1,2,3,2,2,1,1,0,0,1,8);

这是我期望的结果:对于 ID = 1001，我的 startValue 是 1，end 是 7 算法将从 val1 到 val7 进行比较，将每个 val 与下一个 val 进行比较，并计算增加值直到它结束。在这种情况下，对于 1001，它将为 0，因为系列将从减少开始。对于 ID 1002，它也将是 3(val1 到 val4，当发生减少时它将停止在 val5)对于 ID 1003，它将是 1，因为开始时只有增加对于 ID 1004，它将是 2，因为它从 3 开始到 val9 结束对于 ID 1005 它将是 2，这是从 val4 到 val6 的增加

我曾尝试通过编写 CURSOR 来执行此操作，但由于我有超过 300K 行的数据，因此需要数小时才能执行。如果您有更好的建议(不使用光标)，我将非常感激。

Answer 1

您可以尝试以下方法。主要部分是将每一行转换为 12 行的表，以及附加的 Increase 列和适当的 WHERE 子句。

输入:

CREATE TABLE #SampleData(
   ID VARCHAR(10),
   val1 INT,
   val2 INT,
   val3 INT,
   val4 INT,
   val5 INT,
   val6 INT,
   val7 INT,
   val8 INT,
   val9 INT,
   val10 INT,
   val11 INT,
   val12 INT,
   startValue INT,
   endValue INT
);

INSERT INTO #SampleData
   (ID,val1,val2,val3,val4,val5,val6,val7,val8,val9,val10,val11,val12,startValue,endValue)
VALUES
   (1001,3,2,1,0,1,2,3,0,0,0,0,0,1,7),
   (1002,1,2,3,4,0,0,0,1,2,3,0,0,1,12),
   (1003,0,3,2,1,0,0,0,0,0,0,0,0,1,12),
   (1004,0,1,2,3,4,0,0,0,0,0,0,0,3,9),
   (1005,1,1,1,1,2,3,2,2,1,1,0,0,1,8),
   (1006,1,2,3,4,4,0,7,0,9,10,0,12,1,12)

T-SQL:

;WITH PreparationCTE AS (
    SELECT
       s.ID,
       v.Increase,
       ROW_NUMBER() OVER (PARTITION BY s.ID ORDER BY v.Nmr) AS Rn1,
       SUM(CASE WHEN v.Increase = -1 THEN 0 ELSE 1 END) OVER (PARTITION BY s.ID ORDER BY v.Nmr) AS Rn2
    FROM #SampleData s
    CROSS APPLY (VALUES
       (1,  Val1,  CASE WHEN Val1 < Val2   THEN 1 WHEN Val1 = Val2   THEN 0 ELSE -1 END),
       (2,  Val2,  CASE WHEN Val2 < Val3   THEN 1 WHEN Val2 = Val3   THEN 0 ELSE -1 END),
       (3,  Val3,  CASE WHEN Val3 < Val4   THEN 1 WHEN Val3 = Val4   THEN 0 ELSE -1 END),
       (4,  Val4,  CASE WHEN Val4 < Val5   THEN 1 WHEN Val4 = Val5   THEN 0 ELSE -1 END),
       (5,  Val5,  CASE WHEN Val5 < Val6   THEN 1 WHEN Val5 = Val6   THEN 0 ELSE -1 END),
       (6,  Val6,  CASE WHEN Val6 < Val7   THEN 1 WHEN Val6 = Val7   THEN 0 ELSE -1 END),
       (7,  Val7,  CASE WHEN Val7 < Val8   THEN 1 WHEN Val7 = Val8   THEN 0 ELSE -1 END),
       (8,  Val8,  CASE WHEN Val8 < Val9   THEN 1 WHEN Val8 = Val9   THEN 0 ELSE -1 END),
       (9,  Val9,  CASE WHEN Val9 < Val10  THEN 1 WHEN Val9 = Val10  THEN 0 ELSE -1 END),
       (10, Val10, CASE WHEN Val10 < Val11 THEN 1 WHEN Val10 = Val1  THEN 0 ELSE -1 END),
       (11, Val11, CASE WHEN Val11 < Val12 THEN 1 WHEN Val11 = Val2  THEN 0 ELSE -1 END),
       (12, Val12, 0)
    ) v(Nmr, Val, Increase)
    WHERE (s.startValue <= v.Nmr) AND (v.Nmr <= s.endValue)
)
SELECT ID, SUM(CASE WHEN (Increase >= 0) AND (Rn1 = Rn2) THEN Increase ELSE 0 END) AS [Count]
FROM PreparationCTE
GROUP BY ID
ORDER BY ID

输出:

----------
ID  Count
----------
1001    0
1002    3
1003    1
1004    2
1005    2
1006    3