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

SELECT visit_date AS date,
concat(round(avg(CASE WHEN order_date IS NOT NULL THEN 1 ELSE 0 END)*100,1),'%') AS cr
FROM(
    SELECT DISTINCT date(a.visit_time) AS visit_date,a.user_id,date(b.order_time) AS order_date
    FROM visit_tb as a
    LEFT JOIN  order_tb AS b
    ON a.user_id = b.user_id AND date(a.visit_time) = date(b.order_time)
    ORDER BY visit_date ASC,user_id ASC
) AS c
GROUP BY visit_date 
ORDER BY visit_date ASC

本人MySQL小白，写了个两层SELECT的解法,就是将visit表和order表进行连接去重从而统计出每个用户每天的访问情况和购买情况，由于使用的是LEFT JOIN 所以即使访问了没购买也会统计出来且order_date是NULL,这样第二层只用计算order_date为NULL的占比就可以了