前回、「SQLの基礎① 〜 簡単なSQL文のご紹介(SELECT句、FROM句、WHERE句) 〜」では、SQLの簡単な例をご紹介いたしましたが、覚えておりますでしょうか!さっき見たから大丈夫だという方もいらっしゃると思いますが、SQL自体そんなに難しくないという意識を持ち続けて頂きたいので、復習から始めたいと思います!反復の勉強法は筆者個人的には非常にインプット効率を高める方法だと思っているので、ぜひ意識してみてください!(できれば、どこかのタイミングで勉強方法に関しても思っていることを書いてみたいと思ってます!)
www.digitaldolphin.jp
目次
前回の復習
早速、前回の復習から始めていきたいと思います!(簡単に思い出せれば、ほぼ読み飛ばしでいいです!反復することの方が大事です。)
まず、SQLとはデータベースを扱うための「データベースのデータを扱うためのプログラム言語」で、具体的には以下のような命令文によりデータを扱うことができるものです。
表1:SQL命令文の例
# | 命令 | 意味 |
---|---|---|
1 | SELECT | 必要なカラムの選択するための命令文 |
2 | FROM | 対象のデータベースを選択するための命令文 |
3 | WHERE | データベースからレコードを抽出する条件指定をするための命令文 |
4 | AND | 条件指定などで使われるもので、"且つ"という意味の命令文 |
また、前回ご紹介したSQL文のクエリ文は以下のようなフォーマットとなっておりました。
基礎的なSQLクエリ
SELECT 項目名1 ,項目名2 ,・・・ FROM テーブル名 WHERE 条件文 ;
そして、具体的に以下の表2の『受注データ』のようなデータベースから特定の条件でデータを抽出するためのクエリはサンプル1のようなものでした。
表2:受注データ
受注ID | 受注日時 | 顧客ID | 購入金額 | 購入店舗 | キャンセル日時 | 返品日時 |
---|---|---|---|---|---|---|
o001 | 2022/06/23 10:00:00 | c001 | ¥10,000 | 東京店 | − | − |
o002 | 2022/06/24 11:30:00 | c002 | ¥6,600 | オンラインショップ | 2022/06/24 11:35:00 | − |
o003 | 2022/06/24 12:00:00 | c004 | ¥6,500 | 東京店 | − | − |
o004 | 2022/06/24 12:10:00 | c007 | ¥4,000 | 横浜店 | − | − |
o005 | 2022/06/24 16:30:00 | c001 | ¥2,000 | オンラインショップ | − | − |
o006 | 2022/06/25 10:00:00 | c001 | ¥10,000 | 東京店 | − | 2022/06/25 12:10:00 |
o007 | 2022/06/26 11:30:00 | c002 | ¥6,600 | オンラインショップ | − | − |
o008 | 2022/06/26 12:00:00 | c004 | ¥6,500 | 東京店 | − | − |
o009 | 2022/06/27 12:10:00 | c007 | ¥4,000 | 横浜店 | − | − |
o010 | 2022/06/28 16:30:00 | c001 | ¥2,000 | オンラインショップ | − | − |
サンプル1
SELECT * FROM 受注データ WHERE キャンセル日時 IS NULL AND 返品日時 IS NULL AND 購入店舗 LIKE 'オンラインショップ' ;
表3:サンプル1の実行結果
受注ID | 受注日時 | 顧客ID | 購入金額 | 購入店舗 | キャンセル日時 | 返品日時 |
---|---|---|---|---|---|---|
o005 | 2022/06/24 16:30:00 | c001 | ¥2,000 | オンラインショップ | − | − |
o007 | 2022/06/26 11:30:00 | c002 | ¥6,600 | オンラインショップ | − | − |
o010 | 2022/06/28 16:30:00 | c001 | ¥2,000 | オンラインショップ | − | − |
上の内容さえ、覚えているよということであれば一旦前回の振り返りは完了です!そして、今日も基礎的な内容ですが、上のものから更にもう少しだけデータの加工に関して、お話ししていきます。
基礎的なSQLの例(GROUP BY句)
前回やったことは、データベースからデータを抽出する上で、SQLの中でも本当に基礎となるSELECT句、FROM句、WHERE句といった本当に基礎的なものでした。そして、今回はほんの少しだけ足を伸ばして、GROUP BY句とHAVING句に関してのご紹介をしていきます!
では、表1の受注データをもとに、店舗毎のキャンセル・返品を除いた売上を毎日算出したいとマーケティング部や経営企画の上司から指示されたとします。そうなった時に、多くの担当者は以前ご紹介したように『受注データ』のExcelファイルをシステム部からもらい、Excelを利用して頑張ろうとしますが、実際にはExcelファイルが数十MBという容量となると、Excelは真っ白になったまま計算が完了しない、動かないという状態になります。そこでSQLを自分でも使ってみようとソフトウェアを利用していこうとします。
この場合、どう対応すれば楽でしょうか。前回の内容だけでやろうとするとめちゃくちゃ大変ですが、WHERE句を利用して、1店舗1店舗の『受注データ』を作成し、データを小さくしてExcelで扱えるようにした上で、Excelで加工するというものです。ただ一方で、店舗が10、20、100、200と増えていくと毎日の運用的にもかなり大変です。そんな時に役立つのが、SQLのGROUP BY句になります。そのGROUP BY句を利用するとどんなSQLクエリになるか、以下にサンプルをご紹介します。段階を踏みたいので、まずは、キャンセルや返品を考慮しないSQLクエリを書きます。
サンプル2
SELECT 購入店舗 ,SUM(購入金額) AS 売上 FROM 受注データ GROUP BY 購入店舗 ;
表4:サンプル2の実行結果
購入店舗 | 売上 |
---|---|
東京店 | ¥33,000 |
オンラインショップ | ¥17,200 |
横浜店 | ¥8,000 |
これによりGROUP BY句で指定した項目毎の必要な情報を集約したデータを抽出することが出来るようになります。そのため、サンプル2の意味としては、店舗毎の購入金額の合計値という意味になります。また、クエリの中で登場するSUMというのはSQLにおける集計関数と呼ばれる関数で、ASというのはカラムやテーブルに別名をつけられる句になります。では、店舗毎の購入金額は出せましたが、キャンセルや返品を除きたいので、前回登場したWHERE句を組み合わせたいがどうすれば良いでしょうか。結論から記載すると以下のようにSQLクエリを書けば実現することができます。
サンプル3
SELECT 購入店舗 ,SUM(購入金額) AS 売上 FROM 受注データ WHERE キャンセル日時 IS NULL AND 返品日時 IS NULL GROUP BY 購入店舗 ;
表5:サンプル3の実行結果
購入店舗 | 売上 |
---|---|
東京店 | ¥33,000 |
オンラインショップ | ¥10,600 |
横浜店 | ¥8,000 |
サンプル3のような形でWHERE句とGROUP BY句を組み合わせる場合には、WHERE句を先、GROUP BY句を後という形でコードを記載することで、条件文付きの集計ができるようになります。
ここまで出来ると担当者は上司に対して、キャンセルと返品を除いた店舗毎の売上を出せましたという報告出来ますが、これだと受注単価や顧客単価が分からないから、受注数や受注顧客数を出してほしいと大半のケースで追加の要望を上司からもらいます。(最初から言えばいいのにと思う内容ですが。。。笑)
では、どのように出すのかというと、次のようになります。
サンプル4
SELECT 購入店舗 ,SUM(購入金額) AS 売上 ,COUNT(受注ID) AS 受注数 ,COUNT(DISTINCT 顧客ID) AS 購入顧客数 FROM 受注データ WHERE キャンセル日時 IS NULL AND 返品日時 IS NULL GROUP BY 購入店舗 ;
表6:サンプル4の実行結果
購入店舗 | 売上 | 受注数 | 購入顧客数 |
---|---|---|---|
東京店 | ¥33,000 | 4 | 2 |
オンラインショップ | ¥10,600 | 3 | 2 |
横浜店 | ¥8,000 | 2 | 1 |
ここで登場させたCOUNTというのも集計関数と呼ばれるSQLの関数を表し、レコード数を算出するための関数です。そして、DISTINCTというのは、カラム中の値のユニーク数を表します。そのため、東京店では、顧客IDが"c001"と"c004"の2人で4つ受注がありますが、顧客IDのユニーク数は2となるため、購入顧客数は2となります。ちなみに、「受注IDにはDISTINCTを入れなくてもいいのか?」という問いは発生するかもしれませんが、受注IDの場合はテーブルの主キーの項目となっているため、DISTINCTは入れても入れなくても結果は同じとなります。
「よし、受注数と購入客数まで算出することができた!」と担当者は思いつつも、また受注単価を出せだの、顧客単価も出せよと言われる気がして、先に出してやろうと思ったとします。そうなった際、意外と多くの担当者は「Excelなら簡単だけど、SQLだとどう書くの?」と少し不安になってしまいます。プログラム言語は、往々にして「わかりそうで本当にそうか?合っているのか?」と思うケースが多いですが、意外と同じ書き方で実現できたります。そのため、以下のように実現できます。
サンプル5
SELECT 購入店舗 ,SUM(購入金額) AS 売上 ,COUNT(受注ID) AS 受注数 ,SUM(購入金額) / COUNT(受注ID) AS 受注単価 ,COUNT(DISTINCT 顧客ID) AS 購入顧客数 ,SUM(購入金額) / COUNT(DISTINCT 顧客ID) AS 顧客単価 FROM 受注データ WHERE キャンセル日時 IS NULL AND 返品日時 IS NULL GROUP BY 購入店舗 ;
表7:サンプル5の実行結果
購入店舗 | 売上 | 受注数 | 受注単価 | 購入顧客数 | 顧客単価 |
---|---|---|---|---|---|
東京店 | ¥33,000 | 4 | ¥8,250 | 2 | ¥16,500 |
オンラインショップ | ¥10,600 | 3 | ¥3,533 | 2 | ¥5,300 |
横浜店 | ¥8,000 | 2 | ¥4,000 | 1 | ¥8,000 |
これでほぼ完成ですが、実は注意点があります。意外とExcelなどでも気になる方は気になられるのでわかる方もいらっしゃるかもしれませんが、割り算をする場合に、もし分母に0が来てしまったら当然エラーが発生します。そこで、エラー発生を加味した上で、以下のように最初から記載しておく方が安全です。
サンプル6
SELECT 購入店舗 ,SUM(購入金額) AS 売上 ,COUNT(受注ID) AS 受注数 ,SUM(購入金額) / NULLIF(COUNT(受注ID),0) AS 受注単価 ,COUNT(DISTINCT 顧客ID) AS 購入顧客数 ,SUM(購入金額) / NULLIF(COUNT(DISTINCT 顧客ID),0) AS 顧客単価 FROM 受注データ WHERE キャンセル日時 IS NULL AND 返品日時 IS NULL GROUP BY 購入店舗 ;
表8:サンプル6の実行結果
購入店舗 | 売上 | 受注数 | 受注単価 | 購入顧客数 | 顧客単価 |
---|---|---|---|---|---|
東京店 | ¥33,000 | 4 | ¥8,250 | 2 | ¥16,500 |
オンラインショップ | ¥10,600 | 3 | ¥3,533 | 2 | ¥5,300 |
横浜店 | ¥8,000 | 2 | ¥4,000 | 1 | ¥8,000 |
今回登場したNULLIF(A,B)という関数は、AがBと一致した場合には、NULLとするという意味になります。そして、データベース上は0で割り算すると当然エラーとなりますが、NULLで割り算をする場合にはNULLという結果になるため、これにより問題は解消されることになります。そのため、割り算をする場合には、分母にはNULLIF("割るもの",0)という形でNULLIFという関数を利用して見てください。
基礎的なSQLの例(HAVING句)
「これでやっと毎日毎日上司に報告するためのSQLクエリが書き終わりました。」と思い、上司にも報告を終えました。ここまで出すと上司としては、感謝しかないです。しかし、よくよくデータを見てみると、さらに気になることが発生して、「この中で受注数が2以下のものは除外してみたい」となるといったさらに追加の要望が発生するケースも意外と多いです。これは筆者が色んなDX推進のプロジェクトに関わらせて頂く中でも多くのクライアントからこれも見たい、これもお願いしたいとやったもの以上に追加でご要望を頂くケースが多かったので、社内となるともっと多いんじゃないかなと思います。そして、筆者のように外部の担当であれば契約もあるので、弾くこともできますが、社内となると弾くのも大変ですよね。。
そこでやっとの登場ですが、HAVINGに関してもご紹介して行きます。
結論、HAVINGに関しては集計関数(COUNTとSUM)に対する条件を指定できるものと考えてください。ちなみに、ここまで集計関数というものを出してきましたが、COUNTとSUM以外にもありますので、代表的なものをまずはご紹介します。
表9:集計関数の例
# | 関数 | 意味 |
---|---|---|
1 | COUNT | レコード数を算出するもの |
2 | SUM | 合計値を出すもの |
3 | MAX | 最大値を出すもの |
4 | MIN | 最小値を出すもの |
5 | AVG | 平均値を出すもの |
そして、ここに挙げた集計関数を利用した条件に対して、利用するものがHAVING句となります。WHERE句はあくまでも1レコード1レコードに対しての条件ですが、HAVING句はレコードを集計関数で集約したものに対する条件を指定できます。
そのため、今回担当者が上司から指示された受注数が2以下のものは除外するという場合には、以下のようなSQLクエリを書くことになります。
サンプル7
SELECT 購入店舗 ,SUM(購入金額) AS 売上 ,COUNT(受注ID) AS 受注数 ,SUM(購入金額) / NULLIF(COUNT(受注ID),0) AS 受注単価 ,COUNT(DISTINCT 顧客ID) AS 購入顧客数 ,SUM(購入金額) / NULLIF(COUNT(DISTINCT 顧客ID),0) AS 顧客単価 FROM 受注データ WHERE キャンセル日時 IS NULL AND 返品日時 IS NULL GROUP BY 購入店舗 HAVING COUNT(受注ID) > 2 ;
表10:サンプル7の実行結果
購入店舗 | 売上 | 受注数 | 受注単価 | 購入顧客数 | 顧客単価 |
---|---|---|---|---|---|
オンラインショップ | ¥10,600 | 3 | ¥3,533 | 2 | ¥5,300 |
あんまり難しくないかもしれませんが、SQL初心者にとっては、「条件式となるとWHERE句とHAVING句のどっちだっけ?」、「WHERE句とGROUP BY句とHAVING句はどの順番で書くんだっけ?」という疑問は意外と最初多いかと思いますが、一つ一つ積み重ねて覚えていってください!そして、お分かりかもしれませんが、HAVING句の場合は、基本的にGROUP BYとのペアで登場することは覚えておいてください。
これでやっと、担当者は上司からの要求を満たすことが出来ました!最初は、どうなることかと思いましたが、意外とやってみると難しくないのも事実だったりします。実際にはこれ以上の要求をもらうことも多いかもしれませんが、今後ご紹介して行きます!
ここまで登場したSQLクエリを公式として最後に記載します。しっかりと公式的に覚えておいてください!
基礎的なSQLクエリ
SELECT 項目名1 ,項目名2 ,・・・ FROM テーブル名 WHERE 条件文 GROUP BY 項目名 HAVING 集計関数を利用した条件文 ;
さいごに
基礎として、2つ目の記事でしたが、いかがだったでしょうか。徐々に難易度は上がってきておりますが、まだ付いて来れておりますでしょうか。もし難しい部分やもっとここを追加で解説してほしいといったことがあれば、ご遠慮なく、メッセージ下さい。
次は、複数のテーブルを利用したSQLをご紹介して行きたいと思いますので、次回も是非ご覧ください!