题目: 计算广告评论比例
假设你有一个ads表，包含ID和广告名称，比如“劳动节衬衫促销”。feed_comments表保存了不同用户在常规信息流中对广告的评论。moments_comments表保存了不同用户在moments中对广告的评论。
编写一个查询，获取广告在feed和moments中的评论比例。
示例：
输入：
feed_comments表
| 列名 | 类型 |
|---|---|
ad_id | 整数 |
user_id | 整数 |
comment_id | 整数 |
moments_comments表
| 列名 | 类型 |
|---|---|
ad_id | 整数 |
user_id | 整数 |
comment_id | 整数 |
ads表
| 列名 | 类型 |
|---|---|
id | 整数 |
name | VARCHAR |
输出:
| 名称 | feed评论比例 | moments评论比例 |
|---|---|---|
| 劳动节 | .6 | .4 |
| Polo衬衫 | .85 | .15 |
答案
对于每个广告，我们想要的是来自feed和moments的评论比例：
% feeds = feed的评论数 / (feed的评论数 + moments的评论数)
% moments = moments的评论数 / (feed的评论数 + moments的评论数)
先看看feed_comments表。想要求每个ad_id的总评论数，我们可以简单地使用GROUP BY来计数。
SELECT ad_id, COUNT(DISTINCT comment_id) AS num_comments
FROM feed_comments AS fc
GROUP BY 1
这里还需要注意到一个细节：如果一个广告在feed_comments表中不存在，但存在于ads表或moments_comments表中，上面的方法就会将该广告过滤掉，使其评论计数为零。
我们可以通过将表左连接到来解决这个问题，同样moments_comments表也需要执行相同的操作。
WITH fc AS (SELECT ads.id AS ad_id, COUNT(DISTINCT comment_id) AS num_commentsFROM adsLEFT JOIN feed_comments AS fc ON ads.id = fc.ad_idGROUP BY 1
),
mc AS (SELECT ads.id AS ad_id, COUNT(DISTINCT comment_id) AS num_commentsFROM adsLEFT JOIN moments_comments AS mc ON ads.id = mc.ad_idGROUP BY 1
)
SELECT * FROM fc,mc
现在给定这两个计数，我们可以将它们与广告表连接起来，获取每个广告的名称，并计算总比例的方程式。
WITH fc AS (SELECT ads.id AS ad_id, COUNT(DISTINCT comment_id) AS num_commentsFROM adsLEFT JOIN feed_comments AS fc ON ads.id = fc.ad_idGROUP BY 1
),
mc AS (SELECT ads.id AS ad_id, COUNT(DISTINCT comment_id) AS num_commentsFROM adsLEFT JOIN moments_comments AS mc ON ads.id = mc.ad_idGROUP BY 1
)
SELECT ads.name, fc.num_comments/(fc.num_comments + mc.num_comments) AS percentage_feed, mc.num_comments/(fc.num_comments + mc.num_comments) AS percentage_moments
FROM ads
LEFT JOIN fc ON ads.id = fc.ad_id
LEFT JOIN mc ON ads.id = mc.ad_id
更多详细答案可关注公众号查阅。
