Googleデータアナリティクス：高度なデータクリーニングSQL関数：CAST・CONCAT・COALESCE

# はじめに

本記事は、Googleデータアナリティクスのプロフェッショナル認定証のプログラムより、参照させて頂いています。興味を持った方は、是非受講してみてください。

https://www.coursera.org/professional-certificates/data-analytics-japanese

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/b583930d-fecb-13ad-85a2-3f44e309d8a5.png" width="800">

# CAST関数

### 例1

SQL テーブルにまだ存在しないデータを インポートするさいには 新しいデータセットのデータ型が 正しくインポートされないことがあります。 そこで使えるのが CAST 関数です。 基本的に、CAST 関数とは あるデータ型から別のデータ型に データを変換する SQL 関数です。

たとえば、こんな感じです。 ローレン家具店というお店で 働いているとします。 オーナーは過去 1 年間の 取引データを収集してきましたが データが適切にフォーマットされておらず データ整理ができないと発覚しました。 そこで、データを変換して もう一度使えるようにします。 

たとえば、すべての購入品を 購入価格順に降順で 並べ替えたいとします。 つまり、最も高額な商品を 最初に表示させる、 ということです。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/34a9dadc-ea14-8955-e9cb-6d8b5b7a4d03.png" width="７00">

89.85 が一番上に表示され、 その下に 799.99 が表示されます。 しかし、799.99 は 89.85 よりも 大きな数字です。 データベースはこれが数字であると 認識していないので 並べ替えられなかったのです。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/443c26c1-c09f-1c60-d55c-eca84b340e68.png" width="７00">

customer_purchase テーブルに戻って スキーマを見ると、データベースが purchase_price を どのようなデータ型だと 認識しているかがわかります。 ここでは、データベースは purchase_price を STRING（文字列）とみなしていますが 実際には FLOAT、つまり小数点を含む 数値です。 なので、89.85 が 799.99 より 上に来てしまったのです。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/6fecd165-8dbb-671d-4585-bffbfa1f5b34.png" width="７00">



文字列で設定されていると、 まず最初の文字で判断し 次に、2 番目の文字を見ます。 ですからたとえば、 apple と orange という単語を 降順で並べ替えたい場合 最初の文字 a と o から始めます。 o は a よりも後に来るので orange は最初に表示され、 次に apple が表示されます。 89.85 と 799.99 についても同じです。 データベースでは最初の文字から 始めます。つまり、それぞれ 8 と 7 です。 8 は 7 よりも大きいので データベースは 89.85 を最初に並べ替え 次に 799.99 を表示します。 データベースはこれらを文字列として 扱うためです。 これらの文字列を FLOAT、つまり小数データとして 認識できないのは、 そのデータ型に設定されていないからです。 

そこで型変換をします。型変換とは データをある型から 別の型に変換することです。 これを CAST 関数で行うのです。 CAST 関数を使用して、purchase_price を 新しい purchase_price に置き換え、 データベース上で STRING ではなく FLOAT として認識させます。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/6c6325a2-a97c-b1fa-d18b-58eeac2d1008.png" width="７00">

これで、purchase_price で 購入データを追えるようになりました。 ローレン家具店で、実際に 分析に使えるデータができました。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/af71389a-e776-8812-c8ac-778953093663.png" width="７00">

### 例2

ローレン家具店の例で使用した 取引データがあります。 今回は、購入日フィールドを チェックしてみましょう。 家具店のオーナーから 12 月の販売促進期間中の 購入金額を調べてくれないかと 依頼がありました。 そこで、2020 年 12 月 1 日から 2020 年 12 月 31 日の間に発生した すべての購入の日付と purchase_price、つまり購入金額を引き出す SQL クエリを書いてみましょう。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/52249534-664c-0db7-1428-6fa247223924.png" width="７00">

4 つの購入が 12 月に発生していますが 日付フィールドが少し変な気がします。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/0af5f4d1-814e-8500-6b54-e700883676da.png" width="７00">

これは、データベースが この日付フィールドを 日付と時刻からなる datetime として 認識しているからです。 この SQL クエリは、日付フィールドが date ではなく datetime であっても 正しく動作します。 しかし、日付フィールドを 日付データ型に変換するように SQL に指示すれば、時刻は表示せず 日付だけを表示できます。 そのためには、再び CAST 関数を 使います。

CAST 関数を使って SELECT 文の日付フィールドを 時間ではなく日付を表示する 新しい日付フィールドに置き換えます。 
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/f61cbc75-5f3c-70f6-d134-a565d30e5cc5.png" width="７00">

これで、12 月の販売期間中の購入について よりきれいな結果が 得られるようになりました。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/86fd2ef5-1060-6639-e0e8-44e11064b8a0.png" width="７00">

# CONCAT関数

CONCAT では、文字列を足し合わせて 一意のキーとして使える 新しい文字列を作ることができます。 

customer_purchase テーブルに戻ると この家具店では同じ商品の異なる色を 販売していることがわかります。 オーナーは、顧客の色の好みを知り それに応じて店舗の在庫を 管理することができます。 問題は、商品の色に関係なく product_code が同じである、 ということです。 そこで、商品を色で分けるための 別の方法を見つけ、顧客がある色を 好むかどうか、知る必要があります。 早速、CONCAT を使用して 一意のキーを作成し、 色で商品を区別することで より簡単に数を数えられるようにします。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/ee5b9826-7558-aca8-344a-e09682197f75.png" width="７00">

これで、各ソファが何回購入されたかを 数えらえるとともに 顧客の色の好みについても 把握することができます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/a8d5be68-1b5c-4afa-5fd5-cc388b64e838.png" width="７00">

CONCAT を使えば、どの色のソファが 一番人気か調べて、 もっと注文することもできます。 

# COALESCE関数

COALESCE は、リスト内の 非 NULL 値を返す SQL 関数です。 NULL 値とは、値がないことです。 テーブルの中にオプションのフィールドが ある場合、そこに入れるべき 適切な値がない行には、 そのフィールドに NULL 値が入ります。 customer_purchase テーブルを使って どういうことかご説明しましょう。 customer_purchase テーブルでは 商品情報がない行が いくつか見られます。 そのため、NULL が表示されています。 しかし、商品名が NULL の行には 代わりに使用できる product_code の データがあることがわかります。 SQL では、ベッドやソファのように 商品名を表示する方が読みやすいので そちらを優先します。 しかし、商品名がない場合は 代わりに product_code を表示するように SQL に指示することができます。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/065bbb23-b50f-c6f2-4921-6adc8a9a2d82.png" width="７00">


そこで、COALESCE 関数の出番です。 例えば、販売された全商品の リストが欲しいとします。 どのような商品が売れたのかを 知るために、product_name 列を 使用します。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/e7b0ff6c-8d07-8009-4271-5c74f3e68778.png" width="７00">

 これで、購入された各商品の情報が得られます。 販売されたすべての商品の リストができ、オーナーが中身を 確認できるようになりました。 また COALESCE は、 NULL 値をスキップして 正しく計算もしてくれるため、 計算時間を短縮することもできます。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/d764326f-8965-67ad-d13e-5bec03349ad0.png" width="７00">