MySQL 按日期的百分比份额

Question

我有以下结构:

    +-------------+---------+------------+
| impressions | device  | dates      |
+-------------+---------+------------+
|        4788 | desktop | 2019-04-29 |
|        6336 | mobile  | 2019-04-29 |
|        2798 | tablet  | 2019-04-29 |
|        4911 | desktop | 2019-04-30 |
|        6005 | mobile  | 2019-04-30 |
|        3103 | tablet  | 2019-04-30 |
|        5053 | desktop | 2019-05-01 |
|        6131 | mobile  | 2019-05-01 |
|        2873 | tablet  | 2019-05-01 |
|        4446 | desktop | 2019-05-02 |
|        6345 | mobile  | 2019-05-02 |
|        2864 | tablet  | 2019-05-02 |

如何计算每个日期每台设备的百分比份额？

Answer 1

按日期分组并使用条件聚合:

select
  dates,
  round(100.0 * sum(case when device = 'desktop' then impressions end) / sum(impressions), 2) as desktop,
  round(100.0 * sum(case when device = 'mobile' then impressions end) / sum(impressions), 2) as mobile,
  round(100.0 * sum(case when device = 'tablet' then impressions end) / sum(impressions), 2) as tablet
from tablename
group by dates;

请参阅demo .
结果:

| dates      | desktop | mobile | tablet |
| ---------- | ------- | ------ | ------ |
| 2019-04-29 | 34.39   | 45.51  | 20.1   |
| 2019-04-30 | 35.03   | 42.83  | 22.13  |
| 2019-05-01 | 35.95   | 43.62  | 20.44  |
| 2019-05-02 | 32.56   | 46.47  | 20.97  |