题解 | #统计用户从访问到下单的转化率#

这道题目要求我们计算商城每天的用户转化率，我们要做的事情如下：

1. 确定总体问题

我们需要计算每天的用户转化率，即在某一天访问商城的用户中有多少比例下了订单。转化率以百分数形式输出，并保留一位小数。转化率的计算公式是：下订单的用户数 / 访问用户数。

2. 分析关键问题

• 计算每日访问用户数：从visit_tb中提取每天的访问用户数。
• 计算每日下订单用户数：从order_tb中提取每天的下订单用户数。
• 计算转化率：将每日下订单用户数与访问用户数结合，计算转化率。
• 格式化输出：将转化率以百分数形式输出，并保留一位小数。

3. 解决每个关键问题的代码及讲解

步骤1：计算每日访问用户数

我们使用WITH子句创建一个临时表daily_visits，计算每天的访问用户数：

WITH daily_visits AS (
    SELECT DATE(visit_time) AS date, COUNT(DISTINCT user_id) AS visit_users
    FROM visit_tb
    GROUP BY DATE(visit_time)
)

• DATE(visit_time) AS date：提取访问日期。
• COUNT(DISTINCT user_id) AS visit_users：计算每天的唯一访问用户数，DISTINCT去重。

步骤2：计算每日下订单用户数

我们使用WITH子句创建另一个临时表daily_orders，计算每天的下订单用户数：

daily_orders AS (
    SELECT DATE(order_time) AS date, COUNT(DISTINCT user_id) AS order_users
    FROM order_tb
    GROUP BY DATE(order_time)
)

• DATE(order_time) AS date：提取订单日期。
• COUNT(DISTINCT user_id) AS order_users：计算每天的唯一下订单用户数。

步骤3：计算转化率

我们将两个临时表连接起来，计算转化率：

SELECT 
    v.date,
    CONCAT(ROUND(COALESCE(o.order_users, 0) / v.visit_users * 100, 1), '%') AS cr
FROM 
    daily_visits v
LEFT JOIN 
    daily_orders o ON v.date = o.date

• LEFT JOIN：确保即使某天没有订单，访问数据也能显示。
• COALESCE(o.order_users, 0)：如果某天没有订单，order_users为NULL，用0替代。
• ROUND(..., 1)：将转化率四舍五入保留一位小数。
• CONCAT(..., '%')：将转化率格式化为百分数。

步骤4：格式化输出

我们使用ORDER BY按日期升序排序输出结果：

ORDER BY 
    v.date;

完整代码

WITH daily_visits AS (
    SELECT DATE(visit_time) AS date, COUNT(DISTINCT user_id) AS visit_users
    FROM visit_tb
    GROUP BY DATE(visit_time)
),
daily_orders AS (
    SELECT DATE(order_time) AS date, COUNT(DISTINCT user_id) AS order_users
    FROM order_tb
    GROUP BY DATE(order_time)
)
SELECT 
    v.date,
    CONCAT(ROUND(COALESCE(o.order_users, 0) / v.visit_users * 100, 1), '%') AS cr
FROM 
    daily_visits v
LEFT JOIN 
    daily_orders o ON v.date = o.date
ORDER BY 
    v.date;