SQL封顶，连续求和

本文介绍了SQL封顶，连续求和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设一个时间序列，其中每个时间点都有一个值.我必须通过计算当前值+上一个值的总和来计算连续和.棘手的是，总和应设置上限，因此不得超过某个值.

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