MATCH_ID

将成功模式匹配作为 INTEGER 值返回。返回值是分区中匹配所在的序号位置。

语法

MATCH_ID()

注意

模式匹配函数必须用在 MATCH 子句 语法中；例如，如果单独调用 MATCH_ID()，Vertica 将返回以下错误消息：

=> SELECT match_id();
ERROR:  query with pattern matching function match_id must include a MATCH clause

示例

下面的语句在 website2.com 网站上分析用户的浏览历史，并确定用户从另一个网站（ website2.com 子句中的Entry ）到达 MATCH ，以及订购（购买）之前浏览的其他页面（Onsite）数的模式。查询同时也输出表示匹配序号的 MATCH_ID() 的值。

SELECT uid,
       sid,
       ts,
       refurl,
       pageurl,
       action,
       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);

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

另请参阅