PostgreSQL SQL语法（二）：值表达式
|
原文地址
本文档为PostgreSQL 9.6.0文档，本转载已得到原译者彭煜玮授权。 值表达式被用于各种各样的环境中，例如在SELECT命令的目标列表中、作为INSERT或UPDATE中的新列值或者若干命令中的搜索条件。为了区别于一个表表达式（是一个表）的结果，一个值表达式的结果有时候被称为一个标量。值表达式因此也被称为标量表达式（或者甚至简称为表达式）。表达式语法允许使用算数、逻辑、集合和其他操作从原始部分计算值。 一个值表达式是下列之一：
在这个列表之外，还有一些结构可以被分类为一个表达式，但是它们不遵循任何一般语法规则。这些通常具有一个函数或操作符的语义并且在Chapter 9中的合适位置解释。一个例子是IS NULL子句。 我们已经在Section 1.2中讨论过常量。下面的小节会讨论剩下的选项。 2.1. 列引用 一个列可以以下面的形式被引用： correlation.columnname
correlation是一个表（有可能以一个模式名限定）的名字，或者是在FROM子句中为一个表定义的别名。如果列名在当前索引所使用的表中都是唯一的，关联名称和分隔用的句点可以被忽略（另见Chapter 7）。 2.2. 位置参数 一个位置参数引用被用来指示一个由 SQL 语句外部提供的值。参数被用于 SQL 函数定义和预备查询中。某些客户端库还支持独立于 SQL 命令字符串来指定数据值，在这种情况中参数被用来引用那些线外数据值。一个参数引用的形式是： $number
例如，考虑一个函数dept的定义： CREATE FUNCTION dept(text) RETURNS dept
AS $$ SELECT * FROM dept WHERE name = $1 $$
LANGUAGE SQL;
这里$1引用函数被调用时第一个函数参数的值。 2.3. 下标 如果一个表达式得到了一个数组类型的值，那么可以抽取出该数组值的一个特定元素： expression[subscript]
或者抽取出多个相邻元素（一个"数组切片"）： [lower_subscript:upper_subscript] （这里，方括号[ ]表示其字面意思）。每一个下标自身是一个表达式，它必须得到一个整数值。 通常，数组表达式必须被加上括号，但是当要被加下标的表达式只是一个列引用或位置参数时，括号可以被忽略。还有，当原始数组是多维时，多个下标可以被连接起来。例如： mytable.arraycolumn[4]
mytable.two_d_column[17][34]
$1[10:42]
(arrayfunction(a,b))[42]
最后一个例子中的圆括号是必需的。详见Section 8.15。 2.4. 域选择 如果一个表达式得到一个组合类型（行类型）的值，那么可以抽取该行的指定域 expression.fieldname
通常行表达式必须被加上括号，但是当该表达式是仅从一个表引用或位置参数选择时，圆括号可以被忽略。例如： mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3
（因此，一个被限定的列引用实际上只是域选择语法的一种特例）。一种重要的特例是从一个组合类型的表列中抽取一个域： (compositecol).somefield
(mytable.compositecol).somefield
这里需要圆括号来显示compositecol是一个列名而不是一个表名，在第二种情况中则是显示mytable是一个表名而不是一个模式名。 在一个选择列表（见Section 7.3）中，你可以通过书写.*来请求一个组合值的所有域： (compositecol).*
2.5. 操作符调用 对于一次操作符调用，有三种可能的语法： expression operator expression（二元中缀操作符）
operator expression（一元前缀操作符）
expression operator（一元后缀操作符）
其中operator记号遵循Section 1.3的语法规则，或者是关键词AND、OR和NOT之一，或者是一个如下形式的受限定操作符名： OPERATOR(schema.operatorname)
哪个特定操作符存在以及它们是一元的还是二元的取决于由系统或用户定义的那些操作符。 2.6. 函数调用 一个函数调用的语法是一个函数的名称（可能受限于一个模式名）后面跟上封闭于圆括号中的参数列表： function_name ([expression [,expression ... ]] )
例如，下面会计算 2 的平方根： sqrt(2)
内建函数的列表在Chapter 9中。其他函数可以由用户增加。 参数可以有选择地被附加名称。 Note: 2.7. 聚集表达式 一个聚集表达式表示在由一个查询选择的行上应用一个聚集函数。一个聚集函数将多个输入减少到一个单一输出值，例如对输入的求和或平均。一个聚集表达式的语法是下列之一： aggregate_name (expression [,... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [,... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]
这里aggregate_name是一个之前定义的聚集（可能带有一个模式名限定），并且expression是任意自身不包含聚集表达式的值表达式或一个窗口函数调用。可选的order_by_clause和filter_clause描述如下。 第一种形式的聚集表达式为每一个输入行调用一次聚集。第二种形式和第一种相同，因为ALL是默认选项。第三种形式为输入行中表达式的每一个可区分值（或者对于多个表达式是值的可区分集合）调用一次聚集。第四种形式为每一个输入行调用一次聚集，因为没有特定的输入值被指定，它通常只对于count(*)聚集函数有用。最后一种形式被用于有序集聚集函数，其描述如下。 大部分聚集函数忽略空输入，这样其中一个或多个表达式得到空值的行将被丢弃。除非另有说明，对于所有内建聚集都是这样。 例如，count(*)得到输入行的总数。count(f1)得到输入行中f1为非空的数量，因为count忽略空值。而count(distinct f1)得到f1的非空可区分值的数量。 一般地，交给聚集函数的输入行是未排序的。在很多情况中这没有关系，例如不管接收到什么样的输入，min总是产生相同的结果。但是，某些聚集函数（例如array_agg 和string_agg）依据输入行的排序产生结果。当使用这类聚集时，可选的order_by_clause可以被用来指定想要的顺序。order_by_clause与查询级别的ORDER BY子句（如Section 7.5所述）具有相同的语法，除非它的表达式总是仅有表达式并且不能是输出列名称或编号。例如： SELECT array_agg(a ORDER BY b DESC) FROM table;
在处理多参数聚集函数时，注意ORDER BY出现在所有聚集参数之后。例如，要这样写： SELECT string_agg(a,',' BY a) 而不能这样写：
后者在语法上是合法的，但是它表示用两个ORDER BY键来调用一个单一参数聚集函数（第二个是无用的，因为它是一个常量）。 如果在order_by_clause之外指定了DISTINCT，那么所有的ORDER BY表达式必须匹配聚集的常规参数。也就是说，你不能在DISTINCT列表没有包括的表达式上排序。 Note: 按照到目前为止的描述，如果一个"normal"聚集中 排序是可选的，在要为它排序输入行时可以在该聚集的常规参数 列表中放置ORDER BY。有一个聚集函数的子集叫 做有序集聚集，它要求一个 order_by_clause，通常是因为 该聚集的计算只对其输入行的特定顺序有意义。有序集聚集的典 型例子包括排名和百分位计算。按照上文的最后一种语法，对于 一个有序集聚集， order_by_clause被写在 WITHIN GROUP (...)中。 order_by_clause中的表达式 会像普通聚集参数一样对每一个输入行计算一次，按照每个 order_by_clause的要求排序并 且交给该聚集函数作为输入参数（这和非 WITHIN GROUP order_by_clause的情况不同，在其中表达 式的结果不会被作为聚集函数的参数）。如果有在 WITHIN GROUP之前的参数表达式，会把它们称 为直接参数以便与列在 order_by_clause中的 聚集参数相区分。与普通聚集参数不同，针对 每次聚集调用只会计算一次直接参数，而不是为每一个输入行 计算一次。这意味着只有那些变量被GROUP BY 分组时，它们才能包含这些变量。这个限制同样适用于根本不在 一个聚集表达式内部的直接参数。直接参数通常被用于百分数 之类的东西，它们只有作为每次聚集计算用一次的单一值才有意 义。直接参数列表可以为空，在这种情况下，写成() 而不是(*)（实际上 PostgreSQL接受两种拼写，但是只有第一 种符合 SQL 标准）。有序集聚集的调用例子： SELECT percentile_disc(0.5) WITHIN GROUP (BY income) FROM households;
percentile_disc
-----------------
50489
这会从表households的 income列得到第 50 个百分位或者中位的值。 这里0.5是一个直接参数，对于百分位部分是一个 在不同行之间变化的值的情况它没有意义。 如果指定了FILTER，那么只有对filter_clause计算为真的输入行会被交给该聚集函数，其他行会被丢弃。例如： SELECT
count(*) AS unfiltered,count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
unfiltered | filtered
------------+----------
10 | 4
(1 row)
预定义的聚集函数在Section 9.20中描述。其他聚集函数可以由用户增加。 一个聚集表达式只能出现在SELECT命令的结果列表或是HAVING子句中。在其他子句（如WHERE）中禁止使用它，因为那些子句的计算在逻辑上是在聚集的结果被形成之前。 当一个聚集表达式出现在一个子查询中，聚集通常在该子查询的行上被计算。但是如果该聚集的参数（以及filter_clause，如果有）只包含外层变量则会产生一个异常：该聚集则属于最近的那个外层，并且会在那个查询的行上被计算。该聚集表达式从整体上则是对其所出现于的子查询的一种外层引用，并且在那个子查询的任意一次计算中都作为一个常量。只出现在结果列表或HAVING子句的限制适用于该聚集所属的查询层次。 2.8. 窗口函数调用 一个窗口函数调用表示在一个查询选择的行的某个部分上应用一个聚集类的函数。和常规聚集函数调用不同，这不会被约束为将被选择的行分组为一个单一的输出行 — 在查询输出中每一个行仍保持独立。不过，窗口函数能够根据窗口函数调用的分组声明（PARTITION BY列表）扫描属于当前行所在分组中的所有行。一个窗口函数调用的语法是下列之一： function_name ([expression [,expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name OVER ( window_definition ) * ) [ window_definition )
其中window_definition的语法是 [ existing_window_name ]
[ PARTITION BY expression [,...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [,...] ]
[ frame_clause ]
而可选的frame_clause是下列之一 { RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end
其中frame_start和frame_end可以是下面形式中的一种 UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING
这里，expression表示任何自身不含有窗口函数调用的值表达式。 window_name是对定义在查询的WINDOW子句中的一个命名窗口声明的引用。还可以使用在WINDOW子句中定义命名窗口的相同语法在圆括号内给定一个完整的window_definition，详见SELECT参考页。值得指出的是，OVER wname并不严格地等价于OVER (wname)，后者表示复制并修改窗口定义，并且在被引用窗口声明包括一个帧子句时会被拒绝。 PARTITION BY选项将查询的行分组成为分区，窗口函数会独立地处理它们。PARTITION BY工作起来类似于一个查询级别的GROUP BY子句，不过它的表达式总是只是表达式并且不能是输出列的名称或编号。如果没有PARTITION BY，该查询产生的所有行被当作一个单一分区来处理。ORDER BY选项决定被窗口函数处理的一个分区中的行的顺序。它工作起来类似于一个查询级别的ORDER BY子句，但是同样不能使用输出列的名称或编号。如果没有ORDER BY，行将被以未指定的顺序被处理。 frame_clause指定构成窗口帧的行集合，它是当前分区的一个子集，窗口函数将作用在该帧而不是整个分区。 帧可以被指定为RANGE或ROWS模式，在两种情况中它都从frame_start运行到frame_end。如果frame_end被忽略，它默认运行到CURRENT ROW。 UNBOUNDED PRECEDING的一个frame_start表示该帧开始于分区的第一行，类似地UNBOUNDED FOLLOWING的一个frame_end表示该帧结束于分区的最后一行。 在RANGE模式下， CURRENT ROW的一个frame_start表示该帧开始于当前行的第一个平级行（一个被ORDER BY认为与当前行等效的行），而CURRENT ROW的一个frame_end表示该帧结束于最后一个等效的ORDER BY平级行。在ROWS模式下，CURRENT ROW仅表示当前行。 value PRECEDING和value FOLLOWING情况当前只在ROWS模式中被允许。它们指示帧开始或结束于当前行之前或之后的指定数量的行。value必须是一个不包含任何变量、聚集函数或窗口函数的整数表达式。该值不能为空或负，但是可以为零，零表示只选择当前行。 默认的帧选项是RANGE UNBOUNDED PRECEDING，它和RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同。如果使用ORDER BY，这会把该帧设置为从分区开始一直到当前行的最后一个ORDER BY平级行的所有行。如果不使用ORDER BY，分区中所有的行都被包括在窗口帧中，因为所有行都成为了当前行的平级行。 限制是frame_start不能为UNBOUNDED FOLLOWING、frame_end不能为UNBOUNDED PRECEDING并且在上述列表中frame_end的选择不能早于frame_start的选择出现 — 例如RANGE BETWEEN CURRENT ROW AND value PRECEDING是不被允许的。 原文地址
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |