pgsql窗口函數解析 · PHP知識總結

> 聚合函數可以將查詢的結果集進行計算并且通常返回一行，窗口函數與聚合函數一樣，也是基于結果集的運算，不同的是窗口函數并不會將結果集進行分組合并后輸出一行，而是將計算的結果合并到結果集運算列上，窗口函數可以訪問的不僅僅是查詢結果的當前行。 > 一個窗口函數調用總是包含一個直接跟在窗口函數名及其參數之后的`OVER`子句。這使得它從句法上和一個普通函數或非窗口函數區分開來 **語法解析：** ``` window_function() OVER ([PARTITION BY <字段1,字段2>] ORDER BY <排序字段> [desc|asc]) 或者： // 定義window子句 window window_function(arg1, arg2,..) OVER ( [PARTITION BY partition_expression] [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }] [frame_clause] ) ``` * OVER：窗口函數關鍵字，`OVER`子句決定究竟查詢中的哪些行被分離出來由窗口函數處理。`OVER`子句中的`PARTITION BY`子句指定了將具有相同`PARTITION BY`表達式值的行分到組或者分區。對于每一行，窗口函數都會在當前行同一分區的行上進行計算 * window_function：窗口函數名，可不帶參數 * PARTITION BY：可選參數。該子句把行分為多個組或分區應用于窗口函數，如果沒有PARTITION BY子句，窗口函數則把所有結果集作為單個分區 * ORDER BY ：指定每個窗口函數應用的分區中行的順序，使用`NULLS FIRST`或`NULLS LAST`選項指定是否null值放在結果集的最前或最后。默認是`NULLS LAST`選項 * frame_clause：子句定義當前分區行中的一組子集行，并對其應用窗口函數，該子集行稱為幀 **通用窗口函數：**         聚合函數只有在調用之后有一個`OVER`子句時才作為窗口函數;否則，它們充當普通的聚合，并為整個集合返回一行。 |函數|描述| |---|---| |row_number()| 返回其分區內的當前行數，從1開始計數 | |rank()| 返回當前行的排名，包含間隔;即對等組中第一行的row_numbe | | dense_rank() | 返回當前行的排名，不包括間隔;這個功能有效地計數對等組 | | percent_rank() | 返回當前行的相對排名，即(rank\- 1) / (總的分區行數 - 1)。因此，該值的范圍從0到1(包含在內)。 | |cume_dist()| 返回當前行的相對排名：(前面的行數或與當前行相同的行數)/(總行數) | | ntile(num_buckets integer) |返回一個從1到參數值的整數，并將分區劃分為盡可能相等的值| | lag ( value anyelement [, offset integer [, default anyelement ]] ) |返回分區中在當前行之前offset行的value;如果沒有這樣的行，則返回default(必須與value具有相同的類型)。 offset和default都是針對當前行求值的。如果省略，offset默認為1，default為NULL。| | lead ( value anyelement [, offset integer [, default anyelement ]] ) | 返回分區中在當前行之后offset行的value；如果沒有這樣的行，則返回default(必須與value具有相同的類型)。 offset和default都是針對當前行求值的。如果省略，offset默認為1，default為NULL。 | | first_value(value anyelement) | 返回在窗口框架的第一行求得的value | | last_value(value anyelement) | 返回在窗口框架的最后一行求得的value | |nth_value(value anyelement,n integer)| 返回在窗口框架的第`n`行求得的`value`(從1開始計數);如果沒有這樣的行，則返回`NULL`。 | * 列出的所有函數都依賴于相關窗口定義的`ORDER BY`子句指定的排序順序。僅考慮`ORDER BY`列時不能區分的行被稱為是**同等行** * `first_value`、`last_value`和`nth_value`只考慮“窗口幀”內的行，它默認情況下包含從分區的開始行直到當前行的最后一個同等行。這對`last_value`可能不會給出有用的結果，有時對`nth_value`也一樣。你可以通過向`OVER`子句增加一個合適的幀聲明（`RANGE`或`GROUPS`）來重定義幀 **使用實例：** ``` // 按type分類并計算每個分類的price平均值，將計算的結果合并到結果集運算列 postgres=# SELECT type,name,price,avg(price) OVER (PARTITION BY type) as avg from products; type | name | price | avg --------+----------+---------+----------------------- 電器 | 電視 | 3299 | 4712.1833333333333333 電器 | iPhone X | 9600 | 4712.1833333333333333 電器 | 手表 | 1237.55 | 4712.1833333333333333 零食 | 方便面 | 3.5 | 5.0250000000000000 零食 | 汽水 | 3.5 | 5.0250000000000000 零食 | 辣條 | 5.6 | 5.0250000000000000 零食 | 薯條 | 7.5 | 5.0250000000000000 衣物 | 鞋子 | 27 | 76.9666666666666667 衣物 | 圍巾 | 93 | 76.9666666666666667 衣物 | 外套 | 110.9 | 76.9666666666666667 ```         對于每一行，在它的分區中的行集被稱為它的窗口幀。一些窗口函數只作用在窗口幀中的行上，而不是整個分區。默認情況下，如果使用`ORDER BY`，則幀包括從分區開始到當前行的所有行，以及后續任何與當前行在`ORDER BY`子句上相等的行。如果`ORDER BY`被忽略，則默認幀包含整個分區中所有的行。         如果行的順序不重要時`ORDER BY`可以忽略。`PARTITION BY`同樣也可以被忽略，在這種情況下會產生一個包含所有行的分區 ``` // 在原先結果集上新增一列，顯示所有price的總和（計算總和包含了分區內的所有行，因此可以忽略over中的表達式） postgres=# SELECT type,name,price,avg(price) OVER (PARTITION BY type) as avg,sum(price) over() as sum from products; type | name | price | avg | sum ------+----------+---------+-----------------------+---------- 電器 | 電視 | 3299 | 4712.1833333333333333 | 14387.55 電器 | iPhone X | 9600 | 4712.1833333333333333 | 14387.55 電器 | 手表 | 1237.55 | 4712.1833333333333333 | 14387.55 零食 | 方便面 | 3.5 | 5.0250000000000000 | 14387.55 零食 | 汽水 | 3.5 | 5.0250000000000000 | 14387.55 零食 | 辣條 | 5.6 | 5.0250000000000000 | 14387.55 零食 | 薯條 | 7.5 | 5.0250000000000000 | 14387.55 衣物 | 圍巾 | 93 | 76.9666666666666667 | 14387.55 衣物 | 外套 | 110.9 | 76.9666666666666667 | 14387.55 衣物 | 鞋子 | 27 | 76.9666666666666667 | 14387.55 (10 rows) ```         在一個查詢中可以包含多個窗口函數，每個窗口函數都可以用不同的`OVER`子句來按不同方式劃分數據，但是它們都作用在由虛擬表定義的同一個行集上。 ``` // 在原先結果集上追加一列，顯示按type分類，price升序后的第一條數據 // 多個over子句可以定義為window 函數進行調用。 postgres=# SELECT type,name,price,avg(price) OVER avg_function as avg,FIRST_VALUE(price) over firstvalue_function as first_price from products postgres-# WINDOW postgres-# firstvalue_function as (PARTITION BY type ORDER BY price asc),avg_function as (PARTITION BY type ORDER BY price desc); type | name | price | avg | first_price ------+----------+---------+-----------------------+------------- 電器 | 手表 | 1237.55 | 4712.1833333333333333 | 1237.55 電器 | 電視 | 3299 | 6449.5000000000000000 | 1237.55 電器 | iPhone X | 9600 | 9600.0000000000000000 | 1237.55 零食 | 汽水 | 3.5 | 5.0250000000000000 | 3.5 零食 | 方便面 | 3.5 | 5.0250000000000000 | 3.5 零食 | 辣條 | 5.6 | 6.5500000000000000 | 3.5 零食 | 薯條 | 7.5 | 7.5000000000000000 | 3.5 衣物 | 鞋子 | 27 | 76.9666666666666667 | 27 衣物 | 圍巾 | 93 | 101.9500000000000000 | 27 衣物 | 外套 | 110.9 | 110.9000000000000000 | 27 (10 rows) ```