MATCH_COLUMNS

指定为 SELECT 列表中的元素，返回查询表中与指定模式匹配的所有列。例如：

=> SELECT MATCH_COLUMNS ('%order%') FROM store.store_orders_fact LIMIT 3;
 order_number | date_ordered | quantity_ordered | total_order_cost | reorder_level
--------------+--------------+------------------+------------------+---------------
       191119 | 2003-03-09   |               15 |             4021 |            23
        89985 | 2003-05-04   |               19 |             2692 |            23
       246962 | 2007-06-01   |               77 |             4419 |            42
(3 rows)

语法

MATCH_COLUMNS ('pattern')

参数

模式

匹配查询表中所有列名的模式，其中 pattern 通常包含以下一个或两个通配符：

_ （下划线）：匹配任意单个字符。
% （百分号）：匹配 0 或多个字符的字符串。

模式还可以包含反斜杠 (\\) 字符，转义嵌入列名中的保留字符：_（下划线）、%（百分号）和反斜杠 (\\) 本身。

特权

无

DDL 的用法

您可以使用 MATCH_COLUMNS 定义数据库对象 — 例如，在 CREATE PROJECTION 中指定来标识投影列，或者在 CREATE TABLE...AS 中标识新表中的列。在所有情况下，Vertica 都会在存储对象 DDL 之前扩展 MATCH_COLUMNS 输出。对原始源表的后续更改不会影响派生的对象定义。

限制

通常，将 MATCH_COLUMNS 指定为 SELECT 列表中的一个元素。例如，CREATE PROJECTION 可调用 MATCH_COLUMNS 来指定要包含在投影中的列。但是，尝试在投影的分段子句中指定列会返回错误：

=> CREATE PROJECTION p_store_orders AS SELECT
    MATCH_COLUMNS('%product%'),
    MATCH_COLUMNS('%store%'),
    order_number FROM store.store_orders_fact SEGMENTED BY MATCH_COLUMNS('products%') ALL NODES;
ERROR 0:  MATCH_COLUMNS() function can only be specified as an element in a SELECT list

=> CREATE PROJECTION p_store_orders AS SELECT
    MATCH_COLUMNS('%product%'),
    MATCH_COLUMNS('%store%'),
    order_number FROM store.store_orders_fact;
WARNING 4468:  Projection <store.p_store_orders_b0> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
WARNING 4468:  Projection <store.p_store_orders_b1> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

如果从支持固定数量实参的函数调用 MATCH_COLUMNS，则 Vertica 将返回错误。例如，UPPER 函数仅支持一个实参；因此，按照以下方式从 UPPER 调用 MATCH_COLUMNS 将返回错误：

=> SELECT MATCH_COLUMNS('emp%') FROM employee_dimension LIMIT 1;
-[ RECORD 1 ]-----------+---------------------------------
employee_key            | 1
employee_gender         | Male
employee_first_name     | Craig
employee_middle_initial | F
employee_last_name      | Robinson
employee_age            | 22
employee_street_address | 5 Bakers St
employee_city           | Thousand Oaks
employee_state          | CA
employee_region         | West

=> SELECT UPPER (MATCH_COLUMNS('emp%')) FROM employee_dimension;
ERROR 10465:  MATCH_COLUMNS() function can only be specified as an element in a SELECT list

相反，HASH 函数接受无限数量的实参，因此可以成功将 MATCH_COLUMNS 作为实参调用：

=> select HASH(MATCH_COLUMNS('emp%')) FROM employee_dimension LIMIT 10;
        HASH
---------------------
 2047284364908178817
 1421997332260827278
 7981613309330877388
  792898558199431621
 5275639269069980417
 7892790768178152349
  184601038712735208
 3020263228621856381
 7056305566297085916
 3328422577712931057
(10 rows)

其他约束

MATCH_COLUMNS 的以下用法无效并返回错误：

在 RECURSIVE WITH 子句的非递归（基础）术语查询中包含 MATCH_COLUMNS

连接 MATCH_COLUMNS 调用的结果：

=> SELECT MATCH_COLUMNS ('%store%')||MATCH_COLUMNS('%store%') FROM store.store_orders_fact;
ERROR 0:  MATCH_COLUMNS() function can only be specified as an element in a SELECT list

在 MATCH_COLUMNS 上设置别名

示例

以下 CREATE PROJECTION 语句使用 MATCH_COLUMNS 指定新投影中的表列：

=> CREATE PROJECTION p_store_orders AS SELECT
    MATCH_COLUMNS('%product%'),
    MATCH_COLUMNS('%store%'),
    order_number FROM store.store_orders_fact;
WARNING 4468:  Projection <store.p_store_orders_b0> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
WARNING 4468:  Projection <store.p_store_orders_b1> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

=> SELECT export_objects('', 'store.p_store_orders_b0');
...

CREATE PROJECTION store.p_store_orders_b0 /*+basename(p_store_orders)*/
(
 product_key,
 product_version,
 store_key,
 order_number
)
AS
 SELECT store_orders_fact.product_key,
        store_orders_fact.product_version,
        store_orders_fact.store_key,
        store_orders_fact.order_number
 FROM store.store_orders_fact
 ORDER BY store_orders_fact.product_key,
          store_orders_fact.product_version,
          store_orders_fact.store_key,
          store_orders_fact.order_number
SEGMENTED BY hash(store_orders_fact.product_key, store_orders_fact.product_version, store_orders_fact.store_key, store_orders_fact.order_number) ALL NODES OFFSET 0;

SELECT MARK_DESIGN_KSAFE(1);

(1 row)

如 EXPORT_OBJECTS 输出所示，Vertica 将两个 MATCH_COLUMNS 调用的结果集存储到新投影的 DDL 中。锚表 DDL 中的后续更改不会影响此投影。