postgresql 窗口函数

窗口函数类似与汇聚函数，不同的是汇聚函数会将 记录集数据 汇聚 成一条记录，而窗口函数则不会，窗口函数也是作用在记录集上，但返回结果不会减少记录行数。

下面给出具体例子：

我们创建产品和产品组两张表：

CREATE TABLE product_groups (

group_id serial PRIMARY KEY,

group_name VARCHAR (255) NOT NULL

);

 

CREATE TABLE products (

product_id serial PRIMARY KEY,

product_name VARCHAR (255) NOT NULL,

price DECIMAL (11, 2),

group_id INT NOT NULL,

FOREIGN KEY (group_id) REFERENCES product_groups (group_id)

);

 

INSERT INTO product_groups (group_name)

VALUES

( 'Smartphone'),

( 'Laptop'),

( 'Tablet');

 

INSERT INTO products (product_name, group_id,price)

VALUES

( 'Microsoft Lumia', 1, 200),

( 'HTC One', 1, 400),

( 'Nexus', 1, 500),

( 'iPhone', 1, 900),

( 'HP Elite', 2, 1200),

( 'Lenovo Thinkpad', 2, 700),

( 'Sony VAIO', 2, 700),

( 'Dell Vostro', 2, 800),

( 'iPad', 3, 700),

( 'Kindle Fire', 3, 150),

( 'Samsung Galaxy Tab', 3, 200);

汇聚函数avg计算每个产品组的平均价为例：

sql语句：

SELECT

group_name,

AVG (price)

FROM

products

INNER JOIN product_groups USING (group_id)

GROUP BY

group_name;

结果：

group_name |          avg

------------+----------------------

Tablet      | 600

Smartphone | 500

Laptop      | 800

(3 rows)

窗口功能：

SQL

SELECT

product_name,

price,

group_name,

AVG (price) OVER ( PARTITION BY group_name)

FROM

products

INNER JOIN product_groups USING (group_id);

结果：

 product_name     |   price   | group_name |          avg

--------------------+---------+------------+----------------------

HP Elite            |   900.00 | Laptop      | 800

Lenovo Thinkpad     | 1100.00 | Laptop      | 800

Sony VAIO           |   600.00 | Laptop      | 800

Dell Vostro         |   600.00 | Laptop      | 800

Microsoft Lumia     |   300.00 | Smartphone | 500

HTC One             |   400.00 | Smartphone | 500

Nexus               |   500.00 | Smartphone | 500

iPhone              |   800.00 | Smartphone | 500

iPad                |   700.00 | Tablet      | 600

Kindle Fire         |   300.00 | Tablet      | 600

Samsung Galaxy Tab |   800.00 | Tablet      | 600

(11 rows)

窗口函数语法：

window_function(arg1, arg2,..) OVER (PARTITION BY expression ORDER BY expression)

一个窗口函数执行在 JOIN, WHERE, GROUP BY 和HAVING子句之后，ORDER BY 子句之前。

内嵌窗口函数：

row_number(), rank(), dense_rank()

first_value(), last_value(), nth_value()

LAG(), LEAD() 等