窗口函数:只能写在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