描述
现有某个商城部分订单数据,用户访问数据,如下所示:
订单信息表: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%;
其他结果同理。
示例1
输入:
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');
输出:
date|cr
2022-09-01|75.0%
2022-09-02|50.0%
2022-09-03|66.7%
答案
解法1:
select
date,
concat(round(buyNbr/viewNbr*100,1),"%") cr
from
(
select
date(order_time) date,
count(distinct user_id) buyNbr
from
order_tb
group by
date(order_time)
) t1
natural join (
select
date(visit_time) date,
count(distinct user_id) viewNbr
from
visit_tb
group by
date(visit_time)
) t2
order by date;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*