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

-- 1. 处理订单表：获取每个用户每日首次下单日期（去重，同一用户单日仅计1次）
WITH t1 AS (
    SELECT 
        user_id, 
        MIN(DATE(order_time)) AS order_time_1  -- 取用户当日首次下单日期
    FROM order_tb  
    GROUP BY DATE(order_time), user_id  -- 按用户+日期分组去重
), 
-- 2. 处理访问表：获取用户每日日首次访问日期（去重，同一用户当日仅计1次）
t2 AS (
    SELECT 
        user_id, 
        MIN(DATE(visit_time)) AS visit_time_1  -- 取用户当日首次访问日期
    FROM visit_tb 
    GROUP BY DATE(visit_time), user_id  -- 按用户+日期分组去重
)  
-- 3. 计算每日转化率：当日下单人数/当日访问人数（百分比）
SELECT 
    t2.visit_time_1 AS date,  -- 统计日期（以访问日期为准）
    -- 计算转化率并格式化：四舍五入保留1位小数+百分号
    CONCAT(
        ROUND(
            SUM(CASE WHEN t1.user_id IS NOT NULL THEN 1 ELSE 0 END)  -- 当日下单用户数
            / COUNT(t2.user_id)  -- 当日访问总用户数
            * 100, 
            1
        ), 
        '%'
    ) AS cr 
FROM t2 
-- 左连接关联：仅匹配"同一用户+同一日期"的访问和下单记录
LEFT JOIN t1 
    ON t2.user_id = t1.user_id 
    AND t2.visit_time_1 = t1.order_time_1 
GROUP BY t2.visit_time_1  -- 按日期分组统计
ORDER BY t2.visit_time_1 ;  -- 按日期升序排序