编辑代码

CREATE DATABASE t_view;
use t_view;
CREATE TABLE t_view (
	访问_id int,
	访问时间 varchar(255),
	访问用户id int,
	访问页面名称 varchar(255)
);

INSERT INTO t_view VALUES (1, '2023-01-01 13:44:39', 1111, '首页');
INSERT INTO t_view VALUES (2, '2023-01-01 16:44:39',  1111, '创建订单页');
INSERT INTO t_view VALUES (3, '2023-01-01 12:44:08',  2222, '首页');
INSERT INTO t_view VALUES (4, '2023-01-02 08:44:39',  3333, '首页');
INSERT INTO t_view VALUES (5, '2023-01-03 13:16:13',  4444, '首页');
INSERT INTO t_view VALUES (6, '2023-01-02 14:44:08',  3333, '创建订单页');
INSERT INTO t_view VALUES (7, '2023-01-01 13:08:39',  2222, '创建订单页');
INSERT INTO t_view VALUES (8, '2023-01-04 13:16:14',  1111, '首页');
INSERT INTO t_view VALUES (9, '2023-01-04 14:44:13',  3333, '首页');
INSERT INTO t_view VALUES (10, '2023-01-03 13:16:39',  2222, '首页');
INSERT INTO t_view VALUES (11, '2023-01-03 13:30:00',  2222, '创建订单页');
 



CREATE DATABASE ord;
use ord;
CREATE TABLE ord (
    订单_id int,
    支付用户id varchar(255),
    套餐内容 varchar(255),
    下单日期 date NOT NULL,
    订单状态  varchar(255)
);
INSERT INTO ord VALUES (10080, 2222,'套餐一', '2023-01-03 14:00:00','支付完成');
INSERT INTO ord VALUES (10081, 3333,'套餐一', '2023-01-04 15:23:00','支付完成');
INSERT INTO ord VALUES (10083, 2222,'套餐二', '2023-01-01 13:28:33','支付完成');
INSERT INTO ord VALUES (10084, 5555,'套餐二', '2023-01-01 13:28:33','支付完成');
INSERT INTO ord VALUES (10085, 1111,'套餐一', '2023-01-01 14:00:00','支付完成');
INSERT INTO ord VALUES (10086, 1111,'套餐一', '2023-01-04 15:23:00','取消');


SELECT * FROM t_view;
SELECT * FROM ord;


#表t_view是用户每日访问记录表，表ord是用户订单表

##问题一：请分别计算“每日访问用户数”；“每日支付完成用户数”，最后结果输出到一张表
select
tv_date as date,
count(distinct 访问用户id) as 每日访问用户数,
count(distinct 支付用户id) as 每日支付完成用户数
from (
    select 访问用户id,substring(访问时间,1,10) as tv_date
    from t_view ) tv
join(
    select 支付用户id,substring(下单日期,1,10) as od_date,订单状态
    from ord
    where 订单状态 = '支付完成' ) od 
on (tv.tv_date = od.od_date)
group by date;

##问题二：计算访问用户下单转化，即每天访问的用户中有多少下单的用户，输出：日期、当天访问用户、当天访问下单用户数
select tv_date as 日期,
count(distinct 访问用户id) as 每日访问用户数,
count(distinct 支付用户id) as 每日支付完成用户数
from(
    select 访问用户id,substring(访问时间,1,10) as tv_date
    from t_view)tv
left join(
    select 支付用户id,substring(下单日期,1,10) as od_date,订单状态
    from ord
    where 订单状态 = '支付完成') od
on (tv.tv_date = od.od_date) and (tv.访问用户id = od.支付用户id)
group by tv_date

##问题三：计算2023-01-01的的下单新用户中有多少复购的用户，复购的定义为有支付完成第二单
select 支付用户id as '复购用户id'
from (
    select ov.支付用户id,count(ov.支付用户id) as num
    from(
        select 支付用户id
        from ord
        where 订单状态 = '支付完成' and substring(下单日期,1,10) = '2023-01-01') od
    left join(
        select 支付用户id
        from ord
        where 订单状态 = '支付完成')ov 
    on ov.支付用户id =od.支付用户id
    group by ov.支付用户id
    ) o
where num>1