大查询左连接问题

Question

如果我们有下一个建设我有问题“不能在没有条件的情况下使用”，但我想收到下一个流量超级页面，和Enroll_Free_Lecture相同的人谁做了pageview和Enroll_Free_Lecture应该在pageview之后，购买相同的人谁做了Enroll_Free_Lecture和购买应该是在Enroll_Free_Lecture之后。流程: 1-t级页面查看Enroll_Free_Lecture后的第二级pageview第三级购买：

​

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
  `data` 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 `data`
  as tb2, UNNEST(hits) as hitss) tb2 where tb2.date <= tb1.date and tb2.type = "pageview" )) 

  or (
  tb1.eventAction = 'Purchase' and exists (
    select 1 
    from `data` tb3, unnest (hits) hits
    where hits.type = 'pageview'
    and tb3.date <= tb1.date
  ) and exists (
    select 1 
    from `data` tb3, unnest (hits) hitss
    where hitss.eventInfo.eventCategory = 'Enroll_Free_Lecture'
    and tb3.date <= tb1.date
  )
)

Answer 1

此错误是由您在WHERE子句中的子查询内执行的比较引起的。如果您按如下所示对比较进行注释，则将运行查询。

with t as(select * from `deploy.ga`)
SELECT
  tb1.*
FROM (
  SELECT
    date,
    hits.type AS type,
    hits.eventInfo.eventCategory AS eventCategory,
    hits.eventInfo.eventAction AS eventAction,
    hits.eventInfo.eventLabel AS person_email,
  FROM
    `deploy.ga`,
    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
        `deploy.ga` AS tb2, 
        UNNEST(hits) AS hitss) tb2
    WHERE
      tb2.type = "pageview" --AND
      --tb2.date <= tb1.date
      )
      )
  OR ( tb1.eventAction = 'Purchase'
    AND EXISTS (
    SELECT
      1
    FROM
      `deploy.ga`tb3,
      UNNEST (hits) hits
    WHERE
      hits.type = 'pageview'
      --AND tb3.date <= tb1.date 
      )
    AND EXISTS (
    SELECT
      1
    FROM
      `deploy.ga`tb3,
      UNNEST (hits) hitss
    WHERE
      hitss.eventInfo.eventCategory = 'Enroll_Free_Lecture'
      --AND tb3.date <= tb1.date 
      ) )

为什么会发生这种情况？我在这里可以看到两个错误：

1. 您正在尝试访问子查询中的别名表。这样做时，对表的引用就会丢失。如果您运行一个非常简单的查询，如select (select 1 ) t, (select count(1) from t)或select (select 1 ) t where (select count(1) from t) = 1，您将注意到没有找到表t。
2. 是您的问题所在:您正在尝试比较来自两个不同表的两个字段，而不使用任何类型的JOIN。如果需要比较来自两个不同表的两个字段，则必须以某种方式连接这些表。

我希望它能帮上忙