随着每个ID的参数变化而连续计数增加
随着每个ID的参数变化而连续计数增加
提问于 2019-05-31 15:42:35
我有一个数据集，其中一个ID有12个不同的值，还有起始值和结束值。我想初始化的是把开始值作为循环的开始参数，结束值作为最后一个参数。根据它们搜索谷值，并找到第一个连续增加的值。
下面我发布了一个示例数据集：
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的开头只有一个递增。对于ID 1005，它将是2，因为它从3开始，在val9结束。对于ID 1005，它将是2，这是从val4到val6的递增
我曾尝试使用写游标来执行此操作，但由于我有超过300K行的数据，因此需要数小时才能执行。如果你有任何更好的建议(不使用光标)，我将非常感谢。
回答 1
Stack Overflow用户
发布于 2019-06-03 00:09:48
您可以尝试以下方法。主要部分是将每一行转换为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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56390789
复制相关文章
相似问题