google-bigquery - 如何离开加入自定义维度和交易
问题描述
我已经从自定义维度(用户 ID 和商家)中留下了连接值。我还想添加交易。我可以就如何将其添加到此查询中获得一些建议吗?还是我必须完全改变结构?这就是我尝试的方式,但是我收到一个语法错误,说它需要 FROM 语句所在的结束输入。
merchants.merchant_Id,
otherMerchants.merchant_id AS otherMerchants,
merchants.market AS market,
COUNT (merchants.transactions) AS transactions,
COUNT(DISTINCT merchants.userId) users
FROM(
SELECT
#här hämtar vi alla userIds och merchantIds
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 4) merchant_Id,
(SELECT VALUE FROM UNNEST (customDimensions) WHERE INDEX = 8) market,
hits.transaction.transactionId) as transactions
FROM `qliro-66ee8.128389777.ga_sessions_20210628`
UNNEST (hits) AS hits
) merchants
LEFT JOIN
(
SELECT
#här hämtar vi alla userIds och merchantIds igen för att kunna nyckla ihop det
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 4) merchant_id,
(SELECT VALUE FROM UNNEST (customDimensions) WHERE INDEX = 8) market,
hits.transaction.transactionId) as transactions
FROM `qliro-66ee8.128389777.ga_sessions_20210628`
UNNEST (hits) AS hits
) otherMerchants
#nycklar på att det är samma user samt inte samma merchant för att inte få dubbelräkning
#nycklar på att det är samma user samt inte samma merchant för att inte få dubbelräkning
ON merchants.userId = otherMerchants.userid
AND merchants.merchant_Id != otherMerchants.merchant_id
AND merchants.transactions = otherMerchants.transactions
AND merchants.market = otherMerchants.market
GROUP BY 1,2,3
解决方案
我觉得你很亲近!你应该做这样的事情
SELECT
merchants.merchant_Id,
otherMerchants.merchant_id AS otherMerchants,
merchants.market AS market,
SUM(merchants.transactions) AS transactions,
COUNT(DISTINCT merchants.userId) users
FROM(
SELECT
(SELECT VALUE FROM UNNEST(t1.customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(t1.customDimensions) WHERE INDEX = 4) merchant_Id,
(SELECT VALUE FROM UNNEST (t1.customDimensions) WHERE INDEX = 8) market,
count(distinct hits.transaction.transactionId) as transactions
FROM `qliro-66ee8.128389777.ga_sessions_20210628` t1,
UNNEST (hits) AS hits
GROUP BY 1,2,3
) merchants
LEFT JOIN
(
SELECT
#här hämtar vi alla userIds och merchantIds igen för att kunna nyckla ihop det
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 3) userid,
(SELECT VALUE FROM UNNEST(customDimensions) WHERE INDEX = 4) merchant_id,
(SELECT VALUE FROM UNNEST (customDimensions) WHERE INDEX = 8) market
FROM `qliro-66ee8.128389777.ga_sessions_20210628`
) otherMerchants
ON merchants.userId = otherMerchants.userid
AND merchants.merchant_Id != otherMerchants.merchant_id
AND merchants.market = otherMerchants.market
GROUP BY 1,2,3
编辑:查询中缺少 GROUP BY 语句
推荐阅读
- mysql - 用于聊天表的 mysql 引擎(堆/内存与 innodb)
- ruby-on-rails - 如何更新关联记录
- vuejs2 - Vue 过滤器中的 HTML 实体
- reactjs - 使用 Express.Router() 设置 Swagger UI
- c# - 使用 .NET Native 构建的 UWP 应用的象征性异常
- java - 使用 JVM Open J9 时应用程序(tomcat)在一段时间后停止响应
- c# - 用数据库中的数据填充组合框,然后从该组合框中用同一数据库中的另一个数据填充另一个组合框
- wordpress - 上传时发生错误。请稍后再试
- django - 如何将基于函数的视图更改为基于类的视图?
- sql - PostgreSQL 两个字段之间的百分比