mysql - 如何在 mySQL 中计算比率并创建连接表的透视输出？

Question

我有两张 table 订单和广告。两者都已合并，但现在需要进行调整。常规枢轴函数如何工作？

Answer 1

我认为你可以只计算测试和订阅中的用户数量，并计算每个组的比例:

SELECT e.experiment_id,
       SUM( e.experiment_assignment = 'test' AND s.user_id IS NOT NULL AND s.subscription_event = 'subscription_start') / SUM(e.experiment_assignment = 'test') as test_ratio,
       SUM( e.experiment_assignment = 'control' AND s.user_id IS NOT NULL AND s.subscription_event = 'subscription_start') / SUM(e.experiment_assignment = 'test') as control_ratio
FROM experiments e LEFT JOIN 
     subscriptions s 
     ON e.user_id = s.user_id
GROUP BY e.experiment_id;

这假设特定实验的两个表中的用户都不重复。

编辑:

如果有重复项，请使用COUNT(DISTINCT):

SELECT e.experiment_id,
       (COUNT(DISTINCT CASE WHEN e.experiment_assignment = 'test' AND s.subscription_event = 'subscription_start' THEN s.user_id END) /
        COUNT(DISTINCT CASE WHEN e.experiment_assignment = 'test' THEN e.user_id END) 
       ) as test_ratio,
       (COUNT(DISTINCT CASE WHEN e.experiment_assignment = 'control' AND s.subscription_event = 'subscription_start' THEN s.user_id END) /
        COUNT(DISTINCT CASE WHEN e.experiment_assignment = 'control' THEN e.user_id END) 
       ) as control_ratio
FROM experiments e LEFT JOIN 
     subscriptions s 
     ON e.user_id = s.user_id
GROUP BY e.experiment_id;