過去の状態のテーブルが見たい（7日前まで）

SELECT
  *
FROM
  `dataset.table_name`
-- 絶対時間
FOR SYSTEM_TIME AS OF TIMESTAMP('2023-12-01 14:00:00', 'Asia/Tokyo');
-- 相対時間
-- FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR);

Yuichi

2つのテーブル（table1とtable2）の差分を両方向で取得

SELECT 'table1' AS source_table, *
FROM (
    SELECT * FROM {table1}
    EXCEPT DISTINCT
    SELECT * FROM {table2}
)
UNION ALL
SELECT 'table2' AS source_table, *
FROM (
    SELECT * FROM {table2}
    EXCEPT DISTINCT
    SELECT * FROM {table1}
)

このクエリでは、以下の変更を行っています:
各サブクエリの結果に source_table という新しいカラムを追加しています。
table1 からの結果には 'table1' という値を、table2 からの結果には 'table2' という値を設定しています。
この修正により、結果セットの各行がどちらのテーブルからのものかを簡単に識別できるようになります。source_table カラムの値を確認することで、その行が table1 に固有のものか、table2 に固有のものかがわかります。
この方法は、両方のテーブルの差分を効率的に取得しつつ、各行の出所を明確にすることができます。データ分析やデバッグの際に特に有用です


```WITH new_table AS (
新しいロジック
)

SELECT
  'new_table' AS source_table
  ,*
FROM (
  SELECT * FROM new_table
  EXCEPT DISTINCT
  SELECT * FROM {{ ref('既存のモデル') }}
)

UNION ALL

SELECT
  'old_table' AS source_table
  ,*
FROM (
  SELECT * FROM {{ ref('既存のモデル') }}
  EXCEPT DISTINCT
  SELECT * FROM new_table
)

Yuichi

テーブルの完全なコピーを作成する

like 句は、新しいテーブルのスキーマ（列の構造）を既存のテーブルと同じにします

CREATE OR REPLACE TABLE `プロジェクトID.データセットID.test_02`
like `プロジェクトID.データセットID.test_01` AS (
SELECT *
FROM `プロジェクトID.データセットID.test_01`
) ;

Yuichi

先月の低速クエリランキング

WITH jobs AS (
  SELECT
    user_email,
    query,
    total_slot_ms,
    TIMESTAMP(DATETIME(TIMESTAMP(creation_time), "Asia/Tokyo")) AS creation_time_jst
  FROM
    `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
),
filtered_jobs AS (
  SELECT
    user_email,
    query,
    total_slot_ms,
    creation_time_jst
  FROM
    jobs
  WHERE
    DATE(creation_time_jst) >= DATE_SUB(DATE(CURRENT_TIMESTAMP(), "Asia/Tokyo"), INTERVAL 1 MONTH)
    AND DATE(creation_time_jst) < DATE(CURRENT_TIMESTAMP(), "Asia/Tokyo")
)
SELECT
  FORMAT_TIMESTAMP("%Y-%m", creation_time_jst) AS query_month,
  user_email,
  query,
  SUM(total_slot_ms) AS total_slot_ms_sum
FROM
  filtered_jobs
GROUP BY
  query_month, user_email, query
ORDER BY
  total_slot_ms_sum DESC
LIMIT  50

Yuichi

ローカルで%%bigquery使いたい

ローカルで%%bigqueryを使用するには、以下の手順を実行します：

まず、必要なライブラリをインストールします：

pip install google-cloud-bigquery pandas pydata-google-auth

Jupyter Notebookを開き、以下のコードを実行して認証を行います：

import pydata_google_auth

credentials = pydata_google_auth.get_user_credentials(
    ['https://www.googleapis.com/auth/bigquery'],
)

このコードを実行すると、GCPへのログイン画面が表示されます。BigQueryの権限を持つユーザーを選択してログインし、表示された認証コードをコピーしてセルの実行結果に入力します

次に、取得した認証情報をBigQueryのマジックコマンドに設定します：

python
from google.cloud.bigquery import magics
magics.context.credentials = credentials

これで%%bigqueryマジックコマンドを使用できるようになります。

例えば：

%%bigquery
SELECT * FROM `your_project.your_dataset.your_table` LIMIT 10

このようにして、ローカル環境のJupyter NotebookでBigQueryを直接操作できるようになります

Yuichi

テーブルが存在していた削除前の時間を取得する
・1 時間前の時点を取得

SELECT UNIX_MILLIS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))

・絶対時間の値を取得

SELECT UNIX_MILLIS('2022-12-05 12:23:34.456789Z')

復元コマンド実行

Cloud Shell から下記コマンド実行
取得した時間に存在していたテーブルを新しいデータセットにコピーする

bq cp test_dataset.test_table01@1670201415117 test_dataset.new_test_table01

注）test_table01：削除したテーブル名を入力
　　1670201415117：手順①で取得した値を入力
　　new_test_table01：新しく作成するテーブル名を入力　※削除したテーブルと同じ名前でもOK

コマンド実行後、Current status が「DONE」となれば成功です。

Yuichi

ドリルアクロス

BigQueryで配列内の重複なし要素数をカウントするUDFを作成し、集計クエリで活用する方法は以下の通りです。

UDFの作成

CREATE OR REPLACE FUNCTION mydataset.distinctCount(arr ARRAY<ANY TYPE>) AS (
  (SELECT COUNT(DISTINCT v) FROM UNNEST(arr) AS v)
);

この粒度でUDF使わないと、エラーになる

集計クエリの例

SELECT
  EXTRACT(YEAR FROM 集計月) AS 集計年,
  店,
  mydataset.distinctCount(
    ARRAY_CONCAT_AGG(ARRAY(SELECT 購買者 FROM UNNEST(Aの購買履歴)))
  ) AS 購買者数
FROM
  `table`
GROUP BY
  集計年, 店
ORDER BY
  集計年, 購買者数 DESC;

udf__distinct_count.sql

{{
    config(
        return_type="int64",
        arguments=[
            {
                "name": "arr",
                "type": "ANY TYPE",
            },
        ],
    )
}}

(select count(distinct v) from unnest(arr) as v)

他のデザインパターン
上記のパターンと違い日の粒度が固定なので、取り回しがしずらいが
件数とUUで冗長な記載を避けれるので🙆

with
    base as (
        select
            -- 月単位に切り捨てた日付
            date(date_trunc(t1.date_col, month)) as month_col,

            -- 全体のIDリスト
            array_agg(t1.id_col) as all_ids,

            -- 条件1に該当するIDリスト
            array_agg(
                case
                    when cond1 then t1.id_col
                end
                ignore nulls
            ) as cond1_ids,

            -- 条件2に該当するIDリスト
            array_agg(
                case
                    when cond2 then t1.id_col
                end
                ignore nulls
            ) as cond2_ids

        from main_table t1
        left join sub_table1 t2 on t1.join_key = t2.join_key
        left join sub_table2 t3 on t1.join_key = t3.join_key
        group by 1
    )

select
    month_col,

    -- 件数
    coalesce(array_length(all_ids), 0) as total_cnt,
    (select count(distinct id) from unnest(all_ids) as id) as total_uu,

    coalesce(array_length(cond1_ids), 0) as cond1_cnt,
    (select count(distinct id) from unnest(cond1_ids) as id) as cond1_uu,

    coalesce(array_length(cond2_ids), 0) as cond2_cnt,
    (select count(distinct id) from unnest(cond2_ids) as id) as cond2_uu

from base
order by month_col desc

Yuichi

途中で集計粒度を変更

with
    -- サンプルデータ（登録日、ユーザーID、サービスカテゴリ）
    sample_data as (
        select *
        from
            unnest(
                array<
                    struct<register_date date, user_id string, service_category string>
                >[
                    (date '2024-01-01', '1', 'サービスA'),
                    (date '2024-01-02', '2', 'サービスB'),
                    (date '2024-01-02', '3', 'サービスA'),
                    (date '2024-02-01', '3', 'サービスB'),
                    (date '2024-02-01', '4', 'サービスA'),
                    (date '2024-02-01', '3', 'サービスB')
                ]
            )
    ),

    -- 登録日（単位：日）ごとにユーザーID配列を作成
    aggregated_ids as (
        select
            register_date,

            -- 全体ユーザーID（サービスA/B問わず）
            array_agg(user_id) as all_user_ids,

            -- サービスAのユーザーID
            array_agg(
                case when service_category = 'サービスA' then user_id end ignore nulls
            ) as s_a_user_ids,

            -- サービスBのユーザーID
            array_agg(
                case when service_category = 'サービスB' then user_id end ignore nulls
            ) as s_b_user_ids,

        from sample_data
        group by 1
    ),

    -- 月単位に変換して配列をマージ（ARRAY_CONCAT_AGG）
    aggregated_counts_month as (
        select
            date_trunc(register_date, month) as register_month,

            -- 全ユーザーIDを月単位で結合
            array_concat_agg(all_user_ids) as all_user_ids,

            -- サービスAユーザーIDを月単位で結合
            array_concat_agg(s_a_user_ids) as s_a_user_ids,

            -- サービスBユーザーIDを月単位で結合
            array_concat_agg(s_b_user_ids) as s_b_user_ids

        from aggregated_ids
        group by 1
    ),

    -- 月単位で件数・ユニークユーザー数を集計
    aggregated_counts as (
        select
            register_month,

            -- 全体の件数（重複あり）
            coalesce(array_length(all_user_ids), 0) as total_cnt,

            -- 全体のユニークユーザー数（重複排除）
            (select count(distinct id) from unnest(all_user_ids) as id) as total_uu,

            -- サービスAの件数とUU
            coalesce(array_length(s_a_user_ids), 0) as total_a_cnt,
            (select count(distinct id) from unnest(s_a_user_ids) as id) as total_a_uu,

            -- サービスBの件数とUU
            coalesce(array_length(s_b_user_ids), 0) as total_b_cnt,
            (select count(distinct id) from unnest(s_b_user_ids) as id) as total_b_uu

        from aggregated_counts_month
    )

-- 最終出力：月ごとの件数・UU（全体、サービスA、サービスB）
select *
from aggregated_counts