はじめに

データベースを扱う際に、異なるテーブルのデータを統合し、重複を排除する必要がある場合があります。本記事では、Google BigQueryを使用して商品コードをキーにし、各テーブルの価格情報を統合して1つの列にまとめる方法について説明します。

サンプルテーブルの作成

まず、Google BigQueryのデータセット data-sandbox-421715.db_test_001 に2つのテーブルを作成します。

テーブル1: `product_master`

このテーブルには、商品コードと仕入価格が格納されています。

CREATE OR REPLACE TABLE `data-sandbox-421715.db_test_001.product_master` (
    product_code STRING,
    purchase_price INT64
);

INSERT INTO `data-sandbox-421715.db_test_001.product_master` (product_code, purchase_price) VALUES
('P001', 100),
('P002', 200),
('P003', 300);

テーブル2: `product_supplier`

このテーブルには、商品コードと標準価格が格納されています。

CREATE OR REPLACE TABLE `data-sandbox-421715.db_test_001.product_supplier` (
    item_code STRING,
    standard_price INT64
);

INSERT INTO `data-sandbox-421715.db_test_001.product_supplier` (item_code, standard_price) VALUES
('P001', 150),
('P002', 250),
('P004', 300);

重複を排除して価格情報を統合するクエリ

次に、上記のテーブルをFULL OUTER JOINを使用して結合し、重複を排除して価格情報を1つの列にまとめるクエリを作成します。

SELECT
    COALESCE(a.product_code, b.item_code) AS product_code,
    COALESCE(b.standard_price, a.purchase_price) AS price
FROM
    `data-sandbox-421715.db_test_001.product_master` a
FULL OUTER JOIN (
    SELECT DISTINCT
        item_code,
        standard_price
    FROM
        `data-sandbox-421715.db_test_001.product_supplier`
) b
ON a.product_code = b.item_code;

クエリの解説

FULL OUTER JOIN:
- FULL OUTER JOINは、両方のテーブルのすべての行を結合し、一方のテーブルにしか存在しない行も含めます。これにより、両方のテーブルに存在するすべての商品コードを取得できます。
COALESCE関数:
- COALESCE関数は、引数の中で最初にNULLでない値を返します。このクエリでは、COALESCE(a.product_code, b.item_code)はa.product_codeがNULLでない場合はその値を返し、NULLの場合はb.item_codeの値を返します。同様に、COALESCE(b.standard_price, a.purchase_price)はb.standard_priceがNULLでない場合はその値を返し、NULLの場合はa.purchase_priceの値を返します。これにより、優先順位をつけて価格情報を取得できます。
SELECT DISTINCT:
- SELECT DISTINCTは、重複を排除して一意の行を取得します。このクエリでは、product_supplierテーブルのitem_codeとstandard_priceの組み合わせが重複しないようにしています。

クエリの結果

上記のクエリを実行すると、以下の結果が得られます：

説明

product_codeがP001の場合、product_supplierのstandard_priceの150が取得されます。
product_codeがP002の場合、product_supplierのstandard_priceの250が取得されます。
product_codeがP003の場合、product_supplierに存在しないため、product_masterのpurchase_priceの300が取得されます。
product_codeがP004の場合、product_masterに存在しないが、product_supplierのstandard_priceの300が取得されます。

この方法で、どちらかのテーブルに存在するすべての行を含め、重複を排除しつつ、価格情報を1つの列にまとめることができます。Google BigQueryを使用することで、大規模なデータセットに対しても効率的にこのような操作を行うことができます。

終わりに

今回の記事では、Google BigQueryを使用して異なるテーブルの価格情報を統合し、重複を排除する方法について説明しました。このようなデータ統合のテクニックは、データの一貫性を保ちつつ、分析の精度を向上させるために非常に重要です。BigQueryの強力なクエリ機能を活用することで、複雑なデータ操作も簡単に行うことができます。今後のデータ処理や分析において、この記事の方法が役立つことを願っています。