【Google Spread Sheets】Connected Sheetsを使用して重複データを確認する。


## コネクテッドシートとは
SQLの知識がなくてもスプレッドシート上で、
BigQueryに保存されているビッグデータへのアクセス、分析、可視化、共有、データの自動更新を行うことができるGoogle Spread Sheetの拡張機能。

・BigQueryのストレージ料金
・BigQueryのクエリ料金
の課金は発生する。

※参考：[Connected Sheetsで始めるデータ分析](https://blog.g-gen.co.jp/entry/connected-sheets)

## コネクテッドシートの使い方
>1. Google スプレッドシートのスプレッドシートを 1 つ作成または開きます。
>2. [データ] をクリックし、[データコネクタ] をクリックしてから、[BigQuery に接続する] をクリックします。
注: [データコネクタ] オプションが表示されない場合は、始める前にをご覧ください。
>3. [接続] をクリックします。
>4. 課金が有効になっている Google Cloud プロジェクトを選択します。
>5. [一般公開データセット] をクリックします。
>6. 検索ボックスに「chicago」と入力し、[chicago_taxi_trips] データセットを選択します。
>7. [taxi_trips] テーブルを選択し、[接続] をクリックします。

※引用：[コネクテッドシートの使用(公式ドキュメント)](https://cloud.google.com/bigquery/docs/connected-sheets?hl=ja#use)

## 業務でコネクテッドシートをどのように使用したか
下記の条件の重複データが無いかチェックしたかった。
また、epubのデータはスプレッドシートでリストアップしたかったので、
それをコネクテッドシートを使用して確認してみた。

1. `content_id`毎に`content_version`が異なる、複数のデータがあるものを抽出する。
2. `content_version`が同一のものが無いかをチェックする
3. `content_format`がjpgのみなら最終バージョンの`content_version`を抽出する。
4. `content_format`がjpgとepubが含まれるものを抽出する。
5. `content_format`がepubのみのものも抽出する。
### 前提事項
・`content_id`の中に、`content_id`のバージョンを管理する`content_version`が複数ある。
・`content_format`はjpgとepubの2種類が存在する。

## 上記のデータの確認手順
### 1)下記のクエリでBigQueyryにデータマートを作成する

```SQL
with master as (
    select
        content_format
        , content_id
        , content_version
        , if(regexp_contains(content_format, r'.*epub.*'), true, false) as is_epub -- conent_foarmatがepubの場合はtrue。
    from
        `テーブル名`
)
select
    content_format
    , cotent_id
    , content_version
    , is_epub

    -- content_id毎にcontent_versionが異なる、複数のデータがあるものを抽出
    -- content_id毎に異なるcontent_versionがあると、row_numberが2以上の行が発生する。
    , row_number() over(partition by content_id order by content_version desc) as content_version_multiple_check
    
    -- content_versionが同一のものが無いかチェック。
    -- content_versionが同一のものがあれば、rankの値が同じ行が複数発生する。
    , rank() over(partition by content_id order by content_version desc) as content_version_singular_check

    -- content_formatがjpgのみなら、最新バージョンのcontent_versionを抽出する。
    -- is_epubを降順に並べて、row_numberが1の時falseであれば、content_formatがjpgのみと判断できる。
    , row_number() over(partition by content_id order by is_epub desc) as content_format_only_jpg

    -- content_formatがjpgとepubのどちらも含まれるものをリストアップ。
    -- content_idとis_epub毎にrow_numberを出して、複数の行が存在すれば、epubとjpgがどちらも存在すると分かる。
    , row_number() over(partition by content_id, is_epub) as content_format_all

    -- content_formatがepubのみのものをリストアップ。
    -- is_epubを昇順に並べて、row_numberが1の時trueであれば、epubのデータしかないと判断することができる。
    , row_number() over(partition by content_id order by is_epub) as content_format_only_epub
from
    master
;
```

### 2)上記のクエリで作成したデータマートテーブルをコネクテッドシートに出力する。

### 3)コネクテッドシートのフィルタ機能を使用して重複確認&epubをリスト化
>1.content_id毎にcontent_versionが異なる、複数のデータがあるものを抽出する。

content_version_multiple_checkが2以上の行が存在しないか確認。

>2.content_versionが同一のものが存在しないかをチェックする

content_version_singular_checkが2以上の行が存在しないか確認。

>3.content_formatがjpgのみなら最終バージョンのcontent_versionを抽出する。

content_format_only_jpgが2以上の行が無いかチェック

>4.content_formatがjpgとepubが含まれるものを抽出する。

content_format_allが2以上の行が存在しないかチェック

>5.content_formatがepubのみのものも抽出する。

content_format_only_epubが2以上の行が存在しないかチェック。

以上で確認してみたところ重複データが無かったので、
epubのデータをリスト化しておしまい。

# おまけ
### WINDOW関数のROW_NUMBER()とRANK()の違い
RANK・・・同じ値があった場合、順位は同じになり、その次は順位を飛ばす。
ROW_NUMBER・・・同じ値があっても同じ順位にはならず、順位をカウントする。
DENSE_RANK・・・同じ値があった場合、順位は同じになり、その次は順位を飛ばさない。

| code| value | RANK | ROW_NUMBER | DENSE_RANK |
|:-:|:-:|:-:|:-:|:-:|
|001|100|1|1|1|
|003|100|1|2|1|
|002|120|3|3|2|
|005|150|4|4|3|
|004|170|5|5|4|

--https://www.projectgroup.info/tips/SQLServer/MSSQL_00000044.html

### カスタムクエリ機能でBigQueryに存在しないカラムを生成する

スプレッドシートのカスタムクエリの使い方は、
[コネクテッドシートでスプレッドシートからBigQueryのデータを利用しよう](https://gktechblog.com/blg102/)という記事で確認できる。
カスタムクエリ内に記述したSQLの実行結果をスプレッドシートに展開することができる。

### BigQueryとConnected Sheetsの同期を手動または自動に選択する
BigQueryとConnected Sheetsの同期は手動か自動に選択することができる。
また、自動更新の単位は1時間・1日・1週間・1ヵ月から選択することができる。

※更新する度にBigQueryに対してクエリが投入されるため、更新頻度が高いとクエリ料金が高くなる。
※参考：[Connected Sheets で始めるデータ分析(データの更新)](https://blog.g-gen.co.jp/entry/connected-sheets#%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E6%9B%B4%E6%96%B0)