如果满足条件，则将列的总和分配给另一列中的一个日期

本文介绍了如果满足条件，则将列的总和分配给另一列中的一个日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

DB-Fiddle:

CREATE TABLE sales ( id int auto_increment primary key, customerID VARCHAR(255), sales_date DATE, sales_volume INT, annual_unqiue_count INT ); INSERT INTO sales (customerID, sales_date, sales_volume, annual_unqiue_count) VALUES ("Customer_01", "2020-03-01", "600", "1"), ("Customer_01", "2020-03-25", "315", "0"), ("Customer_02", "2020-03-18", "208", "1"), ("Customer_02", "2020-07-25", "140", "0"), ("Customer_03", "2020-10-18", "400", "1"), ("Customer_03", "2020-12-06", "500", "0"), ("Customer_03", "2020-12-18", "438", "0"), ("Customer_03", "2020-12-25", "917", "0");

预期结果:

customerID sales_date SUM(annual_unqiue_count) SUM(sales_volume) Customer_01 2020-03-01 1 915 (=600+315) Customer_01 2020-03-25 0 0 Customer_02 2020-03-18 1 348 (=208+140) Customer_02 2020-07-25 0 0 Customer_03 2020-10-18 1 2255 (=400+500+438+917) Customer_03 2020-12-06 0 0 Customer_03 2020-12-18 0 0 Customer_03 2020-12-25 0 0

在结果中，我想将每个 customer 的 SUM(sales_volume) 分配 到 sales_dateannual_unqiue_count <>0.

到目前为止，我尝试使用此查询，但无法使其工作:

So far I tried to go with this query but could not make it work:

SELECT customerID, sales_date, SUM(annual_unqiue_count), SUM(sales_volume) FROM sales GROUP BY 1,2 HAVING SUM(annual_unqiue_count) <> 0;

我需要改变什么才能得到预期的结果?

What do I need to change to get the expected result?

推荐答案

你想要窗口函数:

select s.*, (case when annual_unqiue_count <> 0 then sum(sales_volume) over (partition by customerId) else 0 end) as sales_volume from sales s;