sql - 将点击非规范化为 session 级数据的最有效方法是什么？

Question

我正在使用标准 SQL 在 Google BigQuery 中分析 Google Analytics 数据。

我经常遇到想要将命中级别信息汇总为 session 级别指标的情况。这涉及对未嵌套的命中进行拖网搜索并仅过滤相关信息。

对于预订流程分析的示例，您最终会得到一个命中级别的数据表，大致如下:

客户，预订步骤

因此，我需要将其非规范化为这种模式:

客户、步骤 1、步骤 2 等...

这是我目前正在使用的那种方法:

WITH normalised AS (
  # data created from trawling through hit level GA page views and selecting relevant rows
  SELECT 1 AS customer, 'step1' AS step UNION ALL 
  SELECT 1, 'step1' UNION ALL 
  SELECT 1, 'step2' UNION ALL 
  SELECT 1, 'step3' UNION ALL 
  SELECT 1, 'step4' UNION ALL 
  SELECT 1, 'step5' UNION ALL 
  SELECT 2, 'step1' UNION ALL 
  SELECT 2, 'step2' UNION ALL 
  SELECT 2, 'step3' UNION ALL 
  SELECT 2, 'step4' UNION ALL 
  SELECT 3, 'step1' UNION ALL 
  SELECT 3, 'step2' UNION ALL 
  SELECT 3, 'step3' UNION ALL 
  SELECT 3, 'step4' UNION ALL 
  SELECT 4, 'step1' UNION ALL 
  SELECT 4, 'step2' UNION ALL 
  SELECT 4, 'step3' UNION ALL 
  SELECT 4, 'step4' UNION ALL 
  SELECT 5, 'step1' UNION ALL 
  SELECT 5, 'step2' UNION ALL 
  SELECT 5, 'step3' UNION ALL 
  SELECT 6, 'step1' UNION ALL 
  SELECT 6, 'step2' UNION ALL 
  SELECT 7, 'step1'
)

查询:

/* aggregate to remove duplicate entries */
SELECT
  customer,
  CASE WHEN SUM(step1) > 0 THEN 1 ELSE 0 END AS step1,
  CASE WHEN SUM(step2) > 0 THEN 1 ELSE 0 END AS step2
  # for each step
FROM (
  /* denormalise into multiple fields */
  SELECT DISTINCT
    customer, 
    CASE WHEN step = 'step1' THEN 1 ELSE 0 END AS step1,
    CASE WHEN step = 'step2' THEN 1 ELSE 0 END AS step2
    # for each step
  FROM normalised
)
GROUP BY customer
ORDER BY customer ASC

是否有更好、更有效的方法来做到这一点？我的解决方案似乎有效，但考虑到涉及的代码量，我不禁想到可能有更简洁的方法。

Answer 1

我认为您可以一步完成您想做的事情:

SELECT customer, 
       MAX(CASE WHEN step = 'step1' THEN 1 ELSE 0 END) AS step1,
       MAX(CASE WHEN step = 'step2' THEN 1 ELSE 0 END) AS step2
FROM normalised
GROUP BY customer;