《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之三：rownum和聚合函数_pgsql的rownum

作者：瀚高PG实验室 （Highgo PG Lab）- 瀚高大李

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。

rownum

rownum是Oracle内部的一个伪列，用来表示数据在结果集中的行数。它是在同一层SQL中结果集查询出来以后，排序以前进行的操作。Oracle的rownum必须从1开始。如果使用>1这样的条件，将一条数据都取不到。Oracle中，把检索结果取出来后，在画面上分页显示的功能，都是使用rownum，并且使用多层子查询来实现的。
PostgreSQL中，没有rownum这样的伪列，但是提供了 limit 和 offset 两个关键字来实现类似的功能。limit表示取得记录的条数。而 offset 是指偏移位置，也就是表示从第几条开始取数据（offset从0开始）。

Oracle rownum

SQL> select rownum, id, name from student;
 
    ROWNUM         ID NAME
---------- ---------- ----------
         1          1 张一
         2          2 王二
         3          3 李三
         4          4 赵四
 
SQL> select rownum ,id,name from student order by name;
 
    ROWNUM         ID NAME
---------- ---------- ----------
         3          3 李三
         2          2 王二
         1          1 张一
         4          4 赵四
 
SQL>  select rownum ,id,name from (select * from student order by name);
 
    ROWNUM         ID NAME
---------- ---------- ----------
         1          3 李三
         2          2 王二
         3          1 张一
         4          4 赵四
 
SQL> select rownum, id, name from student where rownum >1 order by name;
 
未选定行
 
SQL> select * from
  2  (
  3  select a.*, rownum rn
  4  from (select * from student)a
  5  where rownum <=3
  6  )
  7  where rn >2;
 
        ID NAME               RN
---------- ---------- ----------
         3 李三                3

PostgreSQL limit 和 offset

postgres=# select * from student;
 id | name
----+------
  1 | 张一
  2 | 王二
  3 | 李三
  4 | 赵四
(4 行记录)
 
postgres=# select * from student order by name;
 id | name
----+------
  3 | 李三
  2 | 王二
  1 | 张一
  4 | 赵四
(4 行记录)
 
postgres=# select * from student limit 2;
 id | name
----+------
  1 | 张一
  2 | 王二
(2 行记录)
 
postgres=# select * from student offset 2;
 id | name
----+------
  3 | 李三
  4 | 赵四
(2 行记录)
 
postgres=# select * from student limit 1 offset 2;
 id | name
----+------
  3 | 李三
(1 行记录)

聚合函数

聚合函数是标准SQL语言中的函数，主要包括求计数(count)，求最大值(max)，求最小值(min)，求和(sum)和求平均数(avg)等几个。Oracle和PostgreSQL都支持聚合函数。但是对求和(sum)和求平均数(avg)而言，Oracle和PostgreSQL有一些不同。

对求和(sum)和求平均数(avg)而言，Oracle由于内部有自动类型转换，所以对于字符型数字（数据类型是字符，但是里面的数据都是数字）可以直接使用。而PostgreSQL内部没有自动类型转换，所以对于字符型数字而言，必须先使用类型转换，转换成数字后再使用函数进行计算。

Oracle 聚合函数

SQL> desc achieve;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 NAME                                               VARCHAR2(10)
 ACHIEVE                                            VARCHAR2(10)
 
SQL> select * from achieve;
 
        ID NAME       ACHIEVE
---------- ---------- ----------
         1 赵大       5200
         2 钱二       4500
         3 孙三       6000
         4 李四       8000
         5 周五
 
SQL>  select min(achieve) from achieve;
 
MIN(ACHIEV
----------
4500
 
SQL>  select max(achieve) from achieve;
 
MAX(ACHIEV
----------
8000
 
SQL>  select count(achieve) from achieve;
 
COUNT(ACHIEVE)
--------------
             4
 
SQL> select sum(achieve) from achieve;
 
SUM(ACHIEVE)
------------
       23700
 
SQL> select avg(achieve) from achieve;
 
AVG(ACHIEVE)
------------
        5925

PostgreSQL 聚合函数

postgres=# \d achieve;
         数据表 "public.achieve"
  栏位   |         类型          | 修饰词
---------+-----------------------+--------
 id      | integer               | 非空
 name    | character varying(10) |
 achieve | character varying(10) |
索引：
    "achieve_pkey" PRIMARY KEY, btree (id)
 
postgres=# select * from achieve;
 id | name | achieve
----+------+---------
  1 | 赵大 | 5200
  2 | 钱二 | 4500
  3 | 孙三 | 6000
  4 | 李四 | 8000
  5 | 周五 |
(5 行记录)
 
postgres=# select min(achieve) from achieve;
 min
------
 4500
(1 行记录)
 
postgres=# select max(achieve) from achieve;
 max
------
 8000
(1 行记录)
 
postgres=# select count(achieve) from achieve;
 count
-------
     4
(1 行记录)
 
postgres=# select sum(achieve) from achieve;
错误:  函数 sum(character varying) 不存在
第1行select sum(achieve) from achieve;
            ^
提示:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select avg(achieve) from achieve;
错误:  函数 avg(character varying) 不存在
第1行select avg(achieve) from achieve;
            ^
提示:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select sum(achieve::int) from achieve;
  sum
-------
 23700
(1 行记录)
 
postgres=# select avg(cast(achieve as int)) from achieve;
          avg
-----------------------
 5925.0000000000000000
(1 行记录)