如果满足条件，则将列的总和分配给另一列中的一个日期 [英] Assign sum of column to one date in another column if criteria is met

问题描述

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;

这篇关于如果满足条件，则将列的总和分配给另一列中的一个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！