代码之家 › 专栏 › 技术社区 › Ben P

计算一段时间内每个用户最常触发的事件

google-bigquery

Ben P · 技术社区 · 9 年前

我想通过计算这段时间内每个用户ID最常点击的事件来增强这个查询,并且只返回这个事件,而不是为它们触发的所有事件。有谁能提出一个实现这一点的好方法吗?

SELECT customDimension.value AS UserID, hits.eventinfo.eventAction AS Size
FROM `*.ga_sessions_*` AS t
  CROSS JOIN UNNEST(hits) AS hits
  CROSS JOIN UNNEST(t.customdimensions) AS customDimension
WHERE (_TABLE_SUFFIX BETWEEN '20170601' AND '20170628')
AND (hits.page.pagePath LIKE "%/shorts%" OR hits.page.pagePath LIKE "%/t-shirts%")
AND hits.eventinfo.eventCategory = "SIZE Filter Click"
AND (hits.eventinfo.eventAction = "S" OR hits.eventinfo.eventAction = "M" OR hits.eventinfo.eventAction = "L" OR hits.eventinfo.eventAction = "XL")
AND customDimension.index = 2
GROUP BY UserID, Size

1 回复 | 直到 9 年前

Willian Fuks 9 年前

WITH data AS(
  select ARRAY<STRUCT<index INT64, value STRING>> [STRUCT(NULL as index, '' as value), STRUCT(0 as index, 'test' as value), STRUCT(2 as index, 'user_1' as value)] customDimensions,  ARRAY<STRUCT<page STRUCT<pagePath STRING>, eventinfo STRUCT<eventcategory STRING, eventaction STRING> >> [STRUCT(STRUCT('/home' as pagePath) as page, STRUCT("cat1" as eventcategory, "act1" as eventaction) as eventinfo), STRUCT(STRUCT('/abcshortsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "S" as eventaction) as eventinfo), STRUCT(STRUCT('/abcshortsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "S" as eventaction) as eventinfo), STRUCT(STRUCT('/abcshortsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "M" as eventaction) as eventinfo), STRUCT(STRUCT('/abc/t-shirtsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "L" as eventaction) as eventinfo)] hits union all
  select ARRAY<STRUCT<index INT64, value STRING>> [STRUCT(2 as index, 'user_2' as value)] customDimensions,  ARRAY<STRUCT<page STRUCT<pagePath STRING>, eventinfo STRUCT<eventcategory STRING, eventaction STRING> >> [STRUCT(STRUCT('shorts' as pagePath) as page, STRUCT("cat1" as eventcategory, "act1" as eventaction) as eventinfo), STRUCT(STRUCT('/abcshortsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "M" as eventaction) as eventinfo), STRUCT(STRUCT('/abcshortsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "M" as eventaction) as eventinfo), STRUCT(STRUCT('/abcshortsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "M" as eventaction) as eventinfo), STRUCT(STRUCT('/abc/t-shirtsabc' as pagePath) as page, STRUCT("SIZE Filter Click" as eventcategory, "L" as eventaction) as eventinfo)] hits
)

SELECT 
  (SELECT value FROM UNNEST(customDimensions) WHERE index = 2 GROUP BY value) UserID,
  (SELECT eventinfo.eventaction size FROM UNNEST(hits) WHERE (REGEXP_CONTAINS(page.pagepath, r'shorts') OR REGEXP_CONTAINS(page.pagepath, r'/t-shirts')) AND eventinfo.eventcategory = 'SIZE Filter Click' GROUP BY eventinfo.eventaction ORDER BY COUNT(eventinfo.eventaction) DESC LIMIT 1) most_clicked_size
FROM data

如果您还想检索最频繁标签的总点击量(在您的案例中的操作),您还可以执行以下操作:

SELECT 
  (SELECT value FROM UNNEST(customDimensions) WHERE index = 2 GROUP BY value) UserID,
  (SELECT AS STRUCT eventinfo.eventaction size, count(1) freq FROM UNNEST(hits) WHERE (REGEXP_CONTAINS(page.pagepath, r'shorts') OR REGEXP_CONTAINS(page.pagepath, r'/t-shirts')) AND eventinfo.eventcategory = 'SIZE Filter Click' GROUP BY eventinfo.eventaction ORDER BY COUNT(eventinfo.eventaction) DESC LIMIT 1) most_clicked_size
FROM data

data 是对实际ga会话数据的模拟。