如何左联接自定义维度和事务

Question

我留下了来自自定义维度(用户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

Answer 1

我觉得你很接近！你应该做这样的

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语句