SQL窗口函数，排序函数

1、窗口函数的基本语法
select SUM() OVER( PARTITION BY__ ORDER BY___)FROM TABLE
PARTITION BY 可以看成是GROUP BY 子句
窗口函数over（）子句详解

2、窗口函数over（PARTITION BY ）的使用方法
window-function over( PARTITION BY)

排序函数的引入

<ranking function> OVER (ORDER BY <order by columns>)

RANK()函数：有并列但不连续，RANK()会返回每一行的等级（序号）

select 
name,
genre,
size,
rank() over(order by size) as `rank`
from game

ORDER BY 对行进行排序将数据按升序或降序排列
RANK（）OVER（ORDER BY …）是一个函数与ORDER BY` 配合返回序号

DENSE_RANK()函数：有并列且连续

select 
name,
genre,
size,
dense_rank() over(order by size) as `rank`
from game

ROW_NUMBER()函数：想获取排序之后的序号，返回行号，永远都是连续的

select 
name,
genre,
size,
row_number() over(order by size) as `rank`
from game

RANK()与ORDER BY多列排序
NTILE(X)函数：将数据分成X组，并给每组分配一个数字（1，2，3……）

select 
name,
genre,
size,
ntile(3) over(order by size) as `rank`
from game

WITH 临时表别名AS (SQL语句)
select * from 临时表别名 where 筛选数据

with ranking as(
	select 
		name,
		released,
		updated,
		rank() over(order by updated desc) as `rank`
		from game
		) 
select 
	*
from
ranking
where 
`rank`=1