【プロゲート】SQL SQL IIの学習内容まとめ

SQL II

1.DISTINCT

実践的なSQLを学ぼう
・このレッスンでは実践的なSQLとして、取得したデータを加工して分析しやすくしたり、便利な関数を使って少し高度な分析を行ったりしてみよう！

検索結果を加工する
・今回は色々なデータの加工の方法を学ぶ
・加工とはデータベースのデータを取得するだけでなく、そのデータ同士を計算したりして、新しいデータを作り出すこと
・このレッスンを終えると、日ごとに何円お金を使ったかなど、お金の使い方を分析できる

重複したデータを省こう
・まず、今まで自分がどんなものにお金を使ったかを知るために、今まで買ったもののリストを作りたい
・試しにnameカラムのデータを取得してみたが、「プリン」のデータが重複してしまって見づらい！
・これも加工することで解決できるか？
・そういった重複データを省く方法がある

DISTINCT
・DISTINCTを用いると、検索結果から重複するデータを除くことが可能
・「DISTINCT(カラム名)」とすることで、検索結果から指定したカラムの重複するデータを除くことができる

DISTINCTの使い方
・DISTINCTはSELECTで取得するカラムに使用することで、重複を省いたデータを取得できる
・purchasesテーブルからnameカラムの重複したデータを省いて取得している

2.四則演算

四則演算を活用しよう
・データベースに保存した購入物の価格を消費税も含んだ値にしたいが、どうすればいいか？
・SQLには取得したデータにかけ算をしたり、割り算をしたりする「四則演算」という機能がある
・これを使うと、消費税を含んだ値にすることができる

四則演算
・SQLでは四則演算「足す(+)、引く(-)、かける(*)、割る(/)」が可能
・記号を使うことで、カラムのデータを計算する
・かける場合はアスタリスク(*)、割る場合はスラッシュ(/)を用いることに注意

四則演算の使い方
・今回はpriceカラムに1.08をかけ、消費税を含んだ値を取得する
・DISTINCTと同様に、四則演算をSELECTで取得するカラムに使用することで、計算後のデータが取得できる

3.SUM

関数を使ってみよう
・消費税も含むと結構なお金を使ってそう... もっと節約するために詳しくデータを分析したいが、どうすればいいか？
・例えば今まで使ったお金の合計、平均などを計算したい
・それなら集計関数を使ってみると良い
・テーブルに保存されている数値データの合計、平均などを求めてくれる

合計を求めよう
・まず手始めに、今まで使った金額の合計を知りたい
・今回の場合、priceカラムのデータの合計を計算すれば、今まで使った金額の合計が分かる

SUM関数
・SQLで数値の合計を計算する場合は、SUMを用いる
・「 SUM(カラム名) 」のようにすることで、指定したカラムに保存されたデータの合計を計算することが可能

SUM関数の使い方
・SUM関数はSELECTで取得するカラムに用いることで、集計結果を取得することができる

WHEREとSUM関数
・SUM関数はWHEREと併用することができる
・WHEREを使うことで、にんじゃわんこが今まで使ったお金の合計金額を取得している

4.AVG

平均を計算しよう
・使っていたお金の合計はわかったので、次は1回あたりに使うお金の平均を取得してみたい
・それではデータの平均を計算する集計関数を見てみよう
・priceカラムに保存されているデータの平均を計算できれば、1回あたりに使うお金の平均が求められる

AVG関数
・SQLで数値の平均を計算する場合は、AVGを用いる
・「AVG(カラム名)」のようにすることで、指定したカラムに保存されたデータの平均を計算することが可能

AVG関数の使い方
・AVG関数はSELECTで取得するカラムに使用することで、計算結果を取得することができる

WHEREとAVG関数
・AVG関数はWHEREと併用することができる
・WHEREを使うことで、にんじゃわんこが買った商品の平均金額を取得している

5.COUNT

データの数を計算しよう
・金額の合計は大きいのに、1回あたりに使う金額が小さかったので、買い物をする頻度が高いということ！今まで何回お金を使ったか知りたい
・それなら保存されているデータの数を数える集計関数を使ってみよう
・purchasesテーブルに保存されているデータの数を数えれば、何回買い物をしたかがわかる

COUNT関数
・COUNT関数は、指定したカラムのデータの合計数を計算してくれる関数
・「COUNT( カラム名 )」とすることで、指定したカラムのデータの数を計算する

COUNT関数とnull
・COUNT関数でカラム名を指定した場合、nullになっているデータの数は計算されない

COUNT関数の使い方
・nullの数も含めてデータの数を計算したい場合は、COUNT関数で * (全てのカラム)を指定する
・* を使った場合、特定のカラムのデータの数ではなく、レコードの数を計算する
・この方法で、nullの数を含めてデータの数を数えられる

WHEREとCOUNT関数
・COUNT関数はWHEREと併用することができる
・WHEREによって、にんじゃわんこがいくつ商品を買ったかを取得している

6.MAX・MIN

最大・最小を求めよう
・今度は節約するために、1番高い商品の値段がいくらだったのかを知りたい！ 商品の値段を高いもの順に並び替えればいいか？
・もっと簡単な方法がある
・保存されているデータの中で最大・最小のものを検索する集計関数
・これを使えば、1番高い商品がいくらだったのかがすぐにわかる

MAX・MIN関数
・SQLでMAXという関数を用いると、指定したカラムのデータの中から最大のデータを取得することができる
・同じく、MINと言う関数を用いることで、最小のデータを取得することができる

MAX・MIN関数の使い方
・MAX,MINも他の集計関数と同様にSELECTで取得したカラムに使用することができる
・priceカラムを指定することでもっとも値段が高かった商品のデータを取得することができる

WHEREとMAX・MIN関数
・MAX,MINも他の集計関数と同様にWHEREと併用することができる
・WHEREを使うことでにんじゃわんこが使った1番高い金額を取得している

7.GROUP BY

データをグループ化しよう
・集計関数は便利だが、日付ごとの金額の合計はWHEREを使って別々に取得しなければならないか？
・例えば日付ごとの金額の合計を一度に出して分析したい
・そのような場合はグループ化という機能を使う必要がある
・日付が同じデータごとに集計関数を使うことをグループ化という

GROUP BY
・GROUP BYを用いると、データをグループ化することができる
・例えば「GROUP BY カラム名」とすることで、指定したカラムで、完全に同一のデータを持つレコードどうしが同じグループとなる

グループ化と集計
・グループ化するには、今までの集計関数を取得するFROMの後ろに「GROUP BY カラム名」を追加する
・集計関数により、各グループごとにデータが集計される

GROUP BYの注意点
・GROUP BYを用いる場合、SELECTで使えるのは、GROUP BYに指定しているカラム名と、集計関数のみ
・SELECTで集計関数を使っていないため、日付ごとに集計された値が取得できない

8.GROUP BY (複数)

複数のカラムを使ってグループ化しよう
・実は複数のカラムでもグループ化ができる
・例えば日付とキャラクターごとの金額の合計を出すこともできる

複数カラムのGROUP BYの書き方
・GROUP BYは複数のカラム名を適用させることができ、その場合は、カラム名同士をコンマ(,)で繋げる

複数カラムのGROUP BY
・GROUP BYに複数のカラム名でグループ化をすると、データの組み合わせの数だけグループができる

複数カラムのGROUP BYでできること
・集計関数により、各グループごとにデータを集計することができる
・例えば、それぞれグループ化したデータに対してSUM関数とCOUNT関数を用いたものとなっている

9.GROUP BY (WHERE)

細かい条件でデータをグループ化しよう
・日付とキャラクターごとに使ったお金の合計は分かったが、もっと細かい条件で検索したい
・例えば、日付ごとの食費に使ったお金の合計は取得できないか？
・それにはWHERE文も一緒に使うと便利
・GROUP BYはWHEREと一緒に使うこともできる

WHEREとGROUP BY
・GROUP BYはWHEREとも併用することができその場合はWHEREの後に書く
・WHEREとGROUP BYと集計関数は以下の順番で実行されていく
・WHEREはまず最初に、そのあとにGROUP BYと関数が実行される

WHEREとGROUP BYの順番（1）
・今回のように日付とキャラクターごとの食費に使ったお金の合計を取得するときは、
①WHEREでカテゴリーが「食費」であるレコードを検索する
②日付とキャラクターでグループ化する
③集計関数で集計するという順番になる

WHEREとGROUP BYの順番（2）
・グループ化した結果に関して、③集計関数で集計する

WHERE と GROUP BYの書き方
・先にWHEREでカテゴリーが食費のレコードを検索してから、日付とキャラクターでグループ化すれば、日ごとの食費に使ったお金がわかる
・WHEREとGROUP BYの順番に気をつけて、実際に演習をしてみよう

10.HAVING

グループ化したデータを更に絞り込もう
・日ごとの使った金額の中でも、1000円以上使った日など、たくさんお金を使った日のデータを取得したいが、どうすればいいか？
・GROUP BYでグループ化したデータから、更に特定のグループのみを取得したい場合はHAVINGを用いる必要がある

HAVING
・GROUP BYでグループ化したデータを更に絞り込みたい場合には、HAVINGを用いる
・「GROUP BY カラム名 HAVING 条件」のようにすることで、条件を満たすグループを取得することができる

WHEREとHAVING
・グループ化した後のデータを絞り込む際、WHEREではなくHAVINGを使うのは、SQLの各コマンドが以下の順番で実行されていくため
・WHEREはまず最初に、そのあとにGROUP BYと関数が実行され、その後にHAVINGが実行される

WHEREとHAVINGの違い
・実行順序によって、WHEREとHAVINGは検索対象に違いがある
・WHEREはグループ化される前のテーブル全体を検索対象とするのに対し、HAVINGはGROUP BYによってグループ化されたデータを検索対象とする

HAVINGの注意点
・HAVINGはグループ化された後のテーブルから検索するため、条件文で使うカラムは必ずグループ化されたテーブルのカラムを使う

11.総合演習

演習問題
・ここまで学んだことの復習として演習問題を用意した
・もしわからない問題があったら、焦らず前のページで復習する