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() 等