**窗口函数基本语法：** ```sql Function(arg1, ..., argn) over([partition by <...>] [order by <...>] [window_clause]) -- Function(arg1, ..., argn) 函数，如sum求和、first_value取第一个值 -- partition by 指明分区字段，如果省略则将所有数据作为一个分区 -- order by 指明每个分区排序的字段和方式，如果省略则按照表中原有顺序排序 -- window_clause，指明相对于当前记录的计算范围，分别有向上preceding、向下following、上下边界值between，省略则默认为当前分区 -- 只有在指定order by子句后才能指定window_clause，window_clause可取的值如下 -- rows between 也叫window子句 -- n+preceding 向前n条 -- n+following 向后n条件 -- current row 当前行 -- unbounded 无边界，从最前面的点到最后面的点 -- unbounded preceding 向前无边界 -- unbounded following 向后无边界 ``` **窗口子句window_clause：** （1）用于进一步细分结果并应用分析函数。 （2）支持两类窗口子句：行类型窗口（rows）、范围类型窗口（range）。 （3）rank、ntile、dense_rank、cume_dist、percent_rank、lead、lag和row_number函数不支持与窗口子句一起使用。 <br/> **行窗口：** 根据当前行之前或之后的行号确定的窗口。 ```sql select name, dept_num as dept, salary as sal, max(salary) over (partition by dept_num order by name rows between 2 preceding and current row) win1, max(salary) over (partition by dept_num order by name rows between 2 preceding and unbounded following) win2, max(salary) over (partition by dept_num order by name rows between 1 preceding and 2 following) win3, max(salary) over (partition by dept_num order by name rows between 2 preceding and 1 preceding) win4, max(salary) over (partition by dept_num order by name rows between 1 following and 2 following) win5, max(salary) over (partition by dept_num order by name rows between current row and current row) win6, max(salary) over (partition by dept_num order by name rows between current row and 1 following) win7, max(salary) over (partition by dept_num order by name rows between current row and unbounded following) win8, max(salary) over (partition by dept_num order by name rows between unbounded preceding and current row) win9, max(salary) over (partition by dept_num order by name rows between unbounded preceding and 1 following) win10, max(salary) over (partition by dept_num order by name rows between unbounded preceding and unbounded following) win11, max(salary) over (partition by dept_num order by name rows 2 preceding) win12 from employee_contract order by dept, name; ``` <br/> **范围窗口：** 是取分组内的值在指定范围区间内的行。 （1）该范围值/区间必须是数字或日期类型； （2）目前只支持一个`ORDER BY`列； ```sql -- 假设当前close值为3000，语句将包含分区内范围从2500到4000的行 sum(close) range between 500 preceding and 1000 following -- 示例 select name, dept_num as dept, salary as sal, max(salary) over (partition by dept_num order by name rows between 2 preceding and current row) win1, salary - 1000 as sal_r_start,salary as sal_r_end, max(salary) over (partition by dept_num order by name range between 1000 preceding and current row) win13 from employee_contract order by dept, name; ```