• 题目：

• 

• sql建表语句：

• Create table If Not Exists Ads
  (ad_id   int,user_id int,action  ENUM ('Clicked', 'Viewed', 'Ignored')
  );
  Truncate table Ads;
  insert into Ads (ad_id, user_id, action)
  values ('1', '1', 'Clicked');
  insert into Ads (ad_id, user_id, action)
  values ('2', '2', 'Clicked');
  insert into Ads (ad_id, user_id, action)
  values ('3', '3', 'Viewed');
  insert into Ads (ad_id, user_id, action)
  values ('5', '5', 'Ignored');
  insert into Ads (ad_id, user_id, action)
  values ('1', '7', 'Ignored');
  insert into Ads (ad_id, user_id, action)
  values ('2', '7', 'Viewed');
  insert into Ads (ad_id, user_id, action)
  values ('3', '5', 'Clicked');
  insert into Ads (ad_id, user_id, action)
  values ('1', '4', 'Viewed');
  insert into Ads (ad_id, user_id, action)
  values ('2', '11', 'Viewed');
  insert into Ads (ad_id, user_id, action)
  values ('1', '2', 'Clicked');

• 分析：看到题目，我们先了解一下公式，了解完成之后，我们先按照ad_id分组，然后用if算出action为Clicked和Viewed的所有次数，然后再算出Clicked的次数，然后判断所有次数是否为零，如果为零，就返回零，如果不为零，就返回Clicked的次数、所有次数，然后*100保留两位小数。

• sql实现：

• select ad_id,round(if(sum(if(action != 'Ignored', 1, 0)) = 0, 0,sum(if(action = 'Clicked', 1, 0)) / sum(if(action != 'Ignored', 1, 0)))*100,2) ctr
  from Ads  -- 多层if判断来实现
  group by ad_id order by ctr desc,ad_id

• pandas例子：

• data = [[1, 1, 'Clicked'], [2, 2, 'Clicked'], [3, 3, 'Viewed'], [5, 5, 'Ignored'], [1, 7, 'Ignored'], [2, 7, 'Viewed'], [3, 5, 'Clicked'], [1, 4, 'Viewed'], [2, 11, 'Viewed'], [1, 2, 'Clicked']]
  ads = pd.DataFrame(data, columns=['ad_id', 'user_id']).astype({'ad_id':'Int64', 'user_id':'Int64', 'action':'object'})

• pandas分析，我们先按照ad_id分组算出所有的次数，然后再算出Clicked的次数，然后两个相除*100保留两位小数，然后我们再把所有id取出来然后在用merge左连接，把NaN值换成0，然后在排序就好了

• 实现：

import pandas as pddef ads_performance(ads: pd.DataFrame) -> pd.DataFrame:total_clicks = ads[ads['action'] == 'Clicked'].groupby('ad_id')['action'].count() --算出Clicked的次数total_views = ads[ads['action'] != 'Ignored'].groupby('ad_id')['action'].count() --算出Clicked和Viewed的总次数ctr = round(100 * total_clicks / total_views,2) --输出ctrads.drop_duplicates(subset = 'ad_id', keep = 'first', inplace = True) -- 找出所有的ad_idctr = ctr.reset_index(name = 'ctr') -- 重新生成索引，然后给action的列名改成ctrans = pd.merge(ads[['ad_id']],ctr,left_on = 'ad_id',right_on = 'ad_id', how = 'left').fillna(0).sort_values(by = ['ctr','ad_id'], ascending = [False,True],inplace = False)   -- 左连接，然后排序return ans

• 兄弟们有错误了要跟我说哦