Postgresql 窗口函数的使用方法
导入数据库连接
- python版本 3.8
- 数据库软件postgresql 版本12.0
- ipython-sql 版本 4.0
- 目的 在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 |
