《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之三:rownum和聚合函数-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介:

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 行记录)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: