时间戳

首页 > 解决方案 > SQL - 单独对多列的所有时间、30 天和 90 天的数据求和

mysql - SQL - 单独对多列的所有时间、30 天和 90 天的数据求和

问题描述

背景：

我有看起来像这样的数据

date        src    subsrc   subsubsrc   param1  param2
2020-02-01  src1    ksjd    dfd8        47      31    
2020-02-02  src1    djsk    zmnc        44      95    
2020-02-03  src2    skdj    awes        92      100   
2020-02-04  src2    mxsf    kajs        80      2     
2020-02-05  src3    skdj    asio        46      53    
2020-02-06  src3    dekl    jdqo        19      18    
2020-02-07  src3    dskl    dqqq        69      18    
2020-02-08  src4    sqip    riow        64      46    
2020-02-09  src5    ss01    qwep        34      34

我正在尝试汇总过去 30 天和过去 90 天的所有时间（无滚动总和）

所以我的最终数据看起来像这样：

src     subsrc  subsubsrc   p1_all  p1_30   p1_90   p2_all  p2_30   p2_90
src1    ksjd    dfd8        7       1       7       98      7        98
src1    djsk    zmnc        0       0       0       0       0         0
src2    skdj    awes        12      12      12      4       4         4
src2    mxsf    kajs        6       6       6       31      31       31
src3    skdj    asio        0       0       0       0       0         0
src3    dekl    jdqo        20      20      20      17      17        17
src3    dskl    dqqq        3       3       3       4       4         4
src4    sqip    qwep        0       0       0       0       0         0
src5    ss01    qwes        15      15      15      2       2         2

关于数据：

我尝试过的：

这就是我想出的：

SELECT src, subsubsrc, subsubsrc,
SUM(param1) as param1_all,
SUM(CASE WHEN DATE_DIFF(CURRENT_DATE,date,day) <= 30 THEN param1 END) as param1_30,
SUM(CASE WHEN DATE_DIFF(CURRENT_DATE,date,day) <= 90 THEN param1 END) as param1_90,
SUM(param2) as param2_all,
SUM(CASE WHEN DATE_DIFF(CURRENT_DATE,date,day) <= 30 THEN param2 END) as param2_30,
SUM(CASE WHEN DATE_DIFF(CURRENT_DATE,date,day) <= 90 THEN param2 END) as param2_90,
FROM `MY_TABLE`
GROUP BY src
ORDER BY src

这实际上可行，但我可以预计对于多个源甚至更多参数列，此查询将变得多长时间。

我一直在尝试一种叫做“过滤聚合函数（或手动枢轴） ”的东西，在这里解释。但我无法为我的案例理解/实施它。

我还查看了几十个答案，其中大多数是每天的总和，或者是这个基本计算的复杂案例。也许我没有正确搜索它。

如您所见，我是 SQL 的新手，非常感谢任何帮助。

标签： mysqlsqlgroup-bypivotquery-optimization

解决方案

您的查询看起来相当不错；条件聚合是透视数据集的规范方法。

一种可能提高性能的方法是更改​​条件表达式中的日期过滤器：使用日期函数会排除使用索引。

相反，您可以将其表述为：

select 
    src, 
    subsrc, 
    subsubsrc,
    sum(param1) as param1_all,
    sum(case when date >= current_date - interval 30 day then param1 end) as param1_30,
    sum(case when date >= current_date - interval 90 day then param1 end) as param1_90,
    sum(param2) as param2_all,
    sum(case when date >= current_date - interval 30 day then param2 end) as param2_30,
    sum(case when date >= current_date - interval 90 day then param2 end) as param2_90
from my_table
group by src, subsrc, subsubsrc
order by src, subsrc, subsubsrc

对于性能，以下索引可能会有所帮助：(src, subsrc, subsubsrc, date).

src, subsrc, subsubsrc请注意，我在子句中包含了所有三个非聚合列 ( ) group by：从 MySQL 5.7 开始，默认情况下这是强制性的（尽管您可以使用 sql 模式来改变这种行为）——大多数其他数据库都实现了相同的约束。

推荐阅读