SQL中的开窗函数(窗口函数)

<窗口函数> over (partition by <用于分组的列名>
        order by <用于排序的列名> [rows between ?? and ???])

-- 如果我们想在每个班级内按成绩排名，得到下面的结果。
select *,
 rank() over (partition by 班级
        order by 成绩 desc) as ranking
from 班级表;

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

sum()/... over ([partition by 列名] [order by 列名] [rows between ... and ...] )
-- 从起点到当前行数据聚合
between unbounded preceding and current row
-- 往前2行到往后1行的数据聚合
between 2 preceding and 1 following

select name,subject,score,
sum(score) over() as sum1,
sum(score) over(partition by subject) as sum2,
sum(score) over(partition by subject order by score) as sum3,
-- 由起点到当前行的窗口聚合，和sum3一样
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,
-- 当前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
-- 当前行的前面一行和后面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding AND 1 following) as sum6,
-- 当前和后面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name | subject | score | sum1 | sum2 | sum3 | sum4 | sum5 | sum6 | sum7 |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孙悟空  | 数学    | 12   | 359  | 185  | 12  | 12  | 12  | 31  | 185  |
| 沙悟净  | 数学    | 19   | 359  | 185  | 31  | 31  | 31  | 104  | 173  |
| 猪八戒  | 数学    | 73   | 359  | 185  | 104  | 104  | 92  | 173  | 154  |
| 唐玄奘  | 数学    | 81   | 359  | 185  | 185  | 185  | 154  | 154  | 81  |
| 猪八戒  | 英语    | 11   | 359  | 80  | 11  | 11  | 11  | 26  | 80  |
| 孙悟空  | 英语    | 15   | 359  | 80  | 26  | 26  | 26  | 49  | 69  |
| 唐玄奘  | 英语    | 23   | 359  | 80  | 49  | 49  | 38  | 69  | 54  |
| 沙悟净  | 英语    | 31   | 359  | 80  | 80  | 80  | 54  | 54  | 31  |
| 孙悟空  | 语文    | 10   | 359  | 94  | 10  | 10  | 10  | 31  | 94  |
| 唐玄奘  | 语文    | 21   | 359  | 94  | 31  | 31  | 31  | 53  | 84  |
| 沙悟净  | 语文    | 22   | 359  | 94  | 53  | 53  | 43  | 84  | 63  |
| 猪八戒  | 语文    | 41   | 359  | 94  | 94  | 94  | 63  | 63  | 41  |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+