假设一个时间序列,其中每个时间点都有一个值.我必须通过计算当前值+上一个值的总和来计算连续和.棘手的是,总和应设置上限,因此不得超过某个值.
Assume a time series where we have one value per point in time. I have to calculate a continues sum by calculating the current value + the sum of the previous value. Tricky part however is, that the sum should be capped and hence not exceed a certain value.
示例: 总和不得超过+2.
Example: Sum is capped at +2.
+-----+---------+------------+ | row | measure | capped sum | +-----+---------+------------+ | 1 | 1 | 1 | | 2 | 3 | 2 | | 3 | 4 | 2 | | 4 | -2 | 0 | | 5 | 1 | 1 | +-----+---------+------------+例如使用上一行"2"中的上限和",并加上当前值,计算第4行的上限和".结果是< 2我们可以按原样获取值.
e.g. the "capped sum" of row 4 is calulated using the "capped sum" from the previous row, "2", and adding the current value. As the result is < 2 we can take the value as it is.
问题是,对于HANA,我无法在封顶金额"字段上使用LAG/Window函数.这会给我一个未知列"错误.
Problem is, with HANA, I cannot use a LAG/Window function on the "capped sum" field. It will give me a "unknown column" error.
有什么主意如何在不使用for循环的情况下解决SQL/HANA SQL脚本中的问题(这会非常慢)?
Any ideas how to resolve this in SQL/HANA SQL Script without using for loops (which would be very slow)?
推荐答案此脚本首先创建一列总和.然后,它使用该列创建一个超额"列,该列累积了累计金额超过上限值.然后,它减去过量值,以便在适当时给出小于2的值.
This script first creates a column of a running sum. Then it uses that column to create a column of "overage", how much the running sum exceeds the capped value, cumulatively. Then it subtracts the overage in order to give a value less than 2 if appropriate.
DECLARE @capped_value INT = 2 ;WITH CTE AS (SELECT rowID,measure, running_total = SUM(measure) OVER (ORDER BY rowID ROWS UNBOUNDED PRECEDING) FROM dbo.test_capped_sum) , CTE2 AS (SELECT *, overage_total = MAX(CTE.running_total) OVER (ORDER BY rowID ROWS UNBOUNDED PRECEDING) - @capped_value FROM CTE) SELECT rowid,measure, CASE WHEN CTE2.overage_total > 0 THEN CTE2.running_total- CTE2.overage_total ELSE CTE2.running_total END AS capped_sum FROM CTE2更多推荐
SQL封顶,连续求和
发布评论