这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

SELECT

从一个或多个数据源返回结果集：表、视图、联接表和已命名查询。

语法

[ AT epoch ] [ WITH-clause ] SELECT [ ALL | DISTINCT ]
    { * | { MATCH_COLUMNS('pattern') | expression [ [AS] alias ] }[,...] }
    [ into-table-clause ]
    [ from-clause ]
    [ where-clause ]
    [ time-series-clause ]
    [ group-by-clause[,...] ]
    [ having-clause[,...] ]
    [ match-clause ]
    [ union-clause ]
    [ intersect-clause ]
    [ except-clause ]
    [ order-by-clause [ offset-clause ]]
    [ limit-clause ]
    [ FOR UPDATE [ OF table-name[,...] ] ]

参数

AT epoch
返回指定时期的数据，其中 epoch 为以下几项之一：

  • EPOCH LATEST：返回数据直到当前时期（但不包括当前时期）。结果集包括来自最新提交的 DML 事务的数据。

  • EPOCH integer：返回数据直到 integer 指定的时期（包括该指定时期）。

  • TIME 'timestamp'：从 timestamp 指定的时期返回数据。

有关 Vertica 如何使用时期的详细信息，请参阅时期。

有关详细信息，请参阅历史查询。

ALL | DISTINCT

  • ALL （默认值）：保留结果集或组中的重复行。

  • DISTINCT：移除结果集或组中的重复行。

ALL 或 DISTINCT 限定符必须紧跟在 SELECT 关键字后。只能有一个关键字实例出现在选择列表中。
*
列出查询的表中的所有列。
MATCH_COLUMNS('pattern')
返回查询的表中与模式匹配的所有列。
expression [[AS] alias]
一个表达式，通常解析为查询的表中的列数据，例如，在 FROM 子句中指定的 列的名称；此外：

您可以选择性地为每个列表达式分配一个临时别名，并在 SELECT 语句的其他位置（例如，在查询谓词或 ORDER BY 子句中）引用该别名。Vertica 使用别名作为查询输出中的列标题。

FOR UPDATE
指定对查询中指定的所有表获取 X 锁定，最常在 READ COMMITTED 隔离中使用。

FOR UPDATE 需要对查询的表具有更新/删除权限，且无法从只读事务中发出。

特权

非超级用户：

  • 对架构的 USAGE 权限

  • 对表或视图的 SELECT 权限

示例

当多个客户端在以下示例查询中运行事务时，如果未使用 FOR UPDATE，则将发生死锁。两个事务获取 S 锁，当两者尝试升级到 X 锁时，将遇到死锁：

=> SELECT balance FROM accounts WHERE account_id=3476 FOR UPDATE;
    ...
=> UPDATE accounts SET balance = balance+10 WHERE account_id=3476;
=> COMMIT;

另请参阅

1 - EXCEPT 子句

合并两个或多个 SELECT 查询。EXCEPT 返回在右侧查询中未查找到的左侧查询特定结果。

语法

SELECT
    EXCEPT except‑query[...]
    [ ORDER BY { column-name  | ordinal-number } [ ASC | DESC ] [,...] ]
    [ LIMIT { integer | ALL } ]
    [ OFFSET integer ]

注意

  • 使用 EXCEPT 子句可从 SELECT 语句中筛选出特定结果。EXCEPT 查询对两个或更多 SELECT 查询的结果执行操作。它只返回不同时存在于右侧查询中的左侧查询中的行。

  • 除非用括号另作指示，否则 Vertica 会从左向右评估同一 SELECT 查询中的多个 EXCEPT 子句。

  • 不能将 ALL 关键字与 EXCEPT 查询一起使用。

  • 各 SELECT 语句的结果必须是可合并的。各语句必须返回相同数量的列，且对应列的数据类型必须兼容。例如，不能对 INTEGER 类型的列和 VARCHAR 类型的列使用 EXCEPT 子句。如果语句不满足这些条件，Vertica 将返回错误。

  • 您可以在 FROM、WHERE 和 HAVING 子句中使用 EXCEPT。

  • 您可以通过在语句中加入 ORDER BY 操作的方式对 EXCEPT 操作的结果进行排序。编写 ORDER BY 列表时，请指定最左侧 SELECT 语句中的列名称，或者指定指示排序依据列的位置的整数。

  • 由于最右侧的查询指定 Vertica 对 EXCEPT 操作的结果执行操作，因此 EXCEPT 查询中最右侧的 ORDER BY、LIMIT 或 OFFSET 子句不需要加括号。包含在 SELECT 查询中且之前出现在 EXCEPT 查询中的任何 ORDER BY、LIMIT 或 OFFSET 子句都必须括在圆括号中。

  • Vertica 支持 EXCEPT 非相关子查询谓词。例如：

    => SELECT * FROM T1
   WHERE T1.x IN
      (SELECT MAX(c1) FROM T2
       EXCEPT
          SELECT MAX(cc1) FROM T3
       EXCEPT 
          SELECT MAX(d1) FROM T4);

示例

假设有下面三个表：

Company_A

  Id  | emp_lname |      dept      | sales
------+-----------+----------------+-------
 1234 | Stephen   | auto parts     |  1000
 5678 | Alice     | auto parts     |  2500
 9012 | Katherine | floral         |   500
 3214 | Smithson  | sporting goods |  1500
(4 rows)

Company_B

  Id  | emp_lname |    dept     | sales
------+-----------+-------------+-------
 4321 | Marvin    | home goods  |   250
 8765 | Bob       | electronics | 20000
 9012 | Katherine | home goods  |   500
 3214 | Smithson  | home goods  |  1500
(4 rows)

Company_C

  Id  | emp_lname |      dept      | sales
------+-----------+----------------+-------
 3214 | Smithson  | sporting goods |  1500
 5432 | Madison   | sporting goods |   400
 7865 | Cleveland | outdoor        |  1500
 1234 | Stephen   | floral         |  1000
(4 rows)

以下查询将返回存在于 Company_A 内但不存在于 Company_B 内的员工的 ID 和姓氏：

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B;
  id  | emp_lname
------+-----------
 1234 | Stephen
 5678 | Alice
(2 rows)

以下查询将按照员工的姓氏对上一查询的结果进行排序：

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B
   ORDER BY emp_lname ASC;
  id  | emp_lname
------+-----------
 5678 | Alice
 1234 | Stephen
(2 rows)

如果按列位置排序，查询将返回相同结果：

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B
   ORDER BY 2 ASC;
  id  | emp_lname
------+-----------
 5678 | Alice
 1234 | Stephen
(2 rows)

以下查询将返回存在于 Company_A 内但不存在于 Company_B 或 Company_C 内的员工的 ID 和姓氏：

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B
   EXCEPT
   SELECT id, emp_lname FROM Company_C;
  id  | emp_lname
------+-----------
 5678 | Alice
(1 row)

以下查询将显示不匹配数据类型的结果：

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT emp_lname, id FROM Company_B;
ERROR 3429:  For 'EXCEPT', types int and varchar are inconsistent
DETAIL:  Columns: id and emp_lname

以下示例使用 VMart 示例数据库，将返回通过商店购买商品且购买金额已超过 500 美元的康涅狄格州客户，但不包括现金支付的客户：

=> SELECT customer_key, customer_name FROM public.customer_dimension
      WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact
         WHERE sales_dollar_amount > 500
         EXCEPT
         SELECT customer_key FROM store.store_sales_fact
         WHERE tender_type = 'Cash')
      AND customer_state = 'CT';
 customer_key |    customer_name
--------------+----------------------
        15084 | Doug V. Lampert
        21730 | Juanita F. Peterson
        24412 | Mary U. Garnett
        25840 | Ben Z. Taylor
        29940 | Brian B. Dobisz
        32225 | Ruth T. McNulty
        33127 | Darlene Y. Rodriguez
        40000 | Steve L. Lewis
        44383 | Amy G. Jones
        46495 | Kevin H. Taylor
(10 rows)

另请参阅

2 - FROM 子句

要查询的数据源的逗号分隔列表。

语法

FROM dataset[,...] [ TABLESAMPLE(percent) ]

参数

dataset
要查询的一组数据，为以下几项之一：
TABLESAMPLE(percent)
指定返回记录的随机采样，其中 percent 指定近似采样大小。percent 值必须介于 0 和 100 之间（不含这两个值），且可以包含十进制值。无法保证返回的记录数是指定的确切百分比。

数据的所有行被选中的机会均等。Vertica 在应用其他查询筛选器之前执行采样。

示例

统计 customer_dimension 表中的所有记录：

=> SELECT COUNT(*) FROM customer_dimension;
 COUNT
-------
 50000
(1 row)

返回表 customer_dimension 中一小部分行的采样：

=> SELECT customer_name, customer_state FROM customer_dimension TABLESAMPLE(0.5) WHERE customer_state='IL';
    customer_name    | customer_state
---------------------+----------------
 Amy Y. McNulty      | IL
 Daniel C. Nguyen    | IL
 Midori O. Greenwood | IL
 Meghan U. Lampert   | IL
 Tiffany Y. Lang     | IL
 Laura S. King       | IL
 Steve T. Nguyen     | IL
 Craig S. Webber     | IL
 Luigi A. Lewis      | IL
 Mark W. Williams    | IL
(10 rows)

2.1 - Table-reference

语法

[[database.]schema.]table[ [AS] alias]

参数

[database.]schema

数据库和架构。默认架构为 public。如果指定一个数据库，它必须是当前数据库。

表
逻辑架构中的表。
[AS] alias
用于引用 table 的临时名称。

2.2 - Joined-table

指定如何联接表。

语法

table‑reference [join-type] JOIN table-reference[ TABLESAMPLE(sampling‑pct) ] [ ON join‑predicate ]

参数

table‑reference
表或其他的 joined-table。
join-type
有效值：
TABLESAMPLE
指定采用简单的随机采样返回近似的记录百分比。总潜在返回集中的所有行包含在采样中的机会均等。Vertica 在应用查询中的其他筛选器之前执行此采样操作。无法保证返回的记录数是 sampling‑pct 定义的确切的记录百分比。

TABLESAMPLE 选项仅可用于用户定义的表和数据收集器 (DC) 表。不支持视图和系统表。

sampling‑pct
指定作为采样的一部分返回的记录百分比。该值必须大于 0 且小于 100。
开启 join‑predicate
等值联接基于联接表中的一个或多个列。其对于 NATURAL 和 CROSS 联接无效，对于所有其他联接类型都是必需的。

备选联接语法选项

Vertica 支持两种较旧的联接语法约定：

  • WHERE 子句中的联接谓词指定的表联接

  • USING 子句指定的表联接

有关详细信息，请参阅联接语法。

示例

下列 SELECT 语句使用 TABLESAMPLE 选项限定其 JOIN 子句：

=> SELECT user_id.id, user_name.name FROM user_name TABLESAMPLE(50)
     JOIN user_id TABLESAMPLE(50) ON user_name.id = user_id.id;
  id  |  name
------+--------
  489 | Markus
 2234 | Cato
  763 | Pompey
(3 rows)

3 - GROUP BY 子句

在 GROUP BY 中将 SELECT 子句与聚合函数一起使用可以收集多个记录中的数据。Vertica 将结果分组成为一个或多个与表达式匹配的行集。

不包含聚合的 GROUP BY 子句的使用方式类似于 SELECT DISTINCT。

ROLLUP 是对 GROUP BY 子句的扩展。 ROLLUP 执行小计聚合。

语法

GROUP BY [/*+GBYTYPE(algorithm)*/] { expression | aggregate‑expression }[,...]

参数

/*+GBYTYPE(algorithm)*/
指定实现 GROUP BY 子句时优先级高于 Vertica 查询优化器可能选择的算法的算法。可以将 algorithm 设置为以下值之一：

  • HASH：GROUPBY HASH 算法

  • PIPE：GROUPBY PIPELINED 算法

有关两种算法的详细信息，请参阅 GROUP BY 实施选项。

表达式
任何表达式，包括 FROM 子句中指定的表中的常数和列引用。例如： 
column,... column, (expression)
aggregate‑expression
列、表达式、CUBE, GROUPING SETS 或 ROLLUP 聚合按顺序排列的列表。

可以将 CUBE 和 ROLLUP 聚合包括在一个 GROUPING SETS 聚合内。 CUBE 和 ROLLUP 聚合可产生大量输出。在这种情况下，使用 GROUPING SETS 仅返回某些结果。

不能将任何聚合包含在 CUBE 或 ROLLUP 表达式中。

可以将多个 GROUPING SETS、CUBE 或 ROLLUP 聚合附加到同一个查询中。例如：

  
GROUP BY a,b,c,d, ROLLUP(a,b)
GROUP BY a,b,c,d, CUBE((a,b),c,d)
GROUP BY a,b,c,d, CUBE(a,b), ROLLUP (c,d)
GROUP BY ROLLUP(a), CUBE(b), GROUPING SETS(c)
GROUP BY a,b,c,d, GROUPING SETS ((a,d),(b,c),CUBE(a,b))
GROUP BY a,b,c,d, GROUPING SETS ((a,d),(b,c),(a,b),(a),(b),())

用法注意事项

  • 表达式不能包括聚合函数。但是，可以将 GROUP BY 子句与 CUBE、GROUPING SETS 和 ROLLUP 一起使用，以返回每个组的汇总值。

  • 创建 GROUP BY 子句时，必须包括 SELECT 列表中显示的所有非聚合列。

  • 如果 GROUP BY 子句包括 WHERE 子句，Vertica 会忽略所有不满足 WHERE 子句的行。

示例

此示例显示了如何将 WHERE 子句与 GROUP BY 一起使用。在这种情况下，示例检索姓氏以 S 开头的所有员工，忽略所有不符合此条件的行。GROUP BY 子句使用 ILIKE 函数仅检索以 S 开头的姓氏。聚合函数 SUM 计算每个组的总休假天数。

=> SELECT employee_last_name, SUM(vacation_days)
   FROM employee_dimension
   WHERE employee_last_name ILIKE 'S%'
   GROUP BY employee_last_name;
 employee_last_name | SUM
--------------------+------
 Sanchez            | 2892
 Smith              | 2672
 Stein              | 2660
(3 rows)

以下示例中的 GROUP BY 子句按供应商地区和供应商地区的最大交易对结果分组：


=> SELECT vendor_region, MAX(deal_size) AS "Biggest Deal"
   FROM vendor_dimension
   GROUP BY vendor_region;
 vendor_region | Biggest Deal
---------------+--------------
 East          |       990889
 MidWest       |       699163
 NorthWest     |        76101
 South         |       854136
 SouthWest     |       609807
 West          |       964005
(6 rows)

以下查询使用 HAVING 子句修改上一个查询，该子句指定仅返回最大交易规模超过 $900,000 的组：

=> SELECT vendor_region, MAX(deal_size) as "Biggest Deal"
   FROM vendor_dimension
   GROUP BY vendor_region
   HAVING MAX(deal_size) > 900000;
 vendor_region | Biggest Deal
---------------+--------------
 East          |       990889
 West          |       964005
(2 rows)

可以将 GROUP BY 子句与标量类型的一维数组一起使用。在以下示例中，grants 为 ARRAY[VARCHAR]，grant_values 为 ARRAY[INT]。

=> CREATE TABLE employees (id INT, department VARCHAR(50), grants ARRAY[VARCHAR], grant_values ARRAY[INT]);

=> COPY employees FROM STDIN;
42|Physics|[US-7376,DARPA-1567]|[65000,135000]
36|Physics|[US-7376,DARPA-1567]|[10000,25000]
33|Physics|[US-7376]|[30000]
36|Astronomy|[US-7376,DARPA-1567]|[5000,4000]
\.

=> SELECT department, grants, SUM(apply_sum(grant_values)) FROM employees GROUP BY grants, department;
 department |          grants          |  SUM
------------+--------------------------+--------
 Physics    | ["US-7376","DARPA-1567"] | 235000
 Astronomy  | ["US-7376","DARPA-1567"] |   9000
 Physics    | ["US-7376"]              |  30000
(3 rows)

不包含聚合的 GROUP BY 子句的使用方式类似于 SELECT DISTINCT。例如，以下两个查询返回相同的结果：

=> SELECT DISTINCT household_id FROM customer_dimension;

=> SELECT household_id FROM customer_dimension GROUP BY household_id;

另请参阅

3.1 - ROLLUP 聚合

作为 GROUP BY 子句的扩展来自动执行小计聚合。 ROLLUP 在单个 SQL 查询内的不同级别跨多个维度执行聚合。

您可以将 ROLLUP 子句与三个分组函数一起使用：

语法

ROLLUP grouping-expression[,...]

参数

group‑expression
以下两项中的一个或两个：

  • 一个包含常数和 FROM 指定的表中的列引用且不是聚合或分组函数的表达式。例如：

    column1, (column2+1), column3+column4

  • 一个多级表达式，是以下几项之一：

    • ROLLUP

    • CUBE

    • GROUPING SETS

限制

GROUP BY ROLLUP 不会对结果排序。要对数据进行排序，ORDER BY 子句必须跟在 GROUP BY 子句之后。

聚合级别

如果 n 为分组列的数量，则 ROLLUP 将生成 n+1 个小计和总计级别。由于 ROLLUP 将删除每一步中最右侧的列，所以请仔细指定列顺序。

假设 ROLLUP(A, B, C) 创建 4 个组：

  • (A, B, C)

  • (A, B)

  • (A)

  • ()

由于 ROLLUP 将删除每一步中最右侧的列，所以没有 (A, C) 和 (B, C) 组。

如果将 2 个或更多的列括在圆括号内，GROUP BY 会将其视为单一实体。例如：

  • ROLLUP(A, B, C) 将创建 4 个组：

    
(A, B, C)
(A, B)
(A)
()

  • ROLLUP((A, B), C) 会将 (A, B) 视为单一实体并创建 3 个组：

    (A, B, C)
(A, B)
()

示例：聚合完整数据集

以下示例显示了如何使用 GROUP BY 子句确定几年内家庭用电和书籍的开支。SUM 聚合函数计算每年在各类别上的总开销。

假设您有一个表，其中包含关于家庭书籍和用电开支的信息：

=> SELECT * FROM expenses ORDER BY Category, Year;
 Year |  Category   | Amount
------+-------------+--------
2005  | Books       |  39.98
2007  | Books       |  29.99
2008  | Books       |  29.99
2005  | Electricity | 109.99
2006  | Electricity | 109.99
2007  | Electricity | 229.98

对于 expenses 表，ROLLUP 计算 2005–2007 年之间各类别的小计：

  • 书籍：$99.96

  • 用电：$449.96

  • 总计：$549.92。

使用 ORDER BY 子句对结果排序：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY ROLLUP(Category, Year) ORDER BY 1,2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96
             |      | 549.92

示例：将 ROLLUP 与 HAVING 子句一起使用

此示例说明如何将 ROLLUP 与 HAVING 子句一起使用，以限制 GROUP BY 结果。以下查询只生成 ROLLUP 类别，其中 year 根据 GROUPING 函数中的表达式得出小计：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY ROLLUP(Category,Year) HAVING GROUPING(Year)=1
   ORDER BY 1, 2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       |      |  99.96
 Electricity |      | 449.96
             |      | 549.92

下一个示例对 (Category, Year) 而非完整结果进行汇总。GROUPING_ID 函数指定聚合 3 个以下的级别：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY ROLLUP(Category,Year) HAVING GROUPING_ID(Category,Year)<3
   ORDER BY 1, 2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96

另请参阅

3.2 - CUBE 聚合

作为 GROUP BY 子句的扩展，自动对指定列执行所有可能的聚合。

您可以将 ROLLUP 子句与三个分组函数一起使用：

语法

GROUP BY group-expression[,...]

参数

group-expression
以下两项中的一个或两个：

  • 一个包含常数和 FROM 指定的表中的列引用且不是聚合或分组函数的表达式。例如：

    column1, (column2+1), column3+column4

  • 一个多级表达式，是以下几项之一：

    • ROLLUP

    • CUBE

    • GROUPING SETS

限制

  • GROUP BY CUBE 不对数据排序。如果要对数据排序，请使用 ORDER BY 子句。ORDER BY 子句必须跟在 GROUP BY 子句的后面。
  • CUBE 可以用在 GROUPING SETS 表达式中，但不可以用在 ROLLUP 表达式或另一个 CUBE 表达式中。

CUBE 聚合级别

如果 n 是指分组列的数量，则 CUBE 将生成 2n 个聚合级别。例如：

CUBE (A, B, C) 将生成所有可能的分组，即产生以下八个组：

  • (A, B, C)
  • (A, B)
  • (A, C)
  • (B, C)
  • (A)
  • (B)
  • (C)
  • ()

如果增加 CUBE 列数，则 CUBE 分组数会呈指数增加。CUBE 查询可能会占用大量资源，并产生不感兴趣的组合。在这种情况下，请考虑使用 GROUPING SETS 聚合，因为通过它可以选择特定的分组。

示例

使用 CUBE 返回所有分组

假设您有一个表，其中包含关于家庭书籍和用电开支的信息：

=> SELECT * FROM expenses ORDER BY Category, Year;
 Year |  Category   | Amount
------+-------------+--------
2005  | Books       |  39.98
2007  | Books       |  29.99
2008  | Books       |  29.99
2005  | Electricity | 109.99
2006  | Electricity | 109.99
2007  | Electricity | 229.98

使用 CUBE 聚合按类别和年份聚合数据：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY CUBE(Category, Year) ORDER BY 1, 2, GROUPING_ID();
 Category    | Year |  SUM
-------------+------+--------
 Books       | 2005 |  39.98
 Books       | 2007 |  29.99
 Books       | 2008 |  29.99
 Books       |      |  99.96
 Electricity | 2005 | 109.99
 Electricity | 2006 | 109.99
 Electricity | 2007 | 229.98
 Electricity |      | 449.96
             | 2005 | 149.97
             | 2006 | 109.99
             | 2007 | 259.97
             | 2008 |  29.99
             |      | 549.92

结果会包含每个类别和年份的小计以及总计 ($549.92)。

将 CUBE 与 HAVING 子句一起使用

此示例显示了如何限制 GROUP BY 结果以及如何将 CUBE 聚合与 HAVING 子句结合使用。此查询仅返回类别合计和总计：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY CUBE(Category,Year) HAVING GROUPING(Year)=1;
 Category    | Year |  SUM
-------------+------+--------
 Books       |      |  99.96
 Electricity |      | 449.96
             |      | 549.92

下一个查询仅返回每个年度两种类别的聚合。GROUPING ID 函数指定忽略总计 ($549.92)：

=> SELECT Category, Year, SUM (Amount) FROM expenses
   GROUP BY CUBE(Category,Year) HAVING GROUPING_ID(Category,Year)<2
   ORDER BY 1, 2, GROUPING_ID();
 Category   | Year |  SUM
------------+------+--------
 Books      | 2005 |  39.98
 Books      | 2007 |  29.99
 Books      | 2008 |  29.99
 Books      |      |  99.96
 Electrical | 2005 | 109.99
 Electrical | 2006 | 109.99
 Electrical | 2007 | 229.98
 Electrical |      | 449.96

另请参阅

3.3 - GROUPING SETS 聚合

GROUPING SETS 聚合是对在您指定的分组上自动执行小计聚合的 GROUP BY 子句的扩展。

您可以将 GROUPING SETS 子句与三个分组函数一起使用：

若要对数据排序，请使用 ORDER BY 子句。ORDER BY 子句必须跟在 GROUP BY 子句之后。

语法

GROUP BY group-expression[,...]

参数

group-expression
以下两项中的一个或两个：

  • 一个包含常数和 FROM 指定的表中的列引用且不是聚合或分组函数的表达式。例如：

    column1, (column2+1), column3+column4

  • 一个多级表达式，是以下几项之一：

    • ROLLUP

    • CUBE

    • GROUPING SETS

定义分组

GROUPING SETS 允许您准确指定结果中需要哪些组。您还可以按照以下方式联接分组：

以下示例子句的分组结果将会显示。

示例：选择分组

此示例显示了如何仅选择您需要的分组。假设您仅希望聚合列，并且您不需要总计。第一个查询会忽略合计。在第二个查询中，您将 () 添加到 GROUPING SETS 列表以获得合计。使用 ORDER BY 子句按分组对结果进行排序：

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY GROUPING SETS((Category, Year), (Year))
   ORDER BY 1, 2, GROUPING_ID();
  Category  | Year |  SUM
------------+------+--------
 Books      | 2005 |  39.98
 Books      | 2007 |  29.99
 Books      | 2008 |  29.99
 Electrical | 2005 | 109.99
 Electrical | 2006 | 109.99
 Electrical | 2007 | 229.98
            | 2005 | 149.97
            | 2006 | 109.99
            | 2007 | 259.97
            | 2008 |  29.99

=> SELECT Category, Year, SUM(Amount) FROM expenses
   GROUP BY GROUPING SETS((Category, Year), (Year), ())
   ORDER BY 1, 2, GROUPING_ID();
  Category  | Year |  SUM
------------+------+--------
 Books      | 2005 |  39.98
 Books      | 2007 |  29.99
 Books      | 2008 |  29.99
 Electrical | 2005 | 109.99
 Electrical | 2006 | 109.99
 Electrical | 2007 | 229.98
            | 2005 | 149.97
            | 2006 | 109.99
            | 2007 | 259.97
            | 2008 |  29.99
            |      | 549.92

另请参阅

4 - HAVING 子句

筛选 GROUP BY 子句的结果。根据语义，HAVING 子句必须发生在 GROUP BY 操作之后。该子句已添加到 SQL 标准，因为 WHERE 子句无法指定聚合函数。

语法

HAVING condition[,...]

参数

condition
明确引用分组列，除非引用显示在聚合函数中。

示例

以下示例返回薪资大于 $800,000 的员工：


=> SELECT employee_last_name, MAX(annual_salary) as highest_salary FROM employee_dimension
     GROUP BY employee_last_name HAVING MAX(annual_salary) > 800000 ORDER BY highest_salary DESC;
 employee_last_name | highest_salary
--------------------+----------------
 Sanchez            |         992363
 Vogel              |         983634
 Vu                 |         977716
 Lewis              |         957949
 Taylor             |         953373
 King               |         937765
 Gauthier           |         927335
 Garnett            |         903104
 Bauer              |         901181
 Jones              |         885395
 Rodriguez          |         861647
 Young              |         846657
 Greenwood          |         837543
 Overstreet         |         831317
 Garcia             |         811231
(15 rows)

5 - INTERSECT 子句

计算两个或更多 SELECT 查询结果的交集。INTERSECT 返回 INTERSECT 操作数左右两侧查询的唯一值。

语法

select‑stmt
    INTERSECT query[...]
    [ order-by-clause  [ offset-clause ]]
    [ limit-clause ]

注意

  • INTERSECT 子句返回 SELECT 查询结果的交集。INTERSECT 查询对两个或更多 SELECT 查询的结果执行操作。INTERSECT 仅返回所有指定查询所得到的行。

  • 不能将 ALL 关键字与 INTERSECT 查询一起使用。

  • 每个 SELECT 查询的结果必须是联集兼容的；必须返回相同数目的列，而且相应的列必须含有兼容的数据类型。例如，不能对 INTERSECT 类型的列和 VARCHAR 类型的列使用 EXCEPT 子句。如果 SELECT 查询不满足这些条件，Vertica 将返回错误。

  • 使用 ORDER BY 子句为 INTERSECT 操作结果进行排序。在 ORDER BY 列表中，指定最左侧 SELECT 语句的列，或指定所按照排序列的所在位置。

  • 您可以在 FROM、WHERE 和 HAVING 子句中使用 INTERSECT。

  • 由于最右侧的查询指定 Vertica 对 INTERSECT 操作的结果执行操作，因此 INTERSECT 查询的 ORDER BY、LIMIT 或者 OFFSET 子句不需要加括号。包含在 SELECT 查询中且之前出现在 INTERSECT 查询中的任何 ORDER BY、LIMIT 或 OFFSET 子句都必须括在圆括号中。

  • 排序列名称来自于第一个 select。

  • Vertica 支持 INTERSECT 非相关子查询谓词。例如：

    => SELECT * FROM T1
   WHERE T1.x IN
      (SELECT MAX(c1) FROM T2
       INTERSECT
          SELECT MAX(cc1) FROM T3
       INTERSECT
          SELECT MAX(d1) FROM T4);

示例

假设有下面三个表：

Company_A

id       emp_lname     dept          sales
------+------------+----------------+-------
1234  | Stephen    | auto parts     | 1000
5678  | Alice      | auto parts     | 2500
9012  | Katherine  | floral         |  500
3214  | Smithson   | sporting goods | 1500

Company_B

id       emp_lname     dept        sales
------+------------+-------------+-------
4321  | Marvin     | home goods  |   250
9012  | Katherine  | home goods  |   500
8765  | Bob        | electronics | 20000
3214  | Smithson   | home goods  |  1500

Company_C

  id  | emp_lname |      dept      | sales
------+-----------+----------------+-------
 3214 | Smithson  | sporting goods |  1500
 5432 | Madison   | sporting goods |   400
 7865 | Cleveland | outdoor        |  1500
 1234 | Stephen   | floral         |  1000

以下查询返回 Company_A 和 Company_B 中都包含的 ID 号以及员工姓氏：

=> SELECT id, emp_lname FROM Company_A
   INTERSECT
   SELECT id, emp_lname FROM Company_B;
 id   | emp_lname
------+-----------
 3214 | Smithson
 9012 | Katherine
(2 rows)

接下来的查询返回相同的两个员工，销售额降序排列：

=> SELECT id, emp_lname, sales FROM Company_A
   INTERSECT
   SELECT id, emp_lname, sales FROM Company_B
   ORDER BY sales DESC;
  id  | emp_lname | sales
------+-----------+-------
 3214 | Smithson  |  1500
 9012 | Katherine |   500
(2 rows)

接下来的查询返回同时为两个公司工作而在 Company_B 公司中的销售额超过 1000 的员工姓名：

=> SELECT id, emp_lname, sales FROM Company_A
   INTERSECT
   (SELECT id, emp_lname, sales FROM company_B WHERE sales > 1000)
   ORDER BY sales DESC;
  id  | emp_lname | sales
------+-----------+-------
 3214 | Smithson  |  1500
(1 row)

接下来的查询返回同时为三个公司工作的员工 ID 号以及员工姓氏。

=> SELECT id, emp_lname FROM Company_A
    INTERSECT
   SELECT id, emp_lname FROM Company_B
   INTERSECT
   SELECT id, emp_lname FROM Company_C;
  id  | emp_lname
------+-----------
 3214 | Smithson
(1 row)

接下来的查询返回的是不匹配的数据类型结果；这两个查询不是联集兼容的：

=> SELECT id, emp_lname FROM Company_A
    INTERSECT
   SELECT emp_lname, id FROM Company_B;
ERROR 3429:  For 'INTERSECT', types int and varchar are inconsistent
DETAIL:  Columns: id and emp_lname

使用 VMart 示例数据库，接下来的查询返回有关通过在线销售渠道购买商品且购买金额超过 400 美元和 500 美金的所有康涅狄格州客户的信息：

=> SELECT customer_key, customer_name from public.customer_dimension
       WHERE customer_key IN (SELECT customer_key
         FROM online_sales.online_sales_fact
         WHERE sales_dollar_amount > 400
         INTERSECT
         SELECT customer_key FROM online_sales.online_sales_fact
         WHERE sales_dollar_amount > 500)
      AND customer_state = 'CT' ORDER BY customer_key;
 customer_key |     customer_name
--------------+------------------------
           39 | Sarah S. Winkler
           44 | Meghan H. Overstreet
           70 | Jack X. Cleveland
          103 | Alexandra I. Vu
          110 | Matt . Farmer
          173 | Mary R. Reyes
          188 | Steve G. Williams
          233 | Theodore V. McNulty
          250 | Marcus E. Williams
          294 | Samantha V. Young
          313 | Meghan P. Pavlov
          375 | Sally N. Vu
          384 | Emily R. Smith
          387 | Emily L. Garcia
...

上一个查询和下一个查询是等效的，并且返回相同的结果：

=> SELECT customer_key,customer_name FROM public.customer_dimension
       WHERE customer_key IN (SELECT customer_key
      FROM online_sales.online_sales_fact
         WHERE sales_dollar_amount > 400
         AND sales_dollar_amount < 500)
   AND customer_state = 'CT' ORDER BY customer_key;

另请参阅

6 - INTO TABLE 子句

从查询结果集中创建表。

语法

永久表：

INTO [TABLE] [[database.]schema.]table

临时表：

INTO [scope] TEMP[ORARY] [TABLE] [[database.]schema.]table
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]

参数

scope
指定临时表定义的可见性：

  • GLOBAL （默认值）：表定义对所有会话可见，并且持续存在，直到显式删除了该表。

  • LOCAL：表定义仅对在其中创建该表定义的会话可见，并在会话结束后删除。

不论此设置如何，都可通过 ON COMMIT DELETE ROWS 和 ON COMMIT PRESERVE ROWS 关键字（见下文）设置临时表数据保留策略。

有关详细信息，请参阅创建临时表。

[database.]schema

数据库和架构。默认架构为 public。如果指定一个数据库，它必须是当前数据库。

表
要创建的表的名称。
ON COMMIT { DELETE | PRESERVE } ROWS
指定数据为事务范围还是会话范围数据：

  • DELETE （默认）会将临时表标记为事务范围数据。每次提交之后，Vertica 会移除所有表数据。

  • PRESERVE 会将临时表标记为会话范围数据，这些数据在单个事务结束后仍会保存。但会话结束后，Vertica 会移除所有表数据。

示例

以下 SELECT 语句包含一个 INTO TABLE 子句，该子句使用 customer_dimension 创建表 newTable：

=> SELECT * INTO TABLE newTable FROM customer_dimension;

以下 SELECT 语句将创建临时表 newTempTable。默认情况下，临时表是在全局范围创建的，因此该表定义对其他会话可见并一直存在，直到其被显式删除。不会将 customer_dimension 数据复制到新表中，且 Vertica 会发出相应的警告：

=> SELECT * INTO TEMP TABLE newTempTable FROM customer_dimension;
WARNING 4102:  No rows are inserted into table "public"."newTempTable" because
  ON COMMIT DELETE ROWS is the default for create temporary table
HINT:  Use "ON COMMIT PRESERVE ROWS" to preserve the data in temporary table
CREATE TABLE

以下 SELECT 语句将创建本地临时表 newTempTableLocal。表仅对在其中创建该表的会话可见，并在会话结束后自动删除。INTO TABLE 子句包括 ON COMMIT PRESERVE ROWS，因此 Vertica 会将所有选择数据复制到新表中：

=> SELECT * INTO LOCAL TEMP TABLE newTempTableLocal ON COMMIT PRESERVE ROWS
     FROM customer_dimension;
CREATE TABLE

7 - LIMIT 子句

指定要从整个结果集或从分区结果集的窗口返回的结果集行数最大值。

语法

应用于整个结果集：

   LIMIT { num-rows | ALL }

应用于分区结果集的窗口：

   LIMIT num‑rows OVER ( PARTITION BY {{< codevar >}}column‑expr‑x{{< /codevar >}}, ORDER BY {{< codevar >}}column‑expr‑y{{< /codevar >}} [ASC | DESC]

参数

num‑rows
要返回的行的最大数量。
ALL
返回所有行，仅当 LIMIT 应用于整个结果集时才有效。
OVER()
指定如何就当前行而言对输入数据进行分区和排序。输入数据是查询在对 FROM、WHERE、GROUP BY 和 HAVING 子句求值之后返回的结果集。

有关详细信息，请参阅下面的将 LIMIT 与窗口分区结合使用。

限制返回的行

LIMIT 指定只返回已查询数据集中的前 k 行。行优先级通过查询的 ORDER BY 子句确定。

例如，以下查询将返回表 customer_dimension 中的前 10 行数据，按列 store_region 和 number_of_employees 的顺序排列：

=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees
     FROM store.store_dimension WHERE number_of_employees <= 12 ORDER BY store_region, number_of_employees LIMIT 10;
 store_region |    location    | store_name | number_of_employees
--------------+----------------+------------+---------------------
 East         | Stamford, CT   | Store219   |                  12
 East         | New Haven, CT  | Store66    |                  12
 East         | New York, NY   | Store122   |                  12
 MidWest      | South Bend, IN | Store134   |                  10
 MidWest      | Evansville, IN | Store30    |                  11
 MidWest      | Green Bay, WI  | Store27    |                  12
 South        | Mesquite, TX   | Store124   |                  10
 South        | Cape Coral, FL | Store18    |                  11
 South        | Beaumont, TX   | Store226   |                  11
 South        | Houston, TX    | Store33    |                  11
(10 rows)

将 LIMIT 与窗口分区结合使用

您可以使用 LIMIT 对查询结果应用窗口分区，并限制每个窗口中返回的行数：

SELECT ... FROM dataset LIMIT num‑rows OVER ( PARTITION BY column‑expr‑x, ORDER BY column‑expr‑y [ASC | DESC] )

其中查询 dataset 在每个 column‑expr‑x 分区中返回具有最高或最低 column‑expr‑y 值的 num‑rows 个行。

例如，以下语句对表 store.store_dimension 进行了查询并在结果集上指定了窗口分区。LIMIT 设置为 2，所以每个窗口分区最多只能显示两行。OVER 子句指定按 store_region 对结果集进行分区，其中每个分区窗口显示某个区域中员工人数最少的两个商店：

=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees FROM store.store_dimension
     LIMIT 2 OVER (PARTITION BY store_region ORDER BY number_of_employees ASC);
 store_region |      location       | store_name | number_of_employees
--------------+---------------------+------------+---------------------
 West         | Norwalk, CA         | Store43    |                  10
 West         | Lancaster, CA       | Store95    |                  11
 East         | Stamford, CT        | Store219   |                  12
 East         | New York, NY        | Store122   |                  12
 SouthWest    | North Las Vegas, NV | Store170   |                  10
 SouthWest    | Phoenix, AZ         | Store228   |                  11
 NorthWest    | Bellevue, WA        | Store200   |                  19
 NorthWest    | Portland, OR        | Store39    |                  22
 MidWest      | South Bend, IN      | Store134   |                  10
 MidWest      | Evansville, IN      | Store30    |                  11
 South        | Mesquite, TX        | Store124   |                  10
 South        | Beaumont, TX        | Store226   |                  11
(12 rows)

8 - MATCH 子句

允许您以搜索事件模式筛选大量历史数据的 SQL 扩展， MATCH 子句为解析分区、排序提供子类，并基于您定义的模式匹配结果表的行。

您指定一个由 DEFINE 子句中定义的事件类型组成的模式作为正则表达式，其中每个事件都与输入表中的一行对应。接下来，您可以在输入事件序列内搜索模式。模式匹配返回符合 PATTERN 子句的连续行序列。例如，模式 P (A B* C) 由三个事件类型组成：A、B 和 C，当 Vertica 在输入表中找到匹配时，相关模式实例必须是一个 A 类型事件，且后接零个或多个 B 类型事件和一个 C 类型事件。

如果您想在点击流分析中根据用户的 Web 浏览行为（页面点击）确定用户的操作，那么模式匹配就尤其有用。有关详细信息，请参阅事件系列模式匹配。

语法

MATCH ( [ PARTITION BY table‑column ] ORDER BY table‑column
    DEFINE event‑name AS boolean‑expr [,...]
    PATTERN pattern‑name AS ( regexp )
    [ rows‑match‑clause ] )

参数

PARTITION BY
定义与 PATTERN 子句中定义的模式相匹配的窗口数据范围。分区子句通过 PATTERN 子句中定义的匹配模式将数据分区。使用 OEDER BY 子句对每个分区中的数据进行排序。如果忽略分区子句，则将整个数据集视为单个分区。
ORDER BY
定义与 PATTERN 子句中定义的模式相匹配的窗口数据范围。对于每个分区，顺序子句指定如何针对模式匹配对输入数据排序。
DEFINE
定义在正则表达式中构成事件类型的 boolean 表达式。例如： 
DEFINE
 Entry    AS RefURL  NOT ILIKE '%website2.com%' AND PageURL ILIKE
                               '%website2.com%',
 Onsite   AS PageURL ILIKE     '%website2.com%' AND Action='V',
  
 Purchase AS PageURL ILIKE     '%website2.com%' AND Action='P'

DEFINE 子句最多可接受 52 个事件。有关示例，请参阅 事件系列模式匹配。

event‑name
每一行需要进行评估的事件名称，如前面示例中的 Entry, Onsite, Purchase。
boolean‑expr
返回 true 或 false 的表达式。boolean_expr 可包含 布尔运算符 和相关 (comparison) 运算符。例如： 
Purchase AS PageURL ILIKE '%website2.com%' AND Action = 'P'
PATTERN pattern‑name
在 PATTERN 子句中定义的模式的名称，例如，P 是如下文定义的模式名称： 
 PATTERN P AS (...)

PATTERN 是由名称和正则表达式组成的搜索模式。

regexp
一个正则表达式，由 DEFINE 子句中定义的事件类型和下面的一个或多个限定符组成。当 Vertica 评估 MATCH 子句时，正则表达式会识别满足表达式条件的行。
rows‑match‑clause
指定如何解决单一行中评估为真的事件多于一个的问题，为以下几项之一：

  • ROWS MATCH ALL EVENTS：如果单一行中的多个事件评估为真，Vertica 将返回以下错误：

    ERROR: pattern events must be mutually exclusive
HINT:  try using ROWS MATCH FIRST EVENT

  • ROWS MATCH FIRST EVENT：如果给定行中的多个事件评估为真，Vertica 将使用该行的 SQL 语句中的第一个事件。

模式语意评估

  • SQL 子句的语意评估顺序是：FROM -> WHERE -> PATTERN MATCH -> SELECT。

  • 数据根据 PARTITION BY 子句的规定分区。如果忽略分区子句，则将整个数据集视为单个分区。

  • 对于每个分区，顺序子句指定如何针对模式匹配对输入数据排序。

  • 对每一行的事件进行评估。一行可以具有 0、1 或 N 个事件评估为真。如果同一行中评估为真的事件超过一个，除非您指定 ROWS MATCH FIRST EVENT，否则 Vertica 将返回一个运行时错误。如果指定了 ROWS MATCH FIRST EVENT，且单一行中评估为真的事件多于一个，Vertica 将选择 SQL 语句中第一个定义的事件用于行。

  • Vertica 通过查找符合 PATTERN 子句中定义模式的连续行序列执行模式匹配。

对于每一个匹配，Vertica 将输出匹配行。并非匹配一部分的行（不满足一个或多个术语）不输出。

  • Vertica 仅报告非重叠匹配。如果发生重叠，Vertica 将选择输入流中发现的第一个匹配。查找到匹配后，Vertica 会在前一次匹配结束后，开始搜索下一个匹配。

  • Vertica 将报告最长可能匹配，而不是匹配的子集。例如，考虑模式：AB（包含输入）：AAAB。因为 A 使用贪婪正则表达式量词 ()，所以 Vertica 会报告所有 A 的输入 (AAAB)，而不是 AAB、AB 或 B。

注释和限制

  • 模式匹配查询中不允许使用 DISTINCT 和 GROUP BY/HAVING 子句。

  • 下面的表达式不可用于 DEFINE 子句：

    • 子查询，如 DEFINE X AS c IN ELECT c FROM table

    • 分析函数，如 DEFINE X AS c <EA1) OVER (ORDER BY 1)

    • 聚合函数，如 DEFINE X AS c <A1)

  • 不可使用相同的模式名称定义不同的事件，例如，X 不允许执行下文：

    DEFINE   X AS c1 <  3
  X AS c1  >= 3

  • 与 MATCH 子句一起使用，Vertica MATCH 子句函数 提供关于模式的额外数据。例如，您可是使用函数返回表示与输入行相匹配的事件名称、匹配的序列号或匹配模式实例的分区范围内唯一标识符。

示例

例如，请参阅事件系列模式匹配。

另请参阅

8.1 - 事件系列模式匹配

搜索事件模式时，您可以使用 SQL MATCH 子句 语法来筛选大量历史数据。您可以将模式指定为正则表达式，然后可以在输入事件序列内搜索该模式。MATCH 提供了分析数据分区和排序的子句，以及对连续行集执行的模式匹配。

如果您想在点击流分析中根据用户的 Web 浏览行为（页面点击）确定用户的操作，那么模式匹配就尤其有用。典型的在线点击流漏斗是：

公司主页 -> 产品主页 -> 搜索 -> 结果 -> 在线购买

您可以使用此单击流漏斗，在用户的 Web 点击序列中搜索匹配并标识该用户：

  • 登录公司主页

  • 导航至产品页面

  • 运行查询

  • 单击搜索结果中的链接

  • 购买

单击流漏斗架构

此主题中的示例使用了此点击流漏斗以及以下 clickstream_log 表架构：

=> CREATE TABLE clickstream_log (
  uid INT,             --user ID
  sid INT,             --browsing session ID, produced by previous sessionization computation
  ts TIME,             --timestamp that occurred during the user's page visit
  refURL VARCHAR(20),  --URL of the page referencing PageURL
  pageURL VARCHAR(20), --URL of the page being visited
  action CHAR(1)       --action the user took after visiting the page ('P' = Purchase, 'V' = View)
);

INSERT INTO clickstream_log VALUES (1,100,'12:00','website1.com','website2.com/home', 'V');
INSERT INTO clickstream_log VALUES (1,100,'12:01','website2.com/home','website2.com/floby', 'V');
INSERT INTO clickstream_log VALUES (1,100,'12:02','website2.com/floby','website2.com/shamwow', 'V');
INSERT INTO clickstream_log values (1,100,'12:03','website2.com/shamwow','website2.com/buy', 'P');
INSERT INTO clickstream_log values (2,100,'12:10','website1.com','website2.com/home', 'V');
INSERT INTO clickstream_log values (2,100,'12:11','website2.com/home','website2.com/forks', 'V');
INSERT INTO clickstream_log values (2,100,'12:13','website2.com/forks','website2.com/buy', 'P');
COMMIT;

以下为 clickstream_log 表的输出：

=> SELECT * FROM clickstream_log;
 uid | sid |    ts    |        refURL        |       pageURL        | action
-----+-----+----------+----------------------+----------------------+--------
   1 | 100 | 12:00:00 | website1.com         | website2.com/home    | V
   1 | 100 | 12:01:00 | website2.com/home    | website2.com/floby   | V
   1 | 100 | 12:02:00 | website2.com/floby   | website2.com/shamwow | V
   1 | 100 | 12:03:00 | website2.com/shamwow | website2.com/buy     | P
   2 | 100 | 12:10:00 | website1.com         | website2.com/home    | V
   2 | 100 | 12:11:00 | website2.com/home    | website2.com/forks   | V
   2 | 100 | 12:13:00 | website2.com/forks   | website2.com/buy     | P
(7 rows)

示例

此示例包括 Vertica MATCH 子句函数，以便分析用户在 website2.com 上的浏览历史记录。它会确定用户执行以下任务的模式：

  • 从其他网站登录 website2.com（进入）

  • 浏览任意数量的其他页面（站内）

  • 做出购买（购买）

在以下语句中，模式 P (Entry Onsite* Purchase) 包括三种事件类型：进入、站内和购买。当 Vertica 在输入表中找到匹配时，相关模式实例必须是一个进入事件类型，且后跟零个或多个站内事件类型以及一个购买事件类型

=> SELECT uid,
       sid,
       ts,
       refurl,
       pageurl,
       action,
       event_name(),
       pattern_id(),
       match_id()
FROM clickstream_log
MATCH
  (PARTITION BY uid, sid ORDER BY ts
   DEFINE
     Entry    AS RefURL  NOT ILIKE '%website2.com%' AND PageURL ILIKE '%website2.com%',
     Onsite   AS PageURL ILIKE     '%website2.com%' AND Action='V',
     Purchase AS PageURL ILIKE     '%website2.com%' AND Action = 'P'
   PATTERN
     P AS (Entry Onsite* Purchase)
   ROWS MATCH FIRST EVENT);

在以下输出中，前四行代表用户 1 的浏览活动的模式，而剩下三行显示了用户 2 的浏览习惯。

 uid | sid |    ts    |        refurl        |       pageurl        | action | event_name | pattern_id | match_id
-----+-----+----------+----------------------+----------------------+--------+------------+------------+----------
   1 | 100 | 12:00:00 | website1.com         | website2.com/home    | V      | Entry      |          1 |        1
   1 | 100 | 12:01:00 | website2.com/home    | website2.com/floby   | V      | Onsite     |          1 |        2
   1 | 100 | 12:02:00 | website2.com/floby   | website2.com/shamwow | V      | Onsite     |          1 |        3
   1 | 100 | 12:03:00 | website2.com/shamwow | website2.com/buy     | P      | Purchase   |          1 |        4
   2 | 100 | 12:10:00 | website1.com         | website2.com/home    | V      | Entry      |          1 |        1
   2 | 100 | 12:11:00 | website2.com/home    | website2.com/forks   | V      | Onsite     |          1 |        2
   2 | 100 | 12:13:00 | website2.com/forks   | website2.com/buy     | P      | Purchase   |          1 |        3
(7 rows)

另请参阅

9 - MINUS 子句

MINUS 是 EXCEPT 的别名。

10 - OFFSET 子句

从结果集的开头忽略指定的行数。

语法

OFFSET rows

参数

start‑row
指定要包含在结果集中的第一行。前面的所有行都将被忽略。

依赖关系

  • 将 ORDER BY 子句与 OFFSET 一起使用。否则，查询将返回结果集的一个不确定子集。

  • 在 SELECT 语句或 UNION 子句中，OFFSET 必须跟在 ORDER BY 子句之后。

  • 当 SELECT 语句或 UNION 子句同时指定 LIMIT 和 OFFSET 时，Vertica 首先处理 OFFSET 语句，然后将 LIMIT 语句应用于其余行。

示例

以下查询从 customer_dimension 表中返回 14 行：

=> SELECT customer_name, customer_gender FROM customer_dimension
   WHERE occupation='Dancer' AND customer_city = 'San Francisco' ORDER BY customer_name;
    customer_name     | customer_gender
----------------------+-----------------
 Amy X. Lang          | Female
 Anna H. Li           | Female
 Brian O. Weaver      | Male
 Craig O. Pavlov      | Male
 Doug Z. Goldberg     | Male
 Harold S. Jones      | Male
 Jack E. Perkins      | Male
 Joseph W. Overstreet | Male
 Kevin . Campbell     | Male
 Raja Y. Wilson       | Male
 Samantha O. Brown    | Female
 Steve H. Gauthier    | Male
 William . Nielson    | Male
 William Z. Roy       | Male
(14 rows)

如果修改上一个查询以指定偏移量 8 (OFFSET 8)，Vertica 将跳过上一个结果集的前八行。查询会返回以下结果：

=> SELECT customer_name, customer_gender FROM customer_dimension
   WHERE occupation='Dancer' AND customer_city = 'San Francisco' ORDER BY customer_name OFFSET 8;
   customer_name   | customer_gender
-------------------+-----------------
 Kevin . Campbell  | Male
 Raja Y. Wilson    | Male
 Samantha O. Brown | Female
 Steve H. Gauthier | Male
 William . Nielson | Male
 William Z. Roy    | Male
(6 rows)

11 - ORDER BY 子句

对一个或多个列或列表达式的查询结果集进行排序。Vertica 使用当前区域设置和排序规则来对字符串值进行比较和排序。

语法

ORDER BY expression [ ASC | DESC ] [,...]

参数

表达式
以下几项之一：

  • SELECT 列表项的名称或序号。序号是指从左边开始计数的结果列的位置。这些序号用于对名称不唯一的列进行排序。序号对于分析函数的 OVER 子句的 ORDER BY 子句无效。

  • 未出现在 SELECT 列表中的列的任意表达式。

  • CASE 表达式。

ASC | DESC
指定是按升序还是降序对值进行排序。NULL 值是排序顺序中的第一个或最后一个，具体取决于数据类型：

  • INTEGER、INT、DATE/TIME：NULL 具有最小值。

  • FLOAT、BOOLEAN、CHAR、VARCHAR、ARRAY、SET：NULL 具有最大值

示例

下面的例子返回根据交易规模降序排列的客户媒体的全部城市和交易规模。

=> SELECT customer_city, deal_siz FROM customer_dimension WHERE customer_name = 'Metamedia'
   ORDER BY deal_size DESC;
  customer_city   | deal_size
------------------+-----------
 El Monte         |   4479561
 Athens           |   3815416
 Ventura          |   3792937
 Peoria           |   3227765
 Arvada           |   2671849
 Coral Springs    |   2643674
 Fontana          |   2374465
 Rancho Cucamonga |   2214002
 Wichita Falls    |   2117962
 Beaumont         |   1898295
 Arvada           |   1321897
 Waco             |   1026854
 Joliet           |    945404
 Hartford         |    445795
(14 rows)

以下示例使用转换函数。该示例返回了错误，因为 ORDER BY 列不在窗口分区中。

=> CREATE TABLE t(geom geometry(200), geog geography(200));
=> SELECT PolygonPoint(geom) OVER(PARTITION BY geom)
   AS SEL_0 FROM t ORDER BY geog;
ERROR 2521: Cannot specify anything other than user defined transforms and partitioning expressions in the ORDER BY list

以下示例使用同一个表更正了此错误。

=> SELECT PolygonPoint(geom) OVER(PARTITION BY geom)
   AS SEL_0 FROM t ORDER BY geom;

以下示例使用 ORDER BY 子句中的一个数组。

=> CREATE TABLE employees (id INT, department VARCHAR(50), grants ARRAY[VARCHAR], grant_values ARRAY[INT]);

=> COPY employees FROM STDIN;
42|Physics|[US-7376,DARPA-1567]|[65000,135000]
36|Physics|[US-7376,DARPA-1567]|[10000,25000]
33|Physics|[US-7376]|[30000]
36|Astronomy|[US-7376,DARPA-1567]|[5000,4000]
\.

=> SELECT * FROM employees ORDER BY grant_values;
 id | department |          grants          |  grant_values
----+------------+--------------------------+----------------
 36 | Astronomy  | ["US-7376","DARPA-1567"] | [5000,4000]
 36 | Physics    | ["US-7376","DARPA-1567"] | [10000,25000]
 33 | Physics    | ["US-7376"]              | [30000]
 42 | Physics    | ["US-7376","DARPA-1567"] | [65000,135000]
(4 rows)

12 - TIMESERIES 子句

空白填充和插值 (GFI) 计算，这是时序分析计算的一个重要组件。有关详细信息和示例，请参阅时序分析。

语法

TIMESERIES slice‑time AS 'length‑and‑time‑unit‑expr' OVER (
  [ PARTITION BY (column‑expr[,...] ] ORDER BY time‑expr ) [ ORDER BY table‑column[,...] ]

参数

slice‑time
TIMESERIES 子句产生的时间列，用于存储空白填充生成的时间片开始时间。

注意：此参数为别名，因此，您可以使用任何名称作为别名。

length‑and‑time‑unit‑expr
INTERVAL DAY TO SECOND 字面量，用于指定时间片计算的时间单位长度。例如： TIMESERIES slice_time AS '3 seconds' ...
OVER()
指定功能分区和排序。 OVER() 还指定时序函数在查询结果集上的操作—换而言之，评估 FROM、WHERE、GROUP BY 和 HAVING 子句之后返回的行。
PARTITION BY (column‑expr[,...] )
按指定的列表达式对数据进行分区。分别在每个分区上执行空白填充和插值。
ORDER BY time‑expr
按TIMESTAMP表达式 time-expr 对数据排序，以计算时序数据的时间信息。

注意

如果在 TIMESERIES OVER() 内没有为每个定义的时间片指定 window-partition-clause，则只产生一个输出记录；否则，则按每个时间片的每个分区产生一个输出记录。插值在该处计算。

如提供包含 TIMESERIES 子句的查询块，则以下是语义执行阶段（评估 FROM 和可选 WHERE 子句之后）：

  1. 计算 time-expression。

  2. 根据 time‑exp 和 'length‑and‑time‑unit‑expr' 的结果在每个输入记录上执行与 TIME_SLICE() 函数一样的计算。

    1. 执行空白填充以生成输入记录中缺失的时间片。

    2. 将此计算结果命名为 slice_time，表示空白填充之后生成的“时序”列（别名）。

  3. 按 expression、slice‑time 对数据分区。针对每个分区执行步骤 4。

  4. 按 time‑expr 对数据排序。插值在此处计算。

TIMESERIES 子句和 TIME_SLICE 函数之间存在语义重叠，主要区别如下：

  • TIMESERIES 仅支持间隔限定符DAY TO SECOND；不允许 YEAR TO MONTH。

  • 与 TIME_SLICE 不同，length‑and‑time‑unit‑expr 中表示的时间片长度和时间单位必须为常量，以便很好地界定时间片间隙。

  • TIMESERIES 执行空白填充，而 TIME_SLICE 函数则不执行。

  • TIME_SLICE 可以根据其第四个输入参数 (start‑or‑end) 的值来返回时间片的开始和结束时间。TIMESERIES，另一方面，始终返回每个时间片的开始时间。要输出每个时间片的结束时间，请写入 SELECT 语句，如下所示：

    => SELECT slice_time + <slice_length>;

限制

  • 在 SQL 查询块中发生 TIMESERIES 子句时，仅以下子句可用于相同查询块中：

    • SELECT

    • FROM

    • WHERE

    • ORDER BY

    不允许使用“GROUP BY”和“HAVING”子句。如果在间隙填充和插值 (GFI) 之前或之后需要执行“GROUP BY”操作，请使用子查询，并且将“GROUP BY”放入外部查询。例如： => SELECT symbol, AVG(first_bid) as avg_bid FROM ( SELECT symbol, slice_time, TS_FIRST_VALUE(bid1) AS first_bid FROM Tickstore WHERE symbol IN ('MSFT', 'IBM') TIMESERIES slice_time AS '5 seconds' OVER (PARTITION BY symbol ORDER BY ts) ) AS resultOfGFI GROUP BY symbol;

  • TIMESERIES 子句出现在 SQL 查询块时，SELECT 列表仅可包含以下：

    例如，以下两个查询返回语法错误，因为 bid1 不是 PARTITION BY 或 GROUP BY 列：

    => SELECT bid, symbol, TS_FIRST_VALUE(bid) FROM Tickstore
   TIMESERIES slice_time AS '5 seconds' OVER (PARTITION BY symbol ORDER BY ts);
   ERROR:  column "Tickstore.bid" must appear in the PARTITION BY list of Timeseries clause or be used in a Timeseries Output function
=> SELECT bid, symbol, AVG(bid) FROM Tickstore
   GROUP BY symbol;
   ERROR:  column "Tickstore.bid" must appear in the GROUP BY clause or be used in an aggregate function

示例

例如，请参阅空白填充和插值 (GFI)。

另请参阅

13 - UNION 子句

合并多个 SELECT 语句的结果。您也可以在 FROM、WHERE 和 HAVING 子句中包含 UNION。

语法

select‑stmt { UNION [ ALL | DISTINCT ] select-stmt }[...]
    [ order-by-clause  [ offset-clause ]]
    [ limit-clause ]

参数

select‑stmt
返回一行或多行的 SELECT 语句，具体取决于您指定的关键字是 DISTINCT 还是 ALL。

以下选项也适用：

DISTINCT | ALL
指定是否返回唯一行：

  • DISTINCT（默认）仅返回唯一行。

  • ALL 可连接所有行，包括重复行。为获得最佳性能，请使用 UNION ALL。

要求

  • UNION 结果集的所有行都必须存在于至少一个其 SELECT 语句的结果集中。

  • 每个 SELECT 语句必须指定相同的列数。

  • 对应的 SELECT 语句列的数据类型必须兼容，否则 Vertica 将返回错误。

UNION 中的 ORDER BY、LIMIT 和 OFFSET 子句

UNION 语句可以指定自己的 ORDER BY、LIMIT 和 OFFSET 子句。例如，对于以下示例中描述的表，以下查询将按 emp_name 对 UNION 结果集进行排序，并将输出限制为前两行：

=> SELECT id, emp_name FROM company_a UNION ALL SELECT id, emp_name FROM company_b ORDER BY emp_name LIMIT 2;
  id  | emp_name
------+----------
 5678 | Alice
 8765 | Bob
(2 rows)

UNION 子句中的每个 SELECT 语句都可以指定自己的 ORDER BY、LIMIT 和 OFFSET 子句。在这种情况下，SELECT 语句必须用括号括起来。Vertica 首先处理 SELECT 语句的 ORDER BY、LIMIT 和 OFFSET 子句，然后再处理 UNION 子句。

例如，以下 UNION 中的每个 SELECT 语句都指定了自己的 ORDER BY 和 LIMIT 子句。Vertica 处理单个查询，然后连接两个结果集：

=> (SELECT id, emp_name FROM company_a ORDER BY emp_name LIMIT 2)
   UNION ALL
   (SELECT id, emp_name FROM company_b ORDER BY emp_name LIMIT 2);
  id  | emp_name
------+-----------
 5678 | Alice
 9012 | Katherine
 8765 | Bob
 9012 | Katherine
(4 rows)

以下要求和限制用于确定 Vertica 处理包含 ORDER BY、LIMIT 和 OFFSET 子句的 UNION 子句的方式：

  • UNION 的 ORDER BY 子句必须使用第一个（最左侧的）SELECT 语句指定列。

  • 始终将 ORDER BY 子句与 LIMIT 和 OFFSET 一起使用。否则，查询将返回结果集的一个不确定子集。

  • ORDER BY 必须位于 LIMIT 和 OFFSET 之前。

  • 当 SELECT 或 UNION 语句同时指定 LIMIT 和 OFFSET 时，Vertica 首先处理 OFFSET 语句，然后将 LIMIT 语句应用于其余行。

非相关子查询中的 UNION

Vertica 支持非相关子查询谓词中的 UNION。例如：

=> SELECT DISTINCT customer_key, customer_name FROM public.customer_dimension WHERE customer_key IN
     (SELECT customer_key FROM store.store_sales_fact WHERE sales_dollar_amount > 500
      UNION ALL
      SELECT customer_key FROM online_sales.online_sales_fact WHERE sales_dollar_amount > 500)
   AND customer_state = 'CT';
 customer_key |     customer_name
--------------+------------------------
         7021 | Luigi T. Dobisz
         1971 | Betty V. Dobisz
        46284 | Ben C. Gauthier
        33885 | Tanya Y. Taylor
         5449 | Sarah O. Robinson
        29059 | Sally Z. Fortin
        11200 | Foodhope
        15582 | John J. McNulty
        24638 | Alexandra F. Jones
 ...

示例

使用这两个表的示例：

company_a

  ID    emp_name       dept       sales
------+------------+-------------+-------
1234  | Stephen    | auto parts  | 1000
5678  | Alice      | auto parts  | 2500
9012  | Katherine  | floral      |  500

company_b

  ID    emp_name       dept       sales
------+------------+-------------+-------
4321  | Marvin     | home goods  |   250
9012  | Katherine  | home goods  |   500
8765  | Bob        | electronics | 20000

从 company_a 和 company_b 中查找所有员工 ID 和姓名

UNION 语句指定选项 DISTINCT，将合并员工的唯一 ID 和姓氏；Katherine 同时为两个公司工作，所以她在结果集中只出现一次。DISTINCT 为默认值，可以忽略：

=> SELECT id, emp_name FROM company_a UNION DISTINCT SELECT id, emp_name FROM company_b ORDER BY id;
  id  | emp_name
------+-----------
 1234 | Stephen
 4321 | Marvin
 5678 | Alice
 8765 | Bob
 9012 | Katherine
(5 rows)

下一个 UNION 语句指定选项 ALL。Katherine 同时为两个公司工作，因此查询返回两条与她相关的记录：

=> SELECT id, emp_name FROM company_a UNION ALL SELECT id, emp_name FROM company_b ORDER BY id;
  id  | emp_name
------+-----------
 1234 | Stephen
 5678 | Alice
 9012 | Katherine
 4321 | Marvin
 9012 | Katherine
 8765 | Bob
(6 rows)

查找每个公司里成绩最好的两名销售人员

每个 SELECT 语句都指定了自己的 ORDER BY 和 LIMIT 子句，因此 UNION 语句将连接每个查询返回的结果集：

=> (SELECT id, emp_name, sales FROM company_a ORDER BY sales DESC LIMIT 2)
   UNION ALL
   (SELECT id, emp_name, sales FROM company_b ORDER BY sales DESC LIMIT 2);
  id  |  emp_name | sales
------+-----------+-------
 8765 | Bob       | 20000
 5678 | Alice     |  2500
 1234 | Stephen   |  1000
 9012 | Katherine |   500
(4 rows)

查找所有员工的销售排序

UNION 语句指定其自己的 ORDER BY 子句，Vertica 将其应用于整个结果：

=> SELECT id, emp_name, sales FROM company_a
   UNION
   SELECT id, emp_name, sales FROM company_b
   ORDER BY sales;
  id  |  emp_name | sales
------+-----------+-------
 4321 | Marvin    |   250
 9012 | Katherine |   500
 1234 | Stephen   |  1000
 5678 | Alice     |  2500
 8765 | Bob       | 20000
(5 rows)

计算各公司各部门的销售总量

每个 SELECT 语句都有自己的 GROUP BY 子句。UNION 将合并每个查询的聚合结果：

=> (SELECT 'Company A' as company, dept, SUM(sales) FROM company_a
    GROUP BY dept)
    UNION
   (SELECT 'Company B' as company, dept, SUM(sales) FROM company_b
    GROUP BY dept)
    ORDER BY 1;
 company   |    dept     |  sum
-----------+-------------+-------
 Company A | auto parts  |  3500
 Company A | floral      |   500
 Company B | electronics | 20000
 Company B | home goods  |   750
(4 rows)

另请参阅

14 - WHERE 子句

指定要包含在查询结果集中的行。

语法

WHERE boolean-expression [ subquery ]...

参数

boolean‑expression
返回 true 或 false 的表达式。结果集仅包括计算结果为 true 的行。该表达式可以包含布尔运算符和以下元素：

使用圆括号将表达式、谓词和布尔运算符进行分组。例如：

... WHERE NOT (A=1 AND B=2) OR C=3;

示例

以下示例返回所有名字以 Amer 字符串开头的东部地区顾客的名字：

=> SELECT DISTINCT customer_name
   FROM customer_dimension
   WHERE customer_region = 'East'
   AND customer_name ILIKE 'Amer%';
 customer_name
---------------
 Americare
 Americom
 Americore
 Americorp
 Ameridata
 Amerigen
 Amerihope
 Amerimedia
 Amerishop
 Ameristar
 Ameritech
(11 rows)

15 - WITH 子句

WITH 子句定义了一个或多个已命名公用表表达式 (CTE)，其中每个 CTE 均封装了一个结果集，该结果集可以被同一个 WITH 子句中的另一个 CTE 引用或被主要查询引用。Vertica 可以对每个引用执行 CTE（内联展开），或将结果集实体化作为临时表，以供其所有引用重复使用。在这两种情况下，WITH 子句都可以帮助简化复杂查询并避免语句重复。

语法

WITH [ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ ] [ RECURSIVE ] {
   cte‑identifier [ ( column-aliases ) ] AS (
   [ subordinate-WITH-clause ]
   query-expression )
} [,...]

参数

/*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/
启用当前 WITH 子句中所有查询的实体化。否则，实体化由配置参数 WithClauseMaterialization 设置，默认设置为 0（禁用）。如果禁用 WithClauseMaterialization，则 WITH 子句的主查询返回时会自动清除实体化。有关详细信息，请参阅WITH 子句的实体化。
RECURSIVE
指定通过重复执行嵌入的 UNION 或 UNION ALL 语句来迭代 WITH 子句的结果集。请参阅下面的递归查询。
cte‑identifier
标识 WITH 子句中的公用表表达式 (CTE)。此标识符可用于同一 WITH 子句中的 CTE，也可用于父 WITH 子句和子 WITH 子句（如果有）中的 CTE。最外层（主）WITH 子句的 CTE 标识符也可用于主要查询。

同一 WITH 子句的所有 CTE 标识符必须是唯一的。例如，以下 WITH 子句定义了两个 CTE，因此它们需要唯一的标识符： regional_sales 和 top_regions：

  
WITH
-- query sale amounts for each region
   regional_sales AS (SELECT ... ),
   top_regions AS ( SELECT ... )
   )
column-aliases
结果集列别名的逗号分隔列表。别名列表必须映射到 CTE 查询中的所有列表达式。如果忽略，则只有查询中使用的名称才能引用结果集列。

在以下示例中，revenue CTE 指定了两个列别名： vkey 和 total_revenue。这两个别名分别映射到列 vendor_key 和聚合表达式 SUM(total_order_cost)。主要查询将引用这些别名：

  
WITH revenue ( vkey, total_revenue ) AS (
   SELECT vendor_key, SUM(total_order_cost)
   FROM store.store_orders_fact
   GROUP BY vendor_key ORDER BY vendor_key)
  
SELECT v.vendor_name, v.vendor_address, v.vendor_city, r.total_revenue
FROM vendor_dimension v JOIN revenue r ON v.vendor_key = r.vkey
WHERE r.total_revenue = (SELECT MAX(total_revenue) FROM revenue )
ORDER BY vendor_name;
subordinate‑WITH‑clause
嵌套在当前 WITH 子句中的 WITH 子句。此 WITH 子句中的 CTE 只能引用同一子句中的 CTE，或者父 WITH 子句和子 WITH 子句中的 CTE。
query-expression
给定 CTE 的查询。

限制

WITH 子句仅支持 SELECT 和 INSERT 语句。它们不支持 UPDATE 或 DELETE 语句。

递归查询

包含 RECURSIVE 选项的 WITH 子句可以重复执行 UNION 或 UNION ALL 查询，从而迭代其自身的输出。递归查询在处理分层结构（例如，经理下属关系）或树状结构数据（如分类法）等自引用数据时十分有用。

配置参数 WithClauseRecursionLimit（默认设置为 8）将设置递归的最大深度。您可以分别使用 ALTER DATABASE 和 ALTER SESSION 在数据库和会话范围内设置此参数。递归将会继续，直到达到配置的最大深度为止，或者直到最后一次迭代返回没有数据为止。

可以按如下方式指定递归 WITH 子句：

WITH [ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ ] RECURSIVE
   cte‑identifier [ ( column-aliases ) ] AS (
     non-recursive-term
     UNION [ ALL ]
     recursive-term
   )

非递归项和递归项由 UNION 或 UNION ALL 分隔：

  • non-recursive-term 查询将其结果集设置在 cte-identifier，在 recursive-term 中递归。

  • UNION 语句的 recursive-term 以递归方式迭代其自身输出。当递归完成时，所有迭代的结果均会编译并在 cte-identifier 中设置。

限制

存在以下限制：

  • 非递归项的 SELECT 列表不能包含通配符 *（星号）或函数 MATCH_COLUMNS。

  • 递归项只能引用目标 CTE 一次。

  • 递归引用不能出现在外联接中。

  • 递归引用不能出现在子查询中。

  • WITH 子句不支持 UNION 选项 ORDER BY、LIMIT 和 OFFSET。

示例

包含一个 CTE 的单个 WITH 子句

下面的 SQL 定义了一个包含单个 CTE 的 WITH 子句 revenue，该子句将聚合表 store.store_orders_fact 中的数据。主要查询将引用两次 WITH 子句结果集：在其 JOIN 子句和谓词中：

-- define WITH clause
WITH revenue ( vkey, total_revenue ) AS (
      SELECT vendor_key, SUM(total_order_cost)
      FROM store.store_orders_fact
      GROUP BY vendor_key ORDER BY 1)
-- End WITH clause

-- primary query
SELECT v.vendor_name, v.vendor_address, v.vendor_city, r.total_revenue
FROM vendor_dimension v JOIN revenue r ON v.vendor_key = r.vkey
WHERE r.total_revenue = (SELECT MAX(total_revenue) FROM revenue )
ORDER BY vendor_name;
   vendor_name    | vendor_address | vendor_city | total_revenue
------------------+----------------+-------------+---------------
 Frozen Suppliers | 471 Mission St | Peoria      |      49877044
(1 row)

包含多个 CTE 的单个 WITH 子句

在以下示例中，WITH 子句包含两个 CTE：

  • regional_sales 每个地区的销售总量

  • top_regions 使用 regional_sales 的结果集确定销售总量最高的三个地区：

主要查询在 top_regions 结果集中按地区和部门聚合销量：


WITH
-- query sale amounts for each region
   regional_sales (region, total_sales) AS (
        SELECT sd.store_region, SUM(of.total_order_cost) AS total_sales
        FROM store.store_dimension sd JOIN store.store_orders_fact of ON sd.store_key = of.store_key
        GROUP BY store_region ),
-- query previous result set
   top_regions AS (
        SELECT region, total_sales
        FROM regional_sales ORDER BY total_sales DESC LIMIT 3
     )

-- primary query
-- aggregate sales in top_regions result set
SELECT sd.store_region AS region, pd.department_description AS department, SUM(of.total_order_cost) AS product_sales
FROM store.store_orders_fact of
JOIN store.store_dimension sd ON sd.store_key = of.store_key
JOIN public.product_dimension pd ON of.product_key = pd.product_key
WHERE sd.store_region IN (SELECT region FROM top_regions)
GROUP BY ROLLUP (region, department) ORDER BY region, product_sales DESC, GROUPING_ID();

 region  |            department            | product_sales
---------+----------------------------------+---------------
 East    |                                  |    1716917786
 East    | Meat                             |     189837962
 East    | Produce                          |     170607880
 East    | Photography                      |     162271618
 East    | Frozen Goods                     |     141077867
 East    | Gifts                            |     137604397
 East    | Bakery                           |     136497842
 East    | Liquor                           |     130410463
 East    | Canned Goods                     |     128683257
 East    | Cleaning supplies                |     118996326
 East    | Dairy                            |     118866901
 East    | Seafood                          |     109986665
 East    | Medical                          |     100404891
 East    | Pharmacy                         |      71671717
 MidWest |                                  |    1287550770
 MidWest | Meat                             |     141446607
 MidWest | Produce                          |     125156100
 MidWest | Photography                      |     122666753
 MidWest | Frozen Goods                     |     105893534
 MidWest | Gifts                            |     103088595
 MidWest | Bakery                           |     102844467
 MidWest | Canned Goods                     |      97647270
 MidWest | Liquor                           |      97306898
 MidWest | Cleaning supplies                |      90775242
 MidWest | Dairy                            |      89065443
 MidWest | Seafood                          |      82541528
 MidWest | Medical                          |      76674814
 MidWest | Pharmacy                         |      52443519
 West    |                                  |    2159765937
 West    | Meat                             |     235841506
 West    | Produce                          |     215277204
 West    | Photography                      |     205949467
 West    | Frozen Goods                     |     178311593
 West    | Bakery                           |     172824555
 West    | Gifts                            |     172134780
 West    | Liquor                           |     164798022
 West    | Canned Goods                     |     163330813
 West    | Cleaning supplies                |     148776443
 West    | Dairy                            |     145244575
 West    | Seafood                          |     139464407
 West    | Medical                          |     126184049
 West    | Pharmacy                         |      91628523
         |                                  |    5164234493
(43 rows)

包含 WITH 子句的 INSERT 语句

以下 SQL 使用 WITH 子句将 JOIN 查询中的数据插入到表 total_store_sales 中：

CREATE TABLE total_store_sales (store_key int, region VARCHAR(20), store_sales numeric (12,2));

INSERT INTO total_store_sales
WITH store_sales AS (
        SELECT sd.store_key, sd.store_region::VARCHAR(20), SUM (of.total_order_cost)
        FROM store.store_dimension sd JOIN store.store_orders_fact of ON sd.store_key = of.store_key
        GROUP BY sd.store_region, sd.store_key ORDER BY sd.store_region, sd.store_key)
SELECT * FROM store_sales;

=> SELECT * FROM total_store_sales ORDER BY region, store_key;
 store_key |  region   | store_sales
-----------+-----------+-------------
         2 | East      | 47668303.00
         6 | East      | 48136354.00
        12 | East      | 46673113.00
        22 | East      | 48711211.00
        24 | East      | 48603836.00
        31 | East      | 46836469.00
        36 | East      | 48461449.00
        37 | East      | 48018279.00
        41 | East      | 48713084.00
        44 | East      | 47808362.00
        49 | East      | 46990023.00
        50 | East      | 47643329.00
         9 | MidWest   | 46851087.00
        15 | MidWest   | 48787354.00
        27 | MidWest   | 48497620.00
        29 | MidWest   | 47639234.00
        30 | MidWest   | 49013483.00
        38 | MidWest   | 48856012.00
        42 | MidWest   | 47297912.00
        45 | MidWest   | 48544521.00
        46 | MidWest   | 48887255.00
         4 | NorthWest | 47580215.00
        39 | NorthWest | 47136892.00
        47 | NorthWest | 48477574.00
         8 | South     | 48131455.00
        13 | South     | 47605422.00
        17 | South     | 46054367.00
...
(50 rows)

另请参阅

WITH 子句