mysql - 如何在表达式中多次使用 COUNT() 实现 OVER？

Question

我有一个关于我正在编写以解决LeetCode问题的查询的问题。这是问题所在：

广告

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+

(ad_id, user_id) 是该表的主键。

此表的每一行都包含广告的 ID、用户的 ID 以及该用户对该广告采取的操作。action 列是一个 ENUM 类型（'Clicked', 'Viewed', 'Ignored'）。

一家公司正在投放广告并希望计算每个广告的效果。

广告的效果是使用点击率 (CTR) 衡量的，其中：

CTR = { 0 如果没有广告点击，则广告点击次数 /（广告点击次数 + 广告浏览量）否则

编写一个 SQL 查询来查找每个 Ad 的 ctr。

将 ctr 舍入到小数点后 2 位。如果出现平局，则按 ctr 降序排列结果表，并按 ad_id 升序排列结果表。

查询结果格式如下例：

广告表：

+-------+---------+---------+
| ad_id | user_id | action  |
+-------+---------+---------+
| 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 |
+-------+---------+---------+

这是示例数据和我尝试的解决方案的一个小技巧。尝试的解决方案转载如下：

SELECT DISTINCT t.ad_id, ROUND(
    IF(
        COUNT(c.ad_id) OVER (PARTITION BY t.ad_id) = 0, 
        0,
      COUNT(c.ad_id) OVER (PARTITION BY t.ad_id) * 100 / ( COUNT(c.ad_id) OVER (PARTITION BY t.ad_id) + COUNT(v.ad_id) OVER (PARTITION BY t.ad_id) )
    ), 2) as ctr
FROM Ads as t
LEFT JOIN Ads as c ON c.ad_id=t.ad_id AND c.user_id=t.user_id AND c.action='Clicked'
LEFT JOIN Ads as v ON v.ad_id=t.ad_id AND v.user_id=t.user_id AND v.action='Viewed'
GROUP BY t.ad_id, c.ad_id, v.ad_id
ORDER BY ctr DESC, t.ad_id

此查询的结果：

ad_id   ctr
1   50.00
2   50.00
3   50.00
5   0.00

正确的结果应该显示：

ad_id ctr
1, 66.67
3, 50.00
2, 33.33
5, 0.00

通过查看示例数据，我的猜测是 COUNT() 实际上并没有像我期望的那样按 t.ad_id 进行分区。50% CTR 结果可以通过我的 CTR 计算来解释，计算计算中的所有“点击”和所有“查看”实例。（另一方面，删除 CTR 计算中的 OVER 语句 - 只是计算，而不是条件 - 不会产生上述结果，正如我的假设所暗示的那样。所以我不确定这一点。）

我使用OVER的方式有问题吗？我的逻辑在这里有缺陷吗？

另外，我还有一个额外的问题：我在这里选择使用 JOIN，因为我假设 JOIN 比使用子查询更快。这是一个公平的假设吗？我正在为 Data Analyst 1 面试而学习 - 你认为面试官是否会关心我是否使用 JOIN 与子查询？

编辑：感谢forpas的解释，我能够想出一个比我原来的更简单的解决方案。我认为 forpas 在下面的回答中的解决方案可能仍然更可取，因为它明确处理表中的 NULL。

SELECT ad_id, ROUND(IF(
    SUM(action='Clicked') = 0,
    0,
    SUM(action='Clicked') * 100 / ( SUM(action='Clicked') + SUM(action='Viewed'))
), 2) as ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id

标签： mysqlsqlwindow-functions

Answer 1

您可以使用条件聚合来做到这一点：

SELECT ad_id,
  ROUND(100 * COALESCE(SUM(action = 'Clicked') / SUM(action IN ('Clicked', 'Viewed')), 0), 2) ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id;

您可以使用窗口函数获得相同的结果SUM()，但我认为这对于性能或可读性而言并不更好：

SELECT DISTINCT ad_id,
  ROUND(
    100 * 
    COALESCE(
      SUM(action = 'Clicked') OVER (PARTITION BY ad_id) / 
      SUM(action IN ('Clicked', 'Viewed')) OVER (PARTITION BY ad_id)
      , 0
    )
    , 2
  ) ctr
FROM Ads
ORDER BY ctr DESC, ad_id;

请参阅演示。
结果：

> ad_id |   ctr
> ----: | ----:
>     1 | 66.67
>     3 | 50.00
>     2 | 33.33
>     5 |  0.00