这是本节的多页打印视图。
点击此处打印.
返回本页常规视图.
分析函数
注意
在本节中具有聚合功能的所有分析函数与 [分析] 一起附加在标题中，以避免两个函数类型之间发生混淆。
Vertica 分析是基于 ANSI 99 标准的 SQL 函数。这些函数用于处理复杂的分析和报告任务，例如：
分析函数将返回聚合结果，但并不对结果集进行分组。它们会多次返回组值，每个记录一个组值。可以使用 window ORDER BY 子句排序组值或分区，但此顺序仅影响函数结果集，而不影响整个查询结果集。
语法
一般
analytic‑function(arguments) OVER(
[ window-partition-clause ]
[ window-order-clause [ window-frame-clause ] ]
)
对于指定窗口
analytic‑function(arguments) OVER(
[ named‑window [ window‑frame‑clause ] ]
)
参数
-
analytic‑function(arguments)
- Vertica 分析函数及其实参。
OVER- 指定如何就当前行而言对窗口框架函数输入进行分区、排序。输入数据是查询在对
FROM、WHERE、GROUP BY 和 HAVING 子句求值之后返回的结果集。
空的 OVER 子句将提供单个节点中的单线程查询最佳性能。
- window‑partition‑clause
- 根据一个或多个列或表达式对输入行进行分组。
如果忽略此子句，不会进行任何分组，分析函数将所有输入行作为一个分区处理。
- window‑order‑clause
- 可以选择指定如何对提供给分析函数的行进行排序。如果
OVER 子句还包括分区子句，则在每个分区中对行进行排序。
-
window‑frame‑clause
- 仅对某些分析函数有效，指定为输入一组与分析函数当前正在评估的行相对的行。函数处理此行及其窗口之后，Vertica 会将当前行向前移动，并相应地调整窗口边界。
- named‑window
- 您在同一个查询中用窗口名称子句定义的一个窗口的名称。此定义将封装窗口分区和排序。当查询调用多个具有类似的
OVER 子句的分析函数时，命名窗口很有用。
窗口名称子句不能指定窗口框架子句。然而，您可以用窗口框架子句将命名窗口限定在 OVER 子句中。
要求
以下要求适用于分析函数：
-
全部需要 OVER 子句。每个函数都有自己的 OVER 子句要求。例如，您可以为一些分析聚合函数（如
SUM）提供一个空的 OVER 子句。对于其他函数，可能需要窗口框架和命令子句，也可能无效。
-
只有查询的 SELECT 和 ORDER BY 子句才能调用分析函数。
-
分析函数不可嵌套。例如，不允许下面的查询：
=> SELECT MEDIAN(RANK() OVER(ORDER BY sal) OVER()).
-
WHERE、GROUP BY 和 HAVING 运算符从技术上讲不是分析函数的一部分。不过，它们可以确定该函数的输入内容。
另请参阅
1 - 窗口分区子句
当指定了窗口分区子句时，它将基于用户提供的表达式拆分函数输入的行。如果没有提供表达式，分区子句可通过使用并行度来提升查询性能。
窗口分区与 GROUP BY 子句相似，区别在于它仅为每个输入行返回一个结果行。如果没有指定窗口分区子句，则会将所有输入行都视为一个分区。
当与分析函数一起使用时，将根据分区计算结果，并在下一个分区开始时重新开始。
语法
{ PARTITION BY expression[,...] | PARTITION BEST | PARTITION NODES }
参数
-
PARTITION BY expression
- 基于此表达式对分区进行排序，其中 expression 可以是列、常数或者针对列构成的任意表达式。对于带有特定分区要求的分析函数，使用
PARTITION BY 。
PARTITION BEST- 使用并行度来提升多个节点中的多线程查询性能。
OVER(PARTITION BEST) 提供多个节点中的多线程查询最佳性能。
以下注意事项适用于使用 PARTITION BEST：
PARTITION NODES- 使用并行度来提升多个节点中的单线程查询性能。
OVER(PARTITION NODES) 提供多个节点中的单线程查询最佳性能。
示例
请参阅窗口分区。
2 - 窗口顺序子句
指定如何对提供给分析函数的行进行排序。如果 OVER 子句也包含窗口分区子句，则会在每个分区中对行进行排序。
窗口顺序子句仅在窗口结果集中指定顺序。除 OVER 子句以外，查询可以拥有自己的 ORDER BY 子句。它优先于窗口顺序子句，并对最终结果集进行排序。
如果没有显式指定任何窗口框架，窗口顺序子句还会创建默认窗口框架。
语法
ORDER BY { expression [ ASC | DESC [ NULLS { FIRST | LAST | AUTO } ] ]
}[,...]
参数
- 表达式
- 列、常数或是针对列构成的任意表达式，用于对输入列进行排序。
ASC | DESC - 将排序顺序指定为升序（默认值）或降序。
NULLS {FIRST | LAST | AUTO} - 指定是否将 null 值放在最前或最后。默认位置排放取决于排序顺序是升序还是降序：
-
升序默认： NULLS LAST
-
降序默认： NULLS FIRST
如果您指定了 NULLS AUTO，Vertica 选择对于此查询最有效的位置排放，即 NULLS FIRST 或 NULLS LAST。
如果您省略所有的排序限定符，Vertica 将使用 ASC NULLS LAST。
有关详细信息，请参阅：
示例
请参阅窗口排序。
3 - 窗口框架子句
指定窗口框架，其中包含一组与分析函数当前正在评估的行相对的行。函数处理此行及其窗口之后，Vertica 会将当前行向前移动，并相应地调整窗口边界。如果 OVER 子句还指定了分区，Vertica 也会检查窗口边界是否跨越了分区边界。此过程将不断重复，直到函数评估了最后一个分区的最后一个行。
语法
{ ROWS | RANGE } { BETWEEN start‑point AND end‑point } | start‑point
start-point / end‑point：
{ UNBOUNDED {PRECEDING | FOLLOWING}
| CURRENT ROW
| constant-value {PRECEDING | FOLLOWING}}
参数
ROWS | RANGE- 指定 Vertica 是否确定窗口框架大小为当前行的物理或逻辑偏移。有关详细信息，请参阅下方的 ROWS 与 RANGE。
-
BETWEEN start‑point AND end‑point
- 指定窗口的第一个行和最后一个行，其中 start‑point 和 end‑point 可以是以下之一（下文将详细讨论）：
start-point 必须解析为小于或等于 end-point 的行或值。
UNBOUNDED PRECEDING- 指定窗口框架扩展到当前分区的第一行。
- start‑point
- 如果
ROWS 或 RANGE 仅指定了一个起点，Vertica 将使用当前行作为终点，并相应地创建窗口框架。在这种情况下，start-point 必须解析为小于或等于当前行的行。
UNBOUNDED FOLLOWING- 指定窗口框架扩展到当前分区的最后一行。
CURRENT ROW- 指定当前行或值为窗口的起点或终点。
-
constant‑value {PRECEDING | FOLLOWING}
- 指定常数值或评估为常数值的表达式。此值指定当前行的物理便宜或逻辑偏移，具体视您是否指定了
ROWS 或 RANGE 而定。
其他依赖项也适用，具体视您是否指定了 ROWS 和 RANGE 而定。有关详细信息，请参阅下方的 ROWS 与 RANGE。
要求
要指定窗口框架，OVER 还必须指定窗口顺序 (ORDER BY) 子句。如果 OVER 子句没有指定窗口框架，则函数会创建从当前分区的当前行扩展到第一行的默认窗口。它等同于以下子句：
RANGE UNBOUNDED PRECEDING AND CURRENT ROW
ROWS 与 RANGE
当前行的窗口框架偏移可能是物理或逻辑偏移：
使用 ROWS 或 RANGE 对于将窗口起点和终点设置为常数值实施了特定要求：
为 ROWS 设置常数值
常数的计算结果必须为正 INTEGER。
为 RANGE 设置常数值
需要满足以下要求：
-
常数的计算结果必须为正数值或 INTERVAL 字面量。
-
如果常数的计算结果为 NUMERIC 值，则 ORDER BY 列类型必须为 NUMERIC 数据类型。
-
如果常数的计算结果为 INTERVAL DAY TO SECOND 子类，则 ORDER BY 列类型必须为以下之一：TIMESTAMP、TIME、DATE 或 INTERVAL DAY TO SECOND。
-
如果常数的计算结果为 INTERVAL YEAR TO MONTH，则 ORDER BY 列类型必须为以下之一：TIMESTAMP、DATE 或 INTERVAL YEAR TO MONTH。
-
窗口顺序子句只可指定一个表达式。
示例
请参阅窗口框架。
4 - 窗口名称子句
定义命名窗口，为分析函数指定窗口分区和顺序子句。此窗口在函数的 OVER 子句中指定。当您编写通过类似的 OVER 子句调用多个分析函数的查询时——例如，它们使用相同的 (PARTITION BY) 分区子句，命名窗口非常有用。
语法
WINDOW window‑name AS ( window-partition-clause [window-order-clause] )
参数
-
WINDOW window‑name
- 指定窗口名称。在同一个查询中，所有窗口名称都必须是唯一的。
-
window‑partition‑clause [window‑order‑clause]
- 当
OVER 子句引用此窗口时调用的子句。
如果窗口忽略了窗口顺序子句，则 OVER 子句可以指定自己的顺序子句。
要求
-
WINDOW 子句不能包含窗口框架子句。
-
同一个查询中的每个 WINDOW 子句都必须拥有唯一的名称。
-
WINDOW 子句可引用另一个已经命名的窗口。例如，下述查询先命名 w1，再命名 w2。因此，定义 WINDOW 的 w2 子句可引用 w1：
=> SELECT RANK() OVER(w1 ORDER BY sal DESC), RANK() OVER w2
FROM EMP WINDOW w1 AS (PARTITION BY deptno), w2 AS (w1 ORDER BY sal);
示例
请参阅命名窗口。
另请参阅
分析函数
5 - ARGMAX [analytic]
此函数是仿照数学函数
argmax(f(x)) 设计的，返回可使
f(x) 成为最大值的 x 值。同样地，ARGMAX 使用两个实参 target 和 arg，这两个实参均为查询数据集中的列或列表达式。ARGMAX 在 target 中查找具有最大非 null 值的行，并返回该行中的 arg 值。如果有多行包含最大 target 值，ARGMAX 将返回其找到的第一行中的 arg 值。
行为类型
不可变
语法
ARGMAX ( target, arg ) OVER ( [ PARTITION BY expression[,...] ] [ window-order-clause ] )
参数
- target, arg
- 查询数据集中的列。
注意
ARGMAX 不支持
空间数据类型：GEOMETRY 和 GEOGRAPHY。
OVER()- 指定以下窗口子句：
-
PARTITION BY expression：根据 expression 中的值对输入行进行分组（分区），该 expression 解析为查询数据集中的一列或多列。如果忽略此子句，ARGMAX 会将所有输入行作为单分区处理。
-
window-order-clause：指定如何对输入行进行排序。如果 OVER 子句还包括分区子句，则会单独在每个分区中对行进行排序。
重要
当有多行包含最大 target 值时，为确保结果一致，请包含一个按 arg 排序的窗口顺序子句。
有关详细信息，请参阅分析函数。
示例
创建并填充表 service_info，其中包含有关各项服务、服务各自的开发组以及服务用户群的信息。users 列中的 NULL 表示该服务尚未发布，因此不会有用户。
=> CREATE TABLE service_info(dev_group VARCHAR(10), product_name VARCHAR(30), users INT);
=> COPY t FROM stdin NULL AS 'null';
>> iris|chat|48193
>> aspen|trading|3000
>> orchid|cloud|990322
>> iris|video call| 10203
>> daffodil|streaming|44123
>> hydrangea|password manager|null
>> hydrangea|totp|1837363
>> daffodil|clip share|3000
>> hydrangea|e2e sms|null
>> rose|crypto|null
>> iris|forum|48193
>> \.
ARGMAX 返回 product_name 列中的值，该值可使 users 列中的值最大。在此示例中，ARGMAX 将返回 totp，表示 totp 服务拥有最大的用户群：
=> SELECT dev_group, product_name, users, ARGMAX(users, product_name) OVER (ORDER BY dev_group ASC) FROM service_info;
dev_group | product_name | users | ARGMAX
-----------+------------------+---------+--------
aspen | trading | 3000 | totp
daffodil | clip share | 3000 | totp
daffodil | streaming | 44123 | totp
hydrangea | e2e sms | | totp
hydrangea | password manager | | totp
hydrangea | totp | 1837363 | totp
iris | chat | 48193 | totp
iris | forum | 48193 | totp
iris | video call | 10203 | totp
orchid | cloud | 990322 | totp
rose | crypto | | totp
(11 rows)
下一个查询对 dev_group 上的数据进行分区，以确定每个开发组创建的最受欢迎的服务。如果分区的 users 列仅包含 NULL 值并使用分区顶部 product_name 中的第一个值打破关系，ARGMAX 将返回 NULL。
=> SELECT dev_group, product_name, users, ARGMAX(users, product_name) OVER (PARTITION BY dev_group ORDER BY product_name ASC) FROM service_info;
dev_group | product_name | users | ARGMAX
-----------+------------------+---------+-----------
iris | chat | 48193 | chat
iris | forum | 48193 | chat
iris | video call | 10203 | chat
orchid | cloud | 990322 | cloud
aspen | trading | 3000 | trading
daffodil | clip share | 3000 | streaming
daffodil | streaming | 44123 | streaming
rose | crypto | |
hydrangea | e2e sms | | totp
hydrangea | password manager | | totp
hydrangea | totp | 1837363 | totp
(11 rows)
另请参阅
ARGMIN [analytic]
6 - ARGMIN [analytic]
此函数是仿照数学函数
argmin(f(x)) 设计的，返回可使
f(x) 成为最小值的 x 值。同样地，ARGMIN 使用两个实参 target 和 arg，这两个实参均为查询数据集中的列或列表达式。ARGMIN 在 target 中查找具有最小非 null 值的行，并返回该行中的 arg 值。如果有多行包含最小 target 值，ARGMIN 将返回其找到的第一行中的 arg 值。
行为类型
不可变
语法
ARGMIN ( target, arg ) OVER ( [ PARTITION BY expression[,...] ] [ window-order-clause ] )
参数
- target, arg
- 查询数据集中的列。
注意
ARGMIN 不支持
空间数据类型：GEOMETRY 和 GEOGRAPHY。
OVER()- 指定以下窗口子句：
-
PARTITION BY expression：根据 expression 中的值对输入行进行分组（分区），该 expression 解析为查询数据集中的一列或多列。如果忽略此子句，ARGMIN 会将所有输入行作为单分区处理。
-
window-order-clause：指定如何对输入行进行排序。如果 OVER 子句还包括分区子句，则会单独在每个分区中对行进行排序。
重要
当有多行包含最小 target 值时，为确保结果一致，请包含一个按 arg 排序的窗口顺序子句。
有关详细信息，请参阅分析函数。
示例
创建并填充表 service_info，其中包含有关各项服务、服务各自的开发组以及服务用户群的信息。users 列中的 NULL 表示该服务尚未发布，因此不会有用户。
=> CREATE TABLE service_info(dev_group VARCHAR(10), product_name VARCHAR(30), users INT);
=> COPY t FROM stdin NULL AS 'null';
>> iris|chat|48193
>> aspen|trading|3000
>> orchid|cloud|990322
>> iris|video call| 10203
>> daffodil|streaming|44123
>> hydrangea|password manager|null
>> hydrangea|totp|1837363
>> daffodil|clip share|3000
>> hydrangea|e2e sms|null
>> rose|crypto|null
>> iris|forum|48193
>> \.
ARGMIN 返回 product_name 列中的值，该值可使 users 列中的值最小。在此示例中，ARGMIN 将返回 totp，表示 totp 服务具有最小的用户群：
=> SELECT dev_group, product_name, users, ARGMIN(users, product_name) OVER (ORDER BY dev_group ASC) FROM service_info;
dev_group | product_name | users | ARGMIN
-----------+------------------+---------+---------
aspen | trading | 3000 | trading
daffodil | clip share | 3000 | trading
daffodil | streaming | 44123 | trading
hydrangea | e2e sms | | trading
hydrangea | password manager | | trading
hydrangea | totp | 1837363 | trading
iris | chat | 48193 | trading
iris | forum | 48193 | trading
iris | video call | 10203 | trading
orchid | cloud | 990322 | trading
rose | crypto | | trading
(11 rows)
下一个查询对 dev_group 上的数据进行分区，以确定每个开发组创建的最不受欢迎的服务。如果分区的 users 列仅包含 NULL 值并使用分区顶部 product_name 中的第一个值打破关系，ARGMIN 将返回 NULL。
=> SELECT dev_group, product_name, users, ARGMIN(users, product_name) OVER (PARTITION BY dev_group ORDER BY product_name ASC) FROM service_info;
dev_group | product_name | users | ARGMIN
-----------+------------------+---------+------------
iris | chat | 48193 | video call
iris | forum | 48193 | video call
iris | video call | 10203 | video call
orchid | cloud | 990322 | cloud
aspen | trading | 3000 | trading
daffodil | clip share | 3000 | clip share
daffodil | streaming | 44123 | clip share
rose | crypto | |
hydrangea | e2e sms | | totp
hydrangea | password manager | | totp
hydrangea | totp | 1837363 | totp
(11 rows)
另请参阅
ARGMAX [analytic]
7 - AVG [analytic]
计算
窗口内一组中表达式的平均值。 AVG 返回与表达式的数字数据类型相同的数据类型。
AVG 分析函数不同于
AVG 聚合函数，后者计算一组行中表达式的平均值。
行为类型
不可变
语法
AVG ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 可以隐式转换为数字数据类型的任何数据。
OVER()- 请参阅分析函数。
溢出处理
默认情况下，当您对数值数据类型调用此函数时，Vertica 允许静默数值溢出。有关此行为以及如何更改它的更多信息，请参阅SUM、SUM_FLOAT 和 AVG 的数字数据类型溢出。
示例
以下查询查找该日历月的销售，并使用默认的 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 窗口返回一个运行/累计平均值（有时称为移动平均值）：
=> SELECT calendar_month_number_in_year Mo, SUM(product_price) Sales,
AVG(SUM(product_price)) OVER (ORDER BY calendar_month_number_in_year)::INTEGER Average
FROM product_dimension pd, date_dimension dm, inventory_fact if
WHERE dm.date_key = if.date_key AND pd.product_key = if.product_key GROUP BY Mo;
Mo | Sales | Average
----+----------+----------
1 | 23869547 | 23869547
2 | 19604661 | 21737104
3 | 22877913 | 22117374
4 | 22901263 | 22313346
5 | 23670676 | 22584812
6 | 22507600 | 22571943
7 | 21514089 | 22420821
8 | 24860684 | 22725804
9 | 21687795 | 22610470
10 | 23648921 | 22714315
11 | 21115910 | 22569005
12 | 24708317 | 22747281
(12 rows)
要返回不是运行（累计）平均值的移动平均值，该窗口应指定 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING：
=> SELECT calendar_month_number_in_year Mo, SUM(product_price) Sales,
AVG(SUM(product_price)) OVER (ORDER BY calendar_month_number_in_year
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)::INTEGER Average
FROM product_dimension pd, date_dimension dm, inventory_fact if
WHERE dm.date_key = if.date_key AND pd.product_key = if.product_key GROUP BY Mo;
Mo | Sales | Average
----+----------+----------
1 | 23869547 | 22117374
2 | 19604661 | 22313346
3 | 22877913 | 22584812
4 | 22901263 | 22312423
5 | 23670676 | 22694308
6 | 22507600 | 23090862
7 | 21514089 | 22848169
8 | 24860684 | 22843818
9 | 21687795 | 22565480
10 | 23648921 | 23204325
11 | 21115910 | 22790236
12 | 24708317 | 23157716
(12 rows)
另请参阅
8 - BOOL_AND [analytic]
返回
窗口内表达式的布尔值。如果所有输入值都为 true，则 BOOL_AND 返回 t。否则，它返回 f。
行为类型
不可变
语法
BOOL_AND ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 布尔数据类型 或任何可隐式转换为布尔数据类型的非布尔数据类型。此函数将返回一个布尔值。
OVER()- 请参阅分析函数。
示例
以下示例说明了如何使用 BOOL_AND、BOOL_OR 和 BOOL_XOR 分析函数。示例表 employee 包含一个用于表示员工类型和年薪的列。
=> CREATE TABLE employee(emptype VARCHAR, yearspaid VARCHAR);
CREATE TABLE
将样本数据插入表中以显示年薪。在多种情况下，员工可以在一年内多次获得报酬。
=> INSERT INTO employee
SELECT 'contractor1', '2014'
UNION ALL
SELECT 'contractor2', '2015'
UNION ALL
SELECT 'contractor3', '2014'
UNION ALL
SELECT 'contractor1', '2014'
UNION ALL
SELECT 'contractor2', '2014'
UNION ALL
SELECT 'contractor3', '2015'
UNION ALL
SELECT 'contractor4', '2014'
UNION ALL
SELECT 'contractor4', '2014'
UNION ALL
SELECT 'contractor5', '2015'
UNION ALL
SELECT 'contractor5', '2016';
OUTPUT
--------
10
(1 row)
查询该表。结果显示在 2014 年获得了两次报酬的员工 (BOOL_AND)，在 2014 年获得了一次或两次报酬的员工 (BOOL_OR)，以及专门显示在 2014 年获得不超过一次报酬的员工 (BOOL_XOR)。
=> SELECT DISTINCT emptype,
BOOL_AND(yearspaid='2014') OVER (PARTITION BY emptype) AS paidtwicein2014,
BOOL_OR(yearspaid='2014') OVER (PARTITION BY emptype) AS paidonceortwicein2014,
BOOL_XOR(yearspaid='2014') OVER (PARTITION BY emptype) AS paidjustoncein2014
FROM employee;
emptype | paidtwicein2014 | paidonceortwicein2014 | paidjustoncein2014
-------------+-----------------+-----------------------+--------------------
contractor1 | t | t | f
contractor2 | f | t | t
contractor3 | f | t | t
contractor4 | t | t | f
contractor5 | f | f | f
(5 rows)
另请参阅
9 - BOOL_OR [analytic]
返回
窗口内表达式的布尔值。如果至少一个输入值为 true，则 BOOL_OR 返回 t。否则，它返回 f。
行为类型
不可变
语法
BOOL_OR ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 布尔数据类型 或任何可隐式转换为布尔数据类型的非布尔数据类型。此函数将返回一个布尔值。
OVER()- 请参阅分析函数。
示例
以下示例说明了如何使用 BOOL_AND、BOOL_OR 和 BOOL_XOR 分析函数。示例表 employee 包含一个用于表示员工类型和年薪的列。
=> CREATE TABLE employee(emptype VARCHAR, yearspaid VARCHAR);
CREATE TABLE
将样本数据插入表中以显示年薪。在多种情况下，员工可以在一年内多次获得报酬。
=> INSERT INTO employee
SELECT 'contractor1', '2014'
UNION ALL
SELECT 'contractor2', '2015'
UNION ALL
SELECT 'contractor3', '2014'
UNION ALL
SELECT 'contractor1', '2014'
UNION ALL
SELECT 'contractor2', '2014'
UNION ALL
SELECT 'contractor3', '2015'
UNION ALL
SELECT 'contractor4', '2014'
UNION ALL
SELECT 'contractor4', '2014'
UNION ALL
SELECT 'contractor5', '2015'
UNION ALL
SELECT 'contractor5', '2016';
OUTPUT
--------
10
(1 row)
查询该表。结果显示在 2014 年获得了两次报酬的员工 (BOOL_AND)，在 2014 年获得了一次或两次报酬的员工 (BOOL_OR)，以及专门显示在 2014 年获得不超过一次报酬的员工 (BOOL_XOR)。
=> SELECT DISTINCT emptype,
BOOL_AND(yearspaid='2014') OVER (PARTITION BY emptype) AS paidtwicein2014,
BOOL_OR(yearspaid='2014') OVER (PARTITION BY emptype) AS paidonceortwicein2014,
BOOL_XOR(yearspaid='2014') OVER (PARTITION BY emptype) AS paidjustoncein2014
FROM employee;
emptype | paidtwicein2014 | paidonceortwicein2014 | paidjustoncein2014
-------------+-----------------+-----------------------+--------------------
contractor1 | t | t | f
contractor2 | f | t | t
contractor3 | f | t | t
contractor4 | t | t | f
contractor5 | f | f | f
(5 rows)
另请参阅
10 - BOOL_XOR [analytic]
返回
窗口内表达式的布尔值。如果只有一个输入值为真，BOOL_XOR 将返回 t。否则，它返回 f。
行为类型
不可变
语法
BOOL_XOR ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 布尔数据类型 或任何可隐式转换为布尔数据类型的非布尔数据类型。此函数将返回一个布尔值。
OVER()- 请参阅分析函数。
示例
以下示例说明了如何使用 BOOL_AND、BOOL_OR 和 BOOL_XOR 分析函数。示例表 employee 包含一个用于表示员工类型和年薪的列。
=> CREATE TABLE employee(emptype VARCHAR, yearspaid VARCHAR);
CREATE TABLE
将样本数据插入表中以显示年薪。在多种情况下，员工可以在一年内多次获得报酬。
=> INSERT INTO employee
SELECT 'contractor1', '2014'
UNION ALL
SELECT 'contractor2', '2015'
UNION ALL
SELECT 'contractor3', '2014'
UNION ALL
SELECT 'contractor1', '2014'
UNION ALL
SELECT 'contractor2', '2014'
UNION ALL
SELECT 'contractor3', '2015'
UNION ALL
SELECT 'contractor4', '2014'
UNION ALL
SELECT 'contractor4', '2014'
UNION ALL
SELECT 'contractor5', '2015'
UNION ALL
SELECT 'contractor5', '2016';
OUTPUT
--------
10
(1 row)
查询该表。结果显示在 2014 年获得了两次报酬的员工 (BOOL_AND)，在 2014 年获得了一次或两次报酬的员工 (BOOL_OR)，以及专门显示在 2014 年获得不超过一次报酬的员工 (BOOL_XOR)。
=> SELECT DISTINCT emptype,
BOOL_AND(yearspaid='2014') OVER (PARTITION BY emptype) AS paidtwicein2014,
BOOL_OR(yearspaid='2014') OVER (PARTITION BY emptype) AS paidonceortwicein2014,
BOOL_XOR(yearspaid='2014') OVER (PARTITION BY emptype) AS paidjustoncein2014
FROM employee;
emptype | paidtwicein2014 | paidonceortwicein2014 | paidjustoncein2014
-------------+-----------------+-----------------------+--------------------
contractor1 | t | t | f
contractor2 | f | t | t
contractor3 | f | t | t
contractor4 | t | t | f
contractor5 | f | f | f
(5 rows)
另请参阅
11 - CONDITIONAL_CHANGE_EVENT [analytic]
从 0 开始向每个行分配一个事件窗口编号，并在当前行中评估参数表达式的结果与上一行不同时，以 1 为增量递增事件窗口编号。
行为类型
不可变
语法
CONDITIONAL_CHANGE_EVENT ( expression ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
expression- 在输入记录中评估的 SQL 标量表达式。expression 的结果可以是任何数据类型。
OVER()- 请参阅分析函数。
注意
分析 window-order-clause 是必需的，但 window-partition-clause 是可选的。
示例
=> SELECT CONDITIONAL_CHANGE_EVENT(bid)
OVER (PARTITION BY symbol ORDER BY ts) AS cce
FROM TickStore;
没有 ORDER BY 子句时，系统将返回错误：
=> SELECT CONDITIONAL_CHANGE_EVENT(bid)
OVER (PARTITION BY symbol) AS cce
FROM TickStore;
ERROR: conditional_change_event must contain an
ORDER BY clause within its analytic clause
有关更多示例，请参阅事件窗口。
另请参阅
12 - CONDITIONAL_TRUE_EVENT [analytic]
从 0 开始向每个行分配一个事件窗口编号，并在当 boolean 参数表达式的结果评估为 true 时，以 1 为增量递增事件窗口编号。例如，假定某列的值的顺序如下：
( 1, 2, 3, 4, 5, 6 )
CONDITIONAL_TRUE_EVENT(a > 3) returns 0, 0, 0, 1, 2, 3.
行为类型
不可变
语法
CONDITIONAL_TRUE_EVENT ( boolean-expression ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
- boolean-expression
- 在输入记录中评估的 SQL 标量表达式，类型 BOOLEAN。
OVER()- 请参阅分析函数。
注意
分析 window-order-clause 是必需的，但 window-partition-clause 是可选的。
示例
> SELECT CONDITIONAL_TRUE_EVENT(bid > 10.6)
OVER(PARTITION BY bid ORDER BY ts) AS cte
FROM Tickstore;
如果忽略 ORDER BY 子句，系统将返回错误：
> SELECT CONDITIONAL_TRUE_EVENT(bid > 10.6)
OVER(PARTITION BY bid) AS cte
FROM Tickstore;
ERROR: conditional_true_event must contain an ORDER BY
clause within its analytic clause
有关更多示例，请参阅事件窗口。
另请参阅
13 - COUNT [analytic]
对
窗口中组内的出现次数进行计数。如果指定 * 或某个非 NULL 常数，COUNT() 会对所有行进行计数。
行为类型
不可变
语法
COUNT ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 返回其 expression 不为 null 的每个组中的行数。可以是生成 BIGINT 的任何表达式。
OVER()- 请参阅分析函数。
示例
使用 窗口框架 中定义的架构时，以下 COUNT 函数将忽略窗口顺序子句和窗口框架子句；否则 Vertica 会将其视为窗口聚合。将报告聚合的窗口视为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
=> SELECT deptno, sal, empno, COUNT(sal)
OVER (PARTITION BY deptno) AS count FROM emp;
deptno | sal | empno | count
--------+-----+-------+-------
10 | 101 | 1 | 2
10 | 104 | 4 | 2
20 | 110 | 10 | 6
20 | 110 | 9 | 6
20 | 109 | 7 | 6
20 | 109 | 6 | 6
20 | 109 | 8 | 6
20 | 109 | 11 | 6
30 | 105 | 5 | 3
30 | 103 | 3 | 3
30 | 102 | 2 | 3
使用 ORDER BY sal 会创建包含默认窗口的移动窗口查询： RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
=> SELECT deptno, sal, empno, COUNT(sal)
OVER (PARTITION BY deptno ORDER BY sal) AS count
FROM emp;
deptno | sal | empno | count
--------+-----+-------+-------
10 | 101 | 1 | 1
10 | 104 | 4 | 2
20 | 100 | 11 | 1
20 | 109 | 7 | 4
20 | 109 | 6 | 4
20 | 109 | 8 | 4
20 | 110 | 10 | 6
20 | 110 | 9 | 6
30 | 102 | 2 | 1
30 | 103 | 3 | 2
30 | 105 | 5 | 3
使用 VMart 架构时，以下查询会查找其每小时工资小于或等于当前员工的员工数。该查询使用默认窗口 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 返回运行/累计平均数（有时称为移动平均数）：
=> SELECT employee_last_name AS "last_name", hourly_rate, COUNT(*)
OVER (ORDER BY hourly_rate) AS moving_count from employee_dimension;
last_name | hourly_rate | moving_count
------------+-------------+--------------
Gauthier | 6 | 4
Taylor | 6 | 4
Jefferson | 6 | 4
Nielson | 6 | 4
McNulty | 6.01 | 11
Robinson | 6.01 | 11
Dobisz | 6.01 | 11
Williams | 6.01 | 11
Kramer | 6.01 | 11
Miller | 6.01 | 11
Wilson | 6.01 | 11
Vogel | 6.02 | 14
Moore | 6.02 | 14
Vogel | 6.02 | 14
Carcetti | 6.03 | 19
...
要返回不是运行（累计）平均数的移动平均数，窗口应指定 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING：
=> SELECT employee_last_name AS "last_name", hourly_rate, COUNT(*)
OVER (ORDER BY hourly_rate ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
AS moving_count from employee_dimension;
另请参阅
14 - CUME_DIST [analytic]
计算
窗口内同一分区中当前行的累计分布或相对于其他行的排序。
CUME_DIST() 返回大于 0 且小于等于 1 的数字，其中该数字表示指定行在由 n 行组成的组中的相对位置。对于第 x 行（假定 ASC 排序），CUME_DIST 的 x 为数值小于等于 x 的行数除以整个分区的总行数。例如，在由三行组成的组中，返回的累计分布值应为 1/3、2/3 和 3/3。
注意
由于给定行的结果取决于同一分区中该行前面的行数，因此在调用此函数时，应始终指定 window-order-clause。
行为类型
不可变
语法
CUME_DIST ( ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
OVER()- 请参阅分析函数。
示例
以下示例返回第一季度每月内不同交易类型销售额的累计分布。
=> SELECT calendar_month_name AS month, tender_type, SUM(sales_quantity),
CUME_DIST()
OVER (PARTITION BY calendar_month_name ORDER BY SUM(sales_quantity)) AS
CUME_DIST
FROM store.store_sales_fact JOIN date_dimension
USING(date_key) WHERE calendar_month_name IN ('January','February','March')
AND tender_type NOT LIKE 'Other'
GROUP BY calendar_month_name, tender_type;
month | tender_type | SUM | CUME_DIST
----------+-------------+--------+-----------
March | Credit | 469858 | 0.25
March | Cash | 470449 | 0.5
March | Check | 473033 | 0.75
March | Debit | 475103 | 1
January | Cash | 441730 | 0.25
January | Debit | 443922 | 0.5
January | Check | 446297 | 0.75
January | Credit | 450994 | 1
February | Check | 425665 | 0.25
February | Debit | 426726 | 0.5
February | Credit | 430010 | 0.75
February | Cash | 430767 | 1
(12 rows)
另请参阅
15 - DENSE_RANK [analytic]
在每个窗口分区内，按窗口的 ORDER BY 子句指定的顺序对查询结果集中的所有行进行排名。DENSE_RANK 函数返回无间隙的排名数字序列。
DENSE_RANK 按以下方式执行：
-
按 ORDER BY 子句指定的顺序对分区行进行排序。
-
比较前一行与当前行的 ORDER BY 值，并按以下方式对当前行进行排名：
DENSE_RANK 始终将排名更改 1 个名次，因此排名序列中不会出现间隙。最大排名值是查询返回的唯一 ORDER BY 值的数量。
行为类型
不可变
语法
DENSE_RANK() OVER (
[ window-partition-clause ]
window-order-clause )
参数
OVER()- 请参阅分析函数。
请参阅分析函数
与 RANK 的对比
RANK 会在排名序列中留下间隙，而 DENSE_RANK 不会出现该情况。以下示例将两种函数的运算操作进行了比较。
示例
以下查询调用 RANK 和 DENSE_RANK，按照年收入对客户进行排名。两种函数返回了不同的排名，如下所示：
=> SELECT employee_region region, employee_key, annual_salary,
RANK() OVER (PARTITION BY employee_region ORDER BY annual_salary) Rank,
DENSE_RANK() OVER (PARTITION BY employee_region ORDER BY annual_salary) "Dense Rank"
FROM employee_dimension;
region | employee_key | annual_salary | Rank | Dense Rank
----------------------------------+--------------+---------------+------+------------
West | 5248 | 1200 | 1 | 1
West | 6880 | 1204 | 2 | 2
West | 5700 | 1214 | 3 | 3
West | 9857 | 1218 | 4 | 4
West | 6014 | 1218 | 4 | 4
West | 9221 | 1220 | 6 | 5
West | 7646 | 1222 | 7 | 6
West | 6621 | 1222 | 7 | 6
West | 6488 | 1224 | 9 | 7
West | 7659 | 1226 | 10 | 8
West | 7432 | 1226 | 10 | 8
West | 9905 | 1226 | 10 | 8
West | 9021 | 1228 | 13 | 9
...
West | 56 | 963104 | 2794 | 2152
West | 100 | 992363 | 2795 | 2153
East | 8353 | 1200 | 1 | 1
East | 9743 | 1202 | 2 | 2
East | 9975 | 1202 | 2 | 2
East | 9205 | 1204 | 4 | 3
East | 8894 | 1206 | 5 | 4
East | 7740 | 1206 | 5 | 4
East | 7324 | 1208 | 7 | 5
East | 6505 | 1208 | 7 | 5
East | 5404 | 1208 | 7 | 5
East | 5010 | 1208 | 7 | 5
East | 9114 | 1212 | 11 | 6
...
另请参阅
SQL 分析
16 - EXPONENTIAL_MOVING_AVERAGE [analytic]
使用平滑系数 X 计算表达式 E 的指数移动平均线 (EMA)。EMA 与简单移动平均线不同，它提供了更稳定的图像，可显示数据随时间的变化。
通过将上一个 EMA 值添加到按照平滑系数成比例变化的当前数据点，可以计算 EMA 值，如以下公式所示：
EMA = EMA0 + (X * (E - EMA0))
其中：
-
E 是当前数据点
-
EMA0 是上一行的 EMA 值。
-
X 是平滑系数。
此函数也可以在行级别工作。例如，EMA 假设给定列中的数据是按照均匀间隔取样的。如果用户的数据点是按照不均匀间隔取样的，则应该在 EMA() 之前运行时间序列空白填充和插值 (GFI) 操作
行为类型
不可变
语法
EXPONENTIAL_MOVING_AVERAGE ( E, X ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
- E
- 其平均值基于一组行计算得出的值。可以是
INTEGER、FLOAT 或 NUMERIC 类型，并且必须是常数。
- X
- 介于 0 和 1 之间的用作平滑系数的正
FLOAT 值。
OVER()- 请参阅分析函数。
示例
以下示例首先在子查询中使用时间序列空白填充和插值 (GFI)，然后对子查询结果执行 EXPONENTIAL_MOVING_AVERAGE 操作。
创建一个简单的四列表：
=> CREATE TABLE ticker(
time TIMESTAMP,
symbol VARCHAR(8),
bid1 FLOAT,
bid2 FLOAT );
插入一些数据，包括 NULL，以便于 GFI 可以执行插值和空白填充：
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:00', 'ABC', 60.45, 60.44);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:01', 'ABC', 60.49, 65.12);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:02', 'ABC', 57.78, 59.25);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:03', 'ABC', null, 65.12);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:04', 'ABC', 67.88, null);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:00', 'XYZ', 47.55, 40.15);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:01', 'XYZ', 44.35, 46.78);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:02', 'XYZ', 71.56, 75.78);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:03', 'XYZ', 85.55, 70.21);
=> INSERT INTO ticker VALUES ('2009-07-12 03:00:04', 'XYZ', 45.55, 58.65);
=> COMMIT;
注意
在空白填充和插值期间，Vertica 在时间片的任意一侧中选择最近的非 NULL 值，并使用此值。例如，如果您使用线性插值方案，但未指定
IGNORE NULLS，并且您的数据包含一个实值和一个 NULL 值，则结果为 NULL。如果任意一侧中有值为 NULL，则结果为 NULL。有关详细信息，请参阅
当时间序列数据包含空值时。
查询您刚创建的表，以便于看到输出：
=> SELECT * FROM ticker;
time | symbol | bid1 | bid2
---------------------+--------+-------+-------
2009-07-12 03:00:00 | ABC | 60.45 | 60.44
2009-07-12 03:00:01 | ABC | 60.49 | 65.12
2009-07-12 03:00:02 | ABC | 57.78 | 59.25
2009-07-12 03:00:03 | ABC | | 65.12
2009-07-12 03:00:04 | ABC | 67.88 |
2009-07-12 03:00:00 | XYZ | 47.55 | 40.15
2009-07-12 03:00:01 | XYZ | 44.35 | 46.78
2009-07-12 03:00:02 | XYZ | 71.56 | 75.78
2009-07-12 03:00:03 | XYZ | 85.55 | 70.21
2009-07-12 03:00:04 | XYZ | 45.55 | 58.65
(10 rows)
以下查询处理表 trades 的列 a 中的每个 2 秒时间片所包含的第一个和最后一个值。查询然后使用平滑系数 50% 计算表达式 fv 和 lv 的指数移动平均线：
=> SELECT symbol, slice_time, fv, lv,
EXPONENTIAL_MOVING_AVERAGE(fv, 0.5)
OVER (PARTITION BY symbol ORDER BY slice_time) AS ema_first,
EXPONENTIAL_MOVING_AVERAGE(lv, 0.5)
OVER (PARTITION BY symbol ORDER BY slice_time) AS ema_last
FROM (
SELECT symbol, slice_time,
TS_FIRST_VALUE(bid1 IGNORE NULLS) as fv,
TS_LAST_VALUE(bid2 IGNORE NULLS) AS lv
FROM ticker TIMESERIES slice_time AS '2 seconds'
OVER (PARTITION BY symbol ORDER BY time) ) AS sq;
symbol | slice_time | fv | lv | ema_first | ema_last
--------+---------------------+-------+-------+-----------+----------
ABC | 2009-07-12 03:00:00 | 60.45 | 65.12 | 60.45 | 65.12
ABC | 2009-07-12 03:00:02 | 57.78 | 65.12 | 59.115 | 65.12
ABC | 2009-07-12 03:00:04 | 67.88 | 65.12 | 63.4975 | 65.12
XYZ | 2009-07-12 03:00:00 | 47.55 | 46.78 | 47.55 | 46.78
XYZ | 2009-07-12 03:00:02 | 71.56 | 70.21 | 59.555 | 58.495
XYZ | 2009-07-12 03:00:04 | 45.55 | 58.65 | 52.5525 | 58.5725
(6 rows)
另请参阅
17 - FIRST_VALUE [analytic]
用于选择表或分区的第一个值（由 window-order-clause 确定），无需使用自联接。当您希望使用第一个值作为计算的基线时，此函数很有用。
将 FIRST_VALUE() 与 window-order-clause 结合使用，以生成具有确定性的结果。如果没有为当前行指定
窗口，则默认窗口为 UNBOUNDED PRECEDING AND CURRENT ROW。
行为类型
不可变
语法
FIRST_VALUE ( expression [ IGNORE NULLS ] ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 要求值的表达式 - 例如常数、列、非分析函数、函数表达式或涉及任何这些项目的表达式。
IGNORE NULLS- 指定返回集中的第一个非 NULL 值，或者如果所有值均为
NULL，则返回 NULL。如果忽略此选项，且集合中的第一个值为 NULL，函数将返回 NULL。
OVER()- 请参阅分析函数。
示例
以下查询希望获得“在星期几进行分区”值集的第一个值，并说明 FIRST_VALUE() 的潜在不确定性：
=> SELECT calendar_year, date_key, day_of_week, full_date_description,
FIRST_VALUE(full_date_description)
OVER(PARTITION BY calendar_month_number_in_year ORDER BY day_of_week)
AS "first_value"
FROM date_dimension
WHERE calendar_year=2003 AND calendar_month_number_in_year=1;
返回的第一个值为 2003 年 1 月 31 日；但是下次运行相同的查询时，第一个值可能是 1 月 24 日或 1 月 3 日，或 10 日或 17 日。这是因为分析 ORDER BY 列 day_of_week 会返回包含关系（多个星期五）的行。这些重复的值使 ORDER BY 求值结果具有不确定性，因为包含关系的行可以按照任何方式排序，这些行中的任何一个都符合称为 day_of_week 的第一个值的条件。
calendar_year | date_key | day_of_week | full_date_description | first_value
--------------+----------+-------------+-----------------------+------------------
2003 | 31 | Friday | January 31, 2003 | January 31, 2003
2003 | 24 | Friday | January 24, 2003 | January 31, 2003
2003 | 3 | Friday | January 3, 2003 | January 31, 2003
2003 | 10 | Friday | January 10, 2003 | January 31, 2003
2003 | 17 | Friday | January 17, 2003 | January 31, 2003
2003 | 6 | Monday | January 6, 2003 | January 31, 2003
2003 | 27 | Monday | January 27, 2003 | January 31, 2003
2003 | 13 | Monday | January 13, 2003 | January 31, 2003
2003 | 20 | Monday | January 20, 2003 | January 31, 2003
2003 | 11 | Saturday | January 11, 2003 | January 31, 2003
2003 | 18 | Saturday | January 18, 2003 | January 31, 2003
2003 | 25 | Saturday | January 25, 2003 | January 31, 2003
2003 | 4 | Saturday | January 4, 2003 | January 31, 2003
2003 | 12 | Sunday | January 12, 2003 | January 31, 2003
2003 | 26 | Sunday | January 26, 2003 | January 31, 2003
2003 | 5 | Sunday | January 5, 2003 | January 31, 2003
2003 | 19 | Sunday | January 19, 2003 | January 31, 2003
2003 | 23 | Thursday | January 23, 2003 | January 31, 2003
2003 | 2 | Thursday | January 2, 2003 | January 31, 2003
2003 | 9 | Thursday | January 9, 2003 | January 31, 2003
2003 | 16 | Thursday | January 16, 2003 | January 31, 2003
2003 | 30 | Thursday | January 30, 2003 | January 31, 2003
2003 | 21 | Tuesday | January 21, 2003 | January 31, 2003
2003 | 14 | Tuesday | January 14, 2003 | January 31, 2003
2003 | 7 | Tuesday | January 7, 2003 | January 31, 2003
2003 | 28 | Tuesday | January 28, 2003 | January 31, 2003
2003 | 22 | Wednesday | January 22, 2003 | January 31, 2003
2003 | 29 | Wednesday | January 29, 2003 | January 31, 2003
2003 | 15 | Wednesday | January 15, 2003 | January 31, 2003
2003 | 1 | Wednesday | January 1, 2003 | January 31, 2003
2003 | 8 | Wednesday | January 8, 2003 | January 31, 2003
(31 rows)
注意
由于词汇规则，day_of_week 结果按照字母顺序返回。每天都不是按照 7 天一周的周期（例如，从星期天开始，然后是星期一、星期二等）的顺序显示，这样的事实对结果没有影响。
要返回具有确定性的结果，请修改查询，使其对唯一 字段（如 date_key）执行分析 ORDER BY 操作：
=> SELECT calendar_year, date_key, day_of_week, full_date_description,
FIRST_VALUE(full_date_description) OVER
(PARTITION BY calendar_month_number_in_year ORDER BY date_key) AS "first_value"
FROM date_dimension WHERE calendar_year=2003;
FIRST_VALUE() 针对一月份分区返回第一个值 1 月 1 日，针对二月份分区返回第一个值 2 月 1 日 。此外，full_date_description 列不包含关系：
calendar_year | date_key | day_of_week | full_date_description | first_value
---------------+----------+-------------+-----------------------+------------
2003 | 1 | Wednesday | January 1, 2003 | January 1, 2003
2003 | 2 | Thursday | January 2, 2003 | January 1, 2003
2003 | 3 | Friday | January 3, 2003 | January 1, 2003
2003 | 4 | Saturday | January 4, 2003 | January 1, 2003
2003 | 5 | Sunday | January 5, 2003 | January 1, 2003
2003 | 6 | Monday | January 6, 2003 | January 1, 2003
2003 | 7 | Tuesday | January 7, 2003 | January 1, 2003
2003 | 8 | Wednesday | January 8, 2003 | January 1, 2003
2003 | 9 | Thursday | January 9, 2003 | January 1, 2003
2003 | 10 | Friday | January 10, 2003 | January 1, 2003
2003 | 11 | Saturday | January 11, 2003 | January 1, 2003
2003 | 12 | Sunday | January 12, 2003 | January 1, 2003
2003 | 13 | Monday | January 13, 2003 | January 1, 2003
2003 | 14 | Tuesday | January 14, 2003 | January 1, 2003
2003 | 15 | Wednesday | January 15, 2003 | January 1, 2003
2003 | 16 | Thursday | January 16, 2003 | January 1, 2003
2003 | 17 | Friday | January 17, 2003 | January 1, 2003
2003 | 18 | Saturday | January 18, 2003 | January 1, 2003
2003 | 19 | Sunday | January 19, 2003 | January 1, 2003
2003 | 20 | Monday | January 20, 2003 | January 1, 2003
2003 | 21 | Tuesday | January 21, 2003 | January 1, 2003
2003 | 22 | Wednesday | January 22, 2003 | January 1, 2003
2003 | 23 | Thursday | January 23, 2003 | January 1, 2003
2003 | 24 | Friday | January 24, 2003 | January 1, 2003
2003 | 25 | Saturday | January 25, 2003 | January 1, 2003
2003 | 26 | Sunday | January 26, 2003 | January 1, 2003
2003 | 27 | Monday | January 27, 2003 | January 1, 2003
2003 | 28 | Tuesday | January 28, 2003 | January 1, 2003
2003 | 29 | Wednesday | January 29, 2003 | January 1, 2003
2003 | 30 | Thursday | January 30, 2003 | January 1, 2003
2003 | 31 | Friday | January 31, 2003 | January 1, 2003
2003 | 32 | Saturday | February 1, 2003 | February 1, 2003
2003 | 33 | Sunday | February 2, 2003 | February 1,2003
...
(365 rows)
另请参阅
18 - LAG [analytic]
在
窗口内当前行之前按照给定的偏移量返回该输入表达式的值。此函数允许您同时访问表中的多行。这对于在可以可靠地知道行的相对位置时比较值很有用。借助它还可以避免成本较高的自联接，从而加快查询处理速度。
有关获取后续行的信息，请参阅 LEAD。
行为类型
不可变
语法
LAG ( expression[, offset ] [, default ] ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
- 表达式
- 要进行求值的表达式，例如常数、列、非统计函数、函数表达式或任何涉及以上内容的表达式。
- offset
- 表示 lag 有多大。默认值为 1（前一行）。此参数的计算结果必须为常数正整数。
- default
- offset 超出表或分区的边界时返回的值。此值必须是一个常数值或者可以解析为常数的表达式；数据类型强制转换为第一个实参的类型。
示例
这个例子计算了表中按照日期所剩下的余额的总和，同时计算了最后一天的先前余额的总和。依照接下来的输入，数据满足以下条件：
-
对于每个 some_id，每个由 month_date 表示的日期都有 1 个行。
-
对于每个 some_id，日期都是连续的；也就是说，如果 2 月 24 号有一行，2 月 26 号有一行，则 2 月 25 号也有一行。
-
每个 some_id 都有相同的一组日期。
=> CREATE TABLE balances (
month_date DATE,
current_bal INT,
some_id INT);
=> INSERT INTO balances values ('2009-02-24', 10, 1);
=> INSERT INTO balances values ('2009-02-25', 10, 1);
=> INSERT INTO balances values ('2009-02-26', 10, 1);
=> INSERT INTO balances values ('2009-02-24', 20, 2);
=> INSERT INTO balances values ('2009-02-25', 20, 2);
=> INSERT INTO balances values ('2009-02-26', 20, 2);
=> INSERT INTO balances values ('2009-02-24', 30, 3);
=> INSERT INTO balances values ('2009-02-25', 20, 3);
=> INSERT INTO balances values ('2009-02-26', 30, 3);
现在使用 LAG 计算当前每一个日期的余额的总和，以及最后一天的先前余额的总和：
=> SELECT month_date,
SUM(current_bal) as current_bal_sum,
SUM(previous_bal) as previous_bal_sum FROM
(SELECT month_date, current_bal,
LAG(current_bal, 1, 0) OVER
(PARTITION BY some_id ORDER BY month_date)
AS previous_bal FROM balances) AS subQ
GROUP BY month_date ORDER BY month_date;
month_date | current_bal_sum | previous_bal_sum
------------+-----------------+------------------
2009-02-24 | 60 | 0
2009-02-25 | 50 | 60
2009-02-26 | 60 | 50
(3 rows)
对于相同的示例数据来说，不可使用接下来的查询，原因是 LAG 嵌套在一个聚合函数中：
=> SELECT month_date,
SUM(current_bal) as current_bal_sum,
SUM(LAG(current_bal, 1, 0) OVER
(PARTITION BY some_id ORDER BY month_date)) AS previous_bal_sum
FROM some_table GROUP BY month_date ORDER BY month_date;
下面的示例使用了 VMart 数据库。LAG 首先返回前一行的年收入值，然后计算当前行收入与前一行收入的差：
=> SELECT occupation, customer_key, customer_name, annual_income,
LAG(annual_income, 1, 0) OVER (PARTITION BY occupation
ORDER BY annual_income) AS prev_income, annual_income -
LAG(annual_income, 1, 0) OVER (PARTITION BY occupation
ORDER BY annual_income) AS difference
FROM customer_dimension ORDER BY occupation, customer_key LIMIT 20;
occupation | customer_key | customer_name | annual_income | prev_income | difference
------------+--------------+----------------------+---------------+-------------+------------
Accountant | 15 | Midori V. Peterson | 692610 | 692535 | 75
Accountant | 43 | Midori S. Rodriguez | 282359 | 280976 | 1383
Accountant | 93 | Robert P. Campbell | 471722 | 471355 | 367
Accountant | 102 | Sam T. McNulty | 901636 | 901561 | 75
Accountant | 134 | Martha B. Overstreet | 705146 | 704335 | 811
Accountant | 165 | James C. Kramer | 376841 | 376474 | 367
Accountant | 225 | Ben W. Farmer | 70574 | 70449 | 125
Accountant | 270 | Jessica S. Lang | 684204 | 682274 | 1930
Accountant | 273 | Mark X. Lampert | 723294 | 722737 | 557
Accountant | 295 | Sharon K. Gauthier | 29033 | 28412 | 621
Accountant | 338 | Anna S. Jackson | 816858 | 815557 | 1301
Accountant | 377 | William I. Jones | 915149 | 914872 | 277
Accountant | 438 | Joanna A. McCabe | 147396 | 144482 | 2914
Accountant | 452 | Kim P. Brown | 126023 | 124797 | 1226
Accountant | 467 | Meghan K. Carcetti | 810528 | 810284 | 244
Accountant | 478 | Tanya E. Greenwood | 639649 | 639029 | 620
Accountant | 511 | Midori P. Vogel | 187246 | 185539 | 1707
Accountant | 525 | Alexander K. Moore | 677433 | 677050 | 383
Accountant | 550 | Sam P. Reyes | 735691 | 735355 | 336
Accountant | 577 | Robert U. Vu | 616101 | 615439 | 662
(20 rows)
接下来的示例使用了 LEAD 和 LAG 返回了当前行工资后的第三行以及当前行工资之前的第五行：
=> SELECT hire_date, employee_key, employee_last_name,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "next_hired" ,
LAG(hire_date, 1) OVER (ORDER BY hire_date) AS "last_hired"
FROM employee_dimension ORDER BY hire_date, employee_key;
hire_date | employee_key | employee_last_name | next_hired | last_hired
------------+--------------+--------------------+------------+------------
1956-04-11 | 2694 | Farmer | 1956-05-12 |
1956-05-12 | 5486 | Winkler | 1956-09-18 | 1956-04-11
1956-09-18 | 5525 | McCabe | 1957-01-15 | 1956-05-12
1957-01-15 | 560 | Greenwood | 1957-02-06 | 1956-09-18
1957-02-06 | 9781 | Bauer | 1957-05-25 | 1957-01-15
1957-05-25 | 9506 | Webber | 1957-07-04 | 1957-02-06
1957-07-04 | 6723 | Kramer | 1957-07-07 | 1957-05-25
1957-07-07 | 5827 | Garnett | 1957-11-11 | 1957-07-04
1957-11-11 | 373 | Reyes | 1957-11-21 | 1957-07-07
1957-11-21 | 3874 | Martin | 1958-02-06 | 1957-11-11
(10 rows)
另请参阅
19 - LAST_VALUE [analytic]
用于选择表或分区的最后一个值（由 window-order-clause 确定），无需使用自联接。 LAST_VALUE 在窗口顺序子句统计后，取分区的最后一个记录。然后此函数根据最后一个记录计算表达式，并返回结果。如果要使用最后值做为计算的基准，该函数非常有帮助。
将 LAST_VALUE() 与 window-order-clause 结合使用，以生成具有确定性的结果。如果没有为当前行指定
窗口，则默认窗口为 UNBOUNDED PRECEDING AND CURRENT ROW。
提示
受默认窗口语义影响，
LAST_VALUE 并非总是会返回分区的最后一个值。如果在分析子句中忽略
窗口框架子句，则
LAST_VALUE 会在此默认窗口中运行。虽然结果并不返回当前分区的最后一个值使其看起来并不直观，但是它返回了窗口的最后一个值，并且此值随当前正在处理的输入行而不断变化。如果您要返回分区的最后一个值，请使用
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。请看下方示例。
行为类型
不可变
语法
LAST_VALUE ( expression [ IGNORE NULLS ] ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 要求值的表达式，例如常数、列、非统计函数、函数表达式或任何涉及以上内容的表达式。
IGNORE NULLS- 指定返回集中的最后一个非 NULL 值，或者如果所有值均为
NULL，则返回 NULL。如果忽略此选项且集合中的最后一个值为 NULL，则函数将返回 NULL。
OVER()- 请参阅分析函数。
示例
如果在分析数据时使用在 窗口框架 中定义的架构，以下查询并不会按照部门显示最高工资数；它会按照部门和工资显示最高工资数。
=> SELECT deptno, sal, empno, LAST_VALUE(sal)
OVER (PARTITION BY deptno ORDER BY sal) AS lv
FROM emp;
deptno | sal | empno | lv
--------+-----+-------+--------
10 | 101 | 1 | 101
10 | 104 | 4 | 104
20 | 100 | 11 | 100
20 | 109 | 7 | 109
20 | 109 | 6 | 109
20 | 109 | 8 | 109
20 | 110 | 10 | 110
20 | 110 | 9 | 110
30 | 102 | 2 | 102
30 | 103 | 3 | 103
30 | 105 | 5 | 105
如果包含了窗口框架子句 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING， LAST_VALUE() 按部门返回最高工资，是信息的准确表达。
=> SELECT deptno, sal, empno, LAST_VALUE(sal)
OVER (PARTITION BY deptno ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM emp;
deptno | sal | empno | lv
--------+-----+-------+--------
10 | 101 | 1 | 104
10 | 104 | 4 | 104
20 | 100 | 11 | 110
20 | 109 | 7 | 110
20 | 109 | 6 | 110
20 | 109 | 8 | 110
20 | 110 | 10 | 110
20 | 110 | 9 | 110
30 | 102 | 2 | 105
30 | 103 | 3 | 105
30 | 105 | 5 | 105
更多示例，请访问 FIRST_VALUE()。
另请参阅
20 - LEAD [analytic]
从
窗口内当前行之后的行返回值，以便您可以同时访问表中的多行。这对于在可以可靠地知道行的相对位置时比较值很有用。借助它还可以避免成本较高的自联接，从而加快查询处理速度。
行为类型
不可变
语法
LEAD ( expression[, offset ] [, default ] ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
- 表达式
- 要进行求值的表达式，例如常数、列、非统计函数、函数表达式或任何涉及以上内容的表达式。
- offset
- 是一个可选参数，默认值为 1（下一行）。此参数的计算结果必须为常数正整数。
- default
- offset 超出表或分区的边界时返回的值。此值必须是一个常数值或者可以解析为常数的表达式；数据类型强制转换为第一个实参的类型。
示例
LEAD 在当前行之后找到了员工的雇佣日期：
=> SELECT employee_region, hire_date, employee_key, employee_last_name,
LEAD(hire_date, 1) OVER (PARTITION BY employee_region ORDER BY hire_date) AS "next_hired"
FROM employee_dimension ORDER BY employee_region, hire_date, employee_key;
employee_region | hire_date | employee_key | employee_last_name | next_hired
-------------------+------------+--------------+--------------------+------------
East | 1956-04-08 | 9218 | Harris | 1957-02-06
East | 1957-02-06 | 7799 | Stein | 1957-05-25
East | 1957-05-25 | 3687 | Farmer | 1957-06-26
East | 1957-06-26 | 9474 | Bauer | 1957-08-18
East | 1957-08-18 | 570 | Jefferson | 1957-08-24
East | 1957-08-24 | 4363 | Wilson | 1958-02-17
East | 1958-02-17 | 6457 | McCabe | 1958-06-26
East | 1958-06-26 | 6196 | Li | 1958-07-16
East | 1958-07-16 | 7749 | Harris | 1958-09-18
East | 1958-09-18 | 9678 | Sanchez | 1958-11-10
(10 rows)
接下来的示例使用 LEAD 和 LAG 返回了当前行工资后的第三行以及当前行工资之前的第五行。
=> SELECT hire_date, employee_key, employee_last_name,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "next_hired" ,
LAG(hire_date, 1) OVER (ORDER BY hire_date) AS "last_hired"
FROM employee_dimension ORDER BY hire_date, employee_key;
hire_date | employee_key | employee_last_name | next_hired | last_hired
------------+--------------+--------------------+------------+------------
1956-04-11 | 2694 | Farmer | 1956-05-12 |
1956-05-12 | 5486 | Winkler | 1956-09-18 | 1956-04-11
1956-09-18 | 5525 | McCabe | 1957-01-15 | 1956-05-12
1957-01-15 | 560 | Greenwood | 1957-02-06 | 1956-09-18
1957-02-06 | 9781 | Bauer | 1957-05-25 | 1957-01-15
1957-05-25 | 9506 | Webber | 1957-07-04 | 1957-02-06
1957-07-04 | 6723 | Kramer | 1957-07-07 | 1957-05-25
1957-07-07 | 5827 | Garnett | 1957-11-11 | 1957-07-04
1957-11-11 | 373 | Reyes | 1957-11-21 | 1957-07-07
1957-11-21 | 3874 | Martin | 1958-02-06 | 1957-11-11
(10 rows)
以下例子返回员工名和工资，以及最高工资和最低工资。
=> SELECT employee_last_name, annual_salary,
NVL(LEAD(annual_salary) OVER (ORDER BY annual_salary),
MIN(annual_salary) OVER()) "Next Highest",
NVL(LAG(annual_salary) OVER (ORDER BY annual_salary),
MAX(annual_salary) OVER()) "Next Lowest"
FROM employee_dimension;
employee_last_name | annual_salary | Next Highest | Next Lowest
--------------------+---------------+--------------+-------------
Nielson | 1200 | 1200 | 995533
Lewis | 1200 | 1200 | 1200
Harris | 1200 | 1202 | 1200
Robinson | 1202 | 1202 | 1200
Garnett | 1202 | 1202 | 1202
Weaver | 1202 | 1202 | 1202
Nielson | 1202 | 1202 | 1202
McNulty | 1202 | 1204 | 1202
Farmer | 1204 | 1204 | 1202
Martin | 1204 | 1204 | 1204
(10 rows)
下一个例子在员工表中每一个助理总监，在当前行总监之后入职的总监的雇用日期。例如，Jackson 被雇佣日期是 2016/12/28，下一个雇佣的总监是 Bauer：
=> SELECT employee_last_name, hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date DESC) as "NextHired"
FROM employee_dimension WHERE job_title = 'Assistant Director';
employee_last_name | hire_date | NextHired
--------------------+------------+------------
Jackson | 2016-12-28 | 2016-12-26
Bauer | 2016-12-26 | 2016-12-11
Miller | 2016-12-11 | 2016-12-07
Fortin | 2016-12-07 | 2016-11-27
Harris | 2016-11-27 | 2016-11-15
Goldberg | 2016-11-15 |
(5 rows)
另请参阅
21 - MAX [analytic]
返回
窗口内某个表达式的最大值。返回值的类型与表达式数据类型相同。
分析函数 MIN() 和 MAX() 可以使用布尔值运行。MAX() 函数可以执行 布尔数据类型 或可隐式转换为布尔值的值。如果至少一个输入值为真，则 MAX() 返回 t (true)。否则，它返回 f (false)。在同一场景中，如果所有输入值都为真，则 MIN() 函数返回 t (true)。否则，它返回 f。
行为类型
不可变
语法
MAX ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 计算其最大值的表达式，通常是列引用。
OVER()- 请参阅分析函数。
示例
下面的查询计算员工年薪与马萨诸塞州最高年薪之间的偏差。
=> SELECT employee_state, annual_salary,
MAX(annual_salary)
OVER(PARTITION BY employee_state ORDER BY employee_key) max,
annual_salary- MAX(annual_salary)
OVER(PARTITION BY employee_state ORDER BY employee_key) diff
FROM employee_dimension
WHERE employee_state = 'MA';
employee_state | annual_salary | max | diff
----------------+---------------+--------+---------
MA | 1918 | 995533 | -993615
MA | 2058 | 995533 | -993475
MA | 2586 | 995533 | -992947
MA | 2500 | 995533 | -993033
MA | 1318 | 995533 | -994215
MA | 2072 | 995533 | -993461
MA | 2656 | 995533 | -992877
MA | 2148 | 995533 | -993385
MA | 2366 | 995533 | -993167
MA | 2664 | 995533 | -992869
(10 rows)
以下示例显示了当您将 MIN 和 MAX 分析函数与布尔值一起使用时，它们之间的区别。该示例创建了一个包含两列的表，添加了两行数据，并显示了 MIN 和 MAX 的示例输出。
CREATE TABLE min_max_functions (emp VARCHAR, torf BOOL);
INSERT INTO min_max_functions VALUES ('emp1', 1);
INSERT INTO min_max_functions VALUES ('emp1', 0);
SELECT DISTINCT emp,
min(torf) OVER (PARTITION BY emp) AS worksasbooleanand,
Max(torf) OVER (PARTITION BY emp) AS worksasbooleanor
FROM min_max_functions;
emp | worksasbooleanand | worksasbooleanor
------+-------------------+------------------
emp1 | f | t
(1 row)
另请参阅
22 - MEDIAN [analytic]
对于每一行，返回每个分区内值集的中值。 MEDIAN 用最高的数字优先级确定实参，将剩余实参隐式转换为该数据类型，并返回该数据类型。
MEDIAN 是实参为 0.5 (50%) 的
PERCENTILE_CONT [analytic] 的一个别名。
行为类型
不可变
语法
MEDIAN ( expression ) OVER ( [ window‑partition‑clause ] )
参数
- 表达式
- 任何
NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回中值或排序后成为中值的内插值。计算中忽略空值。
OVER()- 如果
OVER 子句指定 window-partition-clause，MEDIAN 会根据一个或多个列或表达式对输入行进行分组。如果忽略此子句，则不会进行分组，且 MEDIAN 会将所有输入行作为单分区处理。
示例
请参阅 计算中间值
另请参阅
23 - MIN [analytic]
返回
窗口内某个表达式的最小值。返回值的类型与表达式数据类型相同。
分析函数 MIN() 和 MAX() 可以使用布尔值运行。MAX() 函数可以执行 布尔数据类型 或可隐式转换为布尔值的值。如果至少一个输入值为真，则 MAX() 返回 t (true)。否则，它返回 f (false)。在同一场景中，如果所有输入值都为真，则 MIN() 函数返回 t (true)。否则，它返回 f。
行为类型
不可变
语法
MIN ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 为其计算最小值的任意表达式，通常为列引用。
OVER()- 请参阅分析函数。
示例
下面的示例显示了如何查询以确定员工年薪与马萨诸塞州最低年薪之间的偏差：
=> SELECT employee_state, annual_salary,
MIN(annual_salary)
OVER(PARTITION BY employee_state ORDER BY employee_key) min,
annual_salary- MIN(annual_salary)
OVER(PARTITION BY employee_state ORDER BY employee_key) diff
FROM employee_dimension
WHERE employee_state = 'MA';
employee_state | annual_salary | min | diff
----------------+---------------+------+------
MA | 1918 | 1204 | 714
MA | 2058 | 1204 | 854
MA | 2586 | 1204 | 1382
MA | 2500 | 1204 | 1296
MA | 1318 | 1204 | 114
MA | 2072 | 1204 | 868
MA | 2656 | 1204 | 1452
MA | 2148 | 1204 | 944
MA | 2366 | 1204 | 1162
MA | 2664 | 1204 | 1460
(10 rows)
以下示例显示了当您将 MIN 和 MAX 分析函数与布尔值一起使用时，它们之间的区别。该示例创建了一个包含两列的表，添加了两行数据，并显示了 MIN 和 MAX 的示例输出。
CREATE TABLE min_max_functions (emp VARCHAR, torf BOOL);
INSERT INTO min_max_functions VALUES ('emp1', 1);
INSERT INTO min_max_functions VALUES ('emp1', 0);
SELECT DISTINCT emp,
min(torf) OVER (PARTITION BY emp) AS worksasbooleanand,
Max(torf) OVER (PARTITION BY emp) AS worksasbooleanor
FROM min_max_functions;
emp | worksasbooleanand | worksasbooleanor
------+-------------------+------------------
emp1 | f | t
(1 row)
另请参阅
24 - NTILE [analytic]
将有序数据集（分区）等分成
窗口内的
{value} 个子集，其中子集通过参数 constant‑value 编号为 1。例如，如果 constant-value= 4，且分区包含 20 行，则 NTILE 会将分区行分成四个相等的子集（每个子集包含五行）。 NTILE 为行指定一个从 1 到 4 的数字，然后将每一行分配给其中一个子集。为第一个子集中的行分配 1，为第二个子集中的五行分配 2，依此类推。
如果分区行数无法被子集数整除，则任何子集的行数不得比任何其他子集多超过 1，且最小的子集具有多余行数。例如，如果 constant-value = 4，且行数 = 21，则第一个子集有 6 行，第二个子集有 5 行，依此类推。
如果子集数大于行数，则对等同于行数的子集进行填充，剩余的子集留空。
行为类型
不可变
语法
NTILE ( constant‑value ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
- constant‑value
- 指定子集的数目，其中 constant‑value 必须为每个分区分解成为正常数。
OVER()- 请参阅分析函数。
示例
下面的查询将每个月的销售额分配至四个子集之一。
=> SELECT calendar_month_name AS MONTH, SUM(sales_quantity),
NTILE(4) OVER (ORDER BY SUM(sales_quantity)) AS NTILE
FROM store.store_sales_fact JOIN date_dimension
USING(date_key)
GROUP BY calendar_month_name
ORDER BY NTILE;
MONTH | SUM | NTILE
-----------+---------+-------
November | 2040726 | 1
June | 2088528 | 1
February | 2134708 | 1
April | 2181767 | 2
January | 2229220 | 2
October | 2316363 | 2
September | 2323914 | 3
March | 2354409 | 3
August | 2387017 | 3
July | 2417239 | 4
May | 2492182 | 4
December | 2531842 | 4
(12 rows)
另请参阅
25 - NTH_VALUE [analytic]
在窗口的第 n 行（从第 1 行开始计数）上求值并返回求值结果。如果指定的行不存在，NTH_VALUE 将返回 NULL。
行为类型
不可变
语法
NTH_VALUE ( expression, row‑number [ IGNORE NULLS ] ) OVER (
[ window-frame-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 计算表达式。该表达式可以是常量、列名称、非解析函数、函数表达式或包含任意各项的表达式。
- row‑number
- 指定要计算的行，其中 row‑number 计算结果为 ≥ 1 的整数。
IGNORE NULLS- 指定返回集中第一个非
NULL 值，如果所有值均为 NULL，则返回 NULL。
OVER()- 请参阅分析函数。
示例
在以下示例中，对于表 t1 中的每个元组（当前行），窗口框架子句将窗口定义如下：
ORDER BY b ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
对于每个窗口，第 n 个值中的 n 为 a+1。 a 是元组中列 a 的值。
NTH_VALUE 返回表达式 b+1 的结果，其中 b 是第 n 行（即窗口中的 a+1 行）中列 b 的值。
=> SELECT * FROM t1 ORDER BY a;
a | b
---+----
1 | 10
2 | 20
2 | 21
3 | 30
4 | 40
5 | 50
6 | 60
(7 rows)
=> SELECT NTH_VALUE(b+1, a+1) OVER
(ORDER BY b ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM t1;
?column?
----------
22
31
(7 rows)
26 - PERCENT_RANK [analytic]
通过用行的排名（减去 1）除以分区中的行数（也减去 1），计算
窗口内组中给定行的行相对排名。PERCENT_RANK 始终返回 0 到 1 之间的值（包含）。任何集中的第一行具有为 0 的 PERCENT_RANK。返回值为 NUMBER。
( rank - 1 ) / ( [ rows ] - 1 )
在前面的公式中，rank 为组中行的排名位置，rows 为 OVER() 语句定义的分区中行的总数量。
行为类型
不可变
语法
PERCENT_RANK ( ) OVER (
[ window-partition-clause ]
window-order-clause )
参数
OVER()- 请参阅分析函数
示例
以下示例可得出第一季度每个月不同状态的毛利润百分比排名：
=> SELECT calendar_month_name AS MONTH, store_state,
SUM(gross_profit_dollar_amount),
PERCENT_RANK() OVER (PARTITION BY calendar_month_name
ORDER BY SUM(gross_profit_dollar_amount)) AS PERCENT_RANK
FROM store.store_sales_fact JOIN date_dimension
USING(date_key)
JOIN store.store_dimension
USING (store_key)
WHERE calendar_month_name IN ('January','February','March')
AND store_state IN ('OR','IA','DC','NV','WI')
GROUP BY calendar_month_name, store_state
ORDER BY calendar_month_name, PERCENT_RANK;
MONTH | store_state | SUM | PERCENT_RANK
----------+-------------+--------+--------------
February | IA | 418490 | 0
February | OR | 460588 | 0.25
February | DC | 616553 | 0.5
February | WI | 619204 | 0.75
February | NV | 838039 | 1
January | OR | 446528 | 0
January | IA | 474501 | 0.25
January | DC | 628496 | 0.5
January | WI | 679382 | 0.75
January | NV | 871824 | 1
March | IA | 460282 | 0
March | OR | 481935 | 0.25
March | DC | 716063 | 0.5
March | WI | 771575 | 0.75
March | NV | 970878 | 1
(15 rows)
以下示例计算了不同职位每名员工的工资百分比排名。
=> SELECT job_title, employee_last_name, annual_salary,
PERCENT_RANK()
OVER (PARTITION BY job_title ORDER BY annual_salary DESC) AS percent_rank
FROM employee_dimension
ORDER BY percent_rank, annual_salary;
job_title | employee_last_name | annual_salary | percent_rank
--------------------+--------------------+---------------+---------------------
Cashier | Fortin | 3196 | 0
Delivery Person | Garnett | 3196 | 0
Cashier | Vogel | 3196 | 0
Customer Service | Sanchez | 3198 | 0
Shelf Stocker | Jones | 3198 | 0
Custodian | Li | 3198 | 0
Customer Service | Kramer | 3198 | 0
Greeter | McNulty | 3198 | 0
Greeter | Greenwood | 3198 | 0
Shift Manager | Miller | 99817 | 0
Advertising | Vu | 99853 | 0
Branch Manager | Jackson | 99858 | 0
Marketing | Taylor | 99928 | 0
Assistant Director | King | 99973 | 0
Sales | Kramer | 99973 | 0
Head of PR | Goldberg | 199067 | 0
Regional Manager | Gauthier | 199744 | 0
Director of HR | Moore | 199896 | 0
Head of Marketing | Overstreet | 199955 | 0
VP of Advertising | Meyer | 199975 | 0
VP of Sales | Sanchez | 199992 | 0
Founder | Gauthier | 927335 | 0
CEO | Taylor | 953373 | 0
Investor | Garnett | 963104 | 0
Co-Founder | Vu | 977716 | 0
CFO | Vogel | 983634 | 0
President | Sanchez | 992363 | 0
Delivery Person | Li | 3194 | 0.00114155251141553
Delivery Person | Robinson | 3194 | 0.00114155251141553
Custodian | McCabe | 3192 | 0.00126582278481013
Shelf Stocker | Moore | 3196 | 0.00128040973111396
Branch Manager | Moore | 99716 | 0.00186567164179104
...
另请参阅
27 - PERCENTILE_CONT [analytic]
一个逆分布函数，在此函数中，对于每行，PERCENTILE_CONT 将返回
窗口内每个分区中的一组值中落入指定百分位的值。例如，如果函数的实参值为 0.5，则函数的结果为数据集的中间值（第 50 个百分位）。PERCENTILE_CONT 假设了连续分布的数据模型。忽略 NULL 值。
PERCENTILE_CONT 通过首先计算百分位存在的行的行数来计算百分位。例如：
row‑number = 1 + percentile‑value * (num‑partition‑rows -1)
如果 row‑number 为完整数（误差范围 0.00001），则百分位为行 row‑number 的值。
否则，Vertica 会在
CEILING(row‑number) 行的值和
FLOOR(row‑number) 行的值之间插入百分位值。换句话说，将按以下方式计算百分位：
( CEILING( row‑number) - row‑number ) * ( value of FLOOR(row‑number) row )
+ ( row‑number - FLOOR(row‑number) ) * ( value of CEILING(row‑number) row)
注意
如果百分位值为 0.5，则 PERCENTILE_CONT 会返回与函数
MEDIAN 相同的结果集。
行为类型
不可变
语法
PERCENTILE_CONT ( percentile ) WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] ) OVER ( [ window-partition-clause ] )
参数
- percentile
- 百分位值，为 0 到 1（包含）范围内的 FLOAT 常数。
-
WITHIN GROUP (ORDER BY expression)
- 指定如何对每个组内的数据进行排序。ORDER BY 仅使用数据类型为 INTEGER、FLOAT、INTERVAL 或 NUMERIC 的列/表达式。NULL 值将被丢弃。
WITHIN GROUP(ORDER BY) 子句不能保证 SQL 结果的顺序。要对最终结果进行排序，请使用 SQL ORDER BY 子句集。
ASC | DESC- 将排序顺序指定为升序（默认值）或降序。
只要 percentile 不是 0.5，在 WITHIN GROUP 子句中指定 ASC 或 DESC 会影响到结果。
OVER()- 请参阅分析函数
示例
该查询会计算威斯康星和哥伦比亚区的前 300 位客户的每组的年收入中间值。
=> SELECT customer_state, customer_key, annual_income, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY annual_income)
OVER (PARTITION BY customer_state) AS PERCENTILE_CONT
FROM customer_dimension WHERE customer_state IN ('DC','WI') AND customer_key < 300
ORDER BY customer_state, customer_key;
customer_state | customer_key | annual_income | PERCENTILE_CONT
----------------+--------------+---------------+-----------------
DC | 52 | 168312 | 483266.5
DC | 118 | 798221 | 483266.5
WI | 62 | 283043 | 377691
WI | 139 | 472339 | 377691
(4 rows)
该查询会计算威斯康星和哥伦比亚区全部客户的每组的年收入中间值。
=> SELECT customer_state, customer_key, annual_income, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY annual_income)
OVER (PARTITION BY customer_state) AS PERCENTILE_CONT
FROM customer_dimension WHERE customer_state IN ('DC','WI') ORDER BY customer_state, customer_key;
customer_state | customer_key | annual_income | PERCENTILE_CONT
----------------+--------------+---------------+-----------------
DC | 52 | 168312 | 483266.5
DC | 118 | 798221 | 483266.5
DC | 622 | 220782 | 555088
DC | 951 | 178453 | 555088
DC | 972 | 961582 | 555088
DC | 1286 | 760445 | 555088
DC | 1434 | 44836 | 555088
...
WI | 62 | 283043 | 377691
WI | 139 | 472339 | 377691
WI | 359 | 42242 | 517717
WI | 364 | 867543 | 517717
WI | 403 | 509031 | 517717
WI | 455 | 32000 | 517717
WI | 485 | 373129 | 517717
...
(1353 rows)
另请参阅
28 - PERCENTILE_DISC [analytic]
一个逆分布函数，在此函数中，对于每行，PERCENTILE_DISC 将返回
窗口内每个分区中的一组值中落入指定百分位的值。 PERCENTILE_DISC() 假设一个离散分布数据模型。 NULL 值将被忽略。
PERCENTILE_DISC 会检查每组中的累计离散值，直到找到大于或等于指定百分位的值。Vertica 会计算每一行的百分位，PERCENTILE_DISC 会输出 WITHIN GROUP(ORDER BY) 列的第一个值，其 CUME_DIST（累计离散）值大于等于实参 FLOAT 值（如 0.4）：
PERCENTILE_DIST(0.4) WITHIN GROUP (ORDER BY salary) OVER(PARTITION BY deptno)...
给定以下查询：
SELECT CUME_DIST() OVER(ORDER BY salary) FROM table-name;
最小的大于 0.4 的 CUME_DIST 值也为 PERCENTILE_DISC。
行为类型
不可变
语法
PERCENTILE_DISC ( percentile ) WITHIN GROUP (
ORDER BY expression [ ASC | DESC ] ) OVER (
[ window-partition-clause ] )
参数
- percentile
- 百分位值，为 0 到 1（包含）范围内的
FLOAT 常数。
-
WITHIN GROUP(ORDER BY expression)
- 指定如何对每个组内的数据进行排序。
ORDER BY 仅使用数据类型为 INTEGER、FLOAT、INTERVAL 或 NUMERIC 的列/表达式。 NULL 值将被丢弃。
WITHIN GROUP(ORDER BY) 子句不能保证 SQL 结果的顺序。要对最终结果进行排序，请使用 SQL
ORDER BY 子句集。
ASC | DESC- 将排序顺序指定为升序（默认值）或降序。
OVER()- 请参阅分析函数
示例
该查询会计算威斯康星和哥伦比亚区的前 300 位客户的每组的第 20 个百分值年收入。
=> SELECT customer_state, customer_key, annual_income,
PERCENTILE_DISC(.2) WITHIN GROUP(ORDER BY annual_income)
OVER (PARTITION BY customer_state) AS PERCENTILE_DISC
FROM customer_dimension
WHERE customer_state IN ('DC','WI')
AND customer_key < 300
ORDER BY customer_state, customer_key;
customer_state | customer_key | annual_income | PERCENTILE_DISC
----------------+--------------+---------------+-----------------
DC | 104 | 658383 | 417092
DC | 168 | 417092 | 417092
DC | 245 | 670205 | 417092
WI | 106 | 227279 | 227279
WI | 127 | 703889 | 227279
WI | 209 | 458607 | 227279
(6 rows)
另请参阅
29 - RANK [analytic]
在每个窗口分区内，按窗口的 ORDER BY 子句指定的顺序对查询结果集中的所有行进行排名。
RANK 按以下方式执行：
-
按 ORDER BY 子句指定的顺序对分区行进行排序。
-
比较前一行与当前行的 ORDER BY 值，并按以下方式对当前行进行排名：
最大排名值等于查询返回的总行数。
行为类型
不可变
语法
RANK() OVER (
[ window-partition-clause ]
window-order-clause )
参数
OVER()- 请参阅分析函数
与 DENSE_RANK 比较
RANK 会在排名序列中留下间隙，而
DENSE_RANK 不会出现这种情况。
示例
以下查询按州对自 2007 年以来成为公司客户的所有客户进行排名。在 customer_since 日期相同的行中，RANK 将为这些行分配相同的排名。当 customer_since 日期发生更改时，RANK 会跳过一个或多个排名 — 例如，在 CA 内从第 12 名到跳到第 14 名，以及从第 17 名跳到第 19 名。
=> SELECT customer_state, customer_name, customer_since,
RANK() OVER (PARTITION BY customer_state ORDER BY customer_since) AS rank
FROM customer_dimension WHERE customer_type='Company' AND customer_since > '01/01/2007'
ORDER BY customer_state;
customer_state | customer_name | customer_since | rank
----------------+---------------+----------------+------
AZ | Foodshop | 2007-01-20 | 1
AZ | Goldstar | 2007-08-11 | 2
CA | Metahope | 2007-01-05 | 1
CA | Foodgen | 2007-02-05 | 2
CA | Infohope | 2007-02-09 | 3
CA | Foodcom | 2007-02-19 | 4
CA | Amerihope | 2007-02-22 | 5
CA | Infostar | 2007-03-05 | 6
CA | Intracare | 2007-03-14 | 7
CA | Infocare | 2007-04-07 | 8
...
CO | Goldtech | 2007-02-19 | 1
CT | Foodmedia | 2007-02-11 | 1
CT | Metatech | 2007-02-20 | 2
CT | Infocorp | 2007-04-10 | 3
...
另请参阅
SQL 分析
30 - ROW_NUMBER [analytic]
为
窗口分区中的每一行分配一系列唯一编号，从 1 开始。通常情况下，ROW_NUMBER 和 RANK 可以互换，但有以下区别：
行为类型
不可变
语法
ROW_NUMBER () OVER (
[ window-partition-clause ]
[ window-order-clause ] )
参数
OVER()- 请参阅分析函数
示例
以下 ROW_NUMBER 查询按 customer_region 对 VMart 表 customer_dimension 中的客户进行分区。在每个分区中，该函数按其窗口顺序子句指定的资历顺序对这些客户进行排名：
=> SELECT * FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY customer_region ORDER BY customer_since) AS most_senior,
customer_region, customer_name, customer_since FROM public.customer_dimension WHERE customer_type = 'Individual') sq
WHERE most_senior <= 5;
most_senior | customer_region | customer_name | customer_since
-------------+-----------------+----------------------+----------------
1 | West | Jack Y. Perkins | 1965-01-01
2 | West | Linda Q. Winkler | 1965-01-02
3 | West | Marcus K. Li | 1965-01-03
4 | West | Carla R. Jones | 1965-01-07
5 | West | Seth P. Young | 1965-01-09
1 | East | Kim O. Vu | 1965-01-01
2 | East | Alexandra L. Weaver | 1965-01-02
3 | East | Steve L. Webber | 1965-01-04
4 | East | Thom Y. Li | 1965-01-05
5 | East | Martha B. Farmer | 1965-01-07
1 | SouthWest | Martha V. Gauthier | 1965-01-01
2 | SouthWest | Jessica U. Goldberg | 1965-01-07
3 | SouthWest | Robert O. Stein | 1965-01-07
4 | SouthWest | Emily I. McCabe | 1965-01-18
5 | SouthWest | Jack E. Miller | 1965-01-25
1 | NorthWest | Julie O. Greenwood | 1965-01-08
2 | NorthWest | Amy X. McNulty | 1965-01-25
3 | NorthWest | Kevin S. Carcetti | 1965-02-09
4 | NorthWest | Sam K. Carcetti | 1965-03-16
5 | NorthWest | Alexandra X. Winkler | 1965-04-05
1 | MidWest | Michael Y. Meyer | 1965-01-01
2 | MidWest | Joanna W. Bauer | 1965-01-06
3 | MidWest | Amy E. Harris | 1965-01-08
4 | MidWest | Julie W. McCabe | 1965-01-09
5 | MidWest | William . Peterson | 1965-01-09
1 | South | Dean . Martin | 1965-01-01
2 | South | Ruth U. Williams | 1965-01-02
3 | South | Steve Y. Farmer | 1965-01-03
4 | South | Mark V. King | 1965-01-08
5 | South | Lucas Y. Young | 1965-01-10
(30 rows)
另请参阅
31 - STDDEV [analytic]
计算当前行相对于
窗口内组的统计样本标准偏差。 STDDEV_SAMP 返回值与为
VAR_SAMP 函数定义的方差的平方根相同：
STDDEV( expression ) = SQRT(VAR_SAMP( expression ))
当 VAR_SAMP 返回 NULL 时，此函数将返回 NULL。
行为类型
不可变
语法
STDDEV ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 任何
NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回的数据类型与参数的数字数据类型相同。
OVER()- 请参阅分析函数
示例
以下示例返回雇员维度表中副经理的薪资标准差：
=> SELECT employee_last_name, annual_salary,
STDDEV(annual_salary) OVER (ORDER BY hire_date) as "stddev"
FROM employee_dimension
WHERE job_title = 'Assistant Director';
employee_last_name | annual_salary | stddev
--------------------+---------------+------------------
Bauer | 85003 | NaN
Reyes | 91051 | 4276.58181261624
Overstreet | 53296 | 20278.6923394976
Gauthier | 97216 | 19543.7184537642
Jones | 82320 | 16928.0764028285
Fortin | 56166 | 18400.2738421652
Carcetti | 71135 | 16968.9453554483
Weaver | 74419 | 15729.0709901852
Stein | 85689 | 15040.5909495309
McNulty | 69423 | 14401.1524291943
Webber | 99091 | 15256.3160166536
Meyer | 74774 | 14588.6126417355
Garnett | 82169 | 14008.7223268494
Roy | 76974 | 13466.1270356647
Dobisz | 83486 | 13040.4887828347
Martin | 99702 | 13637.6804131055
Martin | 73589 | 13299.2838158566
...
另请参阅
32 - STDDEV_POP [analytic]
计算统计总体标准差，并返回
窗口内总体方差的平方根。STDDEV_POP() 返回值与 VAR_POP() 函数的平方根相同：
STDDEV_POP( expression ) = SQRT(VAR_POP( expression ))
VAR_POP 返回 Null 时，STDDEV_POP 返回 Null。
行为类型
不可变
语法
STDDEV_POP ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 任何
NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回的数据类型与参数的数字数据类型相同。
OVER()- 请参阅分析函数。
示例
以下示例返回雇员维度表中副经理的薪资总体标准差：
=> SELECT employee_last_name, annual_salary,
STDDEV_POP(annual_salary) OVER (ORDER BY hire_date) as "stddev_pop"
FROM employee_dimension WHERE job_title = 'Assistant Director';
employee_last_name | annual_salary | stddev_pop
--------------------+---------------+------------------
Goldberg | 61859 | 0
Miller | 79582 | 8861.5
Goldberg | 74236 | 7422.74712548456
Campbell | 66426 | 6850.22125098891
Moore | 66630 | 6322.08223926257
Nguyen | 53530 | 8356.55480080699
Harris | 74115 | 8122.72288970008
Lang | 59981 | 8053.54776538731
Farmer | 60597 | 7858.70140687825
Nguyen | 78941 | 8360.63150784682
另请参阅
33 - STDDEV_SAMP [analytic]
计算当前行相对于
窗口内组的统计样本标准偏差。STDDEV_SAM的返回值与为 VAR_SAMP 函数定义的方差的平方根相同：
STDDEV( expression ) = SQRT(VAR_SAMP( expression ))
当 VAR_SAMP 返回 NULL 时，STDDEV_SAMP 将返回 NULL。
注意
STDDEV_SAMP() 在语义上与非标准函数
STDDEV() 相同。
行为类型
不可变
语法
STDDEV_SAMP ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 任何
NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回与实参数字数据类型相同的数据类型。
OVER()- 请参阅分析函数
示例
以下示例返回 employee 维度表中副经理薪资的样本标准差：
=> SELECT employee_last_name, annual_salary,
STDDEV(annual_salary) OVER (ORDER BY hire_date) as "stddev_samp"
FROM employee_dimension WHERE job_title = 'Assistant Director';
employee_last_name | annual_salary | stddev_samp
--------------------+---------------+------------------
Bauer | 85003 | NaN
Reyes | 91051 | 4276.58181261624
Overstreet | 53296 | 20278.6923394976
Gauthier | 97216 | 19543.7184537642
Jones | 82320 | 16928.0764028285
Fortin | 56166 | 18400.2738421652
Carcetti | 71135 | 16968.9453554483
Weaver | 74419 | 15729.0709901852
Stein | 85689 | 15040.5909495309
McNulty | 69423 | 14401.1524291943
Webber | 99091 | 15256.3160166536
Meyer | 74774 | 14588.6126417355
Garnett | 82169 | 14008.7223268494
Roy | 76974 | 13466.1270356647
Dobisz | 83486 | 13040.4887828347
...
另请参阅
34 - SUM [analytic]
计算
窗口内一组行的表达式总和。它将返回浮点表达式的 DOUBLE PRECISION 值。否则，返回值与表达式数据类型相同。
行为类型
不可变
语法
SUM ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 任何
NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回的数据类型与参数的数字数据类型相同。
OVER()- 请参阅分析函数
溢出处理
如果在使用 SUM 时遇到数据溢出问题，请使用
SUM_FLOAT 将数据转换为浮点。
默认情况下，当您对数值数据类型调用此函数时，Vertica 允许静默数值溢出。有关此行为以及如何更改它的更多信息，请参阅SUM、SUM_FLOAT 和 AVG 的数字数据类型溢出。
示例
以下查询返回店面 1 月份所有收益的累计和：
=> SELECT calendar_month_name AS month, transaction_type, sales_quantity,
SUM(sales_quantity)
OVER (PARTITION BY calendar_month_name ORDER BY date_dimension.date_key) AS SUM
FROM store.store_sales_fact JOIN date_dimension
USING(date_key) WHERE calendar_month_name IN ('January')
AND transaction_type= 'return';
month | transaction_type | sales_quantity | SUM
---------+------------------+----------------+------
January | return | 7 | 651
January | return | 3 | 651
January | return | 7 | 651
January | return | 7 | 651
January | return | 7 | 651
January | return | 3 | 651
January | return | 7 | 651
January | return | 5 | 651
January | return | 1 | 651
January | return | 6 | 651
January | return | 6 | 651
January | return | 3 | 651
January | return | 9 | 651
January | return | 7 | 651
January | return | 6 | 651
January | return | 8 | 651
January | return | 7 | 651
January | return | 2 | 651
January | return | 4 | 651
January | return | 5 | 651
January | return | 7 | 651
January | return | 8 | 651
January | return | 4 | 651
January | return | 10 | 651
January | return | 6 | 651
...
另请参阅
35 - VAR_POP [analytic]
返回
窗口内组中非 null 数字集（忽略 null 值）的统计总体方差。结果的计算方法为 expression 与 expression 均值之差的平方和除以剩余行数：
(SUM( expression * expression ) - SUM( expression ) * SUM( expression ) / COUNT( expression )) / COUNT( expression )
行为类型
不可变
语法
VAR_POP ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 任何
NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回的数据类型与参数的数字数据类型相同
OVER()- 请参阅分析函数
示例
下述示例计算 2007 年 1 月商店销售事实表的累计总体方差：
=> SELECT date_ordered,
VAR_POP(SUM(total_order_cost))
OVER (ORDER BY date_ordered) "var_pop"
FROM store.store_orders_fact s
WHERE date_ordered BETWEEN '2007-01-01' AND '2007-01-31'
GROUP BY s.date_ordered;
date_ordered | var_pop
--------------+------------------
2007-01-01 | 0
2007-01-02 | 89870400
2007-01-03 | 3470302472
2007-01-04 | 4466755450.6875
2007-01-05 | 3816904780.80078
2007-01-06 | 25438212385.25
2007-01-07 | 22168747513.1016
2007-01-08 | 23445191012.7344
2007-01-09 | 39292879603.1113
2007-01-10 | 48080574326.9609
(10 rows)
另请参阅
36 - VAR_SAMP [analytic]
为
窗口内组中的每行返回非 NULL 数字集（将忽略集中的 NULL 值）的样本方差。结果的计算方法如下所示：
(SUM( expression * expression ) - SUM( expression ) * SUM( expression ) / COUNT( expression ) )
/ (COUNT( expression ) - 1 )
此函数与
VARIANCE 在一个方面有所不同：对于某个元素的输入集，VARIANCE 返回 0，而 VAR_SAMP 返回 NULL。
行为类型
不可变
语法
VAR_SAMP ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 任何
NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回的数据类型与参数的数字数据类型相同
OVER()- 请参阅分析函数
Null 处理
示例
下述示例计算 2007 年 12 月商店销售订单事实表的样本方差：
=> SELECT date_ordered,
VAR_SAMP(SUM(total_order_cost))
OVER (ORDER BY date_ordered) "var_samp"
FROM store.store_orders_fact s
WHERE date_ordered BETWEEN '2007-12-01' AND '2007-12-31'
GROUP BY s.date_ordered;
date_ordered | var_samp
--------------+------------------
2007-12-01 | NaN
2007-12-02 | 90642601088
2007-12-03 | 48030548449.3359
2007-12-04 | 32740062504.2461
2007-12-05 | 32100319112.6992
2007-12-06 | 26274166814.668
2007-12-07 | 23017490251.9062
2007-12-08 | 21099374085.1406
2007-12-09 | 27462205977.9453
2007-12-10 | 26288687564.1758
(10 rows)
另请参阅
37 - VARIANCE [analytic]
为
窗口内组中的每行返回非 NULL 数字集（将忽略集中的 NULL 值）的样本方差。结果的计算方法如下所示：
( SUM( expression * expression ) - SUM( expression ) * SUM( expression ) / COUNT( expression )) / (COUNT( expression ) - 1 )
VARIANCE 返回 expression 的方差，按以下方式计算：
注意
提供非标准函数
VARIANCE，以便和其他数据库兼容。它在语义上与
VAR_SAMP 相同。
行为类型
不可变
语法
VAR_SAMP ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 任何 NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回的数据类型与参数的数字数据类型相同。
OVER()- 请参阅分析函数
示例
下述示例计算 2007 年 12 月商店销售订单事实表的累计方差：
=> SELECT date_ordered,
VARIANCE(SUM(total_order_cost))
OVER (ORDER BY date_ordered) "variance"
FROM store.store_orders_fact s
WHERE date_ordered BETWEEN '2007-12-01' AND '2007-12-31'
GROUP BY s.date_ordered;
date_ordered | variance
--------------+------------------
2007-12-01 | NaN
2007-12-02 | 2259129762
2007-12-03 | 1809012182.33301
2007-12-04 | 35138165568.25
2007-12-05 | 26644110029.3003
2007-12-06 | 25943125234
2007-12-07 | 23178202223.9048
2007-12-08 | 21940268901.1431
2007-12-09 | 21487676799.6108
2007-12-10 | 21521358853.4331
(10 rows)
另请参阅