MySQL 按 GROUP BY 结果过滤

Question

我不知道这是否可以通过子查询有效实现，或者如何为此构建查询。我必须提取一些关于不再使用我们系统的人的知识。假设我们有 3 个用户，用户 ID 分别为 1024、1234 和 5678；用户 1024 和 1234 使用主题 A，用户 5678 使用主题 B:

$ SELECT * FROM user;  | $ SELECT * FROM user_theme;
+------+------+        | +------+-------+
|   id | name |        | | user | theme |
+------+------+        | +------+-------+
| 1024 | John |        | | 1024 |     A |
| 1234 | Jane |        | | 1234 |     A |
| 5678 | Jeff |        | | 5678 |     B |
+------+------+        | +------+-------+

使用情况跟踪表如下所示:

$ SELECT * FROM user_usage;
+----+------+---------------------+------+
| id | user | date                | uses |
+----+------+---------------------+------+
|  1 | 1234 | 2014-08-02 00:00:00 |    5 |
|  2 | 1234 | 2014-08-03 00:00:00 |    5 |
|  3 | 1234 | 2014-08-04 00:00:00 |    3 |
|  4 | 1234 | 2014-08-05 00:00:00 |    6 |
|  5 | 1024 | 2014-08-02 00:00:00 |    8 |
|  6 | 1024 | 2014-08-03 00:00:00 |    7 |
|  7 | 1024 | 2014-08-04 00:00:00 |    4 |
|  8 | 1024 | 2014-08-05 00:00:00 |    6 |
|  9 | 1024 | 2014-09-02 00:00:00 |    1 |
| 10 | 1024 | 2014-09-03 00:00:00 |    2 |
| 11 | 1024 | 2014-09-04 00:00:00 |    3 |
| 12 | 1024 | 2014-09-05 00:00:00 |    4 |
| 13 | 5678 | 2014-08-02 00:00:00 |    8 |
| 14 | 5678 | 2014-08-03 00:00:00 |    7 |
| 15 | 5678 | 2014-08-04 00:00:00 |    4 |
| 16 | 5678 | 2014-08-05 00:00:00 |    6 |
| 17 | 5678 | 2014-09-02 00:00:00 |    1 |
| 18 | 5678 | 2014-09-03 00:00:00 |    2 |
| 19 | 5678 | 2014-09-04 00:00:00 |    3 |
| 20 | 5678 | 2014-09-05 00:00:00 |    4 |
+----+------+---------------------+------+

我想详细了解 2014-09 年我们系统的使用量下降了多少(又名:2014-08 年有使用数据，但 2014-09 年不再有使用数据)，按主题分组。所以我想写一些类似的东西:

SELECT
    user_theme.theme,
    SUM(user_usage.users) 'uses lost'
FROM
    user_theme
    LEFT JOIN user_usage 
        ON user_theme.user = user_usage.user
WHERE
    ...
GROUP BY
    user_theme.theme
# HAVING ...?

并得到如下结果:

+-------+-----------+
| theme | uses lost |
+-------+-----------+
|     A |        19 |
|     B |         0 |
+-------+-----------+

其中 19 来自 SUM(uses)，其中 WHERE user = 1234 AND YEAR(date) = 2014 AND MONTH(date) = 8。我不知道我提前关心 user = 1234 的 SUM(uses) ，因为我只知道我需要将 user 1234 包含在 SUM(uses) 中的 WHERE 子句，因为 WHERE user = 1234 AND YEAR(date) = 2014 AND MONTH(date) = 9 的 SUM(uses) 为 0。

实际上有很多用户和一些主题(大约 20K 用户，大约 10 个主题)，所以理想情况下，我想我想避免在代码中进行过滤，而不是直接在数据库中进行过滤。有没有办法在 MySQL 中使用原始 SQL 查询有效地做到这一点？

Answer 1

以下查询将当前月份与上个月进行比较:

set @current_month = now();
set @previous_month = date_sub(@current_month, interval 1 month);

set @current_month = concat(year(@current_month), month(@current_month));
set @previous_month = concat(year(@previous_month), month(@previous_month));

select a.`theme`, sum(ifnull(b.uses_lost,0)) as uses_lost
from
`user_theme` as a
left outer join
(
select `user`, sum(uses) as uses_lost
from `user_usage`
where concat(year(`date`), month(`date`)) = @previous_month
  and `user` not in (
    select `user`
    from `user_usage`
    where concat(year(`date`), month(`date`)) = @current_month)
group by `user`
) as b
on (a.`user`=b.`user`)
group by a.`theme`;

fiddle for play

主要思想是找到上个月使用过系统且当月没有行的所有用户