LEFT OUTER JOIN 在 bigquery 上创建子查询时出错 [英] LEFT OUTER JOIN Error creating a subquery on bigquery
本文介绍了LEFT OUTER JOIN 在 bigquery 上创建子查询时出错的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试从我 bq 上的事件表中评估 MAL、WAL 和 DAU...
I'm trying to eval MAL, WAL and DAU from a event table on my bq...
我创建一个查询查找 DAU 并与他一起查找 WAU 和 MAU,但它不起作用,我收到了这个错误:
I create a query find DAU and with him find WAU and MAU, but it does not work, i received this error:
LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
这是我的问题
WITH dau AS (
SELECT
date,
COUNT(DISTINCT(events.device_id)) as DAU_explorer
FROM `workspace.event_table` as events
GROUP BY 1
)
SELECT
date,
dau,
(SELECT
COUNT(DISTINCT(device_id))
FROM `workspace.event_table` as events
WHERE events.date BETWEEN DATE_ADD(dau.date, INTERVAL -30 DAY) AND dau.date
) AS mau,
(SELECT
COUNT(DISTINCT(device_id)) as DAU_explorer
FROM `workspace.event_table` as events
WHERE events.date BETWEEN DATE_ADD(dau.date, INTERVAL -7 DAY) AND dau.date
) AS wau
FROM dau
我的错误在哪里?不能在 bq 上运行这样的子查询吗?
Where is my error? Is not possible run subqueries like this on bq?
推荐答案
试试这个:
WITH data AS (
SELECT DATE(creation_date) date, owner_user_id device_id
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE EXTRACT(YEAR FROM creation_date)=2017
)
#standardSQL
SELECT DATE_SUB(date, INTERVAL i DAY) date_grp
, COUNT(DISTINCT IF(i<31,device_id,null)) unique_30_day_users
, COUNT(DISTINCT IF(i<8,device_id,null)) unique_7_day_users
FROM `data`, UNNEST(GENERATE_ARRAY(1, 30)) i
GROUP BY 1
ORDER BY date_grp
LIMIT 100
OFFSET 30
如果您正在寻找更有效的解决方案,请尝试近似结果.
这篇关于LEFT OUTER JOIN 在 bigquery 上创建子查询时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文