はじめに
本記事は、Googleデータアナリティクスのプロフェッショナル認定証のプログラムより、参照させて頂いています。興味を持った方は、是非受講してみてください。
CAST関数
例1
SQL テーブルにまだ存在しないデータを インポートするさいには 新しいデータセットのデータ型が 正しくインポートされないことがあります。 そこで使えるのが CAST 関数です。 基本的に、CAST 関数とは あるデータ型から別のデータ型に データを変換する SQL 関数です。
たとえば、こんな感じです。 ローレン家具店というお店で 働いているとします。 オーナーは過去 1 年間の 取引データを収集してきましたが データが適切にフォーマットされておらず データ整理ができないと発覚しました。 そこで、データを変換して もう一度使えるようにします。
たとえば、すべての購入品を 購入価格順に降順で 並べ替えたいとします。 つまり、最も高額な商品を 最初に表示させる、 ということです。
89.85 が一番上に表示され、 その下に 799.99 が表示されます。 しかし、799.99 は 89.85 よりも 大きな数字です。 データベースはこれが数字であると 認識していないので 並べ替えられなかったのです。
customer_purchase テーブルに戻って スキーマを見ると、データベースが purchase_price を どのようなデータ型だと 認識しているかがわかります。 ここでは、データベースは purchase_price を STRING（文字列）とみなしていますが 実際には FLOAT、つまり小数点を含む 数値です。 なので、89.85 が 799.99 より 上に来てしまったのです。
文字列で設定されていると、 まず最初の文字で判断し 次に、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 として認識させます。
これで、purchase_price で 購入データを追えるようになりました。 ローレン家具店で、実際に 分析に使えるデータができました。
例2
ローレン家具店の例で使用した 取引データがあります。 今回は、購入日フィールドを チェックしてみましょう。 家具店のオーナーから 12 月の販売促進期間中の 購入金額を調べてくれないかと 依頼がありました。 そこで、2020 年 12 月 1 日から 2020 年 12 月 31 日の間に発生した すべての購入の日付と purchase_price、つまり購入金額を引き出す SQL クエリを書いてみましょう。
4 つの購入が 12 月に発生していますが 日付フィールドが少し変な気がします。
これは、データベースが この日付フィールドを 日付と時刻からなる datetime として 認識しているからです。 この SQL クエリは、日付フィールドが date ではなく datetime であっても 正しく動作します。 しかし、日付フィールドを 日付データ型に変換するように SQL に指示すれば、時刻は表示せず 日付だけを表示できます。 そのためには、再び CAST 関数を 使います。
CAST 関数を使って SELECT 文の日付フィールドを 時間ではなく日付を表示する 新しい日付フィールドに置き換えます。
これで、12 月の販売期間中の購入について よりきれいな結果が 得られるようになりました。
CONCAT関数
CONCAT では、文字列を足し合わせて 一意のキーとして使える 新しい文字列を作ることができます。
customer_purchase テーブルに戻ると この家具店では同じ商品の異なる色を 販売していることがわかります。 オーナーは、顧客の色の好みを知り それに応じて店舗の在庫を 管理することができます。 問題は、商品の色に関係なく product_code が同じである、 ということです。 そこで、商品を色で分けるための 別の方法を見つけ、顧客がある色を 好むかどうか、知る必要があります。 早速、CONCAT を使用して 一意のキーを作成し、 色で商品を区別することで より簡単に数を数えられるようにします。
これで、各ソファが何回購入されたかを 数えらえるとともに 顧客の色の好みについても 把握することができます。
CONCAT を使えば、どの色のソファが 一番人気か調べて、 もっと注文することもできます。
COALESCE関数
COALESCE は、リスト内の 非 NULL 値を返す SQL 関数です。 NULL 値とは、値がないことです。 テーブルの中にオプションのフィールドが ある場合、そこに入れるべき 適切な値がない行には、 そのフィールドに NULL 値が入ります。 customer_purchase テーブルを使って どういうことかご説明しましょう。 customer_purchase テーブルでは 商品情報がない行が いくつか見られます。 そのため、NULL が表示されています。 しかし、商品名が NULL の行には 代わりに使用できる product_code の データがあることがわかります。 SQL では、ベッドやソファのように 商品名を表示する方が読みやすいので そちらを優先します。 しかし、商品名がない場合は 代わりに product_code を表示するように SQL に指示することができます。
そこで、COALESCE 関数の出番です。 例えば、販売された全商品の リストが欲しいとします。 どのような商品が売れたのかを 知るために、product_name 列を 使用します。
これで、購入された各商品の情報が得られます。 販売されたすべての商品の リストができ、オーナーが中身を 確認できるようになりました。 また COALESCE は、 NULL 値をスキップして 正しく計算もしてくれるため、 計算時間を短縮することもできます。
