LoginSignup
1

More than 3 years have passed since last update.

BigQuery / MySQLで日次の仮想テーブル(連番の仮想表)を作成する

Last updated at Posted at 2019-03-13

はじめに

こんな感じに過去1年分の仮想テーブルを作成するSQLです。
日次ベースの仮想テーブルを、累積グラフ作成の際の基準とします。
JOIN させていく形で利用します。

image.png

SQL

BigQuery


SELECT
  DATE_SUB(CURRENT_DATE(), INTERVAL n day) as date 
FROM
  UNNEST(GENERATE_ARRAY(1, 365)) AS n
;

MySQL

SELECT
  CURDATE() - INTERVAL num DAY AS date
FROM ( 
  SELECT
    @num := 0 AS num
  UNION
  SELECT
    @num := @num + 1 AS num
  FROM
    information_schema.COLUMNS
  LIMIT 365
) t1;

参考

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
1