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

最新推荐文章于 2025-02-02 17:13:13 发布

原创最新推荐文章于 2025-02-02 17:13:13 发布 · 621 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据分析 #mysql

本文详细介绍了SQL中的窗口函数，重点讲解了`rank()over()`函数的使用，包括如何进行分区(partition by)和排序(order by)，并通过实例解析了如何在SQLZOO上运用这些知识解决实际问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主知识点九：窗口函数

【知识点引入】

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