MySQL - 每种类型值的总计

Question

我有一个名为门户事件的表，用于存储网站上的事件 - 事件发生的时间及其类型(“l”、“w”或“a”)。

我正在尝试获取过去 6 个月内每种类型的总数(忽略未发生事件的任何月份)，也就是说，我最终会得到如下结果:

date_identifier    total_l    total_a    total_w
2017-07            5          4          10
2017-08            8          2          13
...etc

这是我的查询。问题是它为我提供了每行相同的值(总体总数)，而不是特定于该事件类型的总数。

SELECT
    DISTINCT SUBSTRING(pe.date, 1, 7) AS date_identifier,
    COUNT(DISTINCT pe_l.id) AS total_l,
    COUNT(DISTINCT pe_w.id) AS total_w,
    COUNT(DISTINCT pe_a.id) AS total_a
FROM 4diboxnnnqt pe
LEFT JOIN 4diboxnnnqt pe_l ON pe_l.user = pe.user && pe_l.type = "l"
LEFT JOIN 4diboxnnnqt pe_a ON pe_a.user = pe.user && pe_a.type = "a"
LEFT JOIN 4diboxnnnqt pe_w ON pe_w.user = pe.user && pe_w.type = "w"
WHERE
    pe.date > DATE_SUB(NOW(), INTERVAL 6 MONTH) &&
    pe.user IN (SELECT user FROM 2oz5a186fle ue JOIN jh4iw0ii196 pc ON ue.user_type IN ("s", "ds") && pc.id = ue.promo && pc.parent_teacher_code = 'nvcdig8isly')
GROUP BY date_identifier

给我，例如:

date_identifier    total_l    total_a    total_w
2017-07            8          2          13
2017-08            8          2          13
2017-09            8          2          13
2017-10            8          2          13
2017-11            8          2          13
2017-12            8          2          13

任何人都可以看到我做错了什么以获得此重复，而不是逐月总数吗？我认为 GROUP_BY 可以解决这个问题。

查询结构背后的基本原理是从事件表中提取日期，以获取发生任何类型的事件时的唯一月份，然后连接到自身，每种类型一次，以获得差异化的总数。逻辑不好？

(P.S.我知道我可以使用子查询来做到这一点，但我认为它们会更慢......)

Answer 1

啊啊。我认为你只需要条件聚合:

SELECT LEFT(pe.date, 7) AS date_identifier,
       SUM(pe.type = 'l') AS total_l,
       SUM(pe.type = 'w') AS total_w,
       SUM(pe.type = 'a') AS total_a
FROM 4diboxnnnqt pe
WHERE pe.date > DATE_SUB(NOW(), INTERVAL 6 MONTH) AND
      pe.user IN (SELECT user
                  FROM 2oz5a186fle ue JOIN
                       jh4iw0ii196 pc
                       ON ue.user_type IN ('s', 'ds') AND
                          pc.id = ue.promo AND
                          pc.parent_teacher_code = 'nvcdig8isly'
                 )
GROUP BY date_identifier;

注释:

• bool 值 and 的 ANSI 标准是 AND，而不是 &&。
• 引号字符串的 ANSI 标准使用单引号，而不是双引号。
• 如果没有 JOIN，我认为 COUNT(DISTINCT) 是不必要的。