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

联接

1: 联接语法
2: 联接条件与筛选条件
3: 内联接

3.1: 等联接和非等联接
3.2: 自然联接
3.3: 交叉联接

4: 外联接
5: 控制联接输入
6: 范围联接
7: 事件序列联接

7.1: 事件系列联接示例的示例架构
7.2: 编写事件系列联接

查询可以合并多个表中的记录或同一个表的多个实例。合并一个或多个表中的记录的查询称为联接。SELECT 语句和子查询中允许联接。

支持的联接类型

Vertica 支持以下联接类型：

内联接（包括自然联接、交叉联接）
左外联接、右外联接和全外联接
等式和范围联接谓词的优化

Vertica 不支持嵌套循环联接。

联接算法

Vertica 的查询优化器使用哈希联接或合并联接算法来实施联接。有关详细信息，请参阅哈希联接与合并联接。

1 - 联接语法

Vertica 支持用于联接表的 ANSI SQL-92 标准，如下所示：

table‑reference [join-type] JOIN table-reference [ ON join-predicate ]

其中，join-type 可以是以下类型之一：

例如：

=> SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id;

注意

对于 NATURAL 和 CROSS 联接， ON join-predicate 子句无效，而对于所有其他联接类型，该子句是必需的。

替代语法选项

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

由 WHERE 子句联接谓词指定的联接

INNER JOIN 等效于在 WHERE 子句中指定其联接谓词的查询。例如，此示例和上一个示例返回相同的结果。它们都分别在 T1.id 和 T2.id 列上指定 T1 和 T2 表之间的内部联接。

=> SELECT * FROM T1, T2 WHERE T1.id = T2.id;

JOIN USING 子句

可以使用 JOIN USING 子句在同名列上联接两个表。例如：

=> SELECT * FROM T1 JOIN T2 USING(id);

默认情况下，由 JOIN USING 指定的联接始终是内联接。

注意

JOIN USING 通过将两个联接列合并为一个来联接两个表。因此，无论联接列中的实际数据如何，这两个联接列的数据类型必须相同或兼容，例如 FLOAT 和 INTEGER。

SQL-92 联接语法的好处

Vertica 建议您使用 SQL-92 联接语法，原因如下：

SQL-92 外联接语法可跨数据库移植；旧语法不能在数据库之间保持一致。
SQL-92 语法可更好地控制是在外联接期间还是之后评估谓词。如果使用旧语法，这同样无法在数据库之间保持一致。
SQL-92 语法消除了在通过外联接联接两个以上的表这种情况下，联接评估顺序的不确定性。

2 - 联接条件与筛选条件

如果不使用 SQL-92 语法，联接条件（联接过程中评估的谓词）很难与筛选条件（联接之后评估的谓词）区分开来，而且在某些情况下根本无法表达。使用 SQL-92 时，联接条件和筛选条件会分为两个不同的子句，即 ON 子句和 WHERE 子句，这样查询更易于理解。

ON 子句 包含关系运算符（例如 <、<=、>、>=、<>、=、<=>）或用于指定合并左输入关系和右输入关系中哪些记录（例如通过匹配外键与主键）的其他谓词。ON 可用于内联接、左外联接、右外联接和全外联接。交叉联接和联合联接不能使用 ON 子句。

内联接返回经 ON 子句评估为 TRUE 的左关系和右关系中的所有行对。在左联接中，联接中左关系的所有行出现在结果中；左关系与右关系中不匹配的行也会出现在结果中，但包含在右关系中获取的任何列中的 NULL。同样地，右联接会保留右关系的所有行，而全联接保留左右两个关系的所有行。
WHERE 子句 在联接执行之后进行评估。它会筛选 FROM 子句返回的记录，清除掉不符合 WHERE 子句条件的所有记录。

Vertica 自动将外联接转换为内联接（如果合适），允许优化器在更广泛的查询计划集中选择计划，从而实现更好的性能。

3 - 内联接

内联接基于联接谓词将两个表中的记录组合在一起，并要求第一个表中每个记录在第二个表中都具有匹配的记录。因此，内联接只会返回两个已联接表中符合联接条件的记录。不包含匹配项的记录将从结果集中排除。

内联接采用以下格式：

SELECT column‑list FROM left-join-table
  [INNER] JOIN right-join-table ON join-predicate

如果您省略 INNER 关键字，则 Vertica 假定内联接。内联接是可交换的和关联的。您可以按任何顺序指定表而不更改结果。

示例

以下示例指定表 store.store_dimension 和 public.employee_dimension 之间的内联接，其记录分别在 store_region 和 employee_region 列中具有匹配值。

=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays
   FROM public.employee_dimension e
   JOIN store.store_dimension s ON s.store_region=e.employee_region
   GROUP BY s.store_region ORDER BY TotalVacationDays;

这种联接也可以表示如下：

=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays
    FROM public.employee_dimension e, store.store_dimension s
    WHERE s.store_region=e.employee_region
    GROUP BY s.store_region ORDER BY TotalVacationDays;

两个查询返回相同的结果集：

 store_region | TotalVacationDays
--------------+-------------------
 NorthWest    |             23280
 SouthWest    |            367250
 MidWest      |            925938
 South        |           1280468
 East         |           1952854
 West         |           2849976
(6 rows)

如果联接的内表 store.store_dimension 有任何行的 store_region 值与表 public.employee_dimension 中的 employee_region 值不匹配，则从结果集中排除这些行。要包含该行，您可以指定外联接。

3.1 - 等联接和非等联接

Vertica 支持含有匹配列值和非匹配列值的任意联接表达式。例如：

SELECT * FROM fact JOIN dim ON fact.x = dim.x;
SELECT * FROM fact JOIN dim ON fact.x > dim.y;
SELECT * FROM fact JOIN dim ON fact.x <= dim.y;
SELECT * FROM fact JOIN dim ON fact.x <> dim.y;
SELECT * FROM fact JOIN dim ON fact.x <=> dim.y;

注意

= 和 <=> 运算符的运行速度通常是最快的。

等联接以等式（匹配列值）为基础。此等式以等于号 (=) 表示，它在使用 SQL-92 语法的 ON 子句或使用更旧的联接语法的 WHERE 子句中用作比较运算符。

下文中第一个示例使用 SQL-92 和 ON 子句将线上销售额表与使用呼叫中心键的呼叫中心表联接在一起，然后查询返回等于 156 的销售日期键：

=> SELECT sale_date_key, cc_open_date FROM online_sales.online_sales_fact
   INNER JOIN online_sales.call_center_dimension
   ON (online_sales.online_sales_fact.call_center_key =
    online_sales.call_center_dimension.call_center_key
   AND sale_date_key = 156);
 sale_date_key | cc_open_date
---------------+--------------
           156 | 2005-08-12
(1 row)

第二个示例使用更旧的联接语法和 WHERE 子句联接上述表，获得了相同的结果：

=> SELECT sale_date_key, cc_open_date
    FROM online_sales.online_sales_fact, online_sales.call_center_dimension
   WHERE online_sales.online_sales_fact.call_center_key =
      online_sales.call_center_dimension.call_center_key
   AND sale_date_key = 156;
 sale_date_key | cc_open_date
---------------+--------------
           156 | 2005-08-12
(1 row)

Vertica 还允许使用包含复合（多列）主键和外键的表。例如，要创建一对包含多列键的表：

=> CREATE TABLE dimension(pk1 INTEGER NOT NULL, pk2 INTEGER NOT NULL);=> ALTER TABLE dimension ADD PRIMARY KEY (pk1, pk2);
=> CREATE TABLE fact (fk1 INTEGER NOT NULL, fk2 INTEGER NOT NULL);
=> ALTER TABLE fact ADD FOREIGN KEY (fk1, fk2) REFERENCES dimension (pk1, pk2);

要联接使用复合键的表，您必须用 Boolean AND 运算符连接两个联接谓词。例如：

=> SELECT * FROM fact f JOIN dimension d ON f.fk1 = d.pk1 AND f.fk2 = d.pk2;

您可以通过含有 NULL=NULL 联接的 <=> 运算符的表达式编写查询。

=> SELECT * FROM fact JOIN dim ON fact.x <=> dim.y;

<=> 运算符与 = 运算符一样，执行等式比较，但它在两个操作数都为 NULL 时返回 true（而不是 NULL），并在一个操作数为 NULL 时返回 false（而不是 NULL）。

=> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
 ?column? | ?column? | ?column?
----------+----------+----------
 t        | t        | f
(1 row)

比较 <=> 运算符和 = 运算符：

=> SELECT 1 = 1, NULL = NULL, 1 = NULL;
 ?column? | ?column? | ?column?
----------+----------+----------
 t        |          |
(1 row)

注意

对主键/外键组合编写 NULL=NULL 联接不是最佳选择，因为 PK/FK 列通常定义为 NOT NULL。

编写联接时，它可帮助您提前知道哪些列包含 NULL 值。例如，员工的雇佣日期就不是一个好选择，因为雇佣日期不可能被忽略。但是，如果一些员工按小时领工资，而一些员工按薪水领工资，这种情况下，可以使用每小时工资列。如果您不确定给定表中的列值，而且希望检查一下，请键入以下命令：

=> SELECT COUNT(*) FROM tablename WHERE columnname IS NULL;

3.2 - 自然联接

自然联接只是一种包含隐式联接谓词的联接。自然联接可以是内联接、左外联接、右外联接或全外联接，它采用以下格式：

SELECT column‑list FROM left-join-table
NATURAL [ INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER ] JOIN right-join-table

默认情况下，自然联接为自然内联接；但自然联接也可以为自然左/右/全外联接。内联接和自然联接的主要区别在于，内联接具有显式联接条件，而自然联接的条件的形成方式是，对具有相同名称和兼容数据类型的表中所有列对进行匹配，使自然联接变为等联接，因为联接条件在普通的列之间是相等的。（如数据类型不兼容，Vertica 将返回错误。）

注意

数据类型强制转换表列出了哪些数据类型可以转换为其他数据类型。如果一种数据类型可转换为其他数据类型，则这两种数据类型兼容。

当 T2 列 val 大于 5 时，以下查询是 T1 表和 T2 表之间的一个简单的自然联接：

=> SELECT * FROM T1 NATURAL JOIN T2 WHERE T2.val > 5;

store_sales_fact 表和 product_dimension 表有两列共享相同的名称和数据类型：product_key 和 product_version。以下示例在这两个表的共享列处创建自然联接：

=> SELECT product_description, sales_quantity FROM store.store_sales_fact
   NATURAL JOIN public.product_dimension;

以下三个查询返回相同的结果，分别表示为基本查询、内联接和自然联接。仅当 store_sales_fact 表和 store_dimension 表中的公共属性为 store_key 时，表的表达式才等效。如果两个表都具有名为 store_key 的列，则自然联接也会具有 store_sales_fact.store_key = store_dimension.store_key 联接条件。由于所有三个实例的结果都相同，因此它们仅显示在第一个（基本）查询中：

=> SELECT store_name FROM store.store_sales_fact, store.store_dimension
   WHERE store.store_sales_fact.store_key = store.store_dimension.store_key
   AND store.store_dimension.store_state = 'MA' ORDER BY store_name;
 store_name
------------
 Store11
 Store128
 Store178
 Store66
 Store8
 Store90
(6 rows)

作为内联接编写的查询：

=> SELECT store_name FROM store.store_sales_fact
   INNER JOIN store.store_dimension
   ON (store.store_sales_fact.store_key = store.store_dimension.store_key)
   WHERE store.store_dimension.store_state = 'MA' ORDER BY store_name;

对于自然联接，通过对由相同的列名称联接的两个表中所有列进行比较，隐式显示联接谓词。结果集对表示一对命名相同的列仅保留一列。

=> SELECT store_name FROM store.store_sales_fact
   NATURAL JOIN store.store_dimension
   WHERE store.store_dimension.store_state = 'MA' ORDER BY store_name;

3.3 - 交叉联接

交叉联接是一种编写起来最简单的联接，但运行速度通常不是最快的，因为它们包含两个表中记录的所有可能组合。交叉联接不包含任何联接条件，它会返回笛卡尔积，其中结果集中的行数等于第一个表中的行数与第二个表中的行数之乘积。

以下查询返回了促销表和商店销售表的所有可能组合：

=> SELECT * FROM promotion_dimension CROSS JOIN store.store_sales_fact;

由于此示例返回了超过 6 亿个记录，因此，许多交叉联接的结果非常大，不易管理。但是，交叉联接非常有用，例如当您想返回一个单行结果集时。

提示

通过使用 WHERE 子句联接谓词过滤掉交叉联接中不需要的记录：

=> SELECT * FROM promotion_dimension p   CROSS JOIN store.store_sales_fact f
   WHERE p.promotion_key LIKE f.promotion_key;

隐式联接与显式联接

Vertica 建议您不要编写隐式交叉联接（FROM 子句中以逗号分隔的表）。这些查询可能会意外忽略了某个联接谓词。

以下查询隐式交叉联接 promotion_dimension 和 store.store_sales_fact 表：

=> SELECT * FROM promotion_dimension, store.store_sales_fact;

更好的做法是明确表达此交叉联接，如下所示：

=> SELECT * FROM promotion_dimension CROSS JOIN store.store_sales_fact;

示例

以下示例创建了两个小表格及其超投影，然后对这两个表运行交叉联接：

=> CREATE TABLE employee(employee_id INT, employee_fname VARCHAR(50));
=> CREATE TABLE department(dept_id INT, dept_name VARCHAR(50));
=> INSERT INTO employee VALUES (1, 'Andrew');
=> INSERT INTO employee VALUES (2, 'Priya');
=> INSERT INTO employee VALUES (3, 'Michelle');
=> INSERT INTO department VALUES (1, 'Engineering');
=> INSERT INTO department VALUES (2, 'QA');
=> SELECT * FROM employee CROSS JOIN department;

在结果集中，交叉联接检索了第一个表中的记录，然后为第二个表中每个行创建一个新行。接着，它对第一个表中的下一个记录重复此操作，依此类推。

 employee_id | employee_name | dept_id | dept_name
 -------------+---------------+---------+-----------
           1 | Andrew        |       1 |  Engineering
           2 | Priya         |       1 |  Engineering
           3 | Michelle      |       1 |  Engineering
           1 | Andrew        |       2 |  QA
           2 | Priya         |       2 |  QA
           3 | Michelle      |       2 |  QA
(6 rows)

4 - 外联接

外联接扩展了内联接的功能。通过外联接，您可以保留一个或两个表中在非保留表中没有匹配行的行。外联接采用以下格式：

SELECT column‑list FROM left-join-table
[ LEFT | RIGHT | FULL ] OUTER JOIN right-join-table ON join-predicate

注意

在语句中忽略 OUTER 关键词不会影响左联接和右联接的结果。LEFT OUTER JOIN和 LEFT JOIN 执行相同的操作并返回相同的结果。

左外联接

左外联接返回左联接（保留）表 T1 的完整记录集以及右联接（非保留）表 T2 中的匹配记录（如适用）。如果 Vertica 未找到匹配项，它会使用 null 值扩展右侧列 (T2)。

=> SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.x = T2.x;

要排除 T2 中不匹配的值，请编写相同的左外联接，但使用 WHERE 子句过滤掉右表中不需要的记录：

=> SELECT * FROM T1 LEFT OUTER JOIN T2
   ON T1.x = T2.x WHERE T2.x IS NOT NULL;

以下示例使用左外联接扩充含有不完整电话号码维度的电话呼叫详细记录。然后，它过滤掉已知的不是来自马萨诸塞州的结果：

=> SELECT COUNT(*) FROM calls LEFT OUTER JOIN numbers
   ON calls.to_phone = numbers.phone WHERE NVL(numbers.state, '') <> 'MA';

右外联接

右外联接返回右联接（保留）表的完整记录集以及左联接（非保留）表中的匹配值。如果 Vertica 从左联接表 (T1) 中未找到匹配记录，则对于 T1 中没有匹配值的任何记录，将在 T1 列中显示 NULL 值。因此，右联接与左联接相似，只是用于联接的表的顺序相反而已。

=> SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.x = T2.x;

上述查询等同于以下查询，其中 T1 RIGHT OUTER JOIN T2 = T2 LEFT OUTER JOIN T1。

=> SELECT * FROM T2 LEFT OUTER JOIN T1 ON T2.x = T1.x;

以下示例会标识没有下单的客户：

=> SELECT customers.customer_id FROM orders RIGHT OUTER JOIN customers
   ON orders.customer_id = customers.customer_id
   GROUP BY customers.customer_id HAVING COUNT(orders.customer_id) = 0;

全外联接

全外联接返回左右两个外联接的结果。联接表包含两个表的所有记录，包括联接任何一侧中的 NULL（缺少匹配）。它非常有用，例如，如果您想查看哪些员工已分配到某个特定部门以及已有一名员工的所有部门，但是您还想查看哪些员工没有分配到某个特定部门以及没有员工的所有部门：

=> SELECT employee_last_name, hire_date FROM  employee_dimension emp
   FULL OUTER JOIN department dept ON emp.employee_key = dept.department_key;

注意

Vertica 还支持以下联接，即外部（保留）表或子查询在一个以上节点中进行复制，而内部（非保留）表或子查询跨多个节点进行分段。例如，在以下查询中，事实表（大多数情况下已分段）出现在联接的非保留表中，这是允许的：

=> SELECT sales_dollar_amount, transaction_type, customer_name
    FROM store.store_sales_fact f RIGHT JOIN customer_dimension d
   ON f.customer_key = d.customer_key;
 sales_dollar_amount | transaction_type | customer_name
---------------------+------------------+---------------
                 252 | purchase         | Inistar
                 363 | purchase         | Inistar
                 510 | purchase         | Inistar
                -276 | return           | Foodcorp
                 252 | purchase         | Foodcorp
                 195 | purchase         | Foodcorp
                 290 | purchase         | Foodcorp
                 222 | purchase         | Foodcorp
                     |                  | Foodgen
                     |                  | Goldcare
(10 rows)

5 - 控制联接输入

默认情况下，优化器使用自己的内部逻辑来确定将一个表作为内部输入还是外部输入联接到另一个表。有时，优化器可能选择将更大的表作为联接的内部输入。但这样做会导致性能和并发问题。

如果配置参数 EnableForceOuter 设置为 1，则您可以通过 ALTER TABLE..FORCE OUTER 控制特定表的联接输入。FORCE OUTER 选项会在 TABLES 系统表中修改表的 force_outer 设置。实施联接时，Vertica 会比较参与联接的表的 force_outer 设置：

如果表设置不同，Vertica 会使用它们来设置联接输入：
- 相比其他表具有较低 force_outer 设置的表会作为内部输入联接到这些表。
- 相比其他表具有较高 force_outer 设置的表会作为外部输入联接到这些表。
如果所有表设置都相同，Vertica 会忽略它们，然后自行决定如何构建联接。

对于所有新定义的表，force_outer 列最初都设置为 5。您可以使用 ALTER TABLE..FORCE OUTER 将 force_outer 重置为等于或大于 0 的值。例如，您可以将 abc 和 xyz 表的 force_outer 设置分别改为 3 和 8：

=> ALTER TABLE abc FORCE OUTER 3;

=> ALTER TABLE xyz FORCE OUTER 8;

根据这些设置，优化器会将 abc 作为内部输入联接到 force_outer 值大于 3 的任何表。优化器会将 xyz 作为外部输入联接到 force_outer 值小于 8 的任何表。

投影继承

直接查询投影时，它会继承其锚表的 force_outer 设置。然后查询会在联接到另一个投影后使用此设置。

启用强制联接输入

配置参数 EnableForceOuter 决定了 Vertica 是否使用表的 force_outer 值来实施联接。默认情况下，此参数设置为 0，并且强制联接输入处于禁用状态。您可以通过 ALTER SESSION 和 ALTER DATABASE 分别在会话和数据库范围中启用强制联接输入：

=> ALTER SESSION SET EnableForceOuter = { 0 | 1 };

=> ALTER DATABASE db-name SET EnableForceOuter =  { 0 | 1 };

如果 EnableForceOuter 设置为 0，则 ALTER TABLE..FORCE OUTER 语句会返回以下警告：

注意

WARNING 0: Set configuration parameter EnableForceOuter for the current session or the database in order to use
force_outer value

查看强制联接输入

EXPLAIN 生成的查询计划会指示 EnableForceOuter 配置参数是否已启用。联接查询可能会包含 force_outer 设置小于或大于默认值 5 的表。在这种情况下，查询计划会包含相关联接查询的 Force outer level 字段。

例如，以下查询联接了 store.store_sales 表和 public.products 表，但这两个表具有相同的 force_outer 设置 (5)。EnableForceOuter已启用，如生成的查询计划中所示：

=> EXPLAIN SELECT s.store_key, p.product_description, s.sales_quantity, s.sale_date
   FROM store.store_sales s JOIN public.products p ON s.product_key=p.product_key
   WHERE s.sale_date='2014-12-01' ORDER BY s.store_key, s.sale_date;

 EnableForceOuter is on
 Access Path:
 +-SORT [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
 |  Order: sales.store_key ASC, sales.sale_date ASC
 |  Execute on: All Nodes
 | +---> JOIN HASH [Cost: 5K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Outer (BROADCAST)(LOCAL ROUND ROBIN)
 | |      Join Cond: (sales.product_key = products.product_key)
 | |      Execute on: All Nodes
 | | +-- Outer -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 3)
 | | |      Projection: store.store_sales_b0
 | | |      Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity
 | | |      Filter: (sales.sale_date = '2014-12-01'::date)
 | | |      Execute on: All Nodes
 | | +-- Inner -> STORAGE ACCESS for products [Cost: 177, Rows: 60K (NO STATISTICS)] (PATH ID: 4)
 | | |      Projection: public.products_b0
 | | |      Materialize: products.product_key, products.product_description
 | | |      Execute on: All Nodes

以下 ALTER TABLE 语句将 public.products 的 force_outer 设置重置为 1：

=> ALTER TABLE public.products FORCE OUTER 1;
ALTER TABLE

此时，为上述联接重新生成的查询包含 Force outer level 字段，并将 public.products 指定为内部输入：

=> EXPLAIN SELECT s.store_key, p.product_description, s.sales_quantity, s.sale_date
   FROM store.store_sales s JOIN public.products p ON s.product_key=p.product_key
   WHERE s.sale_date='2014-12-01' ORDER BY s.store_key, s.sale_date;

 EnableForceOuter is on
 Access Path:
 +-SORT [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
 |  Order: sales.store_key ASC, sales.sale_date ASC
 |  Execute on: All Nodes
 | +---> JOIN HASH [Cost: 5K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Outer (BROADCAST)(LOCAL ROUND ROBIN)
 | |      Join Cond: (sales.product_key = products.product_key)
 | |      Execute on: All Nodes
 | | +-- Outer -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 3)
 | | |      Projection: store.store_sales_b0
 | | |      Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity
 | | |      Filter: (sales.sale_date = '2014-12-01'::date)
 | | |      Execute on: All Nodes
 | | +-- Inner -> STORAGE ACCESS for products [Cost: 177, Rows: 60K (NO STATISTICS)] (PATH ID: 4)
 | | |      Projection: public.products_b0
 | | |      Force outer level: 1
 | | |      Materialize: products.product_key, products.product_description
 | | |      Execute on: All Nodes

如果将 public.products 的 force_outer 设置更改为 8，Vertica 会创建将 public.products 指定为外部输入的另一个查询计划：

=> ALTER TABLE public.products FORCE OUTER 8;
ALTER TABLE

=> EXPLAIN SELECT s.store_key, p.product_description, s.sales_quantity, s.sale_date
   FROM store.store_sales s JOIN public.products p ON s.product_key=p.product_key
   WHERE s.sale_date='2014-12-01' ORDER BY s.store_key, s.sale_date;


EnableForceOuter is on
 Access Path:
 +-SORT [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
 |  Order: sales.store_key ASC, sales.sale_date ASC
 |  Execute on: All Nodes
 | +---> JOIN HASH [Cost: 5K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Inner (BROADCAST)
 | |      Join Cond: (sales.product_key = products.product_key)
 | |      Materialize at Output: products.product_description
 | |      Execute on: All Nodes
 | | +-- Outer -> STORAGE ACCESS for products [Cost: 20, Rows: 60K (NO STATISTICS)] (PATH ID: 3)
 | | |      Projection: public.products_b0
 | | |      Force outer level: 8
 | | |      Materialize: products.product_key
 | | |      Execute on: All Nodes
 | | |      Runtime Filter: (SIP1(HashJoin): products.product_key)
 | | +-- Inner -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 4)
 | | |      Projection: store.store_sales_b0
 | | |      Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity
 | | |      Filter: (sales.sale_date = '2014-12-01'::date)
 | | |      Execute on: All Nodes

限制

Vertica 在执行以下操作时会忽略 force_outer 设置：

外联接：无论已联接表的 force_outer 设置如何，Vertica 通常都会遵循 OUTER JOIN 子句。
MERGE 语句联接。
包含 SYNTACTIC_JOIN 提示的查询。
半联接查询，例如 SEMI JOIN。
联接到子查询，其中有一个子查询总是按 force_outer 设置为 5 进行处理，而不论在此子查询中已联接表的 force_outer 设置如何。此设置决定了相对于其他联接输入，将子查询指定为内部还是外部输入。如果联接两个子查询，优化器会决定哪个是内部输入，哪个是外部输入。

6 - 范围联接

Vertica 为联接 ON 子句中的 <、<=、>、>= 和 BETWEEN 谓词进行了性能优化。如果一个表的列限定在另一个表的两个列所指定的范围内，这些优化将特别有用。

键范围

多个连续的键值可映射到相同的维度值。例如，以 IPv4 地址表及其所有者为例。因为大型 IP 地址子网（范围）可属于同一个所有者，所以此维度可表示为：

=> CREATE TABLE ip_owners(
      ip_start INTEGER,
      ip_end INTEGER,
      owner_id INTEGER);
=> CREATE TABLE clicks(
      ip_owners INTEGER,
      dest_ip INTEGER);

将点击流与其目标相关联的查询可以使用与以下使用范围优化的联接类似的联接：

=> SELECT owner_id, COUNT(*) FROM clicks JOIN ip_owners
   ON clicks.dest_ip BETWEEN ip_start AND ip_end
   GROUP BY owner_id;

要求

<、<=、>、>= 或 BETWEEN 运算符必须作为最高级别的连接谓词显示，才能使范围联接优化起作用，如以下示例所示：

`BETWEEN `作为唯一的谓词：

```
=> SELECT COUNT(*) FROM fact JOIN dim
    ON fact.point BETWEEN dim.start AND dim.end;

```

比较运算符作为最高级别的谓词（在 `AND` 内）：

```
=> SELECT COUNT(*) FROM fact JOIN dim
    ON fact.point > dim.start AND fact.point < dim.end;

```

`BETWEEN `作为最高级别的谓词（在 `AND` 内）：

```
=> SELECT COUNT(*) FROM fact JOIN dim
   ON (fact.point BETWEEN dim.start AND dim.end) AND fact.c <> dim.c;

```

查询未优化，因为 `OR` 是最高级别的谓词（反意连接词）：

```
=> SELECT COUNT(*) FROM fact JOIN dim
   ON (fact.point BETWEEN dim.start AND dim.end) OR dim.end IS NULL;

```

注意

在许多用例中，范围联接查询中的表达式都得到了优化。
如果范围列可以包含 NULL 值（指示它们是开放式的），则将 NULL 替换为非常大或非常小的值，即可使用范围联接优化：
```
=> SELECT COUNT(*) FROM fact JOIN dim
   ON fact.point BETWEEN NVL(dim.start, -1) AND NVL(dim.end, 1000000000000);
```
如果同一个 ON 子句中有一个以上范围谓词集，谓词的指定顺序可能会影响优化的效果：
```
=> SELECT COUNT(*) FROM fact JOIN dim ON fact.point1 BETWEEN dim.start1 AND dim.end1
   AND fact.point2 BETWEEN dim.start2 AND dim.end2;
```
优化器会选择第一个范围进行优化，因此编写查询，确保让您最想优化的范围最先出现在语句中。
物理架构的任何特性都不会直接影响范围联接优化的使用；不需要对架构进行调试即可受益于优化。
范围联接优化可应用于不包含任何其他谓词的联接，也可应用于 HASH 或 MERGE 联接。
要确定优化是否正在使用，请在 EXPLAIN 计划中搜索 RANGE。

7 - 事件序列联接

事件序列联接是一个 Vertica SQL 扩展，它可以在两个系列的度量间隔并不完全一致（例如时间戳不匹配）时分析这两个系列。您可以直接比较两个系列的值，而不是将系列都标准化为相同的度量间隔。

事件序列联接是外联接的扩展，但它不会在存在不匹配时用 NULL 值填充非保留侧，而是用根据以前值获得的插值填充非保留侧。

编写常规联接与事件序列联接的不同之处在于 ON 子句中使用的 INTERPOLATE 谓词。例如，以下两个语句就说明了这一不同之处，更多详情请参阅编写事件系列联接。

与常规联接类似，事件序列联接具有内部和外部联接模式，这在之后的主题中会进行介绍。

有关完整的语法（包括注释和限制）请参阅 INTERPOLATE

7.1 - 事件系列联接示例的示例架构

如果您不打算运行查询，只想查看示例，则可以跳过此主题，直接转至编写事件系列联接。

hTicks 表和 aTicks 表的架构

后面的示例使用以下 hTicks 表和 aTicks 表的架构：

尽管 TIMESTAMP 更常用于事件序列列，但为了让输出更简单，此主题中的示例使用了 TIME。

这两个表的输出：

显示出间隙的示例查询

完全外部联接显示了时间戳中的间隙：

=> SELECT * FROM hTicks h FULL OUTER JOIN aTicks a ON h.time = a.time;
 stock |   time   | price | stock |   time   | price
-------+----------+-------+-------+----------+--------
 HPQ   | 12:00:00 | 50.00 | ACME  | 12:00:00 | 340.00
 HPQ   | 12:01:00 | 51.00 |       |          |
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 333.80
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 340.20
 HPQ   | 12:06:00 | 52.00 |       |          |
       |          |       | ACME  | 12:03:00 | 340.10
(6 rows)

Bid 表和 Asks 表的架构

后面的示例使用以下 hTicks 表和 aTicks 表。

这两个表的输出：

显示出间隙的示例查询

完全外部联接显示了时间戳中的间隙：

=> SELECT * FROM bid b FULL OUTER JOIN ask a ON b.time = a.time;
 stock |   time   | price  | stock |   time   | price
-------+----------+--------+-------+----------+--------
 HPQ   | 12:00:00 | 100.10 | ACME  | 12:00:00 |  80.00
 HPQ   | 12:01:00 | 100.00 | HPQ   | 12:01:00 | 101.00
 ACME  | 12:00:00 |  80.00 | ACME  | 12:00:00 |  80.00
 ACME  | 12:03:00 |  79.80 |       |          |
 ACME  | 12:05:00 |  79.90 |       |          |
       |          |        | ACME  | 12:02:00 |  75.00
(6 rows)

7.2 - 编写事件系列联接

此主题中的示例包含时间戳不匹配项，就像在现实情形中可能会遇到的一样；例如，在没有交易时，股票在一段时间内可能会处于非活动状态，这时要想比较时间戳不匹配的两支股票会有一定挑战。

hTicks 表和 aTicks 表

如示例 ticks 架构所述，表 hTicks 在 12:02、12:03 和 12:04 缺少输入行，表 aTicks 在 12:01、12:02 和 12:04 缺少输入行。

通过全外联接查询事件系列

此查询使用传统的全外联接，在 hTicks 表和 aTicks 表之间找到了位于 12:00 和 12:05 的匹配项，然后用 NULL 值填充缺失的数据点。

=> SELECT * FROM hTicks h FULL OUTER JOIN aTicks a ON (h.time = a.time);
 stock |   time   | price | stock |   time   | price
-------+----------+-------+-------+----------+--------
 HPQ   | 12:00:00 | 50.00 | ACME  | 12:00:00 | 340.00
 HPQ   | 12:01:00 | 51.00 |       |          |
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 333.80
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 340.20
 HPQ   | 12:06:00 | 52.00 |       |          |
       |          |       | ACME  | 12:03:00 | 340.10
(6 rows)

要为缺失的数据点将空白替换为内插值，可使用 INTERPOLATE 谓词创建事件序列联接。联接条件仅限于 ON 子句，该子句会在两个输入表的时间戳列中对等同谓词求值。换句话说，对于外表 hTicks 中的每个行，为内表 aTicks 中每个行的每个组合对 ON 子句谓词进行求值。

简单地重写全外联接可将 INTERPOLATE 谓词与所需的 PREVIOUS VALUE 关键字结合使用。请注意，对于事件序列数据，在事件序列数据上执行全外联接是最常见的场景，在此场景中，您可以同时保留两个表中的所有行。

=> SELECT * FROM hTicks h FULL OUTER JOIN aTicks a
   ON (h.time INTERPOLATE PREVIOUS VALUE a.time);

Vertica 使用此表以前的值内插缺失数据（在全外联接中显示为 NULL）：

![event series join](/images/hticks1.png)

注意

上面的输出顺序与普通全外联接不同，因为在数据系列数据中，对于每只股票（hTicks 和 aTicks），内插都是独立发生的，并且其中的数据根据等同谓词进行分区和排序。这意味着，内插发生在分区内部，而不是跨分区发生。

如果查看普通全外联接的输出，您可以发现这两个表在 12:00 和 12:05 时间列中存在一个匹配，但在 12:01，则不存在 ACME 的条目记录。因此，相关操作会根据 aTicks 表中以前的值为 ACME (ACME,12:00,340) 内插一个值。

通过左外联接查询事件系列

您也可以使用左外联接和右外联接。例如，您可能决定仅保留 hTicks 的值。因此，您将写入左外联接：

=> SELECT * FROM hTicks h LEFT OUTER JOIN aTicks a
   ON (h.time INTERPOLATE PREVIOUS VALUE a.time);
 stock |   time   | price | stock |   time   | price
-------+----------+-------+-------+----------+--------
 HPQ   | 12:00:00 | 50.00 | ACME  | 12:00:00 | 340.00
 HPQ   | 12:01:00 | 51.00 | ACME  | 12:00:00 | 340.00
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 333.80
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 340.20
 HPQ   | 12:06:00 | 52.00 | ACME  | 12:05:00 | 340.20
(5 rows)

以下数据与使用传统的左外联接得到的数据相似：

=> SELECT * FROM hTicks h LEFT OUTER JOIN aTicks a ON h.time = a.time;
 stock |   time   | price | stock |   time   | price
-------+----------+-------+-------+----------+--------
 HPQ   | 12:00:00 | 50.00 | ACME  | 12:00:00 | 340.00
 HPQ   | 12:01:00 | 51.00 |       |          |
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 333.80
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 340.20
 HPQ   | 12:06:00 | 52.00 |       |          |
(5 rows)

请注意，右外联接与保存表的行为相同，只是顺序相反。

通过内联接查询事件系列

请注意，如果忽视所有空白，INNER 事件序列联接的行为方式与普通的 ANSI SQL-99 联接相同。因此，这时不会内插任何内容，而且以下两个查询等效，都会返回相同的结果集。

普通的内联接：

=> SELECT * FROM HTicks h JOIN aTicks a
    ON (h.time INTERPOLATE PREVIOUS VALUE a.time);
 stock |   time   | price | stock |   time   | price
-------+----------+-------+-------+----------+--------
 HPQ   | 12:00:00 | 50.00 | ACME  | 12:00:00 | 340.00
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 333.80
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 340.20
(3 rows)

事件序列内联接：

=> SELECT * FROM hTicks h INNER JOIN aTicks a ON (h.time = a.time);
 stock |   time   | price | stock |   time   | price
-------+----------+-------+-------+----------+--------
 HPQ   | 12:00:00 | 50.00 | ACME  | 12:00:00 | 340.00
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 333.80
 HPQ   | 12:05:00 | 51.00 | ACME  | 12:05:00 | 340.20
(3 rows)

Bid 表和 Ask 表

使用 bid 表和 ask 表的示例架构，编写全外联接以内插缺失的数据点：

=> SELECT * FROM bid b FULL OUTER JOIN ask a
    ON (b.stock = a.stock AND b.time INTERPOLATE PREVIOUS VALUE a.time);

在下列输出中，股票 HPQ 的第一行显示了 NULL，因为在 12:01 之前不存在 HPQ 的条目记录。

 stock |   time   | price  | stock |   time   | price
-------+----------+--------+-------+----------+--------
 ACME  | 12:00:00 |  80.00 | ACME  | 12:00:00 |  80.00
 ACME  | 12:00:00 |  80.00 | ACME  | 12:02:00 |  75.00
 ACME  | 12:03:00 |  79.80 | ACME  | 12:02:00 |  75.00
 ACME  | 12:05:00 |  79.90 | ACME  | 12:02:00 |  75.00
 HPQ   | 12:00:00 | 100.10 |       |          |
 HPQ   | 12:01:00 | 100.00 | HPQ   | 12:01:00 | 101.00
(6 rows)

另外，请注意 ask 表的同一个行 (ACME,12:02,75) 出现了三次。第一次出现是因为 bid 表中没有 ask 中的行，因此 Vertica 使用 12:02 的 ACME 值 (75.00) 插入了缺失值。第二次出现是因为 bid 中的行 (ACME,12:05,79.9) 在 ask 中没有相应的匹配项。ask 中包含 (ACME,12:02,75) 的行是最接近的行，因此使用它来插入值。

如果编写普通的全外联接，则可以发现哪些地方出现了不匹配的时间戳：

=> SELECT * FROM bid b FULL OUTER JOIN ask a ON (b.time = a.time);
 stock |   time   | price  | stock |   time   | price
-------+----------+--------+-------+----------+--------
 ACME  | 12:00:00 |  80.00 | ACME  | 12:00:00 |  80.00
 ACME  | 12:03:00 |  79.80 |       |          |
 ACME  | 12:05:00 |  79.90 |       |          |
 HPQ   | 12:00:00 | 100.10 | ACME  | 12:00:00 |  80.00
 HPQ   | 12:01:00 | 100.00 | HPQ   | 12:01:00 | 101.00
       |          |        | ACME  | 12:02:00 |  75.00
(6 rows)