我一直收到错误：标量子查询产生了不止一个元素。我试过将ARRAY放在每个SELECT前面（我在另一个问题下读到要这样做），并试着取出每个选择行以确定哪个不起作用，但似乎没有任何帮助。我试图揭示每个在上次访问时从未成为购买者的用户所采取的最后一个操作。任何帮助都是如此感谢!以下是我得到的结果：
WITH
a AS
(WITH
orig AS
(SELECT\*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`),
non_buyers AS
(SELECT fullVisitorId as visitor_id,
MAX(visitNumber) as last_visit,
FROM orig
GROUP BY fullVisitorId
HAVING COUNT(totals.transactions)=0)
SELECT non_buyers.visitor_id as visitor_id,
non_buyers.last_visit as last_visit,
(SELECT MAX(hitNumber) FROM UNNEST( hits ) ) as last_hit
FROM non_buyers LEFT JOIN orig ON non_buyers.visitor_id = orig.fullVisitorID AND non_buyers.last_visit = orig.visitNumber),
orig AS
(SELECT\*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`)
SELECT (SELECT type FROM UNNEST(hits)) as type,
(SELECT page.pagePath FROM UNNEST(hits)) as page,
\--(SELECT product.v2ProductName FROM UNNEST(hits)) as product,--I know this line needs more work because it is repetitive.
(SELECT eventInfo.eventAction FROM UNNEST(hits)) as action,
(SELECT eventInfo.eventLabel FROM UNNEST(hits)) as label,
visitor_id,
last_visit,
last_hit
FROM a LEFT JOIN orig ON a.visitor_id = orig.fullVisitorID AND a.last_visit = orig.visitNumber AND a.last_hit = (SELECT hitNumber FROM UNNEST( hits) )我正在尝试获取包含以下字段的帧：visitor_id，last_visit，last_hit，类型，页面，产品，操作，从未购买过任何东西的每个用户的标签
1条答案
按热度按时间pgky5nke1#
...我想，在屈服并发布问题后（在处理这个问题6个多小时后），我会立即找到答案。以下是对任何感兴趣的人的答案。尽管如此，如果有人对如何简化代码有任何想法，我洗耳恭听。