一列在正负两个不同方向上的累加和

Question

我怎样才能得到想要的结果。我想先计算负数的累加和，然后再将累加和与正数相加。

CREATE TABLE dbo.tb(
[group_name] [nvarchar](255) 
,[value] [float] 
)
INSERT INTO [dbo].[tb]
([group_name]
,[value])
VALUES
('A',-3),('A',-2),('A',-1),('A',1),('A',3),('B',-2)

通过编写以下代码，将显示结果列

SELECT [group_name]
,[value]
,sum(value) OVER (PARTITION BY group_name ORDER BY value asc ) result
FROM [dbo].[tb]

如何获得想要的列?

<表类>group_name价值结果的tbody><<tr>336253161150323B222

您可以使用条件和窗口函数如下:

SELECT group_name, value,
CASE 
WHEN 
value<0 THEN
SUM(CASE WHEN value<0 THEN value END) OVER 
(PARTITION BY group_name ORDER BY value DESC)
ELSE
SUM(CASE WHEN value>=0 THEN value END) OVER 
(PARTITION BY group_name ORDER BY value) + 
MAX(CASE WHEN value<0 THEN value END) OVER 
(PARTITION BY group_name)
END AS desired
FROM tb
ORDER BY group_name, value

查看演示

与@ahmed类似，但使用cte,sign()和abs进行重构

with cte as
(select group_name,
value, 
sum(value) over (partition by group_name, sign(value) order by abs(value)) as sum,
max(case when value<0 then value end) over (partition by group_name) as max_neg
from your_table)

select *, case when value<0 then sum else sum+max_neg end as final_sum
from cte