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')