MYSQL 窗口函数+表连接+子查询

Sql后续操作：

窗口函数+表连接+子查询

1、窗口函数：

标准语法：

窗口函数 over (partition by 用于分组的字段名 order by 用于排序的字段名)

常用窗口函数：

窗口函数的作用：

e.g. rank()over()：在指定分区(partition by)对指定字段排序(order by)然后依次赋予排名的函数，得到对应每行的 排名序号 是结果

窗口函数有点像：在其他地方独立开出一个窗口，用于你想干的e.g. 排序…，然后弄好了之后跟原表放到一起(SELECT…)，然后后续操作（排序ORDER BY，选择输出LIMIT..）

• 窗口函数只能写在select字句中
• 窗口函数中的partition by子句可以指定数据的分区，但group by分区 重分组，partition by只分区 不去重
• 窗口函数中没有partition by子句时，即不对数据分区，直接整个表为一个区
• 排序窗口函数

order by子句是必选项，窗口函数中order by子句 在分区内 对数据行排序

rank()、 dense_ rank()、row_ number()指定排序赋值方法，对比三个排序窗口函数的异同

rank()：跳跃式排序，e.g.  99, 99, 90，89，得到排名为1，1, 3, 4

dense_rank()：并列连续型排序，e.g.  99, 99, 90, 89,得到的排名为1, 1, 2, 3

row_number()：连续型排序，e.g.  99, 99, 90，89，得到的排名为1, 2, 3, 4

SELECT yr,party,votes,RANK() OVER(PARTITION BY yr ORDER BY votes DESC) AS posn FROM ge WHERE constituency='S14000021' ORDER BY party,yr;

SELECT party,votes,RANK() OVER(ORDER BY votes DESC) FROM ge WHERE constituency='S14000024' AND yr=2017 ORDER BY party;

SELECT name,confirmed,RANK() OVER(ORDER BY confirmed DESC) 确诊排名,deaths,RANK() OVER(ORDER BY deaths DESC) 死亡人数排名 FROM covid WHERE whn<'2020-04-20' ORDER BY confirmed DESC;

• 偏移分析函数

偏移分析窗口函数中order by子句是必选项

lag()和lead()指定偏移的方向，lag是向上偏移, 行向上取数据，lead是向下偏移，行向下取数据

SELECT name,date_format(whn,'%Y-%m-%d') date,confirmed 当前累计确诊人数, LAG(confirmed,1) OVER(PARTITION BY name ORDER BY whn) 昨日确诊人数,confirmed-LAG(confirmed,1) OVER(PARTITION BY name ORDER BY whn) 新增确诊人数 FROM covid WHERE name in('France','Germany') AND month(whn)=1 ORDER BY whn

SELECT name,date_format(whn,'%Y-%m-%d'),confirmed-LAG(confirmed,1) OVER(ORDER BY whn) FROM covid WHERE name='Italy' AND WEEKDAY(whn)=0 ORDER BY whn;

weekday()是库函数, 0-6分别是周一到周日

2、表的连接

基础语法：内连接、外连接

内连接

• select字段名 from表名1  (inner)  join表名2 on表名1.字段名=表名2.字段名

内连接inner可以省略，直接使用join默认为内连接

join表连接通过on表名1.字段名=表名2.字段名，将两个表格各自的字段等值连接来匹配连接，无法匹配的填充null值

左连接

• select 字段名 from 表名1 left join表名2 on表名1.字段名=表名2.字段名

右连接

• select字段名 from表名1 right join表名2 on表名1.字段名=表名2.字段名

外连接：原理图（笛卡尔积

内连接：inner join

       就是对于那些没有匹配上的null的行全部不要

左连接：

左连接就是表左边的全部保留，即使含有没有匹配成功有null的情况

右连接：正好相反

综上外连接：允许左右都可null的连接，内：不允许所有，左：不允许左边有null，右：不允许有右边null

SELECT team1,team2,player FROM game JOIN goal ON game.id=goal.matchid WHERE player like 'Mario%';

SELECT eteam.teamname,game.mdate,game.id FROM eteam JOIN game ON eteam.id=game.team1 WHERE eteam.coach='Fernando Santos'

JOIN ON 两个表是可以用别名的，上面可以改成

SELECT tm.teamname,gm.mdate,gm.id FROM eteam tm JOIN game gm ON tm.id=gm.team1 WHERE tm.coach='Fernando Santos'

表连接的时候，字段名唯一的情况可以不指定表名，而字段名不唯一时必须指明表名

SELECT t.name 教师, d.name 教授课程 FROM teacher t LEFT JOIN dept d ON t.dept=d.id

SELECT name FROM casting c JOIN actor a ON c.actorid=a.id WHERE ord=1 GROUP BY a.id HAVING count(movieid)>=30（存疑，运行不出来）

SELECT player,teamid,coach,gtime FROM eteam JOIN goal ON eteam.id=goal.teamid WHERE gtime<=10 GROUP BY teamid

数据库报错：Column 'temTable.customStatus' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

原因：group by 后面是要跟着的 select 中所有不是聚合函数的字段。

解决：把该字段放在group by 后面即可。

3、子查询

SELECT name FROM world WHERE gdp>(SELECT max(gdp) FROM world WHERE continent='Europe')