PostgreSQL>窗口函数的用法

PostgreSQL之窗口函数的用法

转载请注明出处:https://www.cnblogs.com/funnyzpc/p/9311281.html

PostgreSQL的高级特性本准备三篇的(递归、窗口函数、JSON),结果中间一直一直加班 和遗忘 拖到现在才写到中篇,欸,加班真不是一件好事情。

  谈谈我对加班的看法吧=> 如果加班能控制在一个小时内,这样会比较好(当然如果不加班的话更好),偶尔适当的加班能提高工作进度,对创业公司来说尤为重要;但,糟糕的地方也不少,加班时间长了容易造成思维缓慢,这对脑子本来就不快的人来说伤害尤其的大(我就是个例子),也容易造成颈椎病、高血压、过劳。。。等等可怕的疾病,尤其还是做IT的一定要注意到这个问题,以上这些话可能有童鞋不会在意,那我就在这里说说我见过的真实的例子,我上一家公司的CTO有比较严重的脊椎病,(他说)坐的时间久了背部尤其的难受,上一家公司总监也经常加班,可能再加上本身体质的原因,心脏现在已经装上了起搏器,同样是上一家公司,我的一同事,也就比我大三岁左右,头发已经有相当部分白了哎,每见到这样的事儿都很难受,人一辈子,如果没有足够的时间去关注生活,关注健康,我们生活内容还剩下什么?

  这次我就简单的讲讲PostgreSQL的高级特性>窗口函数

  我先用表格列出PostgreSQL里面的窗口函数,(源文档在这里>http://www.postgres.cn/docs/9.3/functions-window.html,推荐去postgre的中文社区看看)

   讲第一个问题之前我先扔出一个需求>如何给查询出来的数据添加一列序号,用最简单的方式实现?

Oracle>使用rownum快速生成

    MySql>使用变量定义:(@i:=@i+1)  as row

    SqlServer>通过定义存储过程的方式

    PostgreSQL>通过函数generate_series(start_value,end_value)

额,以上方式我大概都用过,对于Oracle的方式虽然语句简单,但是涉及到排序的时候可就乱了,mysql的方式也还算可以,但是这样并没有通用性,子查询的时候会相当麻烦,同时个人觉得这更像是存储过程和sql的结合体,也破坏了Sql本该有的形式,PostgreSQL的方式虽然不错,但是总要指定起始和终止值,这个在生成测试数据的时候还好用,具体业务开发用起来可就麻烦多了;这里,当然有更好的实现方式>窗口函数,这个属性在主流的数据库系统中都有实现(以前用oracle的时候竟然没发现这么好用的东西,好遗憾)。

  这里我先放出表结构语句:

 1 DROP TABLE IF EXISTS "public"."products";
 2 CREATE TABLE "public"."products" (
 3     "id" varchar(10) COLLATE "default",
 4     "name" text COLLATE "default",
 5     "price" numeric,
 6     "uid" varchar(14) COLLATE "default",
 7     "type" varchar(100) COLLATE "default"
 8 )
 9 WITH (OIDS=FALSE);
10 
11 BEGIN;
12 INSERT INTO "public"."products" VALUES ('0006', 'iPhone X', '9600', null, '电器');
13 INSERT INTO "public"."products" VALUES ('0012', '电视', '3299', '4', '电器');
14 INSERT INTO "public"."products" VALUES ('0004', '辣条', '5.6', '4', '零食');
15 INSERT INTO "public"."products" VALUES ('0007', '薯条', '7.5', '1', '零食');
16 INSERT INTO "public"."products" VALUES ('0009', '方便面', '3.5', '1', '零食');
17 INSERT INTO "public"."products" VALUES ('0005', '铅笔', '7', '4', '文具');
18 INSERT INTO "public"."products" VALUES ('0014', '作业本', '1', null, '文具');
19 INSERT INTO "public"."products" VALUES ('0001', '鞋子', '27', '2', '衣物');
20 INSERT INTO "public"."products" VALUES ('0002', '外套', '110.9', '3', '衣物');
21 INSERT INTO "public"."products" VALUES ('0013', '围巾', '93', '5', '衣物');
22 INSERT INTO "public"."products" VALUES ('0008', '香皂', '17.5', '2', '日用品');
23 INSERT INTO "public"."products" VALUES ('0010', '水杯', '27', '3', '日用品');
24 INSERT INTO "public"."products" VALUES ('0015', '洗发露', '36', '1', '日用品');
25 INSERT INTO "public"."products" VALUES ('0011', '毛巾', '15', '1', '日用品');
26 INSERT INTO "public"."products" VALUES ('0003', '手表', '1237.55', '5', '电器');
27 INSERT INTO "public"."products" VALUES ('0016', '绘图笔', '15', null, '文具');
28 INSERT INTO "public"."products" VALUES ('0017', '汽水', '3.5', null, '零食');
29 COMMIT;

这我先用第一个函数row_number() ,一句即可实现>

  select type,name,price,row_number() over(order by price asc) as idx from products ;

结果>

用窗口函数的好处不仅仅可实现序号列,还可以在over()内按指定的列排序,上图是按照price列升序。

  这里,对于以上提到的一个问题,根据上面的数据 我再做个扩充>如果需要在类别(type)内按照价格(price) 升序排列(就是在类别内做排序),该怎么做呢?

  当然也很简单,只需要在窗口(over())中声明分隔方式 Partition .

  分类排序序号,row_number() 实现>

 select type,name,price,row_number() over(PARTITION by type order by price asc) as idx from products ;

  查询结果>

   上面的问题这里需求完美实现,额,这里其实还可以做个扩充,你可以注意到零食类别内的 方便面和汽水价格是一样的,如何将零食和汽水并列第一呢?答案是:用窗口函数>rank()

   分类排序序号并列, rank() 实现>

 SELECT type,name,price,rank() over(partition by type order by price asc) from products;

  SQL输出>

  需求又完美的实现了,但,注意到没,零食类别中的第三个 辣条 排到第三了,如果这里需要在类别里面能保持序号不重不少(将辣条排名至第二),如何实现呢?答案>使用窗口函数 dense_rank()

  分类排序序号并列顺序,dense_rank() 实现>

 SELECT type,name,price,dense_rank() over(partition by type order by price asc) from products;

  SQL输出>

  OK,以上的几个窗口函数已经能实现大多数业务需求了,如果有兴趣可以看看一些特殊业务可能用到的功能,比如说如何限制序号在0到1之间排序呢?

  限制序号在0~1之间(0作为第一个序),窗口函数 percernt_rank() >

 SELECT type,name,price,percent_rank() over(partition by type order by price asc) from products;

  SQL语句输出>

注意:上面的percernt_rank()函数默认是从0开始排序的,如果需要使用相对0~1之间的排名,需要这样:

  限制序号在0~1之间相对排名,窗口函数 cume_dist() 实现>

SELECT type,name,price,cume_dist() over(partition by type order by price asc) from products;

  SQL语句输出>

注意:上面的序号是相对于0开始排序的。

  对于排序序号还可以限制最大序号,这样做:

  限制最大序号为指定数字序号 ntile(val1) 实现 >

SELECT type,name,price,ntile(2) over(partition by type order by price asc) from products;

  SQL语句输出 >

窗口函数还可以实现在子分类排序的情况下取偏移值,这样实现>

  获取到排序数据的每一项的偏移值(向下偏移) , lag(val1,val2,val3) 函数实现>

SELECT id,type,name,price,lag(id,1,'') over(partition by type order by price asc) as topid from products;

  SQL语句输出 >

注意:函数lag(val1,val2,val3) 中的三个参数分别为->(输出的上一条记录的字段,偏移值,无偏移值的默认值);以上这里的偏移值为1,偏移字段为id,无偏移默认值为空('')

  若获取数据项偏移值(向上偏移) , lead(val1,val2,val3)>

SELECT id,type,name,price,lead(id,1,'') over(partition by type order by price asc) as downid from products;

   SQL 语句输出 >

当然,窗口函数还可以实现每个子类排序中的第一项的某个字段的值,可以这样实现:

  获取分类子项排序中的第一条记录的某个字段的值, first_value(val1) 实现>

 SELECT id,type,name,price,first_value(name) over(partition by type order by price asc) from products;

  SQL语句输出>

注意:以上函数取的是排序子类记录中的第一条记录的name字段。

  当然也可以向下取分类排序中的最后一条记录的某个字段, last_value(val1)实现>

 SELECT id,type,name,price,last_value(name) over(partition by type order by price range between unbounded preceding and unbounded following) from products; -- order by type asc ;-- ,price asc;

  SQL 语句输出 >

额,这里需要说明的是,当取分类在最后一条记录的时候 自然排序下不可以在over() 使用排序字段,不然取得的值为相对于当前记录的值,故这里按价格(price) 升序的时候指定 排序字段 -> range between unbounded preceding and unbounded following

  窗口函数还能在分类排序下取得指定序号记录的某个字段,这样:

  取得排序字段项目中指定序号记录的某个字段值, nth_value(val1,val2)>

SELECT id,type,name,price,nth_value(name,2) OVER(partition by type order by price range between unbounded preceding and unbounded following ) from products;

  SQL语句输出 >

  额,窗口函数在单独使用的时候能省略很多不必要的查询 ,比如子查询、聚合查询,当然窗口函数能做得更多(配合聚合函数使用的时候) ,额,这里我给出一个示例 >

  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;

  SQL 语句输出>

上面的语句看起来会有点儿晕,查询语句子项就像是在输出参数项里面直接写子查询的感觉,事实上为使语句有更好的可读性,窗口条件可以放在from后面 ,这样子>

 1 select 
 2     id,type,name,price,
 3     sum(price) over w1 类别金额合计,
 4     (sum(price) over (order by type))/sum(price) over() 类别总额占所有品类商品百分比,
 5     round(price/(sum(price) over w2),3) 子除类别百分比,
 6     rank() over w3 排名,
 7     sum(price) over() 金额总计
 8 from 
 9     products 
10 WINDOW 
11     w1 as (partition by type),
12     w2 as (partition by type rows between unbounded preceding and unbounded following),
13     w3 as (partition by type order by price desc)
14 ORDER BY 
15     type,price asc; 

现在是 2018-07-22 21:59:31 ,各位晚安~

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

你真的会玩SQL吗?实用函数方法汇总

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2069
来自专栏青玉伏案

OracleDBA之表管理

  下面是Oracle表管理的部分,用到的测试表是oracle数据库中scott用户下的表做的测试,有的实验也用到了hr用户的数据,以下这些东西是我的麦库上存的...

1978
来自专栏Java学习网

程序员的进化

程序员的进化   不久前,在互联网上出现了一篇有趣的文章,讲的是对于同一个问题,不同层次的程序员编出的Python代码显示出了不同的风格,代码都很简单,有趣。我...

3107
来自专栏机器学习从入门到成神

关于SQL数据库中的范式

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

821
来自专栏james大数据架构

你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2087
来自专栏影子

PostgreSQL=>递归查询

转载请注明源地址:http://www.cnblogs.com/funnyzpc/p/8232073.html

1403
来自专栏数据和云

【云和恩墨大讲堂】从执行计划洞察ORACLE优化器的“小聪明”

作者简介 黄浩 惠普 十年一剑,十年磨砺。3年通信行业,写就近3万条SQL;5年制造行业,遨游在ETL的浪潮;2年性能优化,厚积薄发自成一家 主题介绍: O...

2833
来自专栏文渊之博

介绍一种非常好用汇总数据的方式GROUPING SETS

介绍   对于任何人而言,用T-SQL语句来写聚会查询都是工作中重要的一环。我们大家也都很熟悉GROUP BY子句来实现聚合表达式,但是如果打算在一个结果集中包...

38211
来自专栏崔庆才的专栏

MySQL 枚举类型的“八宗罪”

2454
来自专栏跟着阿笨一起玩NET

关于SQL Server数据库设计的感悟,请指教

有问题的时候,我经常回来博客园寻找答案,久而久之,总结了一些东西。 妄自菲薄,请大家多指出错误,并给出意见 数据库设计三范式基本原则 第一范式:数据库...

672

扫码关注云+社区