统计用户从访问到下单的转化率_

现有某个商城部分订单数据，用户访问数据，如下所示：

订单信息表：order_tb（订单id：order_id，用户id：user_id，订单金额：order_price，订单创建时间：order_time）

order_id	user_id	order_price	order_time
101	11	380	2022-09-01 09:00:00
102	12	200	2022-09-01 10:00:00
103	13	260	2022-09-01 12:00:00
104	11	100	2022-09-02 11:00:00
105	12	150	2022-09-02 12:00:00
106	12	1200	2022-09-02 13:00:00
107	11	60	2022-09-03 09:00:00
108	13	380	2022-09-03 09:30:00

访问信息表：visit_tb（访问信息id：info_id，用户id：user_id，访问时间：visit_time，离开时间：leave_time）

info_id	user_id	visit_time	leave_time
911	10	2022-09-01 08:00:00	2022-09-01 09:02:00
912	11	2022-09-01 08:30:00	2022-09-01 09:10:00
913	12	2022-09-01 09:50:00	2022-09-01 10:12:00
914	13	2022-09-01 11:40:00	2022-09-01 12:22:00
921	11	2022-09-02 10:30:00	2022-09-02 11:05:00
922	11	2022-09-02 12:00:00	2022-09-02 12:02:00
923	12	2022-09-02 11:40:00	2022-09-02 13:15:00
924	13	2022-09-02 09:00:00	2022-09-02 09:02:00
925	14	2022-09-02 10:00:00	2022-09-02 10:40:00
931	10	2022-09-03 09:00:00	2022-09-03 09:22:00
932	11	2022-09-03 08:30:00	2022-09-03 09:10:00
933	13	2022-09-03 09:00:00	2022-09-03 09:32:00

请统计该商城每天用户从访问到下订单的转化率。
要求输出：日期，转化率（该日下订单人数/访问人数，以百分数形式输出并四舍五入保留1位小数）

注：输出结果按照日期升序排序；

示例数据结果如下：

date	cr
2022-09-01	75.0%
2022-09-02	50.0%
2022-09-03	66.7%

结果解释：

以2022-09-01为例，该日共计有user_id为10、11、12、13共计4名用户访问商城，

其中11、12、13共计3名用户下了订单，故转化率为3/4=75.0%；

其他结果同理。

输入

drop table if exists  `order_tb` ; 
CREATE TABLE `order_tb` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`order_price` int(11) NOT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_id`));
INSERT INTO order_tb VALUES(101,11,380,'2022-09-01 09:00:00'); 
INSERT INTO order_tb VALUES(102,12,200,'2022-09-01 10:00:00'); 
INSERT INTO order_tb VALUES(103,13,260,'2022-09-01 12:00:00'); 
INSERT INTO order_tb VALUES(104,11,100,'2022-09-02 11:00:00'); 
INSERT INTO order_tb VALUES(105,12,150,'2022-09-02 12:00:00'); 
INSERT INTO order_tb VALUES(106,12,1200,'2022-09-02 13:00:00'); 
INSERT INTO order_tb VALUES(107,11,60,'2022-09-03 09:00:00'); 
INSERT INTO order_tb VALUES(108,13,380,'2022-09-03 09:30:00'); 

drop table if exists  `visit_tb` ; 
CREATE TABLE `visit_tb` (
`info_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`visit_time` datetime NOT NULL,
`leave_time` datetime NOT NULL,
PRIMARY KEY (`info_id`));
INSERT INTO visit_tb VALUES(0911,10,'2022-09-01 08:00:00','2022-09-01 09:02:00'); 
INSERT INTO visit_tb VALUES(0912,11,'2022-09-01 08:30:00','2022-09-01 09:10:00'); 
INSERT INTO visit_tb VALUES(0913,12,'2022-09-01 09:50:00','2022-09-01 10:12:00'); 
INSERT INTO visit_tb VALUES(0914,13,'2022-09-01 11:40:00','2022-09-01 12:22:00'); 
INSERT INTO visit_tb VALUES(0921,11,'2022-09-02 10:30:00','2022-09-02 11:05:00'); 
INSERT INTO visit_tb VALUES(0922,11,'2022-09-02 12:00:00','2022-09-02 12:02:00'); 
INSERT INTO visit_tb VALUES(0923,12,'2022-09-02 11:40:00','2022-09-02 13:15:00'); 
INSERT INTO visit_tb VALUES(0924,13,'2022-09-02 09:00:00','2022-09-02 09:02:00'); 
INSERT INTO visit_tb VALUES(0925,14,'2022-09-02 10:00:00','2022-09-02 10:40:00'); 
INSERT INTO visit_tb VALUES(0931,10,'2022-09-03 09:00:00','2022-09-03 09:22:00'); 
INSERT INTO visit_tb VALUES(0932,11,'2022-09-03 08:30:00','2022-09-03 09:10:00'); 
INSERT INTO visit_tb VALUES(0933,13,'2022-09-03 09:00:00','2022-09-03 09:32:00');

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

输入

输出

问题信息

热门推荐

相关试题