Postgresql窗口函数

窗口函数源文档（PostgreSQL 9.3.1 中文手册）

窗口函数后必需跟着一个over子句。

一、聚合函数作为窗口函数

任何内建的或自定义的聚合函数都可以作为窗口函数，仅当调用跟着OVER子句的聚合函数，作为窗口函数；否则它们作为常规的聚合。

推荐看下面这个文档，讲得通俗易懂。
Postgresql窗口函数（一）

二、 通用窗口函数

推荐看下面这个文档，讲得通俗易懂。
Postgresql窗口函数（二）

三、使用案例

1.数据准备>

DROP TABLE IF EXISTS "public"."products";
CREATE TABLE "public"."products" (
    "id" varchar(10) COLLATE "default",
    "name" text COLLATE "default",
    "price" numeric,
    "uid" varchar(14) COLLATE "default",
    "type" varchar(100) COLLATE "default"
)
WITH (OIDS=FALSE);

BEGIN;
INSERT INTO "public"."products" VALUES ('0006', 'iPhone X', '9600', null, '电器');
INSERT INTO "public"."products" VALUES ('0012', '电视', '3299', '4', '电器');
INSERT INTO "public"."products" VALUES ('0004', '辣条', '5.6', '4', '零食');
INSERT INTO "public"."products" VALUES ('0007', '薯条', '7.5', '1', '零食');
INSERT INTO "public"."products" VALUES ('0009', '方便面', '3.5', '1', '零食');
INSERT INTO "public"."products" VALUES ('0005', '铅笔', '7', '4', '文具');
INSERT INTO "public"."products" VALUES ('0014', '作业本', '1', null, '文具');
INSERT INTO "public"."products" VALUES ('0001', '鞋子', '27', '2', '衣物');
INSERT INTO "public"."products" VALUES ('0002', '外套', '110.9', '3', '衣物');
INSERT INTO "public"."products" VALUES ('0013', '围巾', '93', '5', '衣物');
INSERT INTO "public"."products" VALUES ('0008', '香皂', '17.5', '2', '日用品');
INSERT INTO "public"."products" VALUES ('0010', '水杯', '27', '3', '日用品');
INSERT INTO "public"."products" VALUES ('0015', '洗发露', '36', '1', '日用品');
INSERT INTO "public"."products" VALUES ('0011', '毛巾', '15', '1', '日用品');
INSERT INTO "public"."products" VALUES ('0003', '手表', '1237.55', '5', '电器');
INSERT INTO "public"."products" VALUES ('0016', '绘图笔', '15', null, '文具');
INSERT INTO "public"."products" VALUES ('0017', '汽水', '3.5', null, '零食');
COMMIT;

2.案例SQL语句>

sum(price) over (partition by type) 类别金额合计,
(sum(price) over (order by type))/sum(price) over() 类别总额占所有品类商品百分比,
round(price/(sum(price) over (partition by type rows between unbounded preceding and unbounded following)),3) 子除类别百分比,
rank() over (partition by type order by price desc) 排名,
sum(price) over() 金额总计
from products ORDER BY type,price asc;

可以简写成如下代码：

select 
    id,type,name,price,
    sum(price) over w1 类别金额合计,
    (sum(price) over (order by type))/sum(price) over() 类别总额占所有品类商品百分比,
    round(price/(sum(price) over w2),3) 子除类别百分比,
    rank() over w3 排名,
    sum(price) over() 金额总计
from 
    products 
WINDOW 
    w1 as (partition by type),
    w2 as (partition by type rows between unbounded preceding and unbounded following),
    w3 as (partition by type order by price desc)
ORDER BY 
    type,price asc; 

注意：简写中over w1等有时候数据库会报错，那么用over(w1)就可以了。
3.SQL 语句输出>

打开思路的案例。这个博客看完更容易将窗口函数结合到实际工作中使用。