时间戳

首页 > 解决方案 > 带日期的 MySQL SUMIFS

sql - 带日期的 MySQL SUMIFS

问题描述

在 MySQL 上遇到 SUMIFS 场景有困难

这是我的数据：

CREATE TABLE opportunities
(close_date DATE NOT NULL,
sale_amount DECIMAL NOT NULL);

INSERT INTO opportunities VALUES ('20190101',100);
INSERT INTO opportunities VALUES ('20190115',115);
INSERT INTO opportunities VALUES ('20190210',150);
INSERT INTO opportunities VALUES ('20190212',180);
INSERT INTO opportunities VALUES ('20190306',200);
INSERT INTO opportunities VALUES ('20190315',220);
INSERT INTO opportunities VALUES ('20190422',250);
INSERT INTO opportunities VALUES ('20190428',260);
INSERT INTO opportunities VALUES ('20190509',300);
INSERT INTO opportunities VALUES ('20190521',310);
INSERT INTO opportunities VALUES ('20190601',325);
INSERT INTO opportunities VALUES ('20190630',375);
INSERT INTO opportunities VALUES ('20190703',400);
INSERT INTO opportunities VALUES ('20190716',500);
INSERT INTO opportunities VALUES ('20190813',550);
INSERT INTO opportunities VALUES ('20190814',575);
INSERT INTO opportunities VALUES ('20190909',625);
INSERT INTO opportunities VALUES ('20190929',650);
INSERT INTO opportunities VALUES ('20191004',700);
INSERT INTO opportunities VALUES ('20191022',750);
INSERT INTO opportunities VALUES ('20191112',800);
INSERT INTO opportunities VALUES ('20191119',900);
INSERT INTO opportunities VALUES ('20191202',950);
INSERT INTO opportunities VALUES ('20191216',975);

我需要完成的是每个月有 1 行（例如 2019-01、2019-02、2019-03 等），对于该行，我需要计算前 6 个月的 sale_amount 的总和. 因此，2019-12 年的 sale_amount 将等于所有 sale_amounts 的总和，close_date 为 2019 年 7 月 1 日至 2019 年 12 月 31 日。根据我提供的数据，我需要我的最终结果如下所示：

Month       Sale_Amount (Prev 6 Months)
2019-01     215
2019-02     545
2019-03     965
2019-04     1475
2019-05     2085
2019-06     2785
2019-07     3470
2019-08     4265
2019-09     5120
2019-10     6060
2019-11     7150
2019-12     8375

这是我到目前为止所尝试的：

SELECT
    DATE_FORMAT(op.close_date, '%Y-%m'),
    SUM(CASE
    WHEN op.close_date >= DATE_FORMAT(DATE_SUB(op.close_date, INTERVAL 5 MONTH) ,'%Y-%m-01')
        AND op.close_date <= LAST_DAY(op.close_date) THEN op.sale_amount
    END)
FROM opportunities op
GROUP BY DATE_FORMAT(op.close_date, '%Y-%m')
ORDER BY DATE_FORMAT(op.close_date, '%Y-%m')

这不起作用，它只返回该月的总和

我得到的实际结果：

Month       Sale_Amount (Prev 6 Months)
2019-01     215
2019-02     330
2019-03     420
2019-04     510
2019-05     610
2019-06     700
2019-07     900
2019-08     1125
2019-09     1275
2019-10     1450
2019-11     1700
2019-12     1925

我正在尝试将其从 Excel 电子表格转换，下面的公式完全符合我的预期，但不确定如何转换为 MySQL

SUMIFS($B$2:$B$500, --sum range
$A$2:$A$500,">="&DATE(YEAR(EOMONTH(A2,-6)+1),MONTH(EOMONTH(A2,-6)+1),1), --close_date >= than first day 6 months ago
$A$2:$A$500,"<="&DATE(YEAR(A2),MONTH(A2),DAY(EOMONTH(A2,0)))) --close date <= last date of current month

希望我的问题足够清楚，在此先感谢

标签： sqlgroup-byrmysql

解决方案

使用窗口函数：

SELECT DATE_FORMAT(op.close_date, '%Y-%m'),
       SUM(op.sale_amount) as this_month,
       SUM(op.sale_amount) OVER (ORDER BY MIN(op.close_date) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as running_6_months
FROM opportunities op
GROUP BY DATE_FORMAT(op.close_date, '%Y-%m')
ORDER BY DATE_FORMAT(op.close_date, '%Y-%m');

推荐阅读