系列文章目录
第一章 行列互转专题
第二章 留存率专题
第三章 连续专题
文章目录
前言
案例解析腾讯，百度，拼多多，知乎大厂SQL面试题。
第一章 行列互转
第一章 行转列
1.1. 面试题：行转列
1.1.1. 抛真题
输入
输出
1.1.2. 分析
1.1.3. 准备工作
drop database if exists db_1;
create database db_1;
use db_1;
– 创建表
CREATE TABLE quarterly_sales (
year INT COMMENT ‘年’,
quarter INT COMMENT ‘季度’,
value DECIMAL(4, 1) COMMENT ‘值’,
PRIMARY KEY (year, quarter)
);
– 插入数据
INSERT INTO quarterly_sales (year, quarter, value)
VALUES
(2023, 1, 1.1),
(2023, 2, 1.2),
(2023, 3, 1.3),
(2023, 4, 1.4),
(2024, 1, 2.1),
(2024, 2, 2.2),
(2024, 3, 2.3),
(2024, 4, 2.4);
todo 需求: 求每年各季度的销售额
1.1.4. 实现
select
year,
sum(case when quarter=1 then value end) as m1,
sum(case when quarter=2 then value end) as m2,
sum(case when quarter=3 then value end) as m3,
sum(case when quarter=4 then value end) as m4
from quarterly_sales
group by year
1.1.5. 小结
（1）考查用case表达式进行数据替换和多条件判断的方法。
（2）遇到行列互换的问题时（见图4.28），可以用下面的万能模板来解决。
select
A,
– 第2步: 值 通过 case 和 sum 来获取
sum(case when B=1 then C else 0 end) as m1,
sum(case when B=2 then C else 0 end) as m2,
sum(case when B=3 then C else 0 end) as m3,
sum(case when B=4 then C else 0 end) as m4
from quarterly_sales
– 第1步: 先分组
group by A
1.2. 面试题：行转列_举一反三
1.2.1. 抛真题
输入
学号 课程 成绩
1 语文 80
1 数学 90
2 语文 75
2 数学 85
输出
1.2.2. 分析
1.2.3. 准备工作
drop database if exists db_1;
create database db_1;
use db_1;
– 创建表
CREATE TABLE score (
student_id INT COMMENT ‘学号’,
course VARCHAR(10) COMMENT ‘课程’,
grade INT COMMENT ‘成绩’,
PRIMARY KEY (student_id, course)
);
– 插入数据
INSERT INTO score (student_id, course, grade)
VALUES
(1, ‘语文’, 80),
(1, ‘数学’, 90),
(2, ‘语文’, 75),
(2, ‘数学’, 85);
– todo 需求: 行转列
1.2.4. 实现
– todo 需求: 行转列
select
student_id,
sum(case when course=‘语文’ then grade else 0 end) as 语文,
sum(case when course=‘数学’ then grade else 0 end) as 数学
from score
group by student_id
第二章 留存率
2.1. 面试题 腾讯视频号游戏直播
2.1.1. 基础知识
某日活跃用户（用户 id）在后续的一周内的留存情况（计算次日留存用户数，3 日留存用户数，7 日留存用户数）
指标定义：
某日活跃用户数，某日活跃的去重用户数。
N 日活跃用户数，某日活跃的用户数在之后的第 N 日活跃用户数。
N 日活跃留存率，N 日留存用户数 / 某日活跃用户数
例：登陆时间（2023-04-01）去重用户数 10000，这批用户在 (2023-04-07) 日仍有 7000 人活跃，则 7 日活跃留存率为 7000/10000=70%
次 N 日活跃用户数，某日活跃的用户数在之后的第 N + 1 日活跃用户数。
次 N 日活跃留存率，N + 1 日留存用户数 / 某日活跃用户数
例：登陆时间（2023-04-01）去重用户数 10000，这批用户在 (2023-04-08) 日仍有 7000 人活跃，则 次 7 日活跃留存率为 7000/10000=70%
2.1.2. 抛真题
输入
dt device user_id is_active
2020-03-01 ios 1001 0
2020-03-01 ios 1002 1
2020-03-01 ios 1004 1
2020-03-01 android 1003 1
2020-03-02 ios 1001 0
2020-03-02 ios 1002 0
2020-03-02 android 1003 1
2020-03-02 ios 1005 1
2020-03-02 ios 1004 1
题目要求: 2020-03-01 的ios设备用户活跃的次日留存率是多少？
输出
sum1 sum2 rate
2 1 0.5
2.1.3. 分析
2.1.4. 准备工作
– todo 准备工作
drop database if exists db_1;
create database if not exists db_1;
use db_1;
create table db_1.tb_tengxun_shipin (
dt varchar(32) comment ‘(日期)’,
device varchar(32) comment ‘设备’,
user_id varchar(32) comment ‘用户编号’,
is_active int comment ‘是否活跃’
)
;
insert into db_1.tb_tengxun_shipin
values
(‘2020-03-01’,‘ios’,‘0001’,0),
(‘2020-03-01’,‘ios’,‘0002’,1),
(‘2020-03-01’,‘ios’,‘0004’,1),
(‘2020-03-01’,‘android’,‘0003’,1),
(‘2020-03-02’,‘ios’,‘0001’,0),
(‘2020-03-02’,‘ios’,‘0002’,0),
(‘2020-03-02’,‘android’,‘0003’,1),
(‘2020-03-02’,‘ios’,‘0005’,1) ,
(‘2020-03-02’,‘ios’,‘0004’,1)
;
select * from db_1.tb_tengxun_shipin;
2.1.5. 实现
方案一
with t1 as (select distinct dt, user_id from tb_tengxun_shipin where device=‘ios’ and dt=‘2020-03-01’ and is_active=1),
t2 as (select distinct dt, user_id from tb_tengxun_shipin where device=‘ios’ and dt=‘2020-03-02’ and is_active=1)
select
count(t1.user_id) sum1,
count(t2.user_id) sum2,
count(t2.user_id) / count(t1.user_id) as rate
from t1 left join t2 on t1.user_id=t2.user_id
;
方案二
with t1 as (
select
*
from tb_tengxun_shipin
where is_active=1 and device=‘ios’
),
t2 as (
select
a.user_id,
a.dt as a_dt,
b.dt as b_dt,
timestampdiff(day, a.dt, b.dt) as date_diff
from t1 a
left join t1 b on a.user_id=b.user_id
)
select
a_dt as dt,
count(distinct user_id) as cnt,
count(distinct if(date_diff=1, user_id, null)) as ciri,
round(count(distinct if(date_diff=1, user_id, null)) / count(distinct user_id), 2) as ciri_rate
from t2
group by a_dt
having a_dt=‘2020-03-01’
;
2.1.6. 小结
2.2. 面试题 百度
2.2.1. 抛真题
说明: 写出用户表 20200401 的次日、次7日留存的具体HQL
一条sql统计出以下指标 （4.1号uv，4.1号在4.2号的留存uv，4.1号在4.8号的留存uv）
输入
cuid os soft_version event_day visit_time duration ext
1 android 1 2020-04-01 1234567 100
1 android 1 2020-04-02 1234567 100
1 android 1 2020-04-08 1234567 100
2 android 1 2020-04-01 1234567 100
3 android 1 2020-04-02 1234567 100
输出
sum1 sum2 sum8 rant1 rant2
2 1 1 0.5 0.5
2.2.2. 分析
2.2.3. 准备工作
drop database if exists db_1;
create database db_1;
use db_1;
create table if not exists db_1.tb_baidu
(
cuid varchar(32) comment ‘用户的唯一标识’,
os varchar(32) comment ‘平台’,
soft_version varchar(32) comment ‘版本’,
event_day varchar(32) comment ‘日期’,
visit_time int comment ‘用户访问时间戳’,
duration int comment ‘用户访问时长’,
ext varchar(32) comment ‘扩展字段’
);
insert into db_1.tb_baidu
values
(1, ‘android’, 1, ‘2020-04-01’, 1234567, 100, ‘’),
(1, ‘android’, 1, ‘2020-04-02’, 1234567, 100, ‘’),
(1, ‘android’, 1, ‘2020-04-08’, 1234567, 100, ‘’),
(2, ‘android’, 1, ‘2020-04-01’, 1234567, 100, ‘’),
(3, ‘android’, 1, ‘2020-04-02’, 1234567, 100, ‘’);
select * from tb_baidu;
2.2.4. 实现
with t1 as (
select
a.cuid,
a.event_day dt1,
b.event_day dt2,
timestampdiff(day, a.event_day, b.event_day) as date_diff
from db_1.tb_baidu a
left join db_1.tb_baidu b on a.cuid=b.cuid and a.event_day!=b.event_day
)
select
dt1 as dt,
count(distinct cuid) as cnt,
count(distinct if(date_diff=1, cuid, null)) as cnt_1,
count(distinct if(date_diff=7, cuid, null)) as cnt_7,
round(count(distinct if(date_diff=1, cuid, null)) / count(distinct cuid), 2) as rate_1,
round(count(distinct if(date_diff=7, cuid, null)) / count(distinct cuid), 2) as rate_7
from t1
group by dt1
;
2.2.5. 小结
第三章 连续专题
3.1 拼多多数据分析面试题：连续3次为球队得分的球员名单
3.1.1. 抛真题
两支篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一个两队分数的明细表（名称为“分数表”）。表中记录了球队、球员号码、球员姓名、得分分数及得分时间。现在球队要对比赛中表现突出的球员进行奖励。
问题：请你写一个SQL语句，统计出连续3次为球队得分的球员名单。
3.1.2. 准备工作
– 创建分数表，并为列名增加注释
CREATE TABLE tb_score (
team_name VARCHAR(50) COMMENT ‘球队名称’,
player_id INT COMMENT ‘球员ID’,
player_name VARCHAR(50) COMMENT ‘球员姓名’,
score INT COMMENT ‘得分’,
score_time DATETIME COMMENT ‘得分时间’
);
– 插入数据以满足条件
INSERT INTO tb_score (team_name, player_id, player_name, score, score_time) VALUES
(‘洛杉矶湖人队’, 23, ‘勒布朗·詹姆斯’, 3, ‘2023-12-25 10:00:00’),
(‘洛杉矶湖人队’, 23, ‘勒布朗·詹姆斯’, 3, ‘2023-12-25 10:15:00’),
(‘洛杉矶湖人队’, 23, ‘勒布朗·詹姆斯’, 1, ‘2023-12-25 10:30:00’),
(‘洛杉矶湖人队’, 3, ‘安东尼·戴维斯’, 2, ‘2023-12-25 10:32:00’),
(‘洛杉矶湖人队’, 23, ‘勒布朗·詹姆斯’, 3, ‘2023-12-25 10:45:00’),
(‘洛杉矶湖人队’, 23, ‘勒布朗·詹姆斯’, 3, ‘2023-12-25 11:00:00’),
(‘洛杉矶湖人队’, 23, ‘勒布朗·詹姆斯’, 2, ‘2023-12-25 11:15:00’),
(‘洛杉矶湖人队’, 23, ‘勒布朗·詹姆斯’, 2, ‘2023-12-25 11:30:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 1, ‘2023-12-25 10:10:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 1, ‘2023-12-25 10:25:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 1, ‘2023-12-25 10:40:00’),
(‘金州勇士队’, 11, ‘克莱·汤普森’, 2, ‘2023-12-25 10:45:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 2, ‘2023-12-25 10:55:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 2, ‘2023-12-25 11:10:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 3, ‘2023-12-25 11:25:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 3, ‘2023-12-25 11:40:00’),
(‘金州勇士队’, 30, ‘斯蒂芬·库里’, 3, ‘2023-12-25 11:55:00’);
select * from tb_score;
3.2 SQL194 知乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
3.2.1. 抛真题
描述
现有某乎问答创作者信息表author_tb如下(其中author_id表示创作者编号、author_level表示创作者级别，共1-6六个级别、sex表示创作者性别)：
author_id author_level sex
101 6 m
102 1 f
103 1 m
104 3 m
105 4 f
106 2 f
107 2 m
108 5 f
109 6 f
110 5 m
创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）：
answer_date author_id issue_id char_len
2023-11-01 101 E001 150
2023-11-01 101 E002 200
2023-11-01 102 C003 50
2023-11-01 103 P001 35
2023-11-01 104 C003 120
2023-11-01 105 P001 125
2023-11-01 102 P002 105
2023-11-02 101 P001 201
2023-11-02 110 C002 200
2023-11-02 110 C001 225
2023-11-02 110 C002 220
2023-11-03 101 C002 180
2023-11-04 109 E003 130
2023-11-04 109 E001 123
2023-11-05 108 C001 160
2023-11-05 108 C002 120
2023-11-05 110 P001 180
2023-11-05 106 P002 45
2023-11-05 107 E003 56
请你统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序），以上例子的输出结果如下：
author_id author_level days_cnt
101 6 3
3.2.2. 准备工作
drop database if exists db_1;
create database db_1;
use db_1;
drop table if exists author_tb;
CREATE TABLE author_tb
(
author_id int(10) NOT NULL,
author_level int(10) NOT NULL,
sex char(10) NOT NULL
);
INSERT INTO author_tb
VALUES
(101, 6, ‘m’),
(102, 1, ‘f’),
(103, 1, ‘m’),
(104, 3, ‘m’),
(105, 4, ‘f’),
(106, 2, ‘f’),
(107, 2, ‘m’),
(108, 5, ‘f’),
(109, 6, ‘f’),
(110, 5, ‘m’);
drop table if exists answer_tb;
CREATE TABLE answer_tb
(
answer_date date NOT NULL,
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL
);
INSERT INTO answer_tb
VALUES
(‘2021-11-1’, 101, ‘E001’, 150),
(‘2021-11-1’, 101, ‘E002’, 200),
(‘2021-11-1’, 102, ‘C003’, 50),
(‘2021-11-1’, 103, ‘P001’, 35),
(‘2021-11-1’, 104, ‘C003’, 120),
(‘2021-11-1’, 105, ‘P001’, 125),
(‘2021-11-1’, 102, ‘P002’, 105),
(‘2021-11-2’, 101, ‘P001’, 201),
(‘2021-11-2’, 110, ‘C002’, 200),
(‘2021-11-2’, 110, ‘C001’, 225),
(‘2021-11-2’, 110, ‘C002’, 220),
(‘2021-11-3’, 101, ‘C002’, 180),
(‘2021-11-4’, 109, ‘E003’, 130),
(‘2021-11-4’, 109, ‘E001’, 123),
(‘2021-11-5’, 108, ‘C001’, 160),
(‘2021-11-5’, 108, ‘C002’, 120),
(‘2021-11-5’, 110, ‘P001’, 180),
(‘2021-11-5’, 106, ‘P002’, 45),
(‘2021-11-5’, 107, ‘E003’, 56);
select * from author_tb;
select * from answer_tb;
3.2.3. 实现
请你统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序），以上例子的输出结果如下：
#todo author_id author_level days_cnt
#todo 101 6 3
with t1 as (
# 1 去重
select
author_id,
# 等差数列1
answer_date,
# 2 等差数列2: 排名 根据 作者id分组, 再根据日期排序
row_number() over (partition by author_id order by answer_date) as rn,
# 3 差 = 等差数列1 - 等差数列2
date_sub(answer_date, interval (row_number() over (partition by author_id order by answer_date)) day) diff_dt
from answer_tb
group by author_id, answer_date
)
, t2 as (
select
author_id,
diff_dt,
count(*) as days
from t1
# 4 求每个人连续登录的天数 每个 --> 分组
group by author_id, diff_dt
# 5 连续回答的天数 >= 3
having days>=3
)
, t3 as (
select
author_id,
max(days) as days_cnt
from t2
# 6 一个人可能存在多次连续回答天数, 求每个人连续回答的最大天数
group by author_id
)
select
a.author_id,
b.author_level,
a.days_cnt
from t3 a join author_tb b on a.author_id=b.author_id
;
补充：碰到连续模板
1. 是否去重
2 .等差数列1 在哪?
3. 等差数列2 在哪?
4. 差 = 等差数列1 - 等差数列2
5. 如果等差数列是连续的, 差是相同的; 反过来 如果差是相同的, 前面的数据肯定是连续的
3.3. 力扣-2173. 最多连胜的次数
https://leetcode.cn/problems/longest-winning-streak/description/
3.3.1. 抛真题
表: Matches
±------------±-----+
| Column Name | Type |
±------------±-----+
| player_id | int |
| match_day | date |
| result | enum |
±------------±-----+
(player_id, match_day) 是该表的主键（具有唯一值的列的组合）。
每一行包括了：参赛选手 id、 比赛时间、 比赛结果。
比赛结果（result）的枚举类型为 (‘Win’, ‘Draw’, ‘Lose’)。
选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。
编写解决方案来计算每个参赛选手最多的连胜数。
结果可以以 任何顺序 返回。
结果格式如下例所示：
示例 1:
输入:
Matches 表:
±----------±-----------±-------+
| player_id | match_day | result |
±----------±-----------±-------+
| 1 | 2022-01-17 | Win |
| 1 | 2022-01-18 | Win |
| 1 | 2022-01-25 | Win |
| 1 | 2022-01-31 | Draw |
| 1 | 2022-02-08 | Win |
| 2 | 2022-02-06 | Lose |
| 2 | 2022-02-08 | Lose |
| 3 | 2022-03-30 | Win |
±----------±-----------±-------+
输出:
±----------±---------------+
| player_id | longest_streak |
±----------±---------------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
±----------±---------------+
解释:
Player 1:
从 2022-01-17 到 2022-01-25, player 1连续赢了三场比赛。
2022-01-31, player 1 平局.
2022-02-08, player 1 赢了一场比赛。
最多连胜了三场比赛。
Player 2:
从 2022-02-06 到 2022-02-08, player 2 输了两场比赛。
最多连赢了0场比赛。
Player 3:
2022-03-30, player 3 赢了一场比赛。
最多连赢了一场比赛。
进阶: 如果我们想计算最长的连续不输的次数（即获胜或平局），你将如何调整？
3.3.2. 准备工作
drop database if exists db_1;
create database db_1;
use db_1;
Create table If Not Exists Matches (player_id int, match_day date, result ENUM(‘Win’, ‘Draw’, ‘Lose’));
Truncate table Matches;
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-17’, ‘Win’);
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-18’, ‘Win’);
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-25’, ‘Win’);
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-31’, ‘Draw’);
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-02-08’, ‘Win’);
insert into Matches (player_id, match_day, result) values (‘2’, ‘2022-02-06’, ‘Lose’);
insert into Matches (player_id, match_day, result) values (‘2’, ‘2022-02-08’, ‘Lose’);
insert into Matches (player_id, match_day, result) values (‘3’, ‘2022-03-30’, ‘Win’);
select * from matches;
3.4. 题目要求: 选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。
编写解决方案来计算每个参赛选手最多的连胜数。结果可以以 任何顺序 返回。
3.4.1 代码实现
with t1 as (
select
player_id,
match_day,
result,
# 1 等差数列1: 排名 根据player_id分组 再根据比赛的日期 排序
row_number() over (partition by player_id order by match_day) as rn1,
# 2 等差数列2: 排名 根据player_id和result 分组 再根据比赛的日期 排序
row_number() over (partition by player_id, result order by match_day) as rn2,
# 3 差 = 等差数列1 - 等差数列2
(row_number() over (partition by player_id order by match_day)) - (row_number() over (partition by player_id, result order by match_day)) as diff
from matches
)
, t2 as (
select
player_id,
diff,
count(*) as cnt
from t1
# 4 过滤 连胜
where result=‘Win’
# 5 求每个人连胜的次数
group by player_id, diff
)
, t3 as (
select
player_id,
max(cnt) as longest_streak
from t2
# 6 求每个人连胜的最大次数
group by player_id
)
select
a.player_id,
ifnull(b.longest_streak, 0) longest_streak # ifnull 判断 如果你给的值为null, 初始化为0
# , coalesce(b.longest_streak, 0) longest_streak # coalesce 返回第一个不为null的值
from (select distinct player_id from matches) a
left join t3 b on a.player_id=b.player_id
3.5. 小鹏面试题: 小鹏汽车充电每辆车连续快充最大次数
3.5.1 抛真题
3.5.1需求: 小鹏汽车充电每辆车连续快充最大次数
输入
id charge_time charge_type
XP1001 2023/11/20 08:45:00 快充
XP1001 2023/11/21 20:45:00 快充
XP1001 2023/11/22 08:45:00 快充
XP1001 2023/11/23 08:45:00 慢充
XP1001 2023/11/25 08:45:00 快充
XP1002 2023/11/25 08:45:00 快充
XP1002 2023/11/25 12:45:00 快充
XP1002 2023/11/25 23:45:00 慢充
XP1003 2023/11/25 23:45:00 慢充
XP1003 2023/11/26 23:45:00 慢充
输出
id cnts
XP1001 3
XP1002 2
XP1003 0
3.4.2. 准备工作
– 小鹏充电
drop database if exists db_1;
create database if not exists db_1;
use db_1;
CREATE TABLE charging_data (
id VARCHAR(50),
charge_time DATETIME,
charge_type VARCHAR(10)
);
INSERT INTO charging_data (id, charge_time, charge_type)
VALUES
(‘XP1001’, ‘2023-11-20 08:45:00’, ‘快充’),
(‘XP1001’, ‘2023-11-21 20:45:00’, ‘快充’),
(‘XP1001’, ‘2023-11-22 08:45:00’, ‘快充’),
(‘XP1001’, ‘2023-11-23 08:45:00’, ‘慢充’),
(‘XP1001’, ‘2023-11-25 08:45:00’, ‘快充’),
(‘XP1002’, ‘2023-11-25 08:45:00’, ‘快充’),
(‘XP1002’, ‘2023-11-25 12:45:00’, ‘快充’),
(‘XP1002’, ‘2023-11-25 23:45:00’, ‘慢充’),
(‘XP1003’, ‘2023-11-25 23:45:00’, ‘慢充’),
(‘XP1003’, ‘2023-11-26 23:45:00’, ‘慢充’)
;
需求: 小鹏汽车充电每辆车连续快充最大次数
3.5.2 代码实现
with t1 as (
select
id,
charge_time,
charge_type,
# 1 等差数列1 排名 根据id分组, 再根据充电时间排序
row_number() over (partition by id order by charge_time) as rn1,
# 2 等差数列2 排名 根据id和充电类型 分组, 再根据充电时间排序
row_number() over (partition by id, charge_type order by charge_time) as rn2,
# 3 差 = 等差数列1 - 等差数列2
(row_number() over (partition by id order by charge_time)) - (row_number() over (partition by id, charge_type order by charge_time)) as diff
from charging_data
)
, t2 as (
select
id,
diff,
count(*) as cnt
from t1
# 4 过滤 快充
where charge_type=‘快充’
# 5 求每辆车连续充电的次数
group by id, diff
)
, t3 as (
select
id,
max(cnt) as max_cnt
from t2
# 7 求每辆车梁旭充电的最大次数
group by id
)
select
a.id,
ifnull(max_cnt, 0) max_cnt
from (select distinct id from charging_data) a
left join t3 b on a.id=b.id
3.6. 美团面试题-查询2023年每个⽉，连续2天都有登陆的⽤⼾名单。
3.6.1. 抛真题
有⼀张 ''⽤⼾登陆记录表"，包含两个字段：⽤⼾id、⽇期。
user_id login_date
1 2023-01-03
1 2023-01-04
1 2023-01-05
1 2023-01-08
1 2023-01-09
1 2023-02-16
1 2023-02-17
1 2023-02-27
2 2023-01-10
2 2023-01-11
2 2023-03-08
2 2023-03-09
3 2023-02-08
3 2023-02-09
3 2023-02-10
3 2023-02-15
3 2023-03-09
3 2023-03-19
【问题】查询2023年每个⽉，连续2天都有登陆的⽤⼾名单。
y_month user_id
2023-01 1
2023-01 2
2023-02 1
2023-02 3
2023-03 2
3.5.2. 准备工作
– todo 1 准备工作
drop database if exists db_1;
create database db_1;
use db_1;
create table tb_user_login(
user_id varchar(32),
login_date varchar(32)
)
;
insert into db_1.tb_user_login
values
(‘1’, ‘2023-01-03’),
(‘1’, ‘2023-01-04’),
(‘1’, ‘2023-01-05’),
(‘1’, ‘2023-01-08’),
(‘1’, ‘2023-01-09’),
(‘1’, ‘2023-02-16’),
(‘1’, ‘2023-02-17’),
(‘1’, ‘2023-02-27’),
(‘2’, ‘2023-01-10’),
(‘2’, ‘2023-01-11’),
(‘2’, ‘2023-03-08’),
(‘2’, ‘2023-03-09’),
(‘3’, ‘2023-02-08’),
(‘3’, ‘2023-02-09’),
(‘3’, ‘2023-02-10’),
(‘3’, ‘2023-02-15’),
(‘3’, ‘2023-03-09’),
(‘3’, ‘2023-03-19’)
;
select * from db_1.tb_user_login;
【问题】查询2023年每个⽉，连续2天都有登陆的⽤⼾名单。
3.5.3. 分析
3.5.4. 实现
【问题】查询2023年每个⽉，连续2天都有登陆的⽤⼾名单。
3.6.2代码实现：
with t1 as (
select
user_id,
date_format(login_date, ‘%Y-%m’) as y_month,
# 等差数列1: 日期
login_date,
# 等差数列2: 排名 根据 用户id和月份 分组, 再根据 登录日期排序
row_number() over (partition by user_id, date_format(login_date, ‘%Y-%m’) order by login_date) as rn,
# 差 = 等差数列1 - 等差数列2
date_sub(login_date, interval (row_number() over (partition by user_id, date_format(login_date, ‘%Y-%m’) order by login_date)) day) diff_dt2
from tb_user_login
# 2023年
where login_date between ‘2023-01-01’ and ‘2023-12-31’
# 去重
group by user_id, login_date
)
, t2 as (
select
user_id, y_month, diff_dt2,
count(*) as days
from t1
# 求每人 每月 连续登录的天数
group by user_id, y_month, diff_dt2
# 连续2天
having days>=2
)
select
distinct y_month, user_id
from t2
;
3.5.5. 小结
第四章 面试补充
1. MySQL 索引使用有哪些注意事项呢？
这个可以从 索引哪些情况会失效和 索引不适合哪些场景两个⽅⾯回答
1）索引哪些情况会失效
（1）查询条件包含or，会导致索引失效。
（2）隐式类型转换，会导致索引失效，例如age字段类型是int，我们where age = “1”，这样就会触发
隐式类型转换。
（3）like通配符会导致索引失效。注意："ABC%“会⾛range索引，”%ABC"索引才会失效。
（4）联合索引，查询时的条件列不是联合索引中的第⼀个列，索引失效。
（5）对索引字段进⾏函数运算。
2）索引不适合哪些场景
数据量少的不适合加索引
更新⽐较频繁的也不适合加索引
离散性低的字段不适合加索引（如性别）
2. MySQL 的SQL优化？
1）加索引
2）⾏列裁剪，避免返回不必要的数据（select 字段）
3）适当分批量进⾏
4）优化sql结构
5）分库分表
3. Hive数仓与mysql数据库的区别？
1）数据规模
Hive⽀持很⼤规模的数据计算；数据库可以⽀持的数据规模较⼩。
2）计算引擎
Hive底层运⾏的是MapReduce程序，但是数据库有⾃⼰的执⾏引擎。
3）数据更新
Hive读多写少，不建议对数据改写；数据库对数据的增删改操作⽐较多。
4）存储数据位置
Hive数据实际是存储在HDFS上，数据库的数据存储在块设备上或者本地⽂件系统中
847
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
