当前位置：首页 > article >正文

转化率(漏斗分析)——mysql计算过程

article 2025/3/1 3:19:05

转化率(漏斗分析)——mysql计算过程

问题：有一张表，记录了不同用户的用户id，浏览页面时间，加入购物车时间，下单时间，支付时间，算出每天的各个环节的转化率

创建表info(含用户id，浏览页面时间，加入购物车时间，下单时间，支付时间)

CREATE TABLE info (id INT,seepage DATE,cart DATE,orders DATE,pay DATE);

清空表内容

TRUNCATE TABLE info;

插入数据

INSERT INTO info (id, seepage, cart, orders, pay)
VALUES 
(1,'2025-01-01','2025-01-02','2025-01-03','2025-01-04'),
(2,'2025-01-01','2025-01-01','2025-01-01','2025-01-01'),
(3,'2025-01-01','2025-01-04','2025-01-04','2025-01-04'),
(4,'2025-01-01','2025-01-02',null,null),
(5,'2025-01-01','2025-01-02','2025-01-02','2025-01-02'),
(6,'2025-01-01',null,null,null),
(7,'2025-01-01','2025-01-02','2025-01-02','2025-01-03'),
(8,'2025-01-01','2025-01-02','2025-01-02','2025-01-02'),
(9,'2025-01-01','2025-01-04','2025-01-05','2025-01-05'),
(10,'2025-01-01','2025-01-02','2025-01-04','2025-01-04'),
(11,'2025-01-03',null,null,null),
(12,'2025-01-03',null,null,null),
(13,'2025-01-04',null,null,null),
(14,'2025-01-03','2025-01-04',null,null),
(15,'2025-01-02','2025-01-03',null,null),
(16,'2025-01-04','2025-01-04',null,null),
(17,'2025-01-01',null,null,null),
(18,'2025-01-05',null,null,null),
(19,'2025-01-02','2025-01-03','2025-01-05',null),
(20,'2025-01-02','2025-01-02','2025-01-05',null),
(21,'2025-01-01','2025-01-02','2025-01-03',null),
(22,'2025-01-01','2025-01-02','2025-01-03','2025-01-04'),
(23,'2025-01-01','2025-01-01','2025-01-01','2025-01-01'),
(24,'2025-01-01','2025-01-03','2025-01-03','2025-01-03'),
(25,'2025-01-01','2025-01-02',null,null),
(26,'2025-01-01','2025-01-02','2025-01-02','2025-01-05'),
(27,'2025-01-01',null,null,null),
(28,'2025-01-01','2025-01-02','2025-01-02','2025-01-04'),
(29,'2025-01-01','2025-01-02','2025-01-02','2025-01-05'),
(30,'2025-01-01','2025-01-02','2025-01-02','2025-01-02')

查询表

select * from info;

在这里插入图片描述

转化率计算

select 
seepage as 时间,
page_t.page_nums as 浏览人数,
cart_t.cart_nums as 加购物车人数,
(cart_t.cart_nums/page_t.page_nums)as 加购物车转化率,
orders_t.orders_nums as 下单人数,
(orders_t.orders_nums/cart_t.cart_nums)as 下单转化率,
pay_t.pay_nums as 支付人数,
(pay_t.pay_nums/orders_t.orders_nums)as 支付转化率
from
(select seepage,count(distinct id) as page_nums from info group by seepage)as page_t
left join (select cart,count(distinct id)as cart_nums from info group by cart)as cart_t on page_t.seepage = cart_t.cart
left join (select orders,count(distinct id)as orders_nums from info group by orders)as orders_t on page_t.seepage = orders_t.orders
left join (select pay,count(distinct id)as pay_nums from info group by pay)as pay_t on page_t.seepage = pay_t.pay;