我有一个数据集,其中一个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行的数据,因此需要数小时才能执行。如果你有任何更好的建议(不使用光标),我将非常感谢。
发布于 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
https://stackoverflow.com/questions/56390789
复制相似问题