sql - 使用 vertica match 子句识别模式的问题
问题描述
我很难理解如何利用 Vertica 的 match 子句来识别用户在我们的网站上搜索某些内容(event_category ='Search')然后看到产品轮播项目(product_list ='banner' AND event_action ='印象')。
在我想确定的模式之前、之后和期间捕获不同的事件,因为页面上显示的产品数量和用户与我们网站的互动可能因会话和用户而异。
原始数据示例
| hit_number | product_list | Event_Category | Event_Action | Event_Label |
|------------|----------------------|----------------|--------------|---------------|
| 105 | (null) | Search | Submit | chocolate |
| 106 | (null) | eec | impression | search-result |
| 107 | search-result | eec | impression | sendData |
| 107 | search-result | eec | impression | sendData |
| 107 | search-result | eec | impression | sendData |
| 107 | search-result | eec | impression | sendData |
| 108 | (null) | (null) | (null) | (null) |
| 109 | (null) | eec | impression | banner |
| 110 | banner-105-chocolate | eec | impression | sendData |
| 110 | banner-105-chocolate | eec | impression | sendData |
| 110 | banner-105-chocolate | eec | impression | sendData |
为了使模式有效,必须至少有 1 个搜索事件和 1 个横幅展示,我已将模式设置为 (Search+ Banner+) 以反映这一点,但是当我运行执行 SQL 查询时我没有返回任何结果如下图所示。
SELECT
page_title
,event_label
,event_name()
,match_id()
,pattern_id()
FROM
(SELECT
unique_visit_id
,hit_number
,event_category
,event_label
,event_action
,product_list
FROM
atomic.ga_sessions_hits_product_expanded
WHERE
1=1
AND ga_sessions_date >= CURRENT_DATE -3
AND unique_visit_id = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
ORDER BY
hit_number ASC) base
Match
(Partition by unique_visit_id Order by hit_number
Define
Search as event_category ='Search' and event_action = 'Submit',
Banner as product_list ilike 'banner-%' and event_action ='impression'
Pattern
P as (Search+ BannerImpression+)
ROWS MATCH FIRST EVENT)
如果有什么我需要澄清的,请告诉我,任何见解或帮助将不胜感激!
解决方案
首先,您要分区的列不在示例输入中。我添加了它,并为输入数据中的所有行赋予了值 42。
您的问题是在该数据片段中没有模式,您命名banner
的事件立即跟随您命名的事件search
最后,我在 DEFINE 子句中添加了另一个事件。如果其他两个不评估为真,other AS true
则将选择刚刚定义为的最后一个(这是 ROWS MATCH FIRST EVENT 的行为)。
然后模式变成(search+ other* banner+)
,然后找到那个模式。
看这里:
WITH
ga_sessions_hits_product_expanded(
unique_visit_id,hit_number,product_list,Event_Category,Event_Action,Event_Label
) AS (
SELECT 42,105,NULL,'Search','Submit','chocolate'
UNION ALL SELECT 42,106,NULL,'eec','impression','search-result'
UNION ALL SELECT 42,107,'search-result','eec','impression','sendData'
UNION ALL SELECT 42,107,'search-result','eec','impression','sendData'
UNION ALL SELECT 42,107,'search-result','eec','impression','sendData'
UNION ALL SELECT 42,107,'search-result','eec','impression','sendData'
UNION ALL SELECT 42,108,NULL,NULL,NULL,NULL
UNION ALL SELECT 42,109,NULL,'eec','impression','banner'
UNION ALL SELECT 42,110,'banner-105-chocolate','eec','impression','sendData'
UNION ALL SELECT 42,110,'banner-105-chocolate','eec','impression','sendData'
UNION ALL SELECT 42,110,'banner-105-chocolate','eec','impression','sendData'
)
SELECT
*
, event_name()
, pattern_id()
, match_id()
FROM ga_sessions_hits_product_expanded
MATCH(
PARTITION BY unique_visit_id ORDER BY hit_number
DEFINE
search AS event_category='Search' AND event_action='Submit'
, banner AS product_list ILIKE 'banner-%' AND event_action='impression'
, other AS true
PATTERN p AS (search+ other* banner+)
ROWS MATCH FIRST EVENT
);
-- out Null display is "NULL".
-- out unique_visit_id | hit_number | product_list | Event_Category | Event_Action | Event_Label | event_name | pattern_id | match_id
-- out -----------------+------------+----------------------+----------------+--------------+---------------+------------+------------+----------
-- out 42 | 105 | NULL | Search | Submit | chocolate | search | 1 | 1
-- out 42 | 106 | NULL | eec | impression | search-result | other | 1 | 2
-- out 42 | 107 | search-result | eec | impression | sendData | other | 1 | 3
-- out 42 | 107 | search-result | eec | impression | sendData | other | 1 | 4
-- out 42 | 107 | search-result | eec | impression | sendData | other | 1 | 5
-- out 42 | 107 | search-result | eec | impression | sendData | other | 1 | 6
-- out 42 | 108 | NULL | NULL | NULL | NULL | other | 1 | 7
-- out 42 | 109 | NULL | eec | impression | banner | other | 1 | 8
-- out 42 | 110 | banner-105-chocolate | eec | impression | sendData | banner | 1 | 9
-- out 42 | 110 | banner-105-chocolate | eec | impression | sendData | banner | 1 | 10
-- out 42 | 110 | banner-105-chocolate | eec | impression | sendData | banner | 1 | 11
-- out (11 rows)
-- out
-- out Time: First fetch (11 rows): 50.632 ms. All rows formatted: 50.721 ms
推荐阅读
- javascript - 创建了一个哈希,当使用长度函数时它返回未定义
- php - 下拉搜索列表无法使变量等于所选选项
- python - 织物回溯:ImportError: cannot import name cd
- jquery - 什么可能导致 iPhone 上的 Chrome 和 Safari 出现这种水平线静态故障?
- dart - Flutter - WebViewScaffold 后面的抽屉菜单
- java - 使用 -Xmx 分配 Java 12.0.1 内存空间不起作用,无法识别 -d64
- c# - 使用新引用更新 Visual Studio 项目(如何找到它们)
- c - 如何在C中重新分配指向较小数组的指针?
- compiler-construction - 实时变量分析以计算基本块的 Def 和 Use 集
- python - 在 3 个“桶”之间分配点的算法