Tableauを勉強するブログ

Tableauを勉強しつつ、学びをまとめます

BigQueryで実績値と予測値をまとめたテーブルを作成した

Tableauではないけれど業務的にデータを触ったので備忘。 初学者なので、内容に不備があればご指摘いただけますと幸いです。

概要

  • BQ上に存在するデータと存在しないデータを組み合わせたテーブルを作成した
  • 月初〜データが存在する最大日→実績値、データの最大日+1日〜月末日→予測値、となるテーブル。縦持ち。
  • アウトプットイメージ
日付 売上 予実
1/1 3 実績値
… … …
1/8 10 実績値
1/9 8 予測値
… … …
1/31 3 実績値

使った技術

  1. UDF関数
  2. 当月末日算出用日付関数
  3. 配列の作成
  4. 配列を行にバラす
  5. 結果

それぞれ詳細

1.UDF関数

  • 予測値算出用関数を2x+yとする
-- 関数を作成
CREATE TEMPORARY FUNCTION uriage(x INT64, y INT64)
RETURNS INT64
LANGUAGE js AS """
  return 2x+y;
""";

-- 代入する値
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)

-- 関数、値をもとにクエリ実行
SELECT x, y, uriage(x, y) as product
FROM numbers;
  • 'numbers'には配列も入れることができるので、後ほど配列を入れる
  • 参考URL -- 公式レファレンス

2. 当月末日算出用日付関数

  • 当月の月数 + 1ヶ月 - 1日
  • 一旦「日付」だけほしかったので整形
-- 当日（日付のみ）
SELECT FORMAT_DATE("%d", TODAY())

-- 当月末日（日付のみ）
SELECT FORMAT_DATE("%d", DATE_ADD(DATE_ADD(DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo") , INTERVAL 1 MONTH), INTERVAL -1 DAY))

3. 配列の作成

  • 2.で取得した「当月末の日付」と、「当日日付」を使って配列を作成
SELECT GENERATE_ARRAY(SELECT FORMAT_DATE("%d", TODAY()), SELECT FORMAT_DATE("%d", DATE_ADD(DATE_ADD(DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo") , INTERVAL 1 MONTH), INTERVAL -1 DAY)), 1) AS dates;

4. 配列を行にバラす（フラット化？というらしい）

SELECT *
FROM UNNEST GENERATE_ARRAY(SELECT FORMAT_DATE("%d", TODAY()), SELECT FORMAT_DATE("%d", DATE_ADD(DATE_ADD(DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo") , INTERVAL 1 MONTH), INTERVAL -1 DAY)), 1)
  AS dates;

5. 結果

  • 今まで作ったいろいろなパーツを集結&ユニオン
-- 関数を作成
CREATE TEMPORARY FUNCTION multiplyInputs(x INT64, y INT64)
RETURNS INT64
LANGUAGE js AS """
  return 2x+y;
""";

-- 代入する値
WITH date_list AS
  (SELECT *, 1 AS y
FROM UNNEST GENERATE_ARRAY(SELECT FORMAT_DATE("%d", TODAY()), SELECT FORMAT_DATE("%d", DATE_ADD(DATE_ADD(DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo") , INTERVAL 1 MONTH), INTERVAL -1 DAY)), 1)
  AS dates;)

-- 関数、値をもとにクエリ実行
--- まずは実数値ゾーン
SELECT
 date, sum(uriage), '実数値' AS indicator
FROM
 hoge.fuga
GROUP BY date

--- 以下、予測値部分
UNION ALL
SELECT dates,  uriage(dates, y), '予測値' AS indicator
FROM date_list;

躓いたところ

  • 本当は今回作成したテーブルをTableauの「カスタムクエリ」に記載したかったが、UDF関数の'create'の時点でエラーが出た。使えなさそう…
  • UDF関数を使用してクエリ実行したあと、viewとして保存しようと思ったができなかった。やむなくテーブルとして保存。
  • テーブルとしてデータを保存するのであれば、毎日洗い変えられるデータに合わせてテーブルを日時更新する必要あり…
  • そもそもBQ内でfor文（ループ処理）を使えないのか？配列を使うのってなんだかセンスがないような
  • 配列も、日付の部分をバラすのではなく日付の形そのままで使う技術があるはず
  • などなど、まだ良いやり方がありそうなので模索したい所存…