月初からの積み上げデータを作る際にBigQueryのJOINルールでハマったのでその解決策を共有します
日次の仮想テーブル(BigQueryで日次の仮想テーブルを作る)を作って月初からその日までの情報をjoinしてcountすることで積み上げグラフを作ろうとしていました。しかし、下記のエラーが...
LEFT OUTER JOIN cannot be used without a condition
that is an equality of fields from both sides of the join
BigQueryではLEFT,OUTER JOINをするときにはイコールで条件を結ばないといけない
解決策
月次積み上げなのでmonthは等しいので(少し無駄になるが)monthの条件も付けてJOINさせる
Before(エラーになるもの)
SELECT t1.date, count(u.id)
FROM
(
SELECT
DATE_SUB(CURRENT_DATE(), INTERVAL n day) as date
FROM UNNEST(GENERATE_ARRAY(1, 365)) AS n ORDER BY n
) AS t1
LEFT JOIN `users` AS u
ON u.date BETWEEN DATE_TRUNC(t1.date, MONTH) AND t1.date -- 月初から指定日
WHERE
u.city_id in (....)
GROUP BY
t1.date
After
SELECT t1.date, count(u.id)
FROM
(
SELECT
DATE_SUB(CURRENT_DATE(), INTERVAL n day) as date
, DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL n day), MONTH) as month -- LEFT JOINのイコール条件作るため
FROM UNNEST(GENERATE_ARRAY(1, 365)) AS n ORDER BY n
) AS t1
LEFT JOIN `users` AS u
ON DATE_TRUNC(u.date, MONTH) = t1.month -- LEFT JOINのイコール条件作るため
AND u.date BETWEEN DATE_TRUNC(t1.date, MONTH) AND t1.date -- 月初から指定日
WHERE
u.city_id in (....)
GROUP BY
t1.date