google-analytics - 将旧版 BigQuery 转换为标准并收到错误“无法解析表名称“s3”:数据集名称丢失。”
问题描述
错误在第 4 行,并且 s3.fullVisitorId 带有下划线。我想它会在该组中的每个选定项目上引发错误。
我尝试在 DISTINCT 之后添加一个 SELECT 语句,并且内联错误消失了,但是当我尝试运行查询时出现错误:“无法解析表名“s3”:数据集名称丢失。”
任何帮助,将不胜感激。我发现将查询从遗留 sql 转换为标准有点令人头疼。
这是完整的查询:
#standardSQL
SELECT
CAST(CONCAT(SUBSTR(date,1,4),'-',SUBSTR(date,5,2),'-',SUBSTR(date,7,2)) AS DATE) AS Date,
COUNT(DISTINCT s3.fullVisitorId) AS users,
COUNT(s0.firstHit) AS carts,
COUNT(s1.firstHit) AS order_details,
COUNT(s2.firstHit) AS order_confirmation
FROM (
SELECT
IFNULL(s3.date,
IFNULL(s0.date,
IFNULL(s1.date,
s2.date))) AS date,
s3.fullVisitorId,
s0.fullVisitorId,
s0.visitId,
s0.firstHit,
s1.firstHit,
s2.firstHit
FROM (
# user subquery
SELECT
date,
fullVisitorId,
visitId
FROM
`big-query-project-34643.162968675.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
AND totals.visits = 1
GROUP BY
date,
fullVisitorId,
visitId) s3
FULL OUTER JOIN
((
# first subquery
SELECT
date,
fullVisitorId,
visitId,
MIN(h.hitNumber) AS firstHit
FROM
`big-query-project-34643.162968675.ga_sessions_*`, unnest(hits) as h
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
AND REGEXP_CONTAINS(h.page.pagePath,
'/cart')
AND totals.visits = 1
GROUP BY
date,
fullVisitorId,
visitId)) s0
ON
s3.fullVisitorId = s0.fullVisitorId
AND s3.visitId = s0.visitId
FULL OUTER JOIN
((
# Second Subquery
SELECT
date,
fullVisitorId,
visitId,
MIN(h.hitNumber) AS firstHit
FROM
`big-query-project-34643.162968675.ga_sessions_*`, unnest(hits) as h
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
AND REGEXP_CONTAINS(h.page.pagePath,
'/order-details')
AND totals.visits = 1
GROUP BY
date,
fullVisitorId,
visitId)) s1
ON
s0.fullVisitorId = s1.fullVisitorId
AND s0.visitId = s1.visitId
FULL OUTER JOIN
((
# Third Subquery
SELECT
date,
fullVisitorId,
visitId,
MIN(h.hitNumber) AS firstHit
FROM
`big-query-project-34643.162968675.ga_sessions_*`, unnest(hits) as h
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
AND REGEXP_CONTAINS(h.page.pagePath,
'/orderconfirmation')
AND totals.visits = 1
GROUP BY
date,
fullVisitorId,
visitId)) s2
ON
s1.fullVisitorId = s2.fullVisitorId
AND s1.visitId = s2.visitId)
GROUP BY
date
ORDER BY
date
解决方案
您在这里有多个问题 - 特别是:在第 14-19 行
s3.fullVisitorId,
s0.fullVisitorId,
s0.visitId,
s0.firstHit,
s1.firstHit,
s2.firstHit
我建议至少提供如下 [例如] 之类的别名
s3.fullVisitorId s3_fullVisitorId,
s0.fullVisitorId s0_fullVisitorId,
s0.visitId,
s0.firstHit s0_firstHit,
s1.firstHit s1_firstHit,
s2.firstHit s2_firstHit
然后在第 4-7 行 - 引用别名
COUNT(DISTINCT s3_fullVisitorId) AS users,
COUNT(s0_firstHit) AS carts,
COUNT(s1_firstHit) AS order_details,
COUNT(s2_firstHit) AS order_confirmation
推荐阅读
- c# - 在 iOS 上使用 RestSharp 100% 丢包率
- scala - Scala:从 Spark 结构化流中读取 Kafka Avro 消息时出错
- php - 当我将数据库数据从 laravel 6 中的控制器传递到视图时,视图中的未定义变量
- android - 如何在互联网上免费发布您的 android studio apk?
- matlab - 相当于octave gui的cwt(matlab)函数?
- javascript - JavaScript 生成的图到一个 div
- asp.net - 在 RabbitMq 的消息列表器中获取 queueName
- mysql - 类别的 Laravel 和 mysql 表名约定
- reactjs - 使用纯 erlang OTP 代替带有 React.js 和 phoenix 框架的 elixir 后端
- python - 将长度为 n 的列表转换为 nxn 数组