英文:
How to reset running total for a column if null is present?
问题
我有一个如下所示的表格
| 客户 | 月初日期 | 销售订单数量 | 价值 | |
|---|---|---|---|---|
| A | 01/06/2022 | 3 | null | |
| B | 01/07/2022 | 2 | null | |
| A | 01/07/2022 | 0 | 1 | |
| A | 01/08/2022 | 0 | 1 | |
| B | 01/08/2022 | 0 | 1 | |
| A | 01/09/2022 | 3 | null | |
| B | 01/09/2022 | 1 | null | |
| A | 01/10/2022 | 1 | null | |
| B | 01/10/2022 | 0 | 1 | |
| A | 01/11/2022 | 0 | 1 |
我试图计算“价值”列的累积总和，按客户和按月初日期升序排列。如果“价值”列中有null值，累积总和必须被重置。
输出如下：
| 客户 | 月初日期 | 销售订单数量 | 累积总和 | |
|---|---|---|---|---|
| A | 01/06/2022 | 3 | null | |
| A | 01/07/2022 | 0 | 1 | |
| A | 01/08/2022 | 0 | 2 | |
| A | 01/09/2022 | 3 | null | |
| A | 01/10/2022 | 1 | null | |
| A | 01/11/2022 | 0 | 1 | |
| B | 01/07/2022 | 2 | null | |
| B | 01/08/2022 | 0 | 1 | |
| B | 01/09/2022 | 1 | null | |
| B | 01/10/2022 | 0 | 1 |
提前感谢。
英文:
I have a table as below
| Customer | Start_Date_of_Month | Sales_order_count | Value | |
|---|---|---|---|---|
| A | 01/06/2022 | 3 | null | |
| B | 01/07/2022 | 2 | null | |
| A | 01/07/2022 | 0 | 1 | |
| A | 01/08/2022 | 0 | 1 | |
| B | 01/08/2022 | 0 | 1 | |
| A | 01/09/2022 | 3 | null | |
| B | 01/09/2022 | 1 | null | |
| A | 01/10/2022 | 1 | null | |
| B | 01/10/2022 | 0 | 1 | |
| A | 01/11/2022 | 0 | 1 |
I'm trying to calculate the running total of Value column with respect to Customer and by Start_Date _of_Month in ascending order. If null is present in the Value column, the running total has to be reset
The output would be like
| Customer | Start_Date_of_Month | Sales_order_count | Running_Total | |
|---|---|---|---|---|
| A | 01/06/2022 | 3 | null | |
| A | 01/07/2022 | 0 | 1 | |
| A | 01/08/2022 | 0 | 2 | |
| A | 01/09/2022 | 3 | null | |
| A | 01/10/2022 | 1 | null | |
| A | 01/11/2022 | 0 | 1 | |
| B | 01/07/2022 | 2 | null | |
| B | 01/08/2022 | 0 | 1 | |
| B | 01/09/2022 | 1 | null | |
| B | 01/10/2022 | 0 | 1 |
Thanks in Advance
答案1
得分: 1
你可以在发现空值时为每个客户定义一个分组，然后使用按这些分组分区的累计和，尝试以下适用于MySQL 8+的查询：
select Customer, Start_Date_of_Month, Sales_order_count,
sum(Value) over (partition by Customer, grp order by Start_Date_of_Month) Running_Total
from
(
select *,
sum(value is null) over (partition by Customer order by Start_Date_of_Month) grp
from table_name
) T
order by Customer, Start_Date_of_Month
英文:
You could define groups for each customer whenever a null value is found, then use the running sum partitioned by these groups, try the following for MySQL 8+:
select Customer, Start_Date_of_Month, Sales_order_count,
sum(Value) over (partition by Customer, grp order by Start_Date_of_Month) Running_Total
from
(
select *,
sum(value is null) over (partition by Customer order by Start_Date_of_Month) grp
from table_name
) T
order by Customer, Start_Date_of_Month
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论