时间戳

首页 > 解决方案 > GROUP BY 和 SUM 但拆分 GROUP BY 结果

mysql - GROUP BY 和 SUM 但拆分 GROUP BY 结果

问题描述

数据集：

User    Internal Hours   External Hours   Date
Dave         9               0           2020-11-01
Joe          1               0           2020-11-01
Joe          1               0           2020-11-02
Chris        2               0           2020-11-01
Joe          3               0           2020-11-03
Chris        2               0           2020-11-02
Chris        0               0           2020-11-03
Dave         3               0           2020-11-02
Joe          5               0           2020-11-03
Chris        0               3           2020-11-03
Chris        0               1           2020-11-01
Dave         0               1           2020-11-01
Joe          0               2           2020-11-01
Joe          0               4           2020-11-02
Joe          0               2           2020-11-03
Chris        0               3           2020-11-02
Chris        0               3           2020-11-03
Dave         0               1           2020-11-03

一个简单的：

SELECT
    User,
    Date
    SUM(External)
    SUMInternal)
FROM table
GROUP BY User,Date

返回：

User   Date      Internal Hours   External Hours
Dave  2020-11-01    12              1          
Dave  2020-11-01    0               1           
Joe   2020-11-01    1               4          
Joe   2020-11-02    1               5           
Chris 2020-11-02    2               6           
Chris 2020-11-03    2               6

或somethgin equivilent（真的很难从我的数据中弄清楚实际总结了什么，但每个用户总是有2个实例，而且所有事情似乎都在2个日期下总结出来）

然而，我所追求的是：

User   Date      Internal Hours   External Hours
Dave  2020-11-01    9               1          
Dave  2020-11-02    3               0           
Dave  2020-11-03    0               1   
Joe   2020-11-01    1               2          
Joe   2020-11-02    1               4       
Joe   2020-11-03    3               2          
Chris 2020-11-01    2               1           
Chris 2020-11-02    2               3          
Chris 2020-11-03    0               3

使用一个结果表，每个日期、每个用户和所有内容都正确总结

我尝试过内部连接、左连接、交叉连接、内部选择，但没有一个工作正常或处理时间超过 5 分钟（超过 135,000 条记录）。

我知道有一个明显的方法我错过了，我不想用子选择强行使用它，并且让这份每日报告需要几个小时。

有什么想法吗？

标签： mysql

解决方案

SELECT
    User,
    `Date`,
    COALESCE(SUM(External), 0),
    COALESCE(SUM(Internal), 0)
FROM (SELECT DISTINCT User FROM test) t1
CROSS JOIN (SELECT DISTINCT `Date` FROM test) t2
LEFT JOIN test USING (User, `Date`)
GROUP BY User, `Date`
ORDER BY 1,2;

小提琴

交叉连接的子查询生成所有可能的用户数据对。即使这对在数据中也不存在，它将出现在输出中。

推荐阅读