使用OVER子句和PARTITION BY子句与计数器计算运行总计 [英] Calculating Running Total with OVER Clause and PARTITION BY Clause with counter

问题描述

如何使用计数器计算每个week_number和user_name的积分总和？
我使用 sum（）OVER（PARTITION BY ORDER BY）

how to calculate the sum of points each week_number and user_name using a counter ? I use sum() OVER (PARTITION BY ORDER BY )

我想拥有最大金额为10。

我该怎么办？

And I would like to have the maximum sum at 10.
How can I do this?

这是我的原始表格：

这是我想要获得的结果：

Here is the result I would like to obtain:

此处是sql代码：

SELECT week_number,
user_name,
sum(points) OVER (PARTITION BY user_name ORDER BY week_number) AS total_prime
FROM team;

推荐答案

尝试一下

select 
  week_number, 
  user_name, 
  points, 
  case when total_prime > 10 then 10 else total_prime end as sum, 
  case when total_prime > 1 and total_prime < 10 then null 
       when total_prime > 10 then total_prime - 10 end as dif
from
  (select 
     week_number, 
     user_name, points, 
     sum(points) over (partition by user_name order by week_number) as total_prime 
   from 
     team) a

我不了解您的第二张表，其中 alan 具有点4 但具有总和2 。

I don't get it in your second table where alan have point 4 but have sum 2.

这篇关于使用OVER子句和PARTITION BY子句与计数器计算运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！