mysql 查询中的几类问题

continue_v

已于 2022-06-30 19:51:23 修改

阅读量352

点赞数

分类专栏：窗口函数文章标签： mysql

于 2022-06-30 19:47:16 首次发布

本文链接：https://blog.csdn.net/continue_v/article/details/125535613

版权

窗口函数专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、留存率问题

次日留存率：第二天有访问行为的留存用户/注册当天的新客总数。

第3日留存率：第3天内内访问的留存用户/注册当天的新客户总数。

第7日留存率：第7天内访问的留存用户/注册当天的新客户总数。

第30日留存率：第30内访问的留存用户/注册当天的新客户总数。

案例：

create table `tb_user_login`
(
`user_id` varchar(300) comment '用户ID',
`login_date` date comment '登录日期'
) engine=innodb;
 
insert into `tb_user_login` (`user_id` , `login_date`)
values
    ('A', '2019/9/2'),
    ('A', '2019/9/3'),
    ('A', '2019/9/4'),
    ('B', '2019/9/2'),
    ('B', '2019/9/4'),
    ('B', '2019/9/10'),
    ('C', '2019/1/1'),
    ('C', '2019/1/2'),
    ('C', '2019/1/30'),
    ('C', '2019/9/3'),
    ('C', '2019/9/4'),
    ('C', '2019/9/5'),
    ('C', '2019/9/11'),
    ('C', '2019/9/12'),
    ('C', '2019/9/13');

问题一：查询连续3天登录的用户ID

思路：1.根据登陆时间字段创建排序辅助列，row_number() over（）窗口函数

2.将登录时间与添加的辅助列差值运算（subdate()函数等同于date_sub（）函数功能），得到新的日期，并根据该日期分组

3.对结果进行筛选

select distinct user_id
from (
    select user_id, login_date,row_number() over(partition by user_id order by login_date) as rn from tb_user_login
) as temp 
group by user_id, subdate(login_date, rn)
having count(1) >= 3;

问题二：查询每天新增用户数以及他们次日留存率和三十日留存率

方法一：

1.根据登陆时间字段创建时间辅助列 min(login_date) over() 得到最早登录时间

2.使用聚合函数sum（）、时间运算函数datediff（）计算登录时间与最早登录时间的差值，差值为1为次日；差值为30为三十日；

3.新增用户数对用户id去重

4.根据计算公式求值

select 
count(distinct user_id) 新增用户数,
sum(datediff(login_date,reg_time)=1) 次日留存数,
sum(datediff(login_date,reg_time)=30) 三十日留存数,
sum(datediff(login_date,reg_time)=1)/count(distinct user_id) 次日留存率,
sum(datediff(login_date,reg_time)=30)/count(distinct user_id) 三十日留存率
from (select user_id,login_date,min(login_date) over(partition by user_id order by login_date) reg_time from tb_user_login)t1
group by reg_time;

方法二：

1.根据登陆时间字段创建时间辅助列 min(login_date) over() 得到最早登录时间，作为子查询表

2.将原表与子表做联合查询，追加联合查询的条件，其他与方法一类似

SELECT 
first_login,
COUNT(DISTINCT t1.user_id) as 新增用户数,
COUNT(DISTINCT t2.user_id) /COUNT(DISTINCT t1.user_id) as 次日留存,
COUNT(DISTINCT t3.user_id) /COUNT(DISTINCT t1.user_id) as 三十日留存 
from (
SELECT user_id,login_date,MIN(login_date) over (PARTITION by user_id ORDER BY login_date ) as first_login from tb_user_login
)t1 
LEFT JOIN tb_user_login as t2 
on t1.user_id = t2.user_id and DATEDIFF(t2.login_date,first_login)=1
left JOIN tb_user_login as t3
on t1.user_id = t3.user_id and DATEDIFF(t3.login_date,first_login)=29
GROUP BY first_login;

二、某商品活动用户下单量问题

公式：每天平均订单量：某段时间内的订单量/天数

案例：

create table `tb_order`
(
`order_id` bigint unsigned comment '订单ID',
`user_id` varchar(300) comment '用户ID',
`order_pay` int comment '订单金额',
`order_time` datetime comment '下单时间'
) engine=innodb;

insert into `tb_order` values 
    (1, 'A', 100, '2021-11-10'),
    (1, 'A', 200, '2021-11-10'),
    (2, 'B', 300, '2021-11-10'),
    (3, 'C', 400, '2021-11-11'),
    (3, 'C', 500, '2021-11-11'),
    (3, 'C', 600, '2021-11-11'),
    (4, 'A', 700, '2021-11-12'),
    (4, 'A', 800, '2021-11-12'),
    (5, 'B', 700, '2021-11-12'),
    (6, 'B', 600, '2021-11-12'),
    (7, 'A', 500, '2021-11-13'),
    (7, 'A', 400, '2021-11-13'),
    (8, 'D', 300, '2021-11-12'),
    (9, 'E', 200, '2021-11-12');

create table `tb_act_apply`
(
`act_id` int unsigned comment '活动编号',
`user_id` varchar(300) comment '用户ID',
`act_time` datetime comment '报名时间'
) engine=innodb;

insert into `tb_act_apply` values 
    (1, 'A', '2021-11-11'),
    (2, 'B', '2021-11-09'),
    (3, 'C', '2021-11-11'),
    (1, 'D', '2021-11-12');

问题一：查询每个活动对应所有用户在报名后产生的总订单金额，总订单数
说明：每个用户只能参与一个活动，用户报名后产生的订单就是参加活动的订单。

思路：订单表与用户报名表联合查询，此处使用 join()……using() 效果等同于 join()……on

SELECT act_id as 活动编号,SUM(order_pay) as 订单金额,COUNT(DISTINCT order_id) as 总订单数 
from tb_order t1 JOIN tb_act_apply t2 using(user_id) 
where t1.order_time>=t2.act_time 
GROUP BY act_id;

问题二：查询每个活动从开始后到“2021年11月13日”前平均每天产生的订单数（活动开始时间定义为最早有用户报名的时间）

思路：1.使用聚合函数 min()…… over（）添加辅助列，得到不同活动的开始时间列，将该表作为一个查询子表

2.筛选订单时间大于活动时间的用户

3.根据商品和活动时间分组

-- 使用聚合函数创建辅助列，得到不同活动的开始时间
SELECT act_id,user_id,act_time,min(act_time) over (PARTITION BY act_id) as begin_time from tb_act_apply;

-- 关键点：对用户ID去重统计用户数量/满足要求的天数
SELECT 
act_id,
COUNT(DISTINCT order_id) / DATEDIFF('2021-11-13',begin_time) as 平均订单数量
from (SELECT act_id,user_id,act_time,min(act_time) over (PARTITION BY act_id) as begin_time from tb_act_apply) t1 JOIN tb_order t2 using(user_id)
WHERE order_time >= act_time and order_time<'2021-11-13' 
GROUP BY act_id,begin_time;

三、流程操作问题

每天用户的平均操作次数：当天的操作次数 / 用户数；注意对用户id去重

案例：

create table `tb_tracking_log`
(
`user_id` varchar(300) comment '用户ID',
`oper_id` int unsigned comment '操作ID',
`log_time` datetime comment '操作时间'
) engine=innodb;

insert into `tb_tracking_log` values 
    ('A', 1, '2021-11-11 09:20:00'),
    ('A', 2, '2021-11-11 09:21:00'),
    ('A', 5, '2021-11-11 09:22:00'),
    ('B', 5, '2021-11-11 09:20:00'),
    ('B', 1, '2021-11-11 09:21:00'),
    ('C', 4, '2021-11-12 09:20:00'),
    ('D', 1, '2021-11-12 09:20:00'),
    ('D', 3, '2021-11-12 09:21:00'),
    ('D', 2, '2021-11-12 09:22:00'),
    ('E', 1, '2021-11-11 09:20:00'),
    ('E', 2, '2021-11-12 09:21:00');

问题一：查询每天的访客数和他们的平均操作次数（简单）

select 
    date(log_time) as date,
    count(distinct user_id) as 访客数,
    count(1) / count(distinct user_id) as 平均操作次数
from tb_tracking_log group by date;

问题二：查询每天符合以下条件的用户ID。
要求：操作1之后是操作2，操作1和2必须相邻

思路：1.使用向下偏移窗口函数，添加辅助列 lead( )…… over(partion by …order by …)作为查询子表

2.将原表与子表联合查询，若原表oper_id=1，子表辅助列next_op_id=2，则满足要求

-- 使用向下偏移窗口函数，添加辅助列lead(exp_str,offset,defval) over(partion by …order by …)
SELECT *,LEAD(`oper_id`,1,0) over (PARTITION by DATE(log_time),user_id ORDER BY log_time) as next_op_id from tb_tracking_log;
-- 与原表联合查询
SELECT user_id 
from (SELECT *,LEAD(`oper_id`,1,0) over (PARTITION by DATE(log_time),user_id ORDER BY log_time)as next_op_id from tb_tracking_log) t1 
where oper_id=1 and next_op_id=2;