Greenplum 数据查询-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

6. 数据查询

数据查询部分的内容基本与 postgresql 类似，大部分可以参考 postgresql 语法。 https://wizardforcel.gitbooks.io/postgresql-doc/655.html

6.1 定义查询

位置参数

位置参数常用语自定义函数的编写，$ 代表传入参数，例：

create function student_detail(text) RETURNS students_info
as $$
select * from students_info where name = $1
$$
language sql;

下标表达式

postgresql 支持数组方式的字段，例：

创建带数组的表

create table employees_info (
  name text,
  salary_by_mon integer[],
  schedule text[][]
);

插入一条记录

insert into employees_info values(
  'tom',
  '{1200, 1100, 1250, 1100, 1100, 1120, 1200, 1100, 1250, 1100, 1100, 1120}',
  '{{"bq_sys","coding"}, {"n1_sys","testing"}}'
);

使用下标表达式进行查询

select salary_by_mon[1] from employees_info;

select salary_by_mon[1:5] from employees_info;

替换

-- 全替换
update employees_info set salary_by_mon = '{1100, 1200, 1150, 1200, 1200}' where name = 'tom';
update employees_info set salary_by_mon = array[1100, 1200, 1150, 1200, 1200] where name = 'tom';

-- 更新数组中的单个值
update employees_info set salary_by_mon[1] = 1500 where name = 'tom';

-- 更新片段
update employees_info set salary_by_mon[1:2] = '{2700, 2400}' where name = 'tom';

类型转换

类型转换指的是从一种数据类型转换为另外一种。 GPDB(与PostgreSQL相同)使用两种等价的方式做类型转换：

CAST ( expression AS type )
expression::type

CAST语法与SQL标准一致，而::语法源于PostgreSQL的历史用法。

6.2 函数和运算符

GPDB 函数有三种类型：不变型(IMMUTABLE)、稳定型(STABLE)、不稳定型(VOLATILE)。 GPDB完全支持所有类型的不变型函数。不变型函数仅仅依赖直接传递的参数列表，而且在给定参数值的情况下总是得到相同的返回值。GPDB支持大部分的稳定性函数。稳定型表明，在单个表上的扫描，使用相同的参数值函数将始终返回相同的结果，但该结果可能会因其他的SQL语句而发生改变。返回值依赖于数据库查询或者参数值的函数归类为稳定型。值得注意的是， current_timestamp 一类的函数可作为稳定型，因为在一个事务中它们的值是不变的。

在GPDB中使用不稳定型函数是受限的。不稳定型表明，即便是单表的扫描，函数值也可能发生变化。相对来说，很少有数据库函数属于这种类型，一些例子如random()、 currval()、 timeofday()等。不过需要提醒的是，所有有副作用的函数都必须是不稳定型，即便其返回值是可预测的(比如setval())。

在GPDB中，数据分散存储在各Segment Instance上 – 每个Segment Instance是一个独立的PostgreSQL数据库。为了防止节点之间的数据出现不一致，任何含有SQL语句或者修改数据库的不稳定函数都不可以在Segment Instance级别执行。比如，函数random()或者timeofday()不允许在GPDB的分布式数据上执行，因为其可能会导致Segment Instance之间的数据不一致。为了确保数据的一致性，不稳定型和稳定型函数可以安全的在语句中使用，因为该语句是在Master上被评估和执行。例如，下面的语句总是在Master上被执行(没有FROM子句的语句)：

SELECT setval('myseq', 201);
SELECT foo();

有时候，含有FROM子句的语句中含有分布式表，并且函数在FROM子句中被简单的用于返回记录集，这种情况可能是允许在Segment上执行的：

SELECT * from foo();

这种规则的一个例外是，函数返回一个表的引用，或者使用了refCursor数据类型。这些类型的函数不能被使用在GPDB中。

6.3 自定义函数

GPDB像PostgreSQL一样支持自定义函数的使用。更过信息可以参看PostgreSQL文档的”扩展SQL”章节。可以使用CREATE FUNCTION命令注册自定义函数，就像”在GPDB中使用函数”章节描述的那样。缺省状态下，函数被声明为非稳定型，因此，如果自定义函数是不变型或者稳定型的，在注册函数时指定其稳定性是很重要的。

在创建自定义函数时，应避免使用致命错误或任何类型的破坏性调用。 GPDB对于此类错误的响应可能会是突然关闭或重启。注意，在GPDB中，自定义函数的共享库文件在每个GPDB主机(Master、 Segment以及Mirror)上的库路径必须相同。

取得随机数字（开始数字，结束数字），自定义函数举例：

CREATE OR REPLACE FUNCTION get_random_number(integer, integer) RETURNS integer AS  
$BODY$  
DECLARE  
    start_int ALIAS FOR $1;  
    end_int ALIAS FOR $2;  
BEGIN  
    RETURN trunc(random() * (end_int-start_int + 1) + start_int);  
END;  
$BODY$  
LANGUAGE plpgsql;

6.4 内置函数和运算符

窗口函数

参考：http://pgsqlcn.com/tutorial/advanced-features.html#window-function

窗口函数可以对表格中，与当前行有某种关联的一组行进行计算。窗口函数和聚合函数执行的计算是类似的。窗口函数和聚合函数的不同之处在于， 使用窗口函数并不会导致多个行被聚合为单个输出行，被计算的各个行将继续保留它们各自的特性。此外，窗口函数在进行计算的时候，还可以访问查询结果里面除当前行之外的其他行。

以下这个例子展示了怎样将每个雇员的薪水与他/她所在部门的平均薪水进行对比：

SELECT depname, empno, salary,
    avg(salary) OVER (PARTITION BY depname)
FROM empsalary;
  depname  | empno | salary |          avg
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

输出中的前三个列都来自于 empsalary 表格，而最后一个列则展示了窗口函数为每个行计算出的结果：窗口函数根据当前行 depname 列的值，计算出了 empsalary 表格中所有具有相同 depname 值的行的平均值。（这个窗口函数实际上和普通的平均聚合函数是同一个函数，只是 OVER 语句使得这个聚合函数被当做成了一个窗口函数，并使用它去对一组特定的行进行计算。）

一个窗口函数调用总是包含一个 OVER 语句，这个语句紧跟在窗口函数的名字和参数之后，而这种语法也将窗口函数和普通函数以及聚合函数区别了开来。 OVER 语句决定了窗口函数在对被查询的行进行处理之前，是如何对那些行进行拆分的。至于 OVER 语句中的 PARTITION BY 表达式，则决定了窗口函数是如何对那些拥有相同 PARTITION BY 值的行进行分组或者分区的。对于表格中的每个行，窗口函数都会对与这个行处于相同分区的各个行进行计算。

此外，通过在 OVER 语句内部使用 ORDER BY 语句，用户可以决定窗口函数在处理各个行时所使用的顺序，就像这样：（OVER 语句内部的 ORDER BY 语句的值可以跟输出行时使用的 ORDER BY 语句的值不同）。

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
  depname  | empno | salary | rank
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2

正如这里所示， rank 函数将根据 ORDER BY 语句定义的顺序，为具有相同 PARTITION BY 值的各个分区中的每个行计算出这些行在自己所处分区中的排名（rank）。 rank 窗口函数不需要给定参数，因为它的行为完全由 OVER 语句决定。

查询的 FROM 语句，用于过滤的 WHERE 语句，以及 GROUP BY 语句和可能有的 HAVING 语句，通过这些语句，用户可以构建出一个“虚拟表”，而窗口函数则可以用于处理这些虚拟表中的行。举个例子，如果一个行因为不符合 WHERE 语句的条件而被移除了，那么窗口函数将不会对这个行进行处理。一个查询语句可以包含多个窗口函数，这些窗口函数可以通过不同的 OVER 语句，以不同的方式对数据进行切片（slice up），但所有窗口函数处理的都是由相同的虚拟表所定义的一组行。

前面曾经说过，如果行的排列顺序并不重要，那么我们可以省略 ORDER BY 语句。与此类似，如果窗口函数要处理的是一个包含了所有行的分区，那么我们也可以省略 PARTITION BY 语句。

窗口函数还有一个重要的相关概念：对于每个行来说，它所在的分区都有一组行可以被称为这个行的窗口框（window frame）。很多（但不是所有）窗口函数都只会对窗口框中的行进行计算，而不是对整个分区的所有行进行计算。

在默认情况下，如果用户使用了 ORDER BY ，那么窗口框将由分区开头直到当前行为止的所有行，以及那些在当前行之后，但是与当前行的 ORDER BY 结果相等的行组成。与此相反，如果用户没有给定 ORDER BY ，那么默认的窗口框将由分区中的所有行组成。以下展示了一个使用 sum 窗口函数的例子：

SELECT salary, sum(salary) OVER () FROM empsalary;
 salary |  sum
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

上面这个查询因为没有在 OVER 语句里面加上 ORDER BY 语句，所以这个查询要处理的窗口框和分区一样，并且因为这个查询没有用到 PARTITION BY 语句，所以这个分区将包含整个表格的所有行：换句话说，每个总和（sum）都是通过对整个表格进行计算得出的，所以对于表格中的每个行，查询都给出了相同的结果。

不过如果我们给这个查询加上 ORDER BY 语句的话，那么将得到一个非常不同的结果：

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 salary |  sum
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

这次的总和是通过将第一行至到当前行的所有薪水值都相加起来，然后再加上所有与当前行具有相同薪水值的行计算得出的（注意结果中那些具有相同薪水值的行的计算结果）。

窗口函数只能在 SELECT 列表或者查询的 ORDER BY 语句中使用，它不能用在 GROUP BY 、 HAVING 和 WHERE 等语句中，这是因为窗口函数在逻辑上需要等到 GROUP BY 这些语句执行完了之后再执行。此外，窗口函数也会在普通的聚合函数执行完毕之后执行，这意味着我们可以将聚合函数用作窗口函数的参数，但反过来则不可以。

用户可以在执行窗口函数之后，使用子查询对计算所得的行进行过滤或者分组：

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

这个查询只会展示内查询里面， rank 值小于 3 的行。

当查询需要用到多个窗口函数时，用户可以用多个 OVER 语句将这些窗口函数一个接一个地写出来，但是这种做法在多个函数需要进行相同的窗口计算（windowing behavior）时，非常容易出错，并且还会产生重复的计算。为了解决这个问题，我们可以在 WINDOW 语句里面对需要进行的窗口计算进行命名，然后在 OVER 语句里面引用这些窗口计算，就像这样：

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

高级分析函数

GPDB 提供了如下这些在PostgreSQL中没有的内置高级分析函数。这些分析函数都是不变型函数。这一部分函数主要用于，矩阵计算，线性回归等。函数较多就不再列出。

https://gpdb.docs.pivotal.io/43120/admin_guide/query/topics/functions-operators.html#topic31

6.5 查询性能

GPDB支持动态分区消除和查询内存优化。GPDB在查询优化时为不同的操作动态的消除不相关分区和分配内存。该强化大大减少了查询对数据的扫描，显著加速查询处理，从而容许更大的并发。

动态分区消除

在GPDB中，运行时的值只能用于在内部动态的减少分区。这样就提高了查询处理的速度。

使用这个动态分区消除特性需要设置Server参数gp_dynamic_partition_pruning。缺省设置为on。将该参数设置为off来关闭动态分区消除。

在多分区表做关联时该参数可能会导致完全不可预测的查询计划出现，需慎用。

内存优化

GPDB对于不同用户的查询、急切释放、查询处理的不同阶段重新分配内存进行内存分配的优化。

这些特征允许更多耗内存的查询，更快的查询和更多的并发。

6.6 查询分析

GPDB会为每个查询设计出一个查询计划。评判一个好的性能的绝对关键是为匹配的查询和数据结构选择正确的查询计划。查询计划决定了该查询将在GPDB的并行执行环境中如何被执行。通过检查糟糕性能查询的查询计划，你可能会找到有效的调优方案。

查询规划器使用数据库的统计信息来选择一个尽可能最低成本的查询计划。成本是对I/O和CPU消耗的衡量(获取磁盘页的数量)。优化的目标是最小化查询计划的执行成本。

可以使用EXPLAIN来查看查询计划。其展现了对该查询的查询计划的评估。例如：

explain select * from names where id=22;

EXPLAIN ANALYZE会真正的执行语句，而不仅仅是计划。这对于查看规划器评估的是否接近实际情况比较有用。例如：

EXPLAIN ANALYZE SELECT * FROM names WHERE id=22;

查看 EXPLAIN 输出

EXPLAIN的输出中每个几点都有一行，其显示基本的节点类型和规划器为该操作作出的成本评估：

cost

以获取的磁盘页数计算，就是说， 1.0等于一个连续的磁盘页读取。第一个评估是开始成本(得到第一条记录时的成本)而第二个评估是总成本(得到全部记录时的成本)。需要注意的是，总成本是假设所有的记录被获取，当然可能并不会获取全部记录(比如使用了LIMIT)。

rows

该计划几点输出的记录数。通常该值小于真实处理或者扫描的数量，其会反映WHERE子句的条件对记录的过滤。顶级节点评估的数量理想状态下与真实返回的、更新的或者删除的数据量接近。

width

所有通过该计划输出的总字节数。译者不太明白这个有什么用。需要重点注意的是，一个上层节点的cost包含其所有子节点的cost。最顶层节点的cost包含了整个计划执行的总cost。这就是规划器要试图减小的数字。同样重要的是，要意识到的一点是， cost仅仅反映了查询规划器在意的东西。尤其特殊的是， cost总不包含结果集传输到客户端的时间。

EXPLAIN示例:

要说明如何阅读EXPLAIN得到的查询计划，参考一下下面这个简单的例子：

EXPLAIN SELECT * FROM names WHERE name = 'Joelle';
QUERY PLAN
--
Gather Motion 2:1 (slice1) (cost=0.00..20.88 rows=1 width=13)
-> Seq Scan on 'names' (cost=0.00..20.88 rows=1 width=13)
Filter: name::text ~~ 'Joelle'::text

从下往上阅读这个计划，执行规划器从顺序扫描names表开始。注意， WHERE子句被用作一个filter条件。这意味着，扫描操作将根据条件检查扫描的每一行，并只输出符合条件的记录。扫描操作的结果配上传到一个汇总移动操作。在GPDB中，汇总移动就是Segment Instance向Master发送记录。该场景下，有2个Segment Instance向1个Master发送(2:1)。这个操作工作在并行查询计划的步骤1(slice1)上。在GPDB中，一个查询计划被分为多个步骤，因此查询计划可以在Segment Instance之间并行的工作。评估的开始成本为00.00(无cost)且总成本为20.88个磁盘页面获取。规划器评估这个查询将返回一行记录。