定义查询

Greenplum数据库基于PostgreSQL的SQL标准实现。

这个主题描述如何在Greenplum数据库中构造SQL查询。

上级主题： 查询数据

SQL词汇

SQL是一种访问数据库的标准语言。该语言由数据存储、检索、分析、查看、操纵等元素组成。使用者可以使用SQL命令来构造Greenplum数据库引擎能够理解的查询和命令。命令由以正确语法顺序排列的合法符号序列构成，最后以分号（;）终结。

更多有关SQL命令的信息请见Greenplum数据库参考指南。

Greenplum数据库使用PostgreSQL的结构和语法，但有一些不同。更多有关PostgreSQL中的SQL规则和概念的信息，请见PostgreSQL文档中的“SQL语法”。

SQL值表达式

SQL值表达式由一个或者多个值、符号、操作符、SQL函数及数据组成。值表达式会比较数据或者执行计算并且返回一个值作为结果。计算包括逻辑的、数学的和集合操作。

下列都是值表达式：

  • 一个聚集表达式
  • 一个数组构造器
  • 一个列引用
  • 一个常量或者字面值
  • 一个相关子查询
  • 一个域选择表达式
  • 一个函数调用
  • INSERT或者UPDATE中的一个新列值
  • 一个操作符调用列引用
  • 函数定义或者预备语句中的一个位置参数引用
  • 一个行构造器
  • 一个标量子查询
  • WHERE子句中的一个搜索条件
  • SELECT命令的一个目标列表
  • 一个类型造型
  • 圆括号中的一个值表达式，可用于分组子表达式以及覆盖有限
  • 一个窗口表达式

函数和操作符等SQL结构是表达式，但它们不遵循任何一般语法规则。更多有关这些结构的信息，请见使用函数和操作符。

列引用

列引用的形式是：

  1. correlation.columnname

这里，correlation是一个FROM子句中定义的表的名字（可能有方案名限定）或者别名，或者是关键词NEW和OLD中的一个。NEW和OLD只能出现在重写规则中，但可以在任何SQL语句中使用其他关系名称。如果列名在该查询的所有表中都唯一，可以省略掉表引用的”correlation.”部分。

位置参数

位置参数是SQL语句或者函数的参数，但使用它们在参数序列中的位置来引用。例如，$1引用第一个参数，$2是第二个参数，以此类推。位置参数的值从SQL语句的外部设置或者在SQL函数被调用时提供。一些客户端库支持在SQL命令之外单独指定数据值，在这种情况下参数引用的是线外数据值。参数引用的形式是：

  1. $number

例如：

  1. CREATE FUNCTION dept(text) RETURNS dept
  2.     AS $$ SELECT * FROM dept WHERE name = $1 $$
  3.     LANGUAGE SQL;

这里，只要该函数被调用，$1就引用第一个函数参数的值。

下标

如果一个表达式得到的是一个数组类型的值，可以按照下面的方式抽取该数组值中的一个特定元素：

  1. expression[subscript]

可以按下面的方式（包括方括号）抽取多个相邻的元素，它们被称为一个数组切片：

  1. expression[lower_subscript:upper_subscript]

每一个下标都是一个表达式且得到一个整数值。

数组表达式通常必须放在圆括号内，但当被指定下标的表达式是一个列引用或者位置参数时可以省略圆括号。当原始数组是多维时可以串接多个下标。例如（包括圆括号）：

  1. mytable.arraycolumn[4]
  1. mytable.two_d_column[17][34]
  1. $1[10:42]
  1. (arrayfunction(a,b))[42]

域选择

如果一个表达式得到一个组合类型（行类型）的值，可以按照下面的方式抽取该行的一个特定域：

  1. expression.fieldname

行表达式通常必须放在圆括号中，但当要从中选择的表达式是一个表引用或者位置参数时可以省略这些圆括号。例如：

  1. mytable.mycolumn
  1. $1.somecolumn
  1. (rowfunction(a,b)).col3

被限定的列引用是域选择语法的一种特殊情况。

操作符调用

操作符调用有下列可能的语法：

  1. expression operator expression(binary infix operator)
  1. operator expression(unary prefix operator)
  1. expression operator(unary postfix operator)

其中operator是一个操作符符号、关键词AND、OR或NOT之一，还可以是下面形式的限定操作符名：

  1. OPERATOR(schema.operatorname)

可用的操作符以及它们是一元的还是二元的取决于系统或者用户定义的操作符。更多有关内建操作符的信息请见内建函数和操作符。

函数调用

函数调用的语法是一个函数名（可能由一个方案名限定），后面跟着圆括号中的参数：

  1. function ([expression [, expression ... ]])

例如，下面的函数调用计算2的平方根：

  1. sqrt(2)

内建函数的分类列表请见Greenplum数据库参考指南。用户也可以增加自定函数。

聚集表达式

聚集表达式在一个查询选择的行上应用一个聚集函数。聚集函数在一组值之上执行一次计算并且返回一个单一值，例如这一组值的总和或者平均。聚集表达式的语法是下列之一：

  • aggregate_name(expression [ , … ] ) — 在所有输入行上操作，预期的结果值非空。ALL是默认。
  • aggregate_name(ALL expression [ , … ] ) — 和第一种形式相同，因为ALL是默认。
  • aggregate_name(DISTINCT expression [ , … ] ) — 在输入行的所有可区分非空值上操作。
  • aggregate_name(*) — 在所有值为空以及非空的行上操作。通常这种形式对于count(*)聚集函数最有用。

其中aggregate_name是一个之前定义的聚集（可能有方案限定）而expression是任何不包含聚集表达式的值表达式。

例如，count(*)会得到输入行的总数，count(f1)得到输入行中f1为非空的数量，而count(distinct f1)会得到f1中可区分非空值的数量。

预定义的聚集函数可见内建函数和操作符。用户也可以增加自定义聚集函数。

Greenplum函数提供MEDIAN聚集函数，它返回PERCENTILE_CONT结果和逆分布函数的特殊聚集表达式的第50个百分位数：

  1. PERCENTILE_CONT(_percentage_) WITHIN GROUP (ORDER BY _expression_)
  1. PERCENTILE_DISC(_percentage_) WITHIN GROUP (ORDER BY _expression_)

当前只能将这两个表达式与关键词WITHIN GROUP一起使用。

聚集函数的限制

下面是聚集表达式的当前限制：

  • Greenplum数据库不支持下列关键词：ALL、DISTINCT、FILTER和OVER。详见表 4。
  • 聚集表达式只能出现在SELECT命令的结果列表或者HAVING子句中。在其他子句（例如WHERE）中不允许聚集表达式，因为那些子句逻辑上会在聚集形式的结果之前计算。这种限制适用于该聚集所属的查询级别。
  • 当聚集表达式出现在子查询中时，该聚集通常在子查询的行之上计算。如果该聚集的参数只包含外层变量，该聚集属于最近的那个外层并且在那个外层查询的行上计算。该查询表达式总的来说是其出现的子查询的外部引用，并且该聚集表达式在那个子查询的任何一次计算中都像一个常量。请参考标量子查询和表 2。
  • Greenplum数据库不支持有多个输入表达式的DISTINCT。

窗口表达式

窗口表达式允许应用开发者更容易地使用标准SQL命令构造复杂的在线分析处理（OLAP）查询。例如，通过窗口查询，用户可以在多个区间上计算移动平均或者总和、在选择的列值改变时重置聚集和排名以及用简单的术语表达合比。

窗口表达式表示将一个窗口函数应用到一个窗口帧，后者在一个特殊的OVER()子句中定义。窗口分区是一个行的集合，这些行被集合起来应用一个窗口函数。和聚集函数（为每个行分组返回一个结果值）不同，窗口函数为每一行返回一个结果值，但是该结果值是针对一个特定窗口分区中的行计算得来。如果没有指定分区，窗口函数会在完整的中间结果集上计算。

窗口表达式的语法是：

  1. window_function ( [expression [, ...]] ) OVER ( window_specification )

其中window_function是列在表 3中的函数之一，expression是任何不含窗口表达式的值表达式，而window_specification是：

  1. [window_name]
  2. [PARTITION BY expression [, ...]]
  3. [[ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, ...]
  4.     [{RANGE | ROWS} 
  5.        { UNBOUNDED PRECEDING
  6.        | expression PRECEDING
  7.        | CURRENT ROW
  8.        | BETWEEN window_frame_bound AND window_frame_bound }]]
  1.  其中*window\_frame\_bound*可以是下列之一：
  1.     UNBOUNDED PRECEDING
  2.     expression PRECEDING
  3.     CURRENT ROW
  4.     expression FOLLOWING
  5.     UNBOUNDED FOLLOWING

窗口只能出现在SELECT命令的选择列表中。例如：

  1. SELECT count(*) OVER(PARTITION BY customer_id), * FROM sales;

OVER子句使得窗口函数与其他聚集或者报表函数相区别。OVER子句定义窗口函数要应用于其上的window_specification。窗口说明具有下列特点：

  • PARTITION BY子句定义窗口函数要应用于其上的窗口分区。如果省略，整个结果集会被当做一个分区。
  • ORDER BY子句定义在窗口分区内用来排序行的表达式。窗口说明的ORDER BY子句是独立的并且与一个常规查询表达式的ORDER BY子句不同。计算排名的窗口函数会要求ORDER BY子句，因为它要确定排名值的排名程度。对于OLAP聚集，使用窗口帧（ROWS | RANGE子句）也要求ORDER BY子句。

注意： 没有一致排序的数据类型列（如time）并不适合在窗口说明的ORDER BY子句中使用。Time（不管有没有指定的时区）缺少一种一致的排序，因为加法和减法无法得到预期的效果。例如，下面的式子并不总是为真： x::time < x::time + ‘2 hour’::interval

  • ROWS/RANGE子句为聚集（非排名）窗口函数定义窗口帧。一个窗口帧在一个窗口分区内定义一个行集合。当一个窗口帧被定义时，窗口函数会在这个移动帧的内容上计算，而不是在整个窗口分区的固定内容上计算。窗口帧可以是基于行的（ROWS）或者基于值的（RANGE）。

类型造型

类型造型指定从一种数据类型到另一种数据类型的转换。Greenplum数据库接受两种等效的类型造型语法：

  1. CAST ( expression AS type )
  2. expression::type

CAST语法符合SQL，带::的语法是PostgreSQL的一种历史用法。

应用于一个已知类型的值表达式的造型是一种运行时类型造型。只有定义了一种合适的类型造型函数时，转换才会成功。这不同于带有常量的转换。应用于一个字符串常值的造型表示为一个文本常量值初始分配一种类型，因此如果该字符串常值的内容是该数据类型输入语法可接受的，该造型对于任何类型都能成功。

如果值表达式必须产生的类型不会有歧义，通常可以省略显式类型造型。例如，当它被赋值给一个表列时，系统自动会应用一个类型造型。系统只会自动应用在系统目录中标记为”OK to apply implicitly”的造型。其他造型必须用显式造型语法来调用以防止在用户不知情的情况下进行预期之外的转换。

标量子查询

标量子查询是一个圆括号中的SELECT查询，它返回正好一行一列。不要将返回多行或者多列的SELECT查询用作一个标量子查询。该查询会运行并且把返回的值用在外围的值表达式中。相关标量子查询包含对于外层查询块的引用。

相关子查询

相关子查询（CSQ）是一个SELECT查询，其WHERE子句或者目标列表包含对于外层子句的引用。CSQ能有效地根据另一个查询的结果来表达结果。Greenplum数据库支持相关子查询，为很多现有的应用提供了兼容性。CSQ可以是一个标量或者表子查询，这取决于它返回一行还是多行。Greenplum数据库不支持跨级关联的相关子查询。

相关子查询例子

例 1 – 标量相关子查询

  1. SELECT * FROM t1 WHERE t1.x 
  2.             > (SELECT MAX(t2.x) FROM t2 WHERE t2.y = t1.y);

例 2 – 相关EXISTS子查询

  1. SELECT * FROM t1 WHERE 
  2. EXISTS (SELECT 1 FROM t2 WHERE t2.x = t1.x);

Greenplum数据库使用下列方法之一运行CSQ：

  • 解除CSQ的嵌套变成连接操作 – 这种方法最高效，并且Greenplum数据库会以这种方法运行大多数CSQ，包括来自TPC-H基准的查询。
  • 在外层查询的每一行上运行CSQ – 这种方法相对低效，Greenplum数据库会用这种方法来运行含有在SELECT列表中或者由OR条件连接的CSQ的查询。

下面的例子展示了如何重写这些类型的查询来改进性能。

例 3 - 选择列表中的CSQ

原始查询

  1. SELECT T1.a,
  2.       (SELECT COUNT(DISTINCT T2.z) FROM t2 WHERE t1.x = t2.y) dt2 
  3. FROM t1;

重写这一查询，让它先执行与t1的内连接，然后再执行与t1的左连接。这种重写只适用于相关条件中的等值连接。

重写的查询

  1. SELECT t1.a, dt2 FROM t1 
  2.        LEFT JOIN 
  3.         (SELECT t2.y AS csq_y, COUNT(DISTINCT t2.z) AS dt2 
  4.               FROM t1, t2 WHERE t1.x = t2.y 
  5.               GROUP BY t1.x) 
  6.        ON (t1.x = csq_y);

例 4 - 由OR子句连接的CSQ

原始查询

  1. SELECT * FROM t1 
  2. WHERE 
  3. x > (SELECT COUNT(*) FROM t2 WHERE t1.x = t2.x) 
  4. OR x < (SELECT COUNT(*) FROM t3 WHERE t1.y = t3.y)

重写这一查询，把它根据OR条件分解成两个部分，然后联合起来。

重写的查询

  1. SELECT * FROM t1 
  2. WHERE x > (SELECT count(*) FROM t2 WHERE t1.x = t2.x) 
  3. UNION 
  4. SELECT * FROM t1 
  5. WHERE x < (SELECT count(*) FROM t3 WHERE t1.y = t3.y)

要查看查询计划，使用EXPLAIN SELECT或者EXPLAIN ANALYZE SELECT。查询计划中的子计划节点表示该查询将在外层查询的每一行上运行，并且该查询是重写的候选。更多有关这些语句的信息，请见查询画像.

高级表函数

Greenplum数据库支持带有TABLE值表达式的表函数。可以用ORDER BY子句为高级表函数排序输入行。可以用SCATTER BY子句重新分布输入行，在SCATTER BY子句中要指定一个或多个列或者一个表达式，这样使得同一个进程可以处理具有特定特点的行。这种用法类似于在创建表时使用DISTRIBUTED BY子句，但是重新分布在查询时才运行。

注意： 基于数据的分布，Greenplum数据库会自动在集群的结点上并行化带有TABLE值参数的表函数。

数组构造器

数组构造器是一个从其成员元素的值构造一个数组值的表达式。简单的数组构造器由关键词ARRAY、一个左方括号[、一个或多个由逗号分隔的表达式（用于数组元素值）以及一个右方括号]组成。例如，

  1. SELECT ARRAY[1,2,3+4];
  2.   array
  3. ---------
  4.  {1,2,7}

数组元素类型是其成员表达式的共同类型，采用和UNION或者CASE结构相同的规则确定。

可以通过嵌套数组构造器构建多维数组值。在内层构造器中，可以省略关键词ARRAY。例如，下面的两个SELECT语句产生相同的结果:

  1. SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
  2. SELECT ARRAY[[1,2],[3,4]];
  3.      array
  4. ---------------
  5.  {{1,2},{3,4}}

由于多维数组必须是矩形，同一层次上的内层构造器必须产生同维的子数组。

多维数组构造器元素不限于一个子ARRAY结构，它们可以是任何产生正确种类数组的东西。例如：

  1. CREATE TABLE arr(f1 int[], f2 int[]);
  2. INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], 
  3. ARRAY[[5,6],[7,8]]);
  4. SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
  5.                      array
  6. ------------------------------------------------
  7.  {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}

可以从子查询的结果构造数组。数组构造器写成关键词ARRAY后面跟上圆括号中的一个子查询。例如：

  1. SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
  2.                           ?column?
  3. -----------------------------------------------------------
  4.  {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}

子查询必须返回单列。作为结果的一维数组中每一个元素对应于子查询结果中的每一行，元素类型匹配子查询的输出列。用ARRAY构建的数组值的下标总是从1开始。

行构造器

行构造器是一个从其成员域的值构建一个行值（也被称为组合值）的表达式。例如，

  1. SELECT ROW(1,2.5,'this is a test');

行构造器的语法是rowvalue.*，当在SELECT列表的顶层使用语法.*时，它会扩展成该行值的元素的列表。例如，如果表t有列f1和f2，下列查询是相同的：

  1. SELECT ROW(t.*, 42) FROM t;
  2. SELECT ROW(t.f1, t.f2, 42) FROM t;

默认情况下，ROW表达式创建的值是一种匿名记录类型。如果有必要，它可以被造型成一种命名的组合类型 — 一个表的行类型或者用CREATE TYPE AS创建的一种组合类型。为了避免歧义，必要时可以显式地对该值造型。例如：

  1. CREATE TABLE mytable(f1 int, f2 float, f3 text);
  2. CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' 
  3. LANGUAGE SQL;

在下面的查询中，不需要对值造型，因为只有一种getf1()函数，所以没有歧义：

  1. SELECT getf1(ROW(1,2.5,'this is a test'));
  2.  getf1
  3. -------
  4.      1
  5. CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
  6. CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT 
  7. $1.f1' LANGUAGE SQL;

现在我们需要一次造型来指示要调用哪个函数：

  1. SELECT getf1(ROW(1,2.5,'this is a test'));
  2. ERROR:  function getf1(record) is not unique
  1. SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
  2.  getf1
  3. -------
  4.      1
  5. SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS 
  6. myrowtype));
  7.  getf1
  8. -------
  9.     11

可以使用行构造器来构建组合值，该值可以被存储在组合类型表列或者被传递给接受组合类型参数的函数。

表达式计算规则

子表达式的计算顺序未被定义。一个操作符或者函数的输入不必从左向右或者按照任何其他固定顺序计算。

如果通过仅计算表达式的一部分就能确定该表达式的结果，那么其他子表达式可能完全不被计算。例如，在下面的表达式中：

  1. SELECT true OR somefunc();

somefunc()可能根本不会被调用。在下面的额表达式中也是如此：

  1. SELECT somefunc() OR true;

这和某些编程语言中的布尔操作符实施的从左向右的计算顺序不同。

不要把有副作用的函数用在复杂表达式中，尤其是在WHERE和HAVING子句中，因为在形成执行计划时会广泛地预处理那些子句。那些子句中的布尔表达式（AND/OR/NOT组合）可能会被以布尔代数法允许的任何方式重新组织。

要强制计算顺序，可使用CASE结构。下面的例子是一种在WHERE子句中避免除零的不可靠的方法：

  1. SELECT ... WHERE x <> 0 AND y/x > 1.5;

下面的例子展示了一种可靠的计算顺序：

  1. SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false 
  2. END;

这种CASE结构的用法会使得优化器无法进行优化尝试，因此只有在必要时才使用它。