MySQL 每用户转化率

Question

我目前正在为我的一位客户开发统计程序，但我似乎不知道如何计算转化率。

表格如下:

User_id     order_id    plan_id
12          12          16     
12          13          2
12          14          2
14          15          5
14          16          16
15          17          16

订单 ID 16是试用，剩下的都是付费的。所以你可以看到用户 15 只有试用期。所以他没有把免费转成付费。

用户 12 和 14 都有试用版 (plan_id=16) 和付费版 (plan_id<>16)，这意味着他已从免费转换为付费。

所以我正在寻找一个用于计算转化率的方向指针。我正在寻找以下方向的查询(或输出我需要的金额):

Converted   Notconverted
1200        1800

这是针对用户的。那么有多少用户为试用过的帐户付费。

我有这个疑问；但我似乎无法将其分组到用户上，因为显然它会返回更多行。

SELECT (SELECT COUNT(*) AS TotalOrders FROM orders where (plan_id<>17) GROUP BY user_id) AS SuccessCount, (SELECT COUNT(*) AS TotalFreeAccounts FROM orders where (plan_id=16) GROUP BY user_id) AS TrialCount

<小时/>

编辑:几乎就在那里(:

到目前为止，我们遇到了以下查询，它似乎适用于转换后的查询。但不适合未皈依的人。作为一个小调整，方案17也是一个尝试:

SELECT 
    count(Distinct TN2.user_id) as Converted,
    count(Distinct TN.user_id) as NotConverted
FROM orders TN
INNER JOIN orders TN2
 on TN.user_id  = TN2.user_id
 and (TN.plan_id = 16 OR TN.plan_id = 17)
 and (TN2.plan_id <> 16 AND TN2.plan_id<>17)

结果完全相同，但这不可能

两者都设置为相同的数字，这是可能的。我很确定这对于未转换的人来说是正确的。

感谢您提前提供的帮助。

Answer 1

假设我理解这个问题:

使用自加入来查找进行了试用(计划 16)的用户，并加入到进行了非试用计划(不是计划 16)的同一用户

使用 LEFT JOIN 是因为我们对那些从 TryOut 开始然后可能已成为非 tryout 计划的人感兴趣。我们不关心那些没有试用的用户。

使用不同是因为我们不关心我们计数的非试用计划，只关心任何计划。

所以在你的例子中:

User_id     order_id    plan_id
12          12          16     
12          13          2
12          14          2
14          15          5
14          16          16
15          17          16

以下查询的结果应该产生(假设逻辑和语法正确)

CONVERTED  NOTCONVERTED
2          1

用户 15 有试用计划，但没有其他计划，因此未转换。用户 12 有一个试用计划和多次非试用，因此只计算一次。用户 14 有一个试用计划和一个其他计划，因此被计入一次。

SELECT count(Distinct TN2.user_ID) as Converted, 
       count(Distinct TN.User_ID) as NotConverted   
FROM tableName TN
LEFT JOIN TableName TN2
 on TN.User_ID  = TN2.user_ID
 and TN.Plan_ID = 16
 and TN2.Plan_ID <> 16