汇总广告点击次数和观看次数 [英] Aggregate ad clicks and views

问题描述

鉴于下表，该表跟踪每个广告系列的点击和观看次数:

Given the following table, which tracks clicks and views per ad campaign:

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

是否可以通过单个查询汇总点击次数和观看次数，即看起来像这样:

Is it possible to aggregate the clicks and views with a single query, i.e. so that it looks like this:

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

推荐答案

按advert_id分组并使用条件聚合

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

这篇关于汇总广告点击次数和观看次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！