请详细的讲解一下sql有哪些窗口函数,并举相关例子说明
时间: 2023-11-28 08:48:07 浏览: 37
SQL中的窗口函数(Window Function)是一种特殊的函数,它可以在查询结果中进行分组、排序、聚合等操作,同时还可以在每个分组内部进行计算。下面是SQL中常用的窗口函数及其用法:
1. ROW_NUMBER():给每行数据一个序号,从1开始递增
```sql
SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank, name, score
FROM student
```
2. RANK():按照指定字段进行排名,相同的值排名相同,排名不连续
```sql
SELECT RANK() OVER (ORDER BY score DESC) AS rank, name, score
FROM student
```
3. DENSE_RANK():与RANK()类似,但排名连续,不会有空缺
```sql
SELECT DENSE_RANK() OVER (ORDER BY score DESC) AS rank, name, score
FROM student
```
4. NTILE():将数据分成n份,返回每个数据所在的组别编号
```sql
SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS group_no
FROM student
```
5. LAG():获取指定列在当前行之前的某一行的值,可以指定偏移量和默认值
```sql
SELECT name, score, LAG(score, 1, 0) OVER (ORDER BY score DESC) AS pre_score
FROM student
```
6. LEAD():与LAG()类似,获取指定列在当前行之后的某一行的值
```sql
SELECT name, score, LEAD(score, 1, 0) OVER (ORDER BY score DESC) AS next_score
FROM student
```
7. SUM()、AVG()、COUNT()等聚合函数:可以在窗口函数中使用,统计每个分组内部的数据
```sql
SELECT name, score, SUM(score) OVER (PARTITION BY class_id) AS class_total_score
FROM student
```
以上是SQL中常用的窗口函数及其用法,可以根据具体的需求选择使用。