はじめに

本記事は、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 値をスキップして正しく計算もしてくれるため、計算時間を短縮することもできます。