コンテンツにスキップ

耳標検索・頭数推移機能追加－GCP環境構築－BigQueryテーブル・View

テーブル作成

v2_bq_1

①GCP管理画面にログインし、ナビゲーションメニューをクリック
②「BigQuery」をクリック
③「SQLワークスペース」をクリック

v2_bq_2

④対象のプロジェクトを選択 ⑤データセット"ja_ds"を選択
⑥「クエリを新規作成」をクリック
⑦クエリ入力欄に、下記SQLをペースト

create table ja_ds.cattle_barn (
    id STRING NOT NULL,
    ip STRING NOT NULL,
    ant STRING NOT NULL,
    name STRING NOT NULL,
);

create table ja_ds.cattle (
    id STRING NOT NULL,
    epc STRING NOT NULL,
    iin STRING NOT NULL,
);

⑧「実行」をクリック

v2_bq_3

データセット"ja_ds"配下に、テーブルが２つ作成されていることを確認

  • cattle
  • cattle_barn

View作成

v2_bq_4

テーブル作成時と同じ手順で、クエリ入力欄に下記テキストをペースト、実行

-- 頭数推移（日別）集計用
CREATE view ja_ds.v_rfid_list_day AS
  SELECT DISTINCT
    DATE(polling_timestamp, 'Asia/Tokyo') AS polling_dt_jst,
    TIMESTAMP(DATE(polling_timestamp, 'Asia/Tokyo'), 'Asia/Tokyo') AS polling_ts_utc,
    DATE(DATETIME_ADD(polling_timestamp, INTERVAL -1 DAY), 'Asia/Tokyo') AS incdec_base_dt_jst,
    epc
  FROM
    ja_ds.rfid;

-- 頭数推移（月別）集計用
CREATE view ja_ds.v_rfid_list_month AS
  SELECT DISTINCT
    TIMESTAMP(DATE_TRUNC(DATE(polling_timestamp, 'Asia/Tokyo'), MONTH), 'Asia/Tokyo') AS polling_ts_utc,
    DATE_TRUNC(DATE(polling_timestamp, 'Asia/Tokyo'), MONTH) AS polling_mth_jst,
    DATE_TRUNC(DATE_ADD(DATE(polling_timestamp, 'Asia/Tokyo'), INTERVAL -1 MONTH), MONTH) AS incdec_base_mth_jst,
    epc
  FROM
    ja_ds.rfid;

-- 頭数推移（日別）減少数
CREATE view ja_ds.v_rfid_dec_day AS
  SELECT
    DATE_ADD(a.polling_dt_jst, interval 1 day) AS polling_dt_jst,
    TIMESTAMP_ADD(a.polling_ts_utc, interval 1 day) AS polling_ts_utc,
    SUM(IF(b.epc is null, 1, 0)) AS cnt
  FROM
    ja_ds.v_rfid_list_day a LEFT OUTER JOIN 
    ja_ds.v_rfid_list_day b ON(a.polling_dt_jst=b.incdec_base_dt_jst AND a.epc=b.epc)
  GROUP BY a.polling_dt_jst,a.polling_ts_utc;

-- 頭数推移（日別）増加数
CREATE view ja_ds.v_rfid_inc_day AS
  SELECT
    b.polling_dt_jst,
    b.polling_ts_utc,
    SUM(IF(a.epc is null, 1, 0)) AS cnt
  FROM ja_ds.v_rfid_list_day b LEFT OUTER JOIN
    ja_ds.v_rfid_list_day a ON(a.polling_dt_jst=b.incdec_base_dt_jst AND a.epc=b.epc)
  GROUP BY b.polling_dt_jst,b.polling_ts_utc;

-- 頭数推移（月別）減少数
CREATE view ja_ds.v_rfid_dec_month AS
  SELECT
    DATE_ADD(a.polling_mth_jst, interval 1 month) AS polling_mth_jst,
    TIMESTAMP(DATE_ADD(a.polling_mth_jst, interval 1 month), 'Asia/Tokyo') AS polling_ts_utc,
    SUM(IF(b.epc is null, 1, 0)) AS cnt
  FROM ja_ds.v_rfid_list_month a LEFT OUTER JOIN
    ja_ds.v_rfid_list_month b ON(a.polling_mth_jst=b.incdec_base_mth_jst AND a.epc=b.epc)
  GROUP BY a.polling_mth_jst,polling_ts_utc;

-- 頭数推移（月別）増加数
CREATE view ja_ds.v_rfid_inc_month AS
  SELECT
    b.polling_mth_jst,
    b.polling_ts_utc,
    SUM(IF(a.epc is null, 1, 0)) AS cnt
  FROM ja_ds.v_rfid_list_month b LEFT OUTER JOIN
    ja_ds.v_rfid_list_month a ON(a.polling_mth_jst=b.incdec_base_mth_jst AND a.epc=b.epc)
  GROUP BY b.polling_mth_jst,b.polling_ts_utc;

-- 牛舎マスタ連結用
CREATE view ja_ds.v_rfid_for_cattle_barn AS
SELECT
  REPLACE(SPLIT(gateway_id,"-")[OFFSET(1)], "_",".") AS ip,
  ant,
  epc,
  polling_timestamp
FROM
  ja_ds.rfid;



v2_bq_5

データセット"ja_ds"配下に、Viewが7つ追加作成されていることを確認

  • v_rfid_dec_day
  • v_rfid_dec_month
  • v_rfid_for_cattle_barn
  • v_rfid_inc_day
  • v_rfid_inc_month
  • v_rfid_list_day
  • v_rfid_list_month



※使用したクエリのテキストは、保存せずに終了してください。


以上でテーブル・View作成は完了です。

Back to top