Easy之1322.广告效果

小灰灰 2022-10-11 13:33 182阅读 0赞

### 表: Ads ###

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ad_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>user_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>action</td> 
   <td>enum</td> 
  </tr> 
 </tbody> 
</table>

(ad\_id, user\_id) 是该表的主键  
该表的每一行包含一条广告的 ID(ad\_id)，用户的 ID(user\_id) 和用户对广告采取的行为 (action)  
action 列是一个枚举类型 (‘Clicked’, ‘Viewed’, ‘Ignored’) 。

### 问题 ###

一家公司正在运营这些广告并想计算每条广告的效果。

广告效果用点击通过率（Click-Through Rate：CTR）来衡量，公式如下:  
写一条SQL语句来查询每一条广告的 ctr ，  
ctr 要保留两位小数。结果需要按 ctr 降序、按 ad\_id 升序 进行排序。

### 示例 ###

查询结果示例如下：

##### Ads 表: #####

<table> 
 <thead> 
  <tr> 
   <th>ad_id</th> 
   <th>user_id</th> 
   <th>action</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>Clicked</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>Clicked</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>Viewed</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>5</td> 
   <td>Ignored</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>7</td> 
   <td>Ignored</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>7</td> 
   <td>Viewed</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>5</td> 
   <td>Clicked</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>4</td> 
   <td>Viewed</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>11</td> 
   <td>Viewed</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2</td> 
   <td>Clicked</td> 
  </tr> 
 </tbody> 
</table>

##### result表: #####

对于 ad\_id = 1, ctr = (2/(2+1)) \* 100 = 66.67  
对于 ad\_id = 2, ctr = (1/(1+2)) \* 100 = 33.33  
对于 ad\_id = 3, ctr = (1/(1+1)) \* 100 = 50.00  
对于 ad\_id = 5, ctr = 0.00, 注意 ad\_id = 5 没有被点击 (Clicked) 或查看 (Viewed) 过  
注意我们不关心 action 为 Ingnored 的广告  
结果按 ctr（降序），ad\_id（升序）排序

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/ads-performance  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

select ad_id,
    	round(ifnull(sum(action='Clicked')/
    	(sum(action ='Clicked')+sum(action='Viewed'))*100,0),2) as ctr
    from ads 
    group by ad_id 
    order by ctr desc, ad_id

其中，sum(action=‘Clicked’)/(sum(action =‘Clicked’)+sum(action=‘Viewed’)等价于sum(action=‘Clicked’)/sum(action<>‘Ignored’)