mysql - 获取所有时间前 5 个国家最近 7 天的总数

Question

我有一个名为 events 的表，如下所示:

 id |    date    | country |  event | count
--------------------------------------------- 
 1  | 2018-03-16 |   RS    | views  | 33577
 2  | 2018-03-21 |   US    | plays  | 232712
 3  | 2018-03-14 |   AR    | clicks | 469159
 4  | 2018-03-18 |   US    | plays  | 415732
 5  | 2018-03-26 |   US    | views  | 442159
 6  | 2018-03-22 |   BA    | plays  | 181610
 7  | 2018-03-21 |   UK    | plays  | 57899
 8  | 2018-03-17 |   CZ    | views  | 64605
 9  | 2018-03-17 |   BA    | plays  | 375444
 10 | 2018-03-22 |   US    | plays  | 148073
 11 | 2018-03-23 |   BA    | views  | 181644
 12 | 2018-03-22 |   CZ    | clicks | 411953
 13 | 2018-03-15 |   AR    | views  | 329597
 14 | 2018-03-18 |   BA    | clicks | 480834
 15 | 2018-03-17 |   CZ    | plays  | 279060
 16 | 2018-03-19 |   MN    | views  | 611975
 17 | 2018-03-26 |   MN    | clicks | 227115
 18 | 2018-03-24 |   BA    | plays  | 176248
 19 | 2018-03-14 |   UK    | views  | 288072
 20 | 2018-03-18 |   UK    | plays  | 598980

我需要获取所有时间前 5 个国家/地区过去 7 天内每个事件的计数总和。目前，我有一个查询可以做到这一点:

SELECT
    `c`.`country`,
    `event`,
    SUM(`count`) AS `total`
FROM
    `eem_events` `a`
INNER JOIN(
    SELECT
        `country`
    FROM
        `eem_events` `b`
    GROUP BY
        `country`
    ORDER BY
        SUM(`count`)
    DESC
LIMIT 5
) AS `c`
WHERE
    `date` > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY
    `c`.`country`,
    `event`

这个查询返回这个:

 country | event  | count 
-----------------------------
   AR    | views  | 43084323
   AR    | plays  | 46487773
   AR    | clicks | 44581794
   BA    | views  | 43084323
   BA    | plays  | 46487773
   BA    | clicks | 44581794
   DE    | views  | 43084323
   DE    | plays  | 46487773
   DE    | clicks | 44581794
   MN    | views  | 43084323
   MN    | plays  | 46487773
   MN    | clicks | 44581794
   UK    | views  | 43084323
   UK    | plays  | 46487773
   UK    | clicks | 44581794 

我想得到的是:

 country |   views   |   plays   |  clicks   
-----------------------------------------------
   AR    | 43084323  | 46487773  | 44581794
   BA    | 43084323  | 46487773  | 44581794
   DE    | 43084323  | 46487773  | 44581794
   MN    | 43084323  | 46487773  | 44581794
   UK    | 43084323  | 46487773  | 44581794 

我现在尝试这样做大约 12 个小时，但我没有更多想法。您可以看到观看次数、播放次数和点击次数具有相同的值。你能帮我重构我的 SQL 查询以获得这个结果吗？

我正在使用 MySQL/MariaDB。

Answer 1

你只需要添加条件SUM()

SELECT
    `c`.`country`,
    SUM(CASE WHEN event = 'views'  THEN `count` ELSE 0 END) AS `views`,
    SUM(CASE WHEN event = 'plays'  THEN `count` ELSE 0 END) AS `plays`,
    SUM(CASE WHEN event = 'clicks' THEN `count` ELSE 0 END) AS `clicks`
FROM
    `eem_events` `a`
INNER JOIN(
    SELECT    `country`
    FROM      `eem_events` `b`
    GROUP BY  `country`
    ORDER BY   SUM(`count`) DESC
    LIMIT 5
) AS `c`
  ON `a`.`country` = `c`.`country`
WHERE
    `date` > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY
    `c`.`country`