推奨する動作環境お知らせログインデータ活用人材育成プログラムコンテンツのTOPへ戻るSQLを学んでみようSQLを使いこなすためにSQLを書いてみようSQLを使いこなせるようになるために、実際に書いてみましょう。SQLを書いてみようここではDr.Sumの関数を使用して、解説文を見ながら、実際にSQLを書いていただきます。関数は単一ではなく複合して使うことが多いので、ぜひ前章の関数を思い出しながら、挑戦してみてください。問1:データ型変換とNULL変換Dr.SumのSQLを使って、文字列を数値に変換し、NULLの場合はNVL関数を使って0に変換する方法を説明します。以下の「売上」テーブルがあるとします。売上ID12345売上金額(文字型)373244567NULL4689122243このテーブルで、売上金額(文字型)列を文字列から数値に変換し、NULLの場合は0に変換するSQLクエリを作成します。以下のSQLクエリを使用します。Executorを開き、データベースを[SQL_Training]に設定して、下記のSQLを入力してみましょう。TO_NUMERIC関数:TO_NUMERICは、文字列の売上金額を数値に変換します。‘0')は、売上金額(文字型)がNULLの場合は’0'を返し、それ以外の場合は売上金額を数値に変換します。SQLの中で文字列を入力する場合、'aaa'のようにシングルクォーテーションで文字列を囲む必要があります。'0'を0とするとエラーになる、これは売上金額(文字型)はこの段階ではまだ文字型なので、「文字としての0」を返してあげる必要があるためです。結果の例このクエリを実行すると、以下の結果になります。売上金額は数値型として扱われるため、これらの金額の集計や平均を取得することが可能になります。売上ID12345売上金額37324456704689122243まとめNVL関数:NULL値を指定したデフォルト値に変換します(ここでは0)。TO_NUMERIC関数:文字列を数値に変換します。売上日から年度を計算する例を示します。以下の「売上」テーブルがあるとします。以下のSQLクエリを使用します。Executorで実行してみましょう。'YYYYMMDD'):売上日を日付型に変換します。-3):売上日から3ヶ月引いた日付を計算します。'YYYY'):3ヶ月引いた日付の年を文字列として取得します。元々の売上日は文字型で登録されています。-3ヶ月の計算をするためには、文字型を日付型に変換する必要があります。計算した結果から"年"の部分のみを抽出したいので、文字型でYYYYの部分だけを再度抽出するようにしています。問3:縦持ちデータを横持ちに変換する縦持ちデータを横持ちに変換するためのSQLについて説明します。これを「ピボット」と呼びます。ピボット操作は、データを行から列に変換するために使用されます。以下の「売上」テーブルがあるとします。売上金額1244345673323534656744326564243このデータは月のデータがレコードごとに入っていますが、1月,2月・・・が列(カラム)になるように変換します。今までに学習した知識を応用すると条件付き集計関数(CASE文とSUM関数)を使用して同様の結果を得ることができます。売上ID12341月12423556742432月4325346432603月673305640このクエリは、各売上IDごとに月ごとの売上金額を列に展開します。END:月が1月の場合、売上金額を返し、それ以外の場合は0を返します。同様に2月と3月の条件も設定します。SUM関数:SUM関数を使用して、各売上IDごとの月別売上金額を集計します。BY句:売上IDでグループ化することで、売上IDごとにデータを集計します。ASのあとに列名を名付けているけど、[1月]のように全角数字を使用しているね。これは列名の先頭文字に半角英数や半角記号を入れると、エラーになってしまうから全角文字を使っているんだよ!PIVOT関数をつかおうさきほどはCASE式を使用して、横持ちデータに変換をしていましたが、Dr.SumはPIVOT関数があります。PIVOT関数を使う方法を説明します。PIVOT関数を使ったSQLクエリDr.SumのPIVOT関数を使って、このデータを横持ちに変換するSQLクエリは次のようになります。売上金額:ここで、SUM(売上金額)は売上金額を合計する集計関数です。INはピボットに使用する列を指定します。結果の例このクエリを実行すると、以下のような結果が得られます。売上ID12341月12423556742432月4325346432603月673305640この結果では、各月の売上金額が別々の列として表示されています。さっきのCASEよりもPIVOTの方が文字が少なくてすっきりしているね。慣れてきたらSQLの効率も考えていこうね。まとめPIVOT関数を使うことで、縦持ちデータを横持ちデータに変換できます。これができたらあなたは初心者の壁を超えているさて、ここまででたくさんのSQLを書いていただきました。SQLには慣れてきたでしょうか?いままでに学習したことを応用すればあなたは"SQLの書ける人"と周りから思われるようになるでしょう。今回のコンテンツは"はじめてのSQL"なので、実はまだまだSQLは奥深いです。本コンテンツの最後として、少しだけ初心者の壁を超えたSQLの紹介をします。サブクエリーを使ってみる階層構造を作ってみるここは「SQLをもっと極めるとこんなこともできるんだ」と認識してもらうためのコンテンツだよ。実際にSQLを書かなくてもOKなので、気楽に読み進めていこうね!サブクエリーサブクエリー(サブクエリ)は、SQLの中で別のSQLを使用する方法です。サブクエリーを使うと、複雑なデータの抽出や分析が可能になります。Dr.Sumでもサブクエリーを使ってデータを操作することができますので、どのように記述をするのか確認をしてみましょう。1最も売上が高いレコードを持つ従業員を取得するSQLの例:結果:従業員ID10売上金額49986説明:[売上データ]テーブルにはさまざまな従業員の売上データがたくさん入っています。従業員ひとりひとりにもたくさんのレコード情報があります。その中で最大の売上金額を持つ従業員を抽出してみました。BYの集計単位が異なるため、複数回クエリをまわさねばならないですが、サブクエリーをつかうとひとつのSQLの中で完結することができます。テーブル結合:売上データテーブル、従業員マスタテーブル、および部署マスタテーブルをJOINします。売上データテーブルの従業員IDを従業員マスタテーブルの従業員IDと結合し、次に従業員マスタテーブルの部署IDを部署マスタテーブルの部署IDと結合します。集計:各部署名ごとに売上金額の合計を計算し、部署売上として表示します。テーブル結合:売上データテーブル、従業員マスタテーブル、および部署マスタテーブルをJOINします。売上データテーブルの従業員IDを従業員マスタテーブルの従業員IDと結合し、次に従業員マスタテーブルの部署IDを部署マスタテーブルの部署IDと結合します。集計:従業員名ごとに売上金額の合計を計算し、従業員売上として表示します。サブクエリーの結合:部署ごとの売上総額を計算した部署クエリと従業員ごとの売上総額を計算した従業員クエリを部署名で結合します。ごとの売上総額(従業員売上)を選択します。長文のSQLとなり、「見たくない」と最初は思ったでしょうが、よくよく紐解いてみると、今までの復習の情報でこのSQLが書けてしまいます。勇気を出して長いSQLも読み解いていけるようにしましょう!階層構造をつくるBIツールでおなじみのドリルダウン。ようにどんどんと細かい情報にしてデータ分析をする方法です。この機能を使いたいのにデータが階層ごとになっていないからうまくできない、ということはございませんか?ここではカラム内の文字列の中で特定の文字列で分割して、階層項目をつくるという内容です。SQLを使うパターン簡単な説明ここでは'https://navi.wingarc.com/product/drsum/12506'のように、'/'(スラッシュ)が含まれている文字列ごとに階層分けして列に格納するSQLを記載しています。上記のようにCASE文を多用してSQLで作ることも可能です。下記の記事を参考にしてください。Scriptというひとつのプログラムの中でより複雑なSQLを記述できるスクリプトが存在します。そちらの例を参考にしてみましょう。Scriptを使うパターンここでは先ほどのSQL文よりも高度なことをしています。先ほどは'/'(スラッシュ)の数を決め打ちしていました。そのため11個目の'/'(スラッシュ)が文字列の中にある場合は対応できていません。Scriptではループ処理があるので、’/’(スラッシュ)の数の最大数を指定しておいて、その数だけループ処理を回しています。下記の記事を参考にしてください。さいごに最後までコンテンツを進めてくださりありがとうございます。フィードバックやコメントをお寄せいただけると幸いです。SQLは難しいと感じられがちですが、ひとつずつ紐解いていくと、「あ、こうなのか」と理解ができると思います。難しいSQLに直面しても、わかるところを見つけ、ひとつずつ解決をしていくようにしましょう。ここまでSQLを学習したあなたなら、以前より高度なデータ分析ができるようになってるよ。