该customDimensions
索引6
对应一个UUID既对会话和命中水平。
在会话级别,我可以使用以下标准SQL查询来检索UUID:
CREATE TEMP FUNCTION customDimensionByIndex(indx INT64, arr ARRAY<STRUCT<index INT64, value STRING>>) AS (
(SELECT x.value FROM UNNEST(arr) x WHERE indx=x.index)
);
SELECT
customDimensionByIndex(6, customDimensions) AS session_uuid -- Customer UUID
FROM `94860076.ga_sessions_20170822`
limit 10
同样,在点击量级别,我可以使用:
CREATE TEMP FUNCTION customDimensionByIndex(indx INT64, arr ARRAY<STRUCT<index INT64, value STRING>>) AS (
(SELECT x.value FROM UNNEST(arr) x WHERE indx=x.index)
);
SELECT
customDimensionByIndex(6, hits.customDimensions) AS hit_uuid -- Customer UUID
FROM `94860076.ga_sessions_20170822`, unnest(hits) as hits
limit 10
但是,我无法在同一查询中使用两者。例如,我想要一个结果集,其中每一行对应一个会话,列为session_uuid
and array_of_hit_uuids
。如何做到这一点?
以下是BigQuery标准SQL
#standardSQL
CREATE TEMP FUNCTION customDimensionByIndex(indx INT64, arr ARRAY<STRUCT<index INT64, value STRING>>) AS (
(SELECT x.value FROM UNNEST(arr) x WHERE indx=x.index)
);
SELECT *
FROM (
SELECT
customDimensionByIndex(6, customDimensions) AS session_uuid,
ARRAY(
SELECT val FROM (
SELECT customDimensionByIndex(6, hits.customDimensions) AS val
FROM UNNEST(hits) AS hits
)
WHERE NOT val IS NULL
) AS hit_uuid
FROM `94860076.ga_sessions_20170822`
)
WHERE session_uuid IS NOT NULL
LIMIT 10
您可以使用公共数据集进行测试
#standardSQL
CREATE TEMP FUNCTION customDimensionByIndex(indx INT64, arr ARRAY<STRUCT<index INT64, value STRING>>) AS (
(SELECT x.value FROM UNNEST(arr) x WHERE indx=x.index)
);
SELECT *
FROM (
SELECT
customDimensionByIndex(2, customDimensions) AS session_uuid,
ARRAY(
SELECT val FROM (
SELECT customDimensionByIndex(1, hits.customDimensions) AS val
FROM UNNEST(hits) AS hits
)
WHERE NOT val IS NULL
) AS hit_uuid
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
)
WHERE session_uuid IS NOT NULL
LIMIT 10
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句