PostgreSQL总结(2)特殊数据类型查询 - array

原创 2016年08月29日 18:06:53

回顾

        前面我们介绍了PostgreSQL的安装和对数据库的一些简单操作。我们知道,对于数据库的大部分操作都是查询操作,PostgreSQL具有丰富的数据类型,有一些特殊的数据类型操作与其他关系型数据库有所区别,这里我们来介绍一下对于这些数据类型的查询。

array查询

        若Table_A和Table_B是多对多的关系,在其他关系型数据库中,我们会再创建一张表来保存Table_A与Table_B之间的关系,因为其他关系型数据库无法使用一条记录来保存这种关系。而在PostgreSQL中,则可以在Table_A和Table_B中分别使用一个数组来保存这种关系,就不需要额外的再创建一张表:在 Table_A 中字段 Column_A_B 是数组类型,Column_A_B 数组中的元素作为 Table_B 的外键;在 Table_B 中字段 Column_B_A 是数组类型,Column_B_A 数组中的元素作为 Table_A 的外键。

        为了更加直观,我们举例说明。有两张表:学生表(student)和课程表(course),一个学生可以选多门课程,一门课程被多个学生选。

-- 学生表
CREATE TABLE student
(
    id bigserial, --主键
    name text, --姓名
    courses bigint[], --所选课程
    CONSTRAINT student_pk PRIMARY KEY(id) 
);

-- 课程表
CREATE TABLE course
(
    id bigserial, --主键
    name text, --课程名称
    e_name text, --课程英文名称
    students bigint[], --选该课程的学生
    CONSTRAINT course_pk PRIMARY KEY(id) 
);

在student表和course表中分别插入几条数据:

-- student插入四条数据
INSERT INTO student
(name, courses)
VALUES
('ZhangSan', array[1,2]),
('LiSi', array[1,3]),
('WangWu', array[2,3]),
('ZhaoLiu', array[1,2,3]);

-- course插入三条数据
INSERT INTO course
(name, e_name, students)
VALUES
('语文', 'Chinese', array[1,2,4]),
('数学', 'Math', array[1,3,4]),
('英语', 'English', array[2,3,4]);

插入结果如下:

=> SELECT * FROM student;
 id |   name   | courses 
----+----------+---------
  1 | ZhangSan | {1,2}
  2 | LiSi     | {1,3}
  3 | WangWu   | {2,3}
  4 | ZhaoLiu  | {1,2,3}
(4 rows)

=> SELECT * FROM course;
 id | name | e_name  | students 
----+------+---------+----------
  1 | 语文 | Chinese | {1,2,4}
  2 | 数学 | Math    | {1,3,4}
  3 | 英语 | English | {2,3,4}
(3 rows)

        目前在student表和course表中都只是保存了其关联的另一张表的id。那么问题来了,如果想查看其关联的表中的其他信息呢?如:想查看”ZhangSan”选了的课程名称有哪些?这就需要使用PostgreSQL中的关键词ANY了(或者使用”&&”)

SELECT s.id sid, c.id cid, s.name sname, s.courses, c.name cname, c.e_name ce_name
FROM student s
LEFT JOIN course c ON c.id = ANY(s.courses)
ORDER BY s.id;

或者:

SELECT s.id sid, c.id cid, s.name sname, s.courses, c.name cname, c.e_name ce_name
FROM student s
LEFT JOIN course c ON array[c.id] && s.courses
ORDER BY s.id;

查询结果如下:

 sid | cid |  sname   | courses | cname | ce_name 
-----+-----+----------+---------+-------+---------
   1 |   1 | ZhangSan | {1,2}   | 语文  | Chinese
   1 |   2 | ZhangSan | {1,2}   | 数学  | Math
   2 |   1 | LiSi     | {1,3}   | 语文  | Chinese
   2 |   3 | LiSi     | {1,3}   | 英语  | English
   3 |   2 | WangWu   | {2,3}   | 数学  | Math
   3 |   3 | WangWu   | {2,3}   | 英语  | English
   4 |   1 | ZhaoLiu  | {1,2,3} | 语文  | Chinese
   4 |   2 | ZhaoLiu  | {1,2,3} | 数学  | Math
   4 |   3 | ZhaoLiu  | {1,2,3} | 英语  | English

        但这个结果好像跟我们预期的还有那么一点点不一样:我们希望的是,关联后的结果条数和student表相同(如分页查询学生所选课程),课程名称也是一个数组并且和课程的id数组对应。在这里,我们做一些改动,查询语句如下:

SELECT s.id sid, s.name sname, s.courses, 
    array(SELECT name FROM course WHERE id = ANY(s.courses)) cname,
    array(SELECT e_name FROM course WHERE id = ANY(s.courses)) ce_name
FROM student s
ORDER BY s.id;

查询结果如下:

 sid |  sname   | courses |      cname       |        ce_name         
-----+----------+---------+------------------+------------------------
   1 | ZhangSan | {1,2}   | {语文,数学}      | {Chinese,Math}
   2 | LiSi     | {1,3}   | {语文,英语}      | {Chinese,English}
   3 | WangWu   | {2,3}   | {数学,英语}      | {Math,English}
   4 | ZhaoLiu  | {1,2,3} | {语文,数学,英语} | {Chinese,Math,English}
(4 rows)

        是不是这样的结果更能让人接受?但是问题又来了:如果course表中有很多个字段,都需要像上面那样合并成一个,那岂不是要有N多个子查询?这样会不会效率很低?关于这个问题,笔者也没有什么太好的解决方案,欢迎大家提出更好的解决方案。目前的处理方式是创建一个视图,将这些信息组合的耗时操作集中处理,不会影响用户。视图的DDL如下:

CREATE VIEW course_info AS
SELECT 
    DISTINCT courses,
    array(SELECT name FROM course WHERE id = ANY(s.courses)) cname,
    array(SELECT e_name FROM course WHERE id = ANY(s.courses)) ce_name
FROM student s;

        那么,在进行关联查询的时候,student表就直接和视图course_info进行关联,查询语句如下:

SELECT s.id sid, s.name sname, s.courses, ci.cname,ci.ce_name
FROM student s
LEFT JOIN course_info ci ON s.courses = ci.courses
ORDER BY s.id;

        我们来看看使用视图作为关联和不使用的情况的执行效率对比:

  • student表与course表关联:
EXPLAIN ANALYZE SELECT s.id sid, s.name sname, s.courses, 
    array(SELECT name FROM course WHERE id = ANY(s.courses)) cname,
    array(SELECT e_name FROM course WHERE id = ANY(s.courses)) ce_name
FROM student s
ORDER BY s.id;

执行计划运行结果如下:


                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=32444.80..32446.80 rows=800 width=72) (actual time=0.114..0.115 rows=4 loops=1)
   Sort Key: s.id
   Sort Method: quicksort  Memory: 26kB
   ->  Seq Scan on student s  (cost=0.00..32406.22 rows=800 width=72) (actual time=0.051..0.101 rows=4 loops=1)
         SubPlan 1
           ->  Bitmap Heap Scan on course  (cost=9.59..20.24 rows=10 width=32) (actual time=0.010..0.011 rows=2 loops=4)
                 Recheck Cond: (id = ANY (s.courses))
                 ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.005..0.005 rows=2 loops=4)
                       Index Cond: (id = ANY (s.courses))
         SubPlan 2
           ->  Bitmap Heap Scan on course course_1  (cost=9.59..20.24 rows=10 width=32) (actual time=0.005..0.005 rows=2 loops=4)
                 Recheck Cond: (id = ANY (s.courses))
                 ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.002..0.002 rows=2 loops=4)
                       Index Cond: (id = ANY (s.courses))
 Total runtime: 0.168 ms
(15 rows)

  • student表与course_info视图关联:
EXPLAIN ANALYZE SELECT s.id sid, s.name sname, s.courses, ci.cname,ci.ce_name
FROM student s
LEFT JOIN course_info ci ON s.courses = ci.courses
ORDER BY s.id;

执行计划运行结果如下:

                                                               QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=40608.35..40610.35 rows=800 width=136) (actual time=0.076..0.076 rows=4 loops=1)
   Sort Key: s.id
   Sort Method: quicksort  Memory: 26kB
   ->  Hash Right Join  (cost=32440.22..40569.78 rows=800 width=136) (actual time=0.066..0.071 rows=4 loops=1)
         Hash Cond: (s_1.courses = s.courses)
         ->  HashAggregate  (cost=32412.22..40511.28 rows=200 width=32) (actual time=0.047..0.047 rows=4 loops=1)
               ->  Seq Scan on student s_1  (cost=0.00..32406.22 rows=800 width=32) (actual time=0.025..0.043 rows=4 loops=1)
                     SubPlan 1
                       ->  Bitmap Heap Scan on course  (cost=9.59..20.24 rows=10 width=32) (actual time=0.005..0.005 rows=2 loops=4)
                             Recheck Cond: (id = ANY (s_1.courses))
                             ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.002..0.002 rows=2 loops=4)
                                   Index Cond: (id = ANY (s_1.courses))
                     SubPlan 2
                       ->  Bitmap Heap Scan on course course_1  (cost=9.59..20.24 rows=10 width=32) (actual time=0.002..0.002 rows=2 loops=4)
                             Recheck Cond: (id = ANY (s_1.courses))
                             ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.001..0.001 rows=2 loops=4)
                                   Index Cond: (id = ANY (s_1.courses))
         ->  Hash  (cost=18.00..18.00 rows=800 width=72) (actual time=0.008..0.008 rows=4 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Seq Scan on student s  (cost=0.00..18.00 rows=800 width=72) (actual time=0.003..0.004 rows=4 loops=1)
 Total runtime: 0.116 ms

        我们可以看到,student表直接与course表关联查询,执行需要0.168 ms,与course_info视图进行关联查询,执行需要0.116 ms。对于关联信息越多的情况,效率提升越明显。

版权声明:本文为博主原创文章,未经博主允许不得转载。欢迎访问:http://blog.csdn.net/cqdz_dj

相关文章推荐

postgresql的查询函数(数组)

示例表: CREATE TABLE students ( sno character varying(32) NOT NULL, --学号 sname character varying(64...
  • djskl
  • djskl
  • 2015年03月16日 22:02
  • 1244

PostgreSQL数组使用

开发的语言有数组的概念,对应于postgresql也有相关的数据字段类型,数组是英文array的翻译,可以定义一维,二维甚至更多维度,数学上跟矩阵很类似。在postgres里面可以直接存储使用,某些场...

postgreSQL ARRAY 类型的使用

postgreSQL 的数组的使用

PostgreSQL中的数据类型

PostgreSQL中的数据类型PostgreSQL中的数据类型 概述 数据的输入和类型转换 数据类型详解 整型 布尔类型 字符类型 时间类型 枚举类型 money类型 bytea类型 其它类型 参考...
  • jpzhu16
  • jpzhu16
  • 2016年08月07日 00:10
  • 3247

postgresql数据库表格名、字段名有特殊字符

表格名称中包含特殊字符、字段名称中如果有特殊字符,在创建表格的时候加入双引号包裹字段名称即可成功创建 例如: CREATE TABLE "Table1-Test" ( "Id-/P" int, La...
  • lifuzl1
  • lifuzl1
  • 2017年06月16日 19:36
  • 608

PostgreSQL字段类型说明

PostgreSQL字段类型说明已有 817 次阅读  2009-02-25 22:49   标签:  PostgreSQL  字段  类型  BIGSERIALSERIAL8 存储自动递增的惟一整数...

PostgreSQL 对数组的遍历

PostgreSQL 对数组的遍历。

PostgreSQL中的数组与Any

建立表: CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text...

给定A, B两个整数,不使用除法和取模运算,求A/B的商和余数

给定A, B两个整数,不使用除法和取模运算,求A/B的商和余数。 1.   最基本的算法是,从小到大遍历: for (i = 2 to A -1)          if (i * B > A)...

利用K-means聚类算法根据经纬度坐标对中国省市进行聚类

K-means聚类算法是一种非层次聚类算法,在最小误差的基础上将数据划分了特定的类,类间利用距离作为相似度指标,两个向量之间的距离越小,其相似度就越高。程序读取全国省市经纬度坐标,然后根据经纬度坐标进...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:PostgreSQL总结(2)特殊数据类型查询 - array
举报原因:
原因补充:

(最多只允许输入30个字)