sql - 验证 BigQuery 中的第一个嵌套页面行是否包含自定义维度值

Question

我正在尝试使用 StandardSQL 在 Big Query 中编写 SQL 查询来根据我的 Google Analytics 数据进行计算。我仍在学习 BigQuery 的工作并适应其复杂性。

我想迭代 GA 表中一段时间​​内的每个 session ，并确定“PAGE”类型的第一个命中。在那次点击中，我想返回特定自定义维度的值。自定义维度是 session 范围的，但我只想知道此特定点击的值。

我认为我需要按 session 进行分区并尝试如下，但我对第二次嵌套感到困惑

SELECT (SELECT value from hits.customDimension where hits.customDimension.index == 13) OVER(fullVisitorID) FROM
      `ga_sessions_2017*` AS t
    WHERE
      _TABLE_SUFFIX BETWEEN '0901'
      AND '0930'
      AND totals.hits > 2) 
      AND row_number() = 1
      AND hit.type = "PAGE"
GROUP BY 1

我已经使用 UDF 尝试了以下操作，但我知道我编写的内容完全没有效率。

#standardSQL
CREATE TEMP FUNCTION
  isDefined(json_str STRING)
  RETURNS STRING
  LANGUAGE js AS """
var row = JSON.parse(json_str);
var defined  = "no";

for(i=0; i < row.length; i++) {
if(row[i].type != "PAGE") continue;
row[i].customDimensions.forEach(function(cd) {
if(cd.index == "13" || cd.index == 13) {
if(cd.value !== null || cd.value !== "") defined  = "yes";
}
});
return defined;
}


""";
SELECT becomesDefined, count(*) FROM (
SELECT
  isDefined(TO_JSON_STRING(t.hits)) as becomesDefined
FROM
  `ga_sessions_2017*` AS t
WHERE
  _TABLE_SUFFIX BETWEEN '0901'
  AND '0930'
  AND totals.hits > 2) GROUP BY 1

Answer 1

如果自定义维度是 session 范围的，那么整行将只有一个值，因为一行等于一个 session 。此查询获取命中范围上的第一个命中 cd13 值，就像您在 session 级别指定的 + 自定义维度值一样(也是 13，但您可以轻松更改它)。您可以对所有内容使用子选择:

SELECT
  (SELECT cd.value 
   FROM  t.hits h,  h.customDimensions cd 
   WHERE type='PAGE' AND cd.index=13
   ORDER BY hitnumber ASC
   LIMIT 1) AS firstHitCd13,

  (SELECT value
   FROM UNNEST(t.customDimensions)
   WHERE index=13) AS sessionLevelCd13

FROM
  `project.dataset.ga_sessions_201712*` t
LIMIT
  1000

第一个子选择使用与自定义维度数组的交叉连接。第二个只是解除了 customDimensions 数组的嵌套。一个结果表行等于源表中的一个 session /行