**窗口函數基本語法：** ```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; ```