SQL之常用函数

一、窗口函数

窗口函数，可以对数据库数据进行实时分析处理：

• 同时具有分组（partition by）和排序（order by）的功能
• 不减少原表的行数，所以经常用来在每组内排名
• 窗口函数原则上只能写在select子句中

语法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

静态窗口函数

• 排名函数，如rank，dense_rank，row_number等
• 分组函数，如ntitle

动态窗口函数

• 聚合函数，如sum，avg，count，max，min等
• 取值函数，如first_value，last_value，nth_value等
• 滑动函数，如lead，lag等

1.静态窗口函数

（1）排名函数

【每个班级内按成绩排名】:

• rank()：并列跳跃排名
• dense_rank()：并列连续排名
• row_number()：连续排名
  

SELECT *,
	RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking,
	DENSE_RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS dense_rank,
	ROW_NUMBER() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num
FROM 班级表

得到结果：

（2）分组函数

[ntitle()函数]：

NTITLE(4)：通过c_id分区后，再将这区域分成4组

SELECT *,
	NTITLE(4) OVER(PARTITION BY c_id)
FROM sc

1-2行属于第一组，3-4行属于第二组，5行属于第三组，6行属于第四组

2.动态窗口函数

（1）聚合函数

• sum()：求和
• avg()：求平均
• percent_rank()：累计百分比
• cume_dist()：累计分布值

【avg()函数】

over（）什么都不加，对整个数据的成绩求平均，窗口数据集是整个数据集

SELECT *, 
	AVG(score) OVER() 
FROM sc

over（）只加排序的参数，按成绩升序排列后对整个数据集成绩求移动平均

SELECT *, 
	AVG(score