mysql - 汇总广告点击次数和观看次数

Question

给定下表，该表跟踪每个广告活动的点击次数和浏览量：

record_id     advert_id     type
-----------------------------------
1             100           click
2             102           click
3             100           view
4             100           view
5             102           view
6             100           view
7             101           view
8             101           click

是否可以通过单个查询聚合点击次数和查看次数，即看起来像这样：

advert_id     clicks      views
-------------------------------
100           1           3
101           1           1
102           1           1

标签： mysql

Answer 1

分组 advert_id并使用条件聚合

select advert_id, 
       sum(type = 'click') as clicks,
       sum(type = 'view') as views
from your_table
group by advert_id