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

コネクテッドシートとは

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

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

※参考：Connected Sheetsで始めるデータ分析

コネクテッドシートの使い方

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

※引用：コネクテッドシートの使用(公式ドキュメント)

業務でコネクテッドシートをどのように使用したか

下記の条件の重複データが無いかチェックしたかった。
また、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にデータマートを作成する

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のデータを利用しようという記事で確認できる。
カスタムクエリ内に記述したSQLの実行結果をスプレッドシートに展開することができる。

BigQueryとConnected Sheetsの同期を手動または自動に選択する

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

※更新する度にBigQueryに対してクエリが投入されるため、更新頻度が高いとクエリ料金が高くなる。
※参考：Connected Sheets で始めるデータ分析(データの更新)