时间戳

首页 > 解决方案 > 如果满足条件，则将列的总和分配给另一列中一个日期的月份

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

问题描述

DB-小提琴

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)   sales_volume
Customer_01          3                  1                       600               915
Customer_01          3                  0                       315                 0
Customer_02          3                  1                       208               348
Customer_02          7                  0                       140                 0
Customer_03         10                  1                       400              2255
Customer_03         12                  0                       500                 0
Customer_03         12                  0                       438                 0
Customer_03         12                  0                       917                 0

结果，我想将SUM(sales_volume)per分配customer给month具有. （注意：每个客户只有一行可以有一个 Annual_unique_account <> 0）sales_dateannual_unqiue_count <> 0

参考这个问题的解决方案，我尝试使用：

SELECT
customerID,
MONTH(sales_date),
SUM(annual_unqiue_count),
SUM(sales_volume),
  (CASE WHEN annual_unqiue_count <> 0
  THEN SUM(sales_volume) over (PARTITION BY customerId)
  ELSE 0 END) AS sales_volume
FROM sales
GROUP BY 1,2;

但是，它并没有给我正确的结果。
你知道我需要改变什么吗？

标签： mysqlsql

解决方案

在这种情况下没有理由使用GROUP BY，因为您想要表的所有行。
使用SUM()窗口函数获取最后一列：

SELECT customerID,
       MONTH(sales_date) sales_date,
       annual_unqiue_count,
       sales_volume,
       annual_unqiue_count *
       SUM(sales_volume) OVER (PARTITION BY customerID) total_sales_volume 
FROM sales

也许您还需要一个WHERE子句来过滤特定年份的行？

请参阅演示。
结果：

> customerID  | sales_date | annual_unqiue_count | sales_volume | total_sales_volume
> :---------- | ---------: | ------------------: | -----------: | -----------------:
> Customer_01 |          3 |                   1 |          600 |                915
> Customer_01 |          3 |                   0 |          315 |                  0
> Customer_02 |          3 |                   1 |          208 |                348
> Customer_02 |          7 |                   0 |          140 |                  0
> Customer_03 |         10 |                   1 |          400 |               2255
> Customer_03 |         12 |                   0 |          500 |                  0
> Customer_03 |         12 |                   0 |          438 |                  0
> Customer_03 |         12 |                   0 |          917 |                  0

推荐阅读