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

返回本页常规视图.

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

示例

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

另请参阅

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)

另请参阅