Postgresql 窗口函数的使用方法

Postgresql 窗口函数的使用方法

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

image.png

# 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

posted on 2020-11-01 22:23  多一点  阅读(259)  评论(0编辑  收藏  举报

导航