BigQueryで1つのSELECTでJOIN(INなどのsemi-join含む)とUNION ALLを組み合わすと以下のようなエラーが発生します。
JOIN (including semi-join) and UNION ALL (comma, date range) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.
結論から言うと
素直に対応するとエラーに書いてあるようにUNION ALLをINNER JOINやOUTER JOINに置き換えれば良いのですが面倒です。
ここでエラーに注目してみると1つのSELECTでやっていることで怒られているので、そのままサブクエリで書けば対応できました。
文章で書くと良くわからないと思うので以下に例を書いておきます。
エラーが発生するクエリ
例えば、2016/9/13に初めてセッションが発生ユーザーで1週間以内に新規登録したユーザーはどのくらいいるのかみたいとします。そうすると以下のようなクエリになるかと思います。
※新規登録はイベントカテゴリがSignUp、イベントアクションがRegisteredとします。
SELECT
fullVisitorId
FROM
(TABLE_DATE_RANGE([project:xxxxxx.ga_sessions_], TIMESTAMP('20160913'), TIMESTAMP('20160919')))
WHERE
hits.eventInfo.eventCategory='SignUp'
AND hits.eventInfo.eventAction='Registered'
AND fullVisitorId IN (
SELECT
fullVisitorId
FROM
[project:xxxxxx.ga_sessions_20160913]
WHERE
visitNumber=1
GROUP BY
fullVisitorId )
DATERANGE(UNION ALL)で9/13~9/20に新規登録したユーザーをとってきて、9/13に初回セッションが発生したユーザーをfullVisitorIdでIN(simi-join)しています。
UNION ALL とJOIN(semi-join含む)を組み合わせているため、さきほどのエラーが発生します。
JOIN (including semi-join) and UNION ALL (comma, date range) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.
UNION ALLをJOINに置き換える
エラーで言われているように素直にUNION ALL(DATE_RANGE)をJOINに書き直してみます。
SELECT
a.fullVisitorId AS fullVisitorId
FROM (
SELECT
fullVisitorId
FROM
(TABLE_DATE_RANGE([project:xxxxxx.ga_sessions_], TIMESTAMP('20160913'), TIMESTAMP('20160920')))
WHERE
hits.eventInfo.eventCategory='SignUp'
AND hits.eventInfo.eventAction='Registered'
GROUP BY
fullVisitorId ) AS a
INNER JOIN (
SELECT
fullVisitorId
FROM
[project:xxxxxx.ga_sessions_20160913]
WHERE
visitNumber=1
GROUP BY
fullVisitorId ) AS b
ON
a.fullVisitorId = b.fullVisitorId
これでエラーは発生しなくなったのですが、もう少し複雑なクエリになってくると面倒です。
UNION ALLをサブクエリで書く
1つのSELECTでやっていることで怒られているので、そのままサブクエリで書けば対応できます。
個人的にはこれで対応しています。
SELECT
fullVisitorId
FROM (
SELECT
fullVisitorId
FROM
(TABLE_DATE_RANGE([project:xxxxxx.ga_sessions_], TIMESTAMP('20160913'), TIMESTAMP('20160920')))
WHERE
hits.eventInfo.eventCategory='SignUp'
AND hits.eventInfo.eventAction='Registered'
GROUP BY
fullVisitorId )
WHERE
fullVisitorId IN (
SELECT
fullVisitorId
FROM
[project:xxxxxx.ga_sessions_20160913]
WHERE
visitNumber=1
GROUP BY
fullVisitorId )
※どなたかもっと良い方法知っていたら教えてください。