MySQL实战2
文章目录
主要内容
- 回访用户
- 如何找到每个人每月消费的最大天数
一.回访用户
1.准备工作
代码如下（示例）:
drop database if exists db_1;
create database db_1;
use db_1;
CREATE TABLE tb_visits (
user_id INT,
date DATE
);
INSERT INTO tb_visits (user_id, date)
VALUES
(1, current_timestamp() - interval 0 day),
(1, current_timestamp() - interval 0 day),
(1, current_timestamp() - interval 1 day),
(1, current_timestamp() - interval 2 day),
(1, current_timestamp() - interval 3 day),
(1, current_timestamp() - interval 4 day),
(2, current_timestamp() - interval 1 day),
(4, current_timestamp() - interval 0 day),
(4, current_timestamp() - interval 1 day),
(4, current_timestamp() - interval 3 day),
(4, current_timestamp() - interval 4 day),
(4, current_timestamp() - interval 62 day),
(4, current_timestamp() - interval 62 day),
(5, current_timestamp() - interval 1 day),
(5, current_timestamp() - interval 3 day),
(5, current_timestamp() - interval 4 day)
;
select * from tb_visits order by user_id, date;
2.目标
-
说明 ：回访用户
-
返回连续访问该页面最⻓的3个用户,按⻓短的倒序排列3个用户
-
问题：在如下的数据库表中，包含有关用户访问网页的信息。完成SQL返回连续访问该页面最长的3个用户，按长短的倒序排列3个用户。
-
输入
-
输出
3.实现
代码如下（示例）:
-- todo 第一步: 去重
with t1 as (
select
distinct user_id, date
from tb_visits
),
-- todo 第二步: 根据 user_id 分堆, 再根据 date 排序(正序)
t2 as (
select
user_id, date,
row_number() over (partition by user_id order by date asc) as rn
from t1
),
-- todo 第三步: 伪代码 dt2 = date -rn
t3 as (
select
user_id, date, rn,
date_add(date, interval -rn day) as dt2
from t2
),
-- todo 第四步: 求每个用戶连续访问的天数, 连续访问的开始日期和结束日期
t4 as (
select
user_id, dt2,
count(1) as cnt,-- 连续天数
min(date) as start_date,-- 开始日期
max(date) as end_date-- 结束日期
from t3
group by user_id, dt2
),
-- todo 第五步: 求每个人访问的最大天数 先排序
t5 as (
select
user_id, dt2, cnt, start_date, end_date,
row_number() over (partition by user_id order by cnt desc) as rn2
from t4
),
-- todo 第六步: 求每个人访问的最大天数 再过滤 ... where rn2=1
t6 as (
select
user_id, dt2, cnt, start_date, end_date, rn2
from t5
where rn2=1
),
-- todo 第七步: 求最大连续天数的top3 先排序
t7 as (
select
user_id, dt2, cnt, start_date, end_date, rn2,
rank() over (order by cnt desc) as rn3
from t6
),
-- todo 第八步: 求最大连续天数的top3 再过滤
t8 as (
select
*
from t7
where rn3<=3
)
select user_id, cnt, start_date, end_date from t8
;
二.如何找到每个人每月消费的最大天数
1.准备工作
代码如下（示例）:
drop database if exists db_1;
create database db_1;
use db_1;
create table tb_card
(
card_nbr varchar(32),
c_date varchar(32),
c_type varchar(32),
c_atm int
);
insert into tb_card
values
(1, '2022-01-01', '网购', 150),
(1, '2022-01-01', '网购', 100),
(1, '2022-01-02', '网购', 200),
(1, '2022-01-03', '网购', 300),
(1, '2022-01-15', '网购', 100),
(1, '2022-01-16', '网购', 200),
(2, '2022-01-06', '网购', 500),
(2, '2022-01-07', '网购', 800),
(1, '2022-02-02', '网购', 200),
(1, '2022-02-03', '网购', 300),
(1, '2022-02-04', '网购', 300),
(1, '2022-02-05', '网购', 300),
(1, '2022-02-08', '网购', 800),
(1, '2022-02-09', '网购', 900),
(2, '2022-02-05', '网购', 500),
(2, '2022-02-06', '网购', 500),
(2, '2022-02-07', '网购', 800),
(2, '2022-02-07', '网购', 850)
;
select * from tb_card;
2.目标
-
说明
-
有一张C_T (列举了部分数据）表示持卡人消费记录，表结构如下：
-
每个月每张卡连续消费的最大天数（如卡在当月只有一次消费则为1）。
-
连续消费天数：指一楼时间内连续每天都有消费，同一天有多笔消费算一天消费，不能跨月份统计。
-
输入
-
输出
3.实现
代码如下（示例）:
with t1 as (
select
distinct card_nbr, c_date
from tb_card
),
t2 as (
select
card_nbr,
substr(c_date, 1, 7) as c_month,
c_date,
--substr(c_date, 1, 7) as c_month：从消费日期中提取出年份和月份，形成一个新的字段c_month。这样我们就可以按照月份进行分组。
row_number() over (partition by card_nbr, substr(c_date, 1, 7) order by
from t1
),
t3 as (
select
card_nbr, c_month, c_date, rn1,
date_add(c_date, interval -rn1 day) as dt2
from t2
),
t4 as (
select
card_nbr, c_month, dt2,
count(1) as cnt -- todo 连续消费的天数
from t3
group by card_nbr, c_month, dt2
)
select
card_nbr, c_month,
max(cnt) as 连续消费的最大天数
from t4
group by card_nbr, c_month
;
4.解释
代码如下（示例）:
以下是每个子查询的解释：
1. 子查询t1：从tb_card表中选择不同的卡号和消费日期。
2. 子查询t2：从t1中选择卡号、消费月份和消费日期，并使用row_number()函数为每个卡号和月份组合编号。
3. 子查询t3：从t2中选择卡号、消费月份、消费日期、编号和消费日期减去编号天数的结果。
4. 子查询t4：从t3中选择卡号、消费月份、消费日期和每个日期组合的连续消费天数，并使用count()函数计算连续消费天数。
最后，查询语句从t4中选择卡号、消费月份和最大连续消费天数，并使用group by子句按卡号和月份分组。
总结
MySQL实战1
以上是今天要讲的内容，实战了：回访用户，如何找到每个人每月消费的最大天数。