数据璐SQL零基础入门教程学习第十天主知识点九：窗口函数

主知识点九：窗口函数

【知识点引入】

最后我们要来学习窗口函数【标准语法】 窗口函数 over (partition by 用于分组的字段名 order by 用于排序的字段名)常用窗口函数 【例题讲解】 我们以排序窗口函数rank()over()为例窗口函数rank()over()是在指定分区(partition by)对指定字段排序(order by)然后依次赋予排名的函数【指定分区partition by和排序order by】 点击链接Window functions - SQLZOO 第三题 【题目】 查询每一年S14000021选区中所有候选人所在的团体party和选票数votes并且对每一年中的所有候选人根据选票数的高低进行排名posn，选票数最高则为第一名，后续以此类推最后根据团体party和年份yr排序先查看题目表格 记录了每一年yr，候选人名字firstname和姓氏lastname,所属选区constituency和团体party，以及选票数votes 查询每一年S14000021选区中所有候选人所在的团体party和选票数votes，写出代码 selectyr,party, votesfrom gewhere constituency = 'S14000021'order by party,yr还需要对筛选出的S14000021选区中的每一年的数据分区然后根据票数高到低排序,用desc降序使用窗口函数 rank()over(partition by yr order by votes desc) as posn将窗口函数写在select子句后 【运行代码】 selectyr,party, votes,rank()over(partition by yr order by votes desc) as posnfrom gewhere constituency = 'S14000021'order by party,yr【运行结果】 【excel演示】 通过以下代码得到表格 selectyr,party, votesfrom gewhere constituency = 'S14000021'order by party,yr对窗口函数rank()over(partition by yr order by votes desc) as posn进行拆解 partition by yr对年份数据分区 order by votes desc对分区内的选票数从高到低排序并给它一个排名的编号，最后得到pson列 【from 子查询和窗口函数】 点击链接Window functions - SQLZOO 第五题 【题目】 查询所有在爱丁堡的选区当选议员所在的选区即对应的团队party已知爱丁堡选区编号为S14000021至S14000026当选议员，即各选区得票数最高的候选人先对选区编号为S14000021至S14000026的各选区分区，然后对每个选区的得票数从高到底排名查询涉及到选区constituency、团队patry、选票数votes写出子查询 selectconstituency,party,votes,rank()over(partition by constituency order by votes desc) as posnfrom gewhere constituency between 'S14000021' and 'S14000026'and yr = 【运行结果】 各选区得票数最高的候选人，即筛选条件posn = 1题目要求选区constituency即团队party【运行代码】 selectconstituency,partyfrom( selectconstituency,party,votes,rank()over(partition by constituency order by votes desc) as posnfrom gewhere constituency between 'S14000021' and 'S14000026'and yr = ) RKwhere RK.posn=1【运行结果】 注意from后的子查询一定要有别名对窗口函数结果筛选一定要再嵌套一个查询，否则会出现语法错误【总结】 【窗口函数语法】 窗口函数 over (partition by 用于分区的字段名 order by 用于排序的字段名)常用窗口函数 【练习题】 【1】Window functions - SQLZOO第二题 【题目】 查询选区为 'S14000024' 的所有候选人所在团体（party）和其得到选票数（votes）、还有候选人得票数在选区内对应的的排名结果按团队party排序 【参考答案】 selectparty,votes,rank() over (order by votes desc) as posnfrom gewhere constituency = 'S14000024'and yr = order by party【2】Window functions - SQLZOO第四题 【题目】 查询爱丁堡各个选区内各团队的排名情况按照排名、选区排序排名情况包括选区constituency，团队party，选票数votes，排名posn已知爱丁堡选区编号为S14000021至S14000026 【参考答案】 selectconstituency,party,votes,rank()over(partition by constituency order by votes desc) as posnfrom gewhere constituency between 'S14000021' and 'S14000026'and yr = order by posn,constituency【3】Window functions - SQLZOO第六题 【题目】 查询在苏格兰的选区内（选区编号以S开头的）每个团体获得的席位数选区候选人得票数为第1名，即获得该选区席位 【参考答案】 selectparty,count(*)from( selectconstituency,party,votes,rank()over(partition by constituency order by votes desc) as posnfrom gewhere constituency like 'S%'and yr = ) rkwhere rk.posn=1group by rk.party【4】Window LAG - SQLZOO第六题 【题目】 查询更新时间为4月20日的国家名，确诊人数，确诊人数排名，死亡人数，死亡人数排名按照确证人数降序排名 【参考答案】 selectname,confirmed,rank()over(order by confirmed desc) rc,deaths,rank()over(order by deaths desc) deathrcfrom covidwhere whn = '-04-20'order by confirmed desc【5】Window LAG - SQLZOO第二题 【题目】 修改给出的代码，查询三月份意大利每天的确诊人数和前一天的确诊人数并按更新时间排序 【窗口函数LAG简介】 LAG(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null【参考答案】 selectname, day(whn), confirmed,lag(confirmed,1) over (partition by name order by whn) lagfrom covidwhere name = 'italy'and month(whn) = 3order by whn【6】Window LAG - SQLZOO第三题 【题目】 修改给出的代码，使用LAG函数查询三月份意大利每日新增确诊数 【参考答案】 selectname, day(whn), (confirmed -lag(confirmed, 1) over (partition by name order by whn)) newfrom covidwhere name = 'italy'and month(whn) = 3order by whn【7】Window LAG - SQLZOO第四题 这题务必认真复习！！！ 【切换数据库操作】 找到页面最右上角的齿轮图标 点击图标 切换数据库引擎为MySQL mysql支持函数date_format()和weekday()，但Microsoft SQl不支持 【题目】 查询意大利每周新增确诊数（显示每周一的数值 weekday(whn) = 0）最后显示国家名，标准日期（-01-27），每周新增人数按照更新时间排序 【参考答案】 selectname, date_format(whn,'%Y-%m-%d') date, (confirmed - lag(confirmed,1)over(partition by name order by whn)) Newfrom covidwhere name = 'Italy'and weekday(whn) = 0order by whn