大查询:LEFT OUTER JOIN 不能在没有连接两侧字段相等的条件下使用 [英] Big query: LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join
本文介绍了大查询:LEFT OUTER JOIN 不能在没有连接两侧字段相等的条件下使用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请帮忙.我创建了下一个查询,但是 gen 问题,我不明白如何解决它在此处输入图片描述
Help, please. I created the next query but gen issue and I don`t understand how to fix it enter image description here
SELECT
tb1.*
FROM
(SELECT
tb1.date,
clientId,
REGEXP_EXTRACT (hits.pagePath,"^([^?]+)?") as page_url,
hits.type as type,
hits.eventInfo.eventCategory AS eventCategory,
hits.eventInfo.eventAction AS eventAction,
hits.eventInfo.eventLabel AS person_email,
FROM
`table` AS tb1, UNNEST (hits) AS hits) as tb1
where tb1.type = "pageview" or (tb1.eventCategory = "Enroll_Free_lecture" and
exists (select tb2.date, tb2.type from(select date, hitss.type as type From `table`
as tb2, UNNEST(hits) as hitss) tb2 where tb2.date <= tb1.date and tb2.type = "pageview" ))
推荐答案
以下为 BigQuery Standard SQL
Below is for BigQuery Standard SQL
#standardSQL
SELECT * EXCEPT(flag)
FROM (
SELECT
tb1.date,
clientId,
REGEXP_EXTRACT (hits.pagePath,"^([^?]+)?") AS page_url,
hits.type AS type,
hits.eventInfo.eventCategory AS eventCategory,
hits.eventInfo.eventAction AS eventAction,
hits.eventInfo.eventLabel AS person_email,
COUNTIF(type = "pageview") OVER(ORDER BY `date`) AS flag
FROM `table` AS tb1,
UNNEST (hits) AS hits
)
WHERE type = "pageview" OR (
eventCategory = "Enroll_Free_lecture"
AND flag > 0
)
这篇关于大查询:LEFT OUTER JOIN 不能在没有连接两侧字段相等的条件下使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文