mysql - 尝试在 MySQL 中划分 2 个单独的查询

Question

我看过一些关于划分两个单独查询的帖子，这些帖子似乎很有帮助，但我仍然无法划分这两个查询。我编写了不同的子查询并遵循了一些示例，但我不断收到错误，因为示例查询似乎更直接(无联接)。

这是第一个查询:

SELECT 
    YEAR(s.created_at) AS year,
    COUNT(*) AS pre_sub_buys
FROM subscription_users s
INNER JOIN users u
ON s.user_id = u.uid
LEFT JOIN canvases c
ON u.email = c.ref_email
WHERE c.is_paid=1 AND c.date_created < s.created_at
GROUP BY year;

我试图将其除以:

SELECT 
    YEAR(s.created_at) AS year,
    COUNT(s.created_at) AS subscribers
FROM subscription_users s
LEFT JOIN canvases c
ON c.entries_updated_at = s.updated_at
GROUP BY year;

本质上，我希望找到预订阅购买和订阅者之间的年平均值。

任何人都可以指导我如何正确执行此操作的正确方向吗？

非常感谢，乔纳森

Answer 1

您可以使用条件聚合来解决此问题:

SELECT 
    YEAR(s.created_at) AS year,

    COUNT(CASE WHEN c.is_paid=1 AND c.date_created < s.created_at THEN 1
               ELSE NULL
          END) AS pre_sub_buys, 

    COUNT(s.created_at) AS subscribers, 

    COUNT(CASE WHEN c.is_paid=1 AND c.date_created < s.created_at THEN 1
               ELSE NULL
          END) / COUNT(s.created_at) AS pre_sub_buys_divided_by_subscribers

FROM subscription_users s
INNER JOIN users u
        ON s.user_id = u.uid
LEFT JOIN canvases c
        ON u.email = c.ref_email 
GROUP BY year;