时间戳

首页 > 解决方案 > 无法加入两个子查询

sql - 无法加入两个子查询

问题描述

我有一张桌子，我想：1）检查满足要求的访问者（VisitorID）2）获取这些访问者访问我网站的所有时间。

我一直在尝试将我的第一个查询与我的所有条件一起加入，另一个查询在其中获得其余相关变量。

SELECT ID, h.Info, h.Action
FROM `table` as A,
UNNEST (hits) as h
WHERE (h.Info LIKE '%PPP%' 
OR h.Info LIKE '%BBB%'
OR h.Info LIKE '%SSS%'
OR h.Info LIKE '%FFF%') AND  
h.Category LIKE '%Lock%' AND
(h.Action LIKE '%Regist%' OR h.Action LIKE '%NotR%') AND
h.Label LIKE '%Success%' 
JOIN
SELECT ID, h.Info, h.Action, time, visits, EXTRACT(TIME FROM TIMESTAMP_SECONDS(StartTime)) AS time_visit, PARSE_DATE("%Y%m%d", date) AS date_visit
FROM `table`,
UNNEST (hits) as h
WHERE (h.Info LIKE '%PPP%' 
OR h.Info LIKE '%BBB%'
OR h.Info LIKE '%SSS%'
OR h.Info LIKE '%FFF%') AND 
date > "20190529") AS B ON A.ID = B.ID

我已经多次更改 JOIN ，包括/排除括号，但我已经没有想法了。

标签： sqlgoogle-bigquery

解决方案

我会继续cte版本

with cte as
(
SELECT ID, h.Info, h.Action
FROM `table` as A,
UNNEST (hits) as h
WHERE (h.Info LIKE '%PPP%' 
OR h.Info LIKE '%BBB%'
OR h.Info LIKE '%SSS%'
OR h.Info LIKE '%FFF%') AND  
h.Category LIKE '%Lock%' AND
(h.Action LIKE '%Regist%' OR h.Action LIKE '%NotR%') AND
h.Label LIKE '%Success%' 
),
cte1 as
(
SELECT ID, h.Info, h.Action, time, visits, EXTRACT(TIME FROM TIMESTAMP_SECONDS(StartTime)) AS time_visit, PARSE_DATE("%Y%m%d", date) AS date_visit
FROM `table`,
UNNEST (hits) as h
WHERE (h.Info LIKE '%PPP%' 
OR h.Info LIKE '%BBB%'
OR h.Info LIKE '%SSS%'
OR h.Info LIKE '%FFF%') AND 
date > "20190529"
) select cte.*,cte1.* from cte join cte1 on cte.id=cte1.id

推荐阅读