注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: Window Functions 初步使用  

2012-07-20 21:11:47|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

     PostgreSQL 支持 Window Functions,可以对查询出的结果集进行分组处理,非常方便,接下来
举个简单的例子演示下,创建一张成绩表,取各科目成绩最高的同学。    
 

--1 创建成绩表

 francs=> create table score ( id serial primary key,subject varchar(32),stu_name varchar(64),score numeric(3,0) );
NOTICE:  CREATE TABLE will create implicit sequence "score_id_seq" for serial column "score.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "score_pkey" for table "score"
CREATE TABLE

francs=> \d score
             Table "francs.score"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 subject  | character varying(32) |
 stu_name | character varying(64) |
 score    | numeric(3,0)          |

   
 
--2 插入测试数据

 francs=> insert into score ( subject,stu_name,score ) values ('Chinese','francs',70);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('English','francs',90);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Math','francs',80);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Chinese','fpzhou',70);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('English','fpzhou',75);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Math','fpzhou',99);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Chinese','tutu',80);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('English','tutu',60);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Math','tutu',65);
INSERT 0 1

francs=> select * from score;
 subject | stu_name | score
---------+----------+-------
 Chinese | francs   |    70
 English | francs   |    90
 Math    | francs   |    80
 Chinese | fpzhou   |    70
 English | fpzhou   |    75
 Math    | fpzhou   |    99
 Chinese | tutu     |    80
 English | tutu     |    60
 Math    | tutu     |    65
(9 rows)

   


--3 方法一:查询各科目最高成绩

 select *
  from (select subject,
               stu_name,
               score,
               row_number() over(partition by subject order by score desc ) as score_desc
          from score) as order_score
 where score_desc < 2 order by subject ;

 subject | stu_name | score | score_desc
---------+----------+-------+------------
 Chinese | tutu     |    80 |          1
 English | francs   |    90 |          1
 Math    | fpzhou   |    99 |          1
(3 rows)

   
  备注:这里使用了 windows function ,其中 "over(partition by subject order by score desc )"是
            windows function 核心,"partition by .."表示将结果集根据指定字段进行分组,上例中是将结
            果集根据 subject 进行分组; "order by .." 是指将每组的结果集根据指定字段排序。

 

--4 方法二:传统的方法

 select a.*
  from score a,
       (select subject, max(score) as score from score group by subject) b
 where a.subject = b.subject
   and a.score = b.score
   order by a.subject;
  
 id | subject | stu_name | score
----+---------+----------+-------
  7 | Chinese | tutu     |    80
  2 | English | francs   |    90
  6 | Math    | fpzhou   |    99
(3 rows)
   

      备注:暂且不考虑语句效率,仅实现功能。


--5 在结果集中增加各科目平均成绩信息

 francs=> select subject,stu_name,score,avg(score) over (partition by subject) from score;
 subject | stu_name | score |         avg        
---------+----------+-------+---------------------
 Chinese | fpzhou   |    70 | 73.3333333333333333
 Chinese | francs   |    70 | 73.3333333333333333
 Chinese | tutu     |    80 | 73.3333333333333333
 English | fpzhou   |    75 | 75.0000000000000000
 English | francs   |    90 | 75.0000000000000000
 English | tutu     |    60 | 75.0000000000000000
 Math    | francs   |    80 | 81.3333333333333333
 Math    | tutu     |    65 | 81.3333333333333333
 Math    | fpzhou   |    99 | 81.3333333333333333
(9 rows)
   


--6 row_number()  窗口函数

 francs=> select subject,stu_name,score,row_number() over (partition by subject) from score;
 subject | stu_name | score | row_number
---------+----------+-------+------------
 Chinese | fpzhou   |    70 |          1
 Chinese | francs   |    70 |          2
 Chinese | tutu     |    80 |          3
 English | fpzhou   |    75 |          1
 English | francs   |    90 |          2
 English | tutu     |    60 |          3
 Math    | francs   |    80 |          1
 Math    | tutu     |    65 |          2
 Math    | fpzhou   |    99 |          3
(9 rows)
   

 备注:使用 row_number() over ... 函数可以对结果集按某字段分组后的记录进行标记,因此
           使用 row_number() 窗口函数很容易实现取分组后指定记录的功能。


--7 rank() over 窗口函数

 francs=>    select subject,stu_name,score,rank() over (partition by subject order by score desc) from score;
 subject | stu_name | score | rank
---------+----------+-------+------
 Chinese | tutu     |    80 |    1
 Chinese | francs   |    70 |    2
 Chinese | fpzhou   |    70 |    2
 English | francs   |    90 |    1
 English | fpzhou   |    75 |    2
 English | tutu     |    60 |    3
 Math    | fpzhou   |    99 |    1
 Math    | francs   |    80 |    2
 Math    | tutu     |    65 |    3
(9 rows)
   

  备注: rank() 窗口函数和 ow_number()  窗口函数类似,但 rank() 窗口函数会将结果集分组后相同
           值的记录的标记相等,例如上例中红色的记录。

 

--8 取行号,相当于Oracle 里的 rownum

francs=> select  row_number() OVER (ORDER BY id) AS rownum ,* from score;
 rownum | id | subject | stu_name | score
--------+----+---------+----------+-------
      1 |  1 | Chinese | francs   |    70
      2 |  2 | English | francs   |    90
      3 |  3 | Math    | francs   |    80
      4 |  4 | Chinese | fpzhou   |    70
      5 |  5 | English | fpzhou   |    75
      6 |  6 | Math    | fpzhou   |    99
      7 |  7 | Chinese | tutu     |    80
      8 |  8 | English | tutu     |    60
      9 |  9 | Math    | tutu     |    65
(9 rows)
   


--9 参考
http://www.postgresql.org/docs/9.2/static/tutorial-window.html
http://www.postgresql.org/docs/9.2/static/functions-window.html

  评论这张
 
阅读(23786)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016