3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

BigQueryで日次の仮想テーブルを作る

Last updated at Posted at 2018-07-17

redashで可視化のため累積グラフを作るときに必要となったので、基準となる日ベースの仮想テーブルを作りました。

いろんな書き方がありそうですがありますが、思いついた書き方はこれ
スタンダードクエリを使っています

-- 昨日から1年間分の日付仮想テーブル
SELECT 
  t1.date
FROM   
  (
    SELECT DATE_SUB(CURRENT_DATE(), INTERVAL n day) as date 
    FROM UNNEST(GENERATE_ARRAY(1, 365)) AS n ORDER BY n  -- 365日分
  )  AS t1
;

ここに JOIN させていく形で今まで作れていなかった各種テーブルが作れそう


参考

mysqlの同様の仮想テーブル作成

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?