Postgresql 窗口函数的使用方法 row_number rank dense_rank over()

Postgresql 窗口函数的使用方法

导入数据库连接

1. python版本 3.8
2. 数据库软件postgresql 版本12.0
3. ipython-sql 版本 4.0
4. 目的 在jupyter notebook 界面使用数据库，实现数据中的窗口函数演示

 

# import sqlalchemy 
# 
# sqlalchemy.create_engine('postgresql://postgres:1234@localhost/postgres  ')

%load_ext sql
##  ipython-sql 载入sql命令环境
%sql postgresql://postgres:1234@localhost/postgres   
## 连接数据库，用户名postgres密码1234

%%sql 
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;

* postgresql://postgres:***@localhost/postgres
Done.
Done.
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
Done.





[]

%%sql 
select * from public.products

* postgresql://postgres:***@localhost/postgres
17 rows affected.

id	name	price	uid	type
0006	iPhone X	9600	None	电器
0012	电视	3299	4	电器
0004	辣条	5.6	4	零食
0007	薯条	7.5	1	零食
0009	方便面	3.5	1	零食
0005	铅笔	7	4	文具
0014	作业本	1	None	文具
0001	鞋子	27	2	衣物
0002	外套	110.9	3	衣物
0013	围巾	93	5	衣物
0008	香皂	17.5	2	日用品
0010	水杯	27	3	日用品
0015	洗发露	36	1	日用品
0011	毛巾	15	1	日用品
0003	手表	1237.55	5	电器
0016	绘图笔	15	None	文具
0017	汽水	3.5	None	零食

%%sql
select type, name,price,row_number() over( order by price asc) as xuhao from public.products

--- 这里的窗口函数可以实现序号列，还可以按照over内的指定的列排序，该语句是按照price列进行升序排列

* postgresql://postgres:***@localhost/postgres
17 rows affected.

type	name	price	xuhao
文具	作业本	1	1
零食	汽水	3.5	2
零食	方便面	3.5	3
零食	辣条	5.6	4
文具	铅笔	7	5
零食	薯条	7.5	6
日用品	毛巾	15	7
文具	绘图笔	15	8
日用品	香皂	17.5	9
日用品	水杯	27	10
衣物	鞋子	27	11
日用品	洗发露	36	12
衣物	围巾	93	13
衣物	外套	110.9	14
电器	手表	1237.55	15
电器	电视	3299	16
电器	iPhone X	9600	17

%%sql
select type, name,price,rank() over( partition by type order by price asc) as rank from public.products

--- 这里实现分类排序，可以按照分类之间内部的价格排序，
--- 可能会出现并列的现象，同时序号会出现间断的情况

* postgresql://postgres:***@localhost/postgres
17 rows affected.

type	name	price	rank
电器	手表	1237.55	1
电器	电视	3299	2
电器	iPhone X	9600	3
零食	汽水	3.5	1
零食	方便面	3.5	1
零食	辣条	5.6	3
零食	薯条	7.5	4
日用品	毛巾	15	1
日用品	香皂	17.5	2
日用品	水杯	27	3
日用品	洗发露	36	4
文具	作业本	1	1
文具	铅笔	7	2
文具	绘图笔	15	3
衣物	鞋子	27	1
衣物	围巾	93	2
衣物	外套	110.9	3

%%sql
select type, name,price,dense_rank() over( partition by type order by price asc) as ds_rank from public.products

--- 这里实现分类排序，可以按照分类之间内部的价格排序，
--- 可能会出现并列的现象，同时序号不少，注意辣条的排名从rank的第三名变成了第二名

* postgresql://postgres:***@localhost/postgres
17 rows affected.

type	name	price	ds_rank
电器	手表	1237.55	1
电器	电视	3299	2
电器	iPhone X	9600	3
零食	汽水	3.5	1
零食	方便面	3.5	1
零食	辣条	5.6	2
零食	薯条	7.5	3
日用品	毛巾	15	1
日用品	香皂	17.5	2
日用品	水杯	27	3
日用品	洗发露	36	4
文具	作业本	1	1
文具	铅笔	7	2
文具	绘图笔	15	3
衣物	鞋子	27	1
衣物	围巾	93	2
衣物	外套	110.9	3

%%sql
select type, name,price,percent_rank() over( partition by type order by price asc) as ps_rank from public.products

--- 这里实现分类排序，可以按照分类之间内部的价格排序，
--- 百分比排名

* postgresql://postgres:***@localhost/postgres
17 rows affected.

type	name	price	ps_rank
电器	手表	1237.55	0.0
电器	电视	3299	0.5
电器	iPhone X	9600	1.0
零食	汽水	3.5	0.0
零食	方便面	3.5	0.0
零食	辣条	5.6	0.666666666666667
零食	薯条	7.5	1.0
日用品	毛巾	15	0.0
日用品	香皂	17.5	0.333333333333333
日用品	水杯	27	0.666666666666667
日用品	洗发露	36	1.0
文具	作业本	1	0.0
文具	铅笔	7	0.5
文具	绘图笔	15	1.0
衣物	鞋子	27	0.0
衣物	围巾	93	0.5
衣物	外套	110.9	1.0

%%sql
select type, name,price,cume_dist() over( partition by type order by price asc) as cd_cume from public.products

--- 这里实现分类排序，可以按照分类之间内部的价格排序，
--- 累计百分比

* postgresql://postgres:***@localhost/postgres
17 rows affected.

type	name	price	cd_cume
电器	手表	1237.55	0.333333333333333
电器	电视	3299	0.666666666666667
电器	iPhone X	9600	1.0
零食	汽水	3.5	0.5
零食	方便面	3.5	0.5
零食	辣条	5.6	0.75
零食	薯条	7.5	1.0
日用品	毛巾	15	0.25
日用品	香皂	17.5	0.5
日用品	水杯	27	0.75
日用品	洗发露	36	1.0
文具	作业本	1	0.333333333333333
文具	铅笔	7	0.666666666666667
文具	绘图笔	15	1.0
衣物	鞋子	27	0.333333333333333
衣物	围巾	93	0.666666666666667
衣物	外套	110.9	1.0

%%sql
select type, name,price,ntile(2) over( partition by type order by price asc) as fenzu from public.products

--- 这里实现分类排序，可以按照分类之间内部的价格排序，
--- 最大化均分,分桶

* postgresql://postgres:***@localhost/postgres
17 rows affected.

type	name	price	fenzu
电器	手表	1237.55	1
电器	电视	3299	1
电器	iPhone X	9600	2
零食	汽水	3.5	1
零食	方便面	3.5	1
零食	辣条	5.6	2
零食	薯条	7.5	2
日用品	毛巾	15	1
日用品	香皂	17.5	1
日用品	水杯	27	2
日用品	洗发露	36	2
文具	作业本	1	1
文具	铅笔	7	1
文具	绘图笔	15	2
衣物	鞋子	27	1
衣物	围巾	93	1
衣物	外套	110.9	2

窗口函数和聚合函数同时使用

%%sql
select id,type,name,price,
sum(price) over(partition by type) 类别金额合计,
sum(price) over(order by type)/ sum(price) over() 类别总金额占所有金额的占比,
round(price /sum(price) over(partition by type),3) 金额占类别金额占比,
rank() over(partition by type order by price desc) 排名,
sum(price) over() 金额总计
from public.products 
order by type, price asc

* postgresql://postgres:***@localhost/postgres
17 rows affected.

id	type	name	price	类别金额合计	类别总金额占所有金额的占比	金额占类别金额占比	排名	金额总计
0003	电器	手表	1237.55	14136.55	0.97452786940621326964	0.088	3	14506.05
0012	电器	电视	3299	14136.55	0.97452786940621326964	0.233	2	14506.05
0006	电器	iPhone X	9600	14136.55	0.97452786940621326964	0.679	1	14506.05
0009	零食	方便面	3.5	20.1	0.97591349816111208772	0.174	3	14506.05
0017	零食	汽水	3.5	20.1	0.97591349816111208772	0.174	3	14506.05
0004	零食	辣条	5.6	20.1	0.97591349816111208772	0.279	2	14506.05
0007	零食	薯条	7.5	20.1	0.97591349816111208772	0.373	1	14506.05
0011	日用品	毛巾	15	95.5	0.98249695816573085023	0.157	4	14506.05
0008	日用品	香皂	17.5	95.5	0.98249695816573085023	0.183	3	14506.05
0010	日用品	水杯	27	95.5	0.98249695816573085023	0.283	2	14506.05
0015	日用品	洗发露	36	95.5	0.98249695816573085023	0.377	1	14506.05
0014	文具	作业本	1	23	0.98408250350715735848	0.043	3	14506.05
0005	文具	铅笔	7	23	0.98408250350715735848	0.304	2	14506.05
0016	文具	绘图笔	15	23	0.98408250350715735848	0.652	1	14506.05
0001	衣物	鞋子	27	230.9	1.00000000000000000000	0.117	3	14506.05
0013	衣物	围巾	93	230.9	1.00000000000000000000	0.403	2	14506.05
0002	衣物	外套	110.9	230.9	1.00000000000000000000	0.480	1	14506.05