【SQLの基本】取得データの加工
はじめに
SQLの基本として取得・作成・更新・削除があると思いますが、その中で最も使われるのは取得、つまりはSELECTです。
SELECT 列名 FROM テーブル名;
SELECTで取得したデータは基本的に取得元のテーブルを切り出したものになると思います。どういうことかというと、例えば以下のようなテーブルがあるとします。
https://www.sejuku.net/blog/72964
テーブルから名前のデータを取り出します。
SELECT 名前 FROM game_character;
出力結果
名前
--------------------------
| ヤマダ |
| スズキ |
| タナカ |
| タカハシ |
| ワタナベ |
出力結果を見ると元のテーブルの以下の部分が切り出されたことになります。
WHERE句を使えばそのまま切り出されたものが取得できるわけではありませんが、テーブルの順番にしたがってデータが取得されるため、取得データの見た目は一定です。
ですが、取得データを加工する方法があります。今回は取得データを加工する各SQLを見ていきましょう。
DISTINCT
distinctは「他とまったく別な、別個の」と訳されるようです。わかりやすくいうと「固有の」とか「唯一無二の」とかが個人的にはしっくりきます。uniqueと類義語にあたります。
DISTINCTを使用することで取得したデータの重複を取り除くことができます。
SELECT DISTINCT 列名 FROM テーブル名;
まずはDISTINCTを使用しない例から。上記のテーブルで職業一覧を取得するとします。
SELECT 職業 FROM game_character;
出力結果
職業
--------------------------
| 勇者 |
| 戦士 |
| 戦士 |
| 魔法使い |
| 僧侶 |
戦士が重複していますね。DISTINCTを使用すればこの重複を取り除きます。使い方としては重複を取り除く列名の前にDISTINCTを記述します。
SELECT DISTINCT 職業 FROM game_character;
出力結果
職業
--------------------------
| 勇者 |
| 戦士 |
| 魔法使い |
| 僧侶 |
戦士が一つ取り除かれました。このように取得したデータの重複を許可しないというのがDISTINCTになります。
使用例ですが、例えば上記のテーブルで全体のユーザが使っている役職は何種類かを調べたいとします。上記のようなユーザが5人しかいないテーブルであればDISTINCTを使用せずとも取得データの見た目でわかります。しかし、例えば10000人いるとすれば10000行のデータが取得されてしまい、見た目では判断できなくなります。そこでDISTINCTを使用することで、重複したデータを取り除く使用されている役職がわかるようになります。
余談ですがRailsのActive Recordでもdistinctはあります。selectメソッドと併用することで列の重複を防ぎます。上述したSQLと同一のデータを取得する方法は以下になります。
GameCharacter.select(:job).distinct
ORDER BY
取得したデータの順番を並び替えることができます。
SELECT 列名 FROM テーブル名 ORDER BY 列名 並び順;
例えば、加工なしにユーザのレベル一覧を取得するSQLを発行します。
SELECT レベル FROM game_character;
出力結果
レベル -------------------------- | 30 | | 42 | | 25 | | 60 | | 20 |
うーん。ちょっと見づらい笑。
この取得データをレベルが小さい順に並べた方が見やすそうですね。その時にORDER BYを使用することでデータを並び替えることができます。
SELECT レベル FROM game_character ORDER BY レベル ASC;
出力結果
レベル -------------------------- | 20 | | 25 | | 30 | | 42 | | 60 |
これでデータをレベルの昇順(小さい順)に並び替えることができました。昇順と指定している部分はASCです。列名の後に並び順を指定する語句としてはASCとDESCがあります。
- ASC・・・データの並びを昇順(小さい順) ※省略可
- DESC・・・データの並びを降順(大きい順)
昇順とは逆に降順で並び替えるにはASCをDESCに置き換えます。
SELECT レベル FROM game_character ORDER BY レベル DESC;
出力結果
レベル -------------------------- | 60 | | 42 | | 30 | | 25 | | 20 |
RailsのActive RecordでのORDER BYはかなりよく使うorderメソッドになります。
GameCharacter.all.order(level: "ASC") GameCharacter.all.order(level: "DESC")
OFFSET・FETCH
ORDER BYと組み合わせて並び替えたデータから上から数件取得する方法があります。それがOFFSETとFETCHです。
SELECT 列名 FROM テーブル名 ORDER BY 列名 並び順 OFFSET 先頭から除外する行数 ROWS FETCH NEXT 取得行数 ROWS ONLY;
だんだんと構文が長くなってきて嫌になりますね笑
先頭から除外する行数というのはつまり「どの行から取得するか」という意味です。例えば加工前の取得したデータが以下だったとします。
レベル -------------------------- | 60 | | 42 | | 30 | | 25 | | 20 |
もし3番目の行から取得したい場合、1番目と2番目の行は除外する必要があります。つまり「先頭から除外する行数」は2となります。
SELECT レベル FROM game_character ORDER BY レベル DESC OFFSET 2 ROWS;
出力結果
レベル -------------------------- | 30 | | 25 | | 20 |
次に取得行数ですがこれはそのまま何行取得するかの意味です。先ほどのOFFSETだけでは先頭から何行除外するかだけでどこまで取得するかを指定していません。FETCHによってどこまで取得するかを指定することができます。例えば、3番目の値だけを取得したい場合、取得行数は1行になります。
SELECT レベル FROM game_character ORDER BY レベル DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
出力結果
レベル -------------------------- | 30 |
上記の説明ではOFFSETとFETCHをわかりやすくするために3番目のデータを取得するという方法を使用しました。最も一般的に使われる用途はトップ3などのように1番上から何件取得するというのが多い気がします。つまり、先頭から除外する行数が0行となりますが、その場合も0と明示しなければなりません。
SELECT レベル FROM game_character ORDER BY レベル DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
出力結果
レベル -------------------------- | 60 | | 42 | | 30 |
RailsのActive Recordではlimitメソッドとoffsetメソッドを掛け合わせます。offsetメソッドはSQLのFETCHと同一の役割でlimitメソッドはFETCHと同じ役割です。
トップ3を取得する場合は以下のようになります。
GameCharacter.all.order(level: "DESC").limit(3)
SQLとは異なりoffsetメソッドで0行と指定する必要はありません。limitメソッドのみで上から3行データを取得してくれます。
3番目のデータを取得する場合、offsetメソッドとlimitメソッドを併用します。
GameCharacter.all.order(level: "DESC").limit(1).offset(2)
