mysql - 使用具有相同 where 子句的 2 个子查询合并 SQL 查询

Question

我的应用程序使用带有 locationId、scoreDateTime、score 和 comment 列的分数表。用户可以对位置进行评分并可选择提交评论。一个小数据集可能如下所示:

mysql> select locationId, scoreDateTime, score, comments from scores;
+-----------------------------+-------------------------+-------+--------------------------------+
| locationId                  | scoreDateTime           | score | comments                       |
+-----------------------------+-------------------------+-------+--------------------------------+
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 17:30:32.899 |     3 | asdfasf                        |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 18:28:46.221 |     3 |                                |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 18:29:56.395 |     3 | safasf                         |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 18:32:10.358 |     3 |                                |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 18:49:32.262 |     3 |                                |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 18:50:33.693 |     3 |                                |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 19:13:58.456 |     3 |                                |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ | 2016-04-17 19:28:10.435 |     3 | asdfasf                        |
| ChIJqZyf8O8F44kRhatfHL4GYe0 | 2016-04-17 23:20:28.857 |     3 | aasdfasfsfsd                   |
| ChIJqZyf8O8F44kRhatfHL4GYe0 | 2016-04-17 23:22:55.254 |     3 | asdfasfasfsafasfsfasf asdfasfd |
| ChIJqZyf8O8F44kRhatfHL4GYe0 | 2016-04-17 23:40:37.106 |     3 |                                |
| ChIJpbSR1a4I44kRemEzTpniis8 | 2016-04-19 11:17:41.836 |     5 | adfgadf                        |
| ChIJF1LAoqgI44kR5EWvRqJPUN4 | 2016-04-19 11:17:52.536 |     4 |                                |
+-----------------------------+-------------------------+-------+--------------------------------+

我想构建一个查询，为每个位置获取以下信息:

• 过去 X 小时的分数统计
• 最近 Y 天的评论数
• 最近 Y 天内任何评论的最新 scoreDateTime(或 NULL)

我的动机是显示位置、他们最近的分数计数、他们的历史评论计数以及他们最新的评论日期时间(或空)。这将给我最近的运行分数计数和评论线索的热度。

以下查询有效。但是，重复的 locationId 列表实际上在生产中会更高。 问题:我想知道是否有一种高效的方法来合并 2 个 locationId 列表，也就是“locationId in (...)”。

select
   x.locationId, count1, count2, count3, count4, count5, IFNULL(commentCount,0) as commentCount, lastCommentDateTime
from

   ( select
        locationId,
        sum(if (score = 1, 1, 0)) count1,
        sum(if (score = 2, 1, 0)) count2,
        sum(if (score = 3, 1, 0)) count3,
        sum(if (score = 4, 1, 0)) count4,
        sum(if (score = 5, 1, 0)) count5
     from
        scores
     where
        scoreDateTime > '2016-04-16 21:38:51.843' and
        locationId in (
           'ChIJqZyf8O8F44kRbNWHQkDkpGQ',
           'ChIJqZyf8O8F44kRhatfHL4GYe0',
           'ChIJCes00a4I44kRKG8zB4KvYTM',
           'ChIJP-eRLq8I44kRKU6VOpTXqTM',
           'ChIJpbSR1a4I44kRemEzTpniis8',
           'ChIJF1LAoqgI44kRip2l7rjO2g4',
           'ChIJF1LAoqgI44kR5EWvRqJPUN4',
           'ChIJF1LAoqgI44kRRD_ZvPUmrGA',
           'ChIJjweq4h0G44kRWoCPQKPdrPM',
           'ChIJf2tVDB4G44kRTYjhl3sjm8M',
           'ChIJ_Vg4giEG44kRq2nvtjEn8yA',
           'ChIJP00qFSMG44kRyKcy2f_S12o'
        )
     group by locationId
   ) as x

left join

   ( select
        locationId,
        count(comments) as commentCount,
        max(scoreDateTime) as lastCommentDateTime
     from
        scores
     where
        comments != "" and
        scoreDateTime > '2016-01-16 00:00:00.000' and
        locationId in (
           'ChIJqZyf8O8F44kRbNWHQkDkpGQ',
           'ChIJqZyf8O8F44kRhatfHL4GYe0',
           'ChIJCes00a4I44kRKG8zB4KvYTM',
           'ChIJP-eRLq8I44kRKU6VOpTXqTM',
           'ChIJpbSR1a4I44kRemEzTpniis8',
           'ChIJF1LAoqgI44kRip2l7rjO2g4',
           'ChIJF1LAoqgI44kR5EWvRqJPUN4',
           'ChIJF1LAoqgI44kRRD_ZvPUmrGA',
           'ChIJjweq4h0G44kRWoCPQKPdrPM',
           'ChIJf2tVDB4G44kRTYjhl3sjm8M',
           'ChIJ_Vg4giEG44kRq2nvtjEn8yA',
           'ChIJP00qFSMG44kRyKcy2f_S12o'
        )
     group by locationId
   ) as y

on x.locationId = y.locationId;

结果如下所示:

mysql> source ../../query3.sql
+-----------------------------+--------+--------+--------+--------+--------+--------------+-------------------------+
| locationId                  | count1 | count2 | count3 | count4 | count5 | commentCount | lastCommentDateTime     |
+-----------------------------+--------+--------+--------+--------+--------+--------------+-------------------------+
| ChIJF1LAoqgI44kR5EWvRqJPUN4 |      0 |      0 |      0 |      1 |      0 |            0 | NULL                    |
| ChIJpbSR1a4I44kRemEzTpniis8 |      0 |      0 |      0 |      0 |      1 |            1 | 2016-04-19 11:17:41.836 |
| ChIJqZyf8O8F44kRbNWHQkDkpGQ |      0 |      0 |      8 |      0 |      0 |            3 | 2016-04-17 19:28:10.435 |
| ChIJqZyf8O8F44kRhatfHL4GYe0 |      0 |      0 |      3 |      0 |      0 |            2 | 2016-04-17 23:22:55.254 |
+-----------------------------+--------+--------+--------+--------+--------+--------------+-------------------------+

Answer 1

看起来您的 2 个查询之间的区别在于 scoreDateTime 和 comments 标准。组合查询的一种方法是使用条件聚合将这些条件移动到您的 select。

此外，mysql 将 bool 值计算为 1 或 0，因此您可以通过删除 if 语句来简化您的 sum 调用。

select
    locationId,
    sum(score = 1 and scoreDateTime > '2016-04-16 21:38:51.843') count1,
    sum(score = 2 and scoreDateTime > '2016-04-16 21:38:51.843') count2,
    sum(score = 3 and scoreDateTime > '2016-04-16 21:38:51.843') count3,
    sum(score = 4 and scoreDateTime > '2016-04-16 21:38:51.843') count4,
    sum(score = 5 and scoreDateTime > '2016-04-16 21:38:51.843') count5,
    sum(comments != "") commentCount,
    max(case when comments != "" then scoreDateTime end) as lastCommentDateTime
    from
    scores
    where
    scoreDateTime > '2016-01-16 00:00:00.000' and
    locationId in (
        'ChIJqZyf8O8F44kRbNWHQkDkpGQ',
        'ChIJqZyf8O8F44kRhatfHL4GYe0',
        'ChIJCes00a4I44kRKG8zB4KvYTM',
        'ChIJP-eRLq8I44kRKU6VOpTXqTM',
        'ChIJpbSR1a4I44kRemEzTpniis8',
        'ChIJF1LAoqgI44kRip2l7rjO2g4',
        'ChIJF1LAoqgI44kR5EWvRqJPUN4',
        'ChIJF1LAoqgI44kRRD_ZvPUmrGA',
        'ChIJjweq4h0G44kRWoCPQKPdrPM',
        'ChIJf2tVDB4G44kRTYjhl3sjm8M',
        'ChIJ_Vg4giEG44kRq2nvtjEn8yA',
        'ChIJP00qFSMG44kRyKcy2f_S12o'
    )
    group by locationId

此查询可以利用 (locationId, scoreDateTime) 上的复合索引