MySQL 基础查询（三）

一、SQL窗口函数
窗口函数，也叫OLAP(Online Anallytical Processing,联机分析处理）,可以对数据库数据进行实时分析处理
基本语法：

<窗口函数> OVER (
[PARTITION BY <用于分组的列名>] -- 可选
[ORDER BY <用于排序的列名>] -- 可选
)

上面 <窗口函数> 的位置，可以放下面两种函数：
（1）聚合函数：如sum.avg,count,max,min等；
（2）专用窗口函数：比如rank, dense_rank, row_number，下面会详细讲到。
我们一般将窗口函数放在select语句中。

-- 在Scores成绩表中，找到Sid为7-10的学生，并计算每个学生的总分显示出来
select a.Sid,a.Cid,a.score,b.sum from scores a JOIN 
(SELECT Sid,SUM(score) sum from Scores GROUP BY Sid) b
on a.Sid = b.Sid
where a.Sid BETWEEN 7 AND 10;

-- 采用窗口函数  MYSQL 8.0以上支持
SELECT
  Sid,Cid,score,
  SUM(score) OVER (PARTITION BY Sid) AS "总分"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;

比如我们上面SQL语句，如果省略掉PARTITION BY

SELECT
  Sid,Cid,score,
  SUM(score) OVER() AS "总分"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;

专用窗口函数：
1.获取数据排名：
ROW_NUMBER()：不考虑并列名次的情况，比如前三名分数都是88，88，77，排名是1，2，3。
RANK()：如果有并列名次的行，会占用下一名次的位置。比如前三名分数都是88，88，77，排名是1，1，3。
DEBSE_RANK()：如果并列名次的行，不占用下一名次的位置。比如前三名分数都是88，88，77，排名是1，1，2。

-- 在Scores成绩表中，找到Sid为7-10的学生，并计算成绩从高到低排名
SELECT *,
  ROW_NUMBER() OVER(
    ORDER BY score DESC
  ) AS "排名"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;

上方使用了ROW_NUMBER()窗口函数，可以看到增加了“排名”这一列。如果将ROW_NUMBER()替换为RANK()，返回结果是下面这样的

如果要进行课程分组，将每组的成绩进行排名

SELECT *,
  RANK() OVER(
		PARTITION BY Cid
    ORDER BY score DESC
  ) AS "排名"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;

2.获取第一名或最后一名：
FIRST_VALUE(<列名>)：获取第一名。
LAST_VALUE(<列名>)：获取最后一名

-- 在Scores成绩表中，找到Sid为7-10的学生，获取每个学生的最高成绩
SELECT *,
  FIRST_VALUE(score) OVER(
    PARTITION BY Sid
    ORDER BY score DESC
  ) AS "最高成绩"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;

3.偏移函数：
LEAD(<列名>,<数值n>)：从当前行访问向下偏移n行的数据。
LAG(<列名>,<数值n>)：从当前行访问上偏移n行的数据。
NTH_VALUE(<列名>,<数值n>)：从结果集中的第N行获取数据。
4.分布函数：
CUME_DIST()：分组内小于、等于当前rank值的行数 / 分组内总行数。
PERCENT_RANK()：返回某列每行的百分比排序，每行按照公式(rank-1) / (rows-1)进行计算。
NTILE(<数值n>)：将结果集整体分为n组，并展现出某一条数据被分配在哪个组中。

-- 获取下面第2行偏移分数
SELECT *,
  LEAD(score,2) OVER(
    ORDER BY score DESC
  ) AS "获取下面第2行score值"
FROM Scores
WHERE Sid BETWEEN 7 AND 10;

第一题：下面表格是用户访问表users，记录了用户id（usr_id）和访问日期（log_date）,求出连续3天以上访问的用户id

我们需要根据这么一个简单的表，求出连续3天以上访问的用户。我们可以按照用户id给访问日期排名，然后再用访问日期减去排名，得到一个时间。如果用户是连续访问的，这个时间就是一样的。一个用户的这个时间如果出现3次及以上，说明这个用户连续访问了3天。
（1）先按照用户id（usr_id）对访问日期（log_date）进行排名，这里要用到DENSE_RANK() 这个窗口函数，给出排名序号。这个函数经常应用于给学生成绩进行排名。

SELECT
   usr_id,
   log_date,
   DENSE_RANK() OVER (PARTITION BY usr_id ORDER BY log_date) AS rank_id
   FROM users

查看结果，多了一个rank_id的列

得到排名后，我们用访问日期减去排名，得到一个时间flg_date

SELECT
   usr_id,
   DATE_SUB(log_date,INTERVAL rank_id DAY) AS flg_date
FROM (
   SELECT
      usr_id,
      log_date,
      DENSE_RANK() OVER (PARTITION BY usr_id ORDER BY log_date) AS rank_id
   FROM users
) AS A

同一个用户有3个及以上flg_date相同，说明用户连续访问了3天，我们对上面查出的这个结果进行分组，并统计判断是否大于3

SELECT
   usr_id,
   DATE_SUB(log_date,INTERVAL rank_id DAY) AS flg_date
FROM (
   SELECT
   usr_id,
   log_date,
   DENSE_RANK() OVER (PARTITION BY usr_id ORDER BY log_date) AS rank_id
   FROM users
) AS A
GROUP BY usr_id,flg_date
HAVING COUNT(flg_date) >=3

这样就已经得出最终结果了，因为题目只想知道usr_id，我们最后再将usr_id展示出来就可以了

SELECT DISTINCT usr_id
FROM(
   SELECT
   usr_id,
   DATE_SUB(log_date,INTERVAL rank_id DAY) AS flg_date
   FROM (
       SELECT
       usr_id,
       log_date,
       DENSE_RANK() OVER (PARTITION BY usr_id ORDER BY log_date) AS rank_id
       FROM users
   ) AS A
   GROUP BY usr_id,flg_date
   HAVING COUNT(flg_date) >=3
) AS B

第二题：找出工资前3高的员工，相同工资并列排名
这道题还是考的窗口函数。
现在有 Employee 员工信息表和Department部门信息表，分别如下：
Employee 员工信息表包括，每个员工对应的工号 Id，姓名 Name, 工资 Salary 和部门编号 DepartmentId。

Department 部门信息表包含部门对应的编号ID和部门名称Name。

编写一个SQL查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回如下：

解题思路：
这里要注意的细节是，最终结果输出的是前三高工资的所有员工，有些员工的工资是一样的，每个部门可能不止3名员工符合这个要求。
我们可以按照部门id（department_id）给员工工资（salary）排名，然后找出每个部门前三高工资对应的所有员工
先按照部门id（department_id）对员工工资（salary）进行排名，这里要用到DENSE_RANK() 这个窗口函数，得到每个部门的工资排名序号

SELECT
   de.name AS department_name,
   em.name AS employee_name,
   em.salary,
   DENSE_RANK() OVER(PARTITION BY de.name ORDER BY em.salary DESC) AS salary_rank
FROM Employee em
JOIN Department de
ON em.department_id = de.id

再将上面的查询作为子查询，查找salary_rank字段<=3的记录

SELECT
   A.department_name,
   A.employee_name,
   A.salary
FROM
(
   SELECT
       de.name AS department_name,
       em.name AS employee_name,
       em.salary,
       DENSE_RANK() OVER(PARTITION BY de.name ORDER BY em.salary DESC) AS salary_rank
   FROM Employee em
   JOIN Department de
   ON em.department_id = de.id
) AS A
WHERE salary_rank <=3

最终结果如下：

第三题：查找 Employee 表中，每个部门重复的工资记录，以及重复的个数
Employee 员工信息表表包括，每个员工对应的工号 Id，姓名 Name , 工资 Salary 和部门编号 DepartmentId 。

Department 部门信息表包含部门对应的编号ID和部门名称Name。

例如上面示例应返回：

通过join函数连接Department表，显示出部门名称，通过GROUP BY 对部门进行分组，使用COUNT聚合函数进行计数，最后通过HAVIGN判断重复的记录

SELECT
   de.name,
   em.salary,
   count(*)
FROM Employee em
JOIN Department de
ON em.department_id = de.id
GROUP BY de.name,em.salary
HAVING count(*)>1

第四题：留存问题
留存率：是用户分析的核心指标之一，留存问题也是一个经常考的题目

给定用户表Users，求出每个日期对应的活跃用户数、次日留存用户数、次日留存率
指标定义：
某日活跃用户数，某日活跃的去重用户数。
N日留存用户数，某日活跃的用户在之后的第N日活跃用户数。
N日活跃留存率，N日留存用户数/某日活跃用户数
例：20210501日去重用户数为10000，这批用户20210503日仍有7000人活跃，则3日活跃留存率为7000/10000=70%
解题思路：N日活跃留存率 = N日留存用户数/某日活跃用户数。我们先得到每日活跃用户数

SELECT log_date AS '日期',COUNT(DISTINCT usr_id) AS '活跃用户数'
FROM Users
GROUP BY log_date;

在得到次日留存用户数

SELECT a.log_date AS 日期,COUNT(DISTINCT b.usr_id) AS '次日留存'
FROM Users a
LEFT JOIN Users b
ON a.usr_id = b.usr_id
AND
DATEDIFF(b.log_date,a.log_date)=1
GROUP BY a.log_date;

次日留存，3日留存，7日留存等只需要更改日期相差的天数即可。比如：DATEDIFF(b.log_date,a.log_date)=1改成=3或者=7
我们最终用一条sql语句得到结果

SELECT
   a.log_date AS 日期,
   COUNT(DISTINCT a.usr_id) AS '活跃用户数',
   COUNT(DISTINCT CASE WHEN DATEDIFF(b.log_date,a.log_date)=1 THEN b.usr_id END) AS '次日留存',
   CONCAT(COUNT(DISTINCT CASE WHEN DATEDIFF(b.log_date,a.log_date)=1 THEN b.usr_id END)/COUNT(DISTINCT a.usr_id)*100,'%') AS '次日留存率'
FROM Users a
LEFT JOIN Users b
ON a.usr_id = b.usr_id
GROUP BY a.log_date;

扩展得到3日留存率。

SELECT
   a.log_date AS 日期,
   COUNT(DISTINCT a.usr_id) AS '活跃用户数',
   COUNT(DISTINCT CASE WHEN DATEDIFF(b.log_date,a.log_date)=1 THEN b.usr_id END) AS '次日留存',
   COUNT(DISTINCT CASE WHEN DATEDIFF(b.log_date,a.log_date)=3 THEN b.usr_id END) AS '三日留存',
   CONCAT(COUNT(DISTINCT CASE WHEN DATEDIFF(b.log_date,a.log_date)=1 THEN b.usr_id END)/COUNT(DISTINCT a.usr_id)*100,'%') AS '次日留存率',
   CONCAT(COUNT(DISTINCT CASE WHEN DATEDIFF(b.log_date,a.log_date)=3 THEN b.usr_id END)/COUNT(DISTINCT a.usr_id)*100,'%') AS '三日留存率'
FROM Users a
LEFT JOIN Users b
ON a.usr_id = b.usr_id
GROUP BY a.log_date;

第五题：列转行
列转行，这个也是大厂经常考的题目，在mysql中我们可以通过union语句将查询结果合并，在hive中可以使用Lateral View与UDTF函数（explode,split）结合使用来实现
1.题

将上表格式转换为下表格式

解题思路：先将每一行单独提取出来，再通过union合并结果

select name,'english' as subject,english as score from a1
union
select name,'maths' as subject,maths as score from a1
union
select name,'music' as subject,music as score from a1;

快速建表语句，可以自己测试。

create table a1 (
name varchar(20),
english int,
maths int,
music int);
insert into a1 values
("Jim",90,88,99);

2.题
Movie表记录了各大电影的类型情况，数据如下：

需要转换成以下格式

解题思路：
在hive中，lateral view用于和split, explode等UDTF函数一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。
语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)
本题只需要借助Lateral View进行拆解重组即可

SELECT movie,category
FROM Movies
LATERAL VIEW explode(category_list)tt AS category