SQLで重複を排除して価格情報を統合する方法

## はじめに

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

## サンプルテーブルの作成

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

#### テーブル1: `product_master`
このテーブルには、商品コードと仕入価格が格納されています。
```sql
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);
```
![スクリーンショット 2024-07-19 22.10.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3364428/9957847c-1eb9-a31f-0e2f-755c5278539f.png)

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

```sql
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);
```
![スクリーンショット 2024-07-19 22.11.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3364428/d0e5df39-4f3d-250f-e880-f553ee3fc73f.png)

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

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

```sql
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;
```

#### クエリの解説

1. **`FULL OUTER JOIN`**:
    - `FULL OUTER JOIN`は、両方のテーブルのすべての行を結合し、一方のテーブルにしか存在しない行も含めます。これにより、両方のテーブルに存在するすべての商品コードを取得できます。

2. **`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`の値を返します。これにより、優先順位をつけて価格情報を取得できます。

3. **`SELECT DISTINCT`**:
    - `SELECT DISTINCT`は、重複を排除して一意の行を取得します。このクエリでは、`product_supplier`テーブルの`item_code`と`standard_price`の組み合わせが重複しないようにしています。

### クエリの結果

上記のクエリを実行すると、以下の結果が得られます：
![スクリーンショット 2024-07-19 22.12.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3364428/1f3f7a31-3e1b-a0c9-ca77-c18c115ffe59.png)


## 説明

- `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の強力なクエリ機能を活用することで、複雑なデータ操作も簡単に行うことができます。今後のデータ処理や分析において、この記事の方法が役立つことを願っています。