SQL学习之路：7

窗口函数：只能写在SELECT里

over([partition by 字段名] [order by 字段名 desc/asc])

partition by 分区依据不去重；不选时为整个表是一个分区

order by 排序依据；必选项

排序窗口函数

rank()over()

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

运行过程：先WHERE筛选 按照ORDER BY 排序 生成原表 —— 窗口函数复制数据表 先分区 再区域里排序 赋予排序序号 返回—— SELECT 运行 原表展示的结果 RANK将序号索引匹配至原表里

rank()

跳跃式排序，若分数99，99，87，85 排序则为1，1，3，4

dense_rank()over()

并列连续排序，若分数99，99，87，85 排序则为1，1，2，3

row_number()over()

连续排序，若分数99，99，87，85 排序则为1，2，3，4

偏移分析函数

lag(字段名，偏移量 [，默认值] ）over()        数值向上偏移

SELECT name, DAY(whn), confirmed,
   LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn)
 FROM covid
WHERE name = 'Italy'
AND MONTH(whn) = 3 AND YEAR(whn) = 2020
ORDER BY whn

SQLZOO里运行失败？我看教学视频里也失败来着？怎么才能测试是否正确呀？

lead(字段名，偏移量 [,默认值])over()   数据向下偏移

SELECT party,votes,rank()over(partition by constituency order by votes desc)as 排名
FROM ge
WHERE constituency ='S14000024' AND yr = 2017
order by party