Googleデータアナリティクス：SQL を使った文字列変数のクリーニング

# はじめに

本記事は、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">

# DISTINCT 

[以前、表計算ソフトで 重複の削除ツール][tool] を使って 重複を削除する方法をご説明しました。 SQL では SELECT 文に DISTINCT を含めることで 同じ処理ができます。 

たとえば、オハイオ州の顧客を 対象とした特別キャンペーンを 実施するとします。 オハイオ州に住む顧客の ID を 取得します。 しかし、顧客情報の中には 複数回入力されているものがあります。  

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/5e96a13d-e1d1-193c-6512-31e5521903fd.png" width="７00">

このクエリでは、テーブルに重複が ある場合、重複を取得できます。 もし顧客 ID 9080 がテーブルに 3 回表示されたら、結果にはその顧客 ID が 3 つあることになります。 しかし、それは欲しい結果ではありません。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/48e4d63a-0170-63df-4321-f59ee9b9dd3a.png" width="７00">

ユニークな顧客 ID のリストが必要なのです。 そのためには、SELECT 文に DISTINCT を追加します。これで、顧客 ID 9080 は、検索結果に 一度だけ表示されるようになります。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/ff79ef0a-3227-83e4-1ec8-ec8e3094634a.png" width="７00">

[tool]: https://qiita.com/mapami/items/ca26bc930d54558641ca

# 文字列関数：LENGTH

文字列変数の長さがわかっている場合 LENGTH を使って文字列変数が 一貫しているかどうかを再確認できます。 データベースによっては LEN と 表記しているものもありますが、同じことです。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/9234b0cc-0dd2-6f42-fb8b-7fb7dcd51a9a.png" width="７00">

この結果、顧客ごとにリストアップされた 各国の文字数のリストが得られます。 ほとんどすべて 2 であるように見えますが これは国フィールドに 2 文字しか 含まれていないことを意味します。 しかし、1 つだけ 3 があると気づきました。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/526009ea-1549-134b-10ae-79a04ecc3eaf.png" width="７00">

そこで、テーブルの中のどの国が 誤ってリストアップされているか調べます。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/ca67055f-feaa-c130-d3c2-67ba0ddd8800.png" width="７00">

このクエリを実行すると、 文字数があるべき値である 2 よりも大きい 2 つの国が表示されました。 間違ってリストアップされた国は、 US を指す USA だとわかります。 このテーブルを作成していれば、 このエントリを USA ではなく US として表示するように テーブルを更新できます。 今回は、このテーブルを作成していないので 更新はしません。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/d27b17b2-bdac-0bde-614c-cbee4e2b9159.png" width="７00">

この問題を解決して、US の代わりに USA と表示されている 2 つの項目を含む アメリカ国内のすべての顧客のリストを 取得できるようにする必要があります。 ここでも SQL クエリで エラーを考慮した結果を得ることができます。 

# 文字列関数：SUBSTRING

SUBSTRING 関数でそれぞれの国の 最初の 2 文字を抜き出し すべての国が一貫して 2 文字だけになるようにします。 US だけを取得したいので、 この関数を ='US' に設定します。 このクエリを実行すると US の代わりに USA となっている顧客を含む 国が US のすべての顧客 ID の リストが得られます。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/94028003-4d19-af25-0bf7-62c1ca28d781.png" width="７00">

この結果を見てみると 顧客 ID が複数回表示されている重複が いくつかあるようです。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/92d26aff-1477-2d2e-5cd9-fd1c2747993c.png" width="７00">

重複を削除する方法を覚えていますか？ customer_id の前に DISTINCT を追加します。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/d23ce98b-e37b-ddf3-da34-222bb40c7a3e.png" width="７00">

これで、このクエリを実行すると US に住んでいる顧客の 顧客 ID をまとめた最終的なリストが できあがります。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/0db65a23-ee08-7d53-62c5-8c241f8a3909.png" width="７00">


# 文字列関数：TRIM

これは、余分なスペースがある項目を見つけ 一貫性を保つために余分なスペースを 削除する必要がある場合に非常に便利です。

たとえば、customer_address テーブルの state 列を調べてみましょう。 国名の列で行ったのと同じように 州列の文字数が一定であることを確認します。 そこで、もう一度 LENGTH 関数を使い 2 文字以上の state があるか調べます。 データテーブルにあるべき数は 2 文字です。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/462d6dfd-6800-826a-17bd-5e702dd48c1e.png" width="７00">


間違ってリストアップされた州があれば、 どのようなものかも把握したいところです。 このクエリを実行すると、結果が表示されます。 2 文字以上ある州が 1 つありますね。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/1ea64dc9-f72c-4be6-53fa-4fb696989000.png" width="７00">

結果をフィルタリングする際、 WHERE 句で LENGTH(state)>2 ステートメントを 使ったので、2 文字以上なはずです。 ということは、SQL がカウントした余分な文字は スペースであることがわかります。 H の後にスペースがあるはずです。 そこで、TRIM 関数を使います。

TRIM 関数を使うには、 スペースを取り除きたい列を SQL に指示します。この場合は state です。 オハイオ州に住む顧客のみが欲しいので、 = 'OH' とタイプすれば、 このとおり、オハイオ州に住むすべての 顧客 ID が、H のあとにスペースがある ものも含めて揃いました。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/781d458c-2802-456a-9648-180d6773d39d.png" width="７00">

:::note info
文字列変数で完全で一貫したデータを 確認すれば、エラーや計算ミスを 避けることができ、多くの時間を 節約できます。 そのために、最初にデータクリーニングを 行うのです。 
:::