抽出したデータの表示順を指定する際、「ORDER BY」句を使います。
ビジネスの場面では、クエリで表示順を調整する場面は少なく、抽出されたレコードを別ツールで表示順指定することが多いため、使うことは少ないでしょう。
一方、SQLの入門書では基本構文とされ、早い段階で履修することになります。
これまで「SELECT」「FROM」「WHERE」と勉強してきたものと比べれば簡単なので、気楽に覚えてしまいましょう。
初心者はORDER BY句で２つのポイントをおさえればOK！
- ORDER BY句でカラムと昇順・降順を指定することで、抽出レコードの表示順を指定できる。
- カラム指定が足りないと、抽出結果の並び順が変わることがある。
最初に演習用サンプルデータを説明します
SELECT句、FROM句、WHERE句での演習同様、魔法の呪文としてコピペしてご利用ください。
WITH sample AS (
SELECT * FROM UNNEST(ARRAY<STRUCT<student_id INT64, jap INT64, mth INT64, sci INT64, sct INT64, eng INT64>>[
(1, 80, 55, 65, 75, 95),
(2, 70, 60, 60, 70, 50),
(3, 75, 80, 70, 70, 90),
(4, 65, 95, 70, 95, 60),
(5, 95, 90, 70, 95, 50),
(6, 90, 85, 75, 85, CAST(NULL AS INT64))
])
)生徒ID（student_id）と５教科のテストの点数、国語（jap）、数学（mth）、理科（sci）、社会（sct）、英語（eng）を用意しています。
生徒ID：6の生徒は、英語のテストは欠席か何かでNULLになっています。
試しに抽出してみると下記のようになります。
SELECT * FROM sample
ORDER BY句を使うと抽出結果のレコード表示順を指定できる
SELECT句、FROM句、WHERE句を使うことで、欲しいデータを抽出する方法を勉強してきました。
ORDER BY句では、抽出したレコードの表示順序を指定する方法を勉強します。
業務の中で、表示順序を指定したい場面には、下記のようなケースがあります。
- 見栄えを良くしたい時。
- 抽出結果の件数が多いけれど最初や最後のレコードの値だけ目視確認したい時。
- プログラム開発などの業務において、一意な表示順の実現が要件に含まれている時。
逆に言うと、非エンジニアのビジネスパーソンがSQLを使う場面では、ORDER BY句の重要性はそこまで高くありません。
全部を完璧に履修しようとするとタイパも悪いので、おさえておきたいポイントはおさえて、重要でないポイントは深追いせず勉強を進めましょう。
ORDER BY句では表示順指定に使うカラム名を指定する
早速ORDER BY句を使ってみましょう。
国語（jap）のテストの結果の昇順（小さい値が先、大きい値が後）で表示するように指定してみます。
-- 演習01
SELECT * FROM sample
ORDER BY jap実際にクエリを実行した結果がこちら。
「ORDER BY」の後ろにカラム名を指定して、どのカラムを基準に並べ替えるかを決めます。
ORDER BY句で「DESC」と指定すると降順にできる
昇順は試したので、次は降順（大きい値が先、小さい値が後）を試してみましょう。
数学（mth）の降順を指定してみます。
-- 演習02
SELECT * FROM sample
ORDER BY mth DESC実際にクエリを実行した結果がこちら。
「ORDER BY」の後ろにカラム名を指定するところまでは昇順と同様ですが、降順ではその後ろに「DESC」と指定しています。
この指定によって降順を指すようになります。
複数カラムの指定などの応用が可能
複数カラムを指定してレコード表示順を指定することもできる
次は理科（sci）の昇順を試してみましょう。
ただし、理科は70点の生徒が３名いるので、理科の点数が同じであれば数学の点数の昇順にします。
-- 演習03
SELECT * FROM sample
ORDER BY sci, mth実際にクエリを実行した結果がこちら。
カラム名の後ろに「,」（半角カンマ）を書いて、値が等しかった時に利用する次の基準カラムを指定できます。
カラム指定はいくつも可能です。
理科（昇順）、社会（昇順）、英語（昇順）、の順で指定してみましょう。
-- 演習04
SELECT * FROM sample
ORDER BY sci, sct, eng実際にクエリを実行した結果がこちら。
複数カラムを指定する時に、昇順と降順を入り混ぜることもできます。
理科（昇順）、社会（降順）、生徒ID（昇順）、の順で指定した場合も見てみましょう。
-- 演習05
SELECT * FROM sample
ORDER BY sci, sct DESC, student_id実際にクエリを実行した結果がこちら。
カラム名でなくカラム番号を指定することもできる
沢山のカラムを指定するのが大変な時もあります。
何番目のカラムを使うか、番号で指定することもできます。
演習05のケースを番号指定に直してみましょう。
-- 演習06
SELECT * FROM sample
ORDER BY 4, 5 DESC, 1実際にクエリを実行した結果がこちら。
演習05と同じ結果になります。
計算結果の並び順を指定することもできる
５教科の合計点での降順を試してみましょう。
-- 演習07
SELECT * FROM sample
ORDER BY (jap + mth + sci + sct + eng) DESC実際にクエリを実行した結果がこちら。
とはいえ、すぐに合っていそうか分かりづらいですね。
次の解説を利用して確認していきましょう。
処理後のカラムを指定することもできる
５教科の合計点での降順を改めて試すのですが、今回はSELECT句で計算し、計算結果を利用してみましょう。
-- 演習08
SELECT *, (jap + mth + sci + sct + eng) AS total
FROM sample
ORDER BY total DESC実際にクエリを実行した結果がこちら。
演習07と同じ並び順になります。
ちなみに、このケースでもカラム番号で対応できます。
-- 演習09
SELECT *, (jap + mth + sci + sct + eng) AS total
FROM sample
ORDER BY 7 DESC実際にクエリを実行した結果がこちら。
ORDER BY句を指定しないと毎回同じ表示順になるとは限らない
ORDER BY句を利用する時の注意事項です。
BigQueryがよしなにしてくれて、良い感じの表示順にしてくれることはありますが、ORDER BY句で指定されていない範囲では表示順の保証はされません。
同じクエリを実行したとしても、１回目と２回目で表示順が変わってしまうことがある、ということです。
これを「冪等性（べきとうせい）が無い」と表現しますが、ビジネスの場面ではあまり使われず耳なじみが無い言葉でしょう。
数学において、冪等性（べきとうせい、英: idempotence、「巾等性」とも書くが読み方は同じ）は、大雑把に言って、ある操作を1回行っても複数回行っても結果が同じであることをいう概念である。
https://ja.wikipedia.org/wiki/%E5%86%AA%E7%AD%89
冪等性を担保するためには、一意な表示順になるために必要なカラムを十分に指定する必要があります。
「一意な表示順になるために必要なカラム」はどうやれば分かるかと言うと、結論、プライマリキーを指定することで実現します。
初心者にとって「プライマリキー」を勉強するのはもっと後が良いでしょう。
プライマリキーを解説する記事へのリンクを設置予定
ORDER BY句を勉強する時に初心者が後回しにして良いポイントX選
ORDER BY句はあまり使わない
ビジネスでデータを加工して可視化する時、表のまま見せるより、グラフなどの図にして見せる場面が多いでしょう。
グラフでデータを取り込む時、レコードの並び順は関係ありません。
たとえばExcelで表として見せるとしても、レコードの並び順はExcel上で指定できます。
実際に実務で使う場面としては、クエリを書いている最中、思ったような結果が得られていそうかを確認するために使うことが多いでしょう。
後述しますが、ORDER BY句は重たいため、「このクエリのORDER BY句は本当に必要なんだっけ？」と見直せるようになると良いでしょう。
ちなみに、システム開発をするエンジニアであれば、画面に一覧表示する機能を開発する時などは並び順指定が重要だったりするので、よく使ったりします。
文字列の表示順に注意
実務では、文字列型での数値を扱う場面があります。
１から１００までの数値があるとして、数値型で昇順に並べると、１、２、３、と意図通りの並び順になるでしょう。
しかし、文字列型で表現された数値を取り扱うと、辞書での表示順となるため、１、１０、１００、１１、１２、１３、となります。
「１００」と「１１」では、２文字目「０」の方が「１」より小さい扱いとなるからです。
意図通りの並び順にしたい時はCASTで数値型にしましょう。
記号を含むなどで数値に直せない時は、やり方を考えて、関数で文字列を加工するなどする必要があります。
実は「ASC」で昇順を指定できる
並び順を指定する時は昇順か降順の２択で、昇順は「ASC」、降順は「DESC」、「ASC」も「DESC」も指定が無ければ「ASC」を省略しているものとして昇順になるわけです。
わざわざ「ASC」を入力する人は少ないので、豆知識程度に覚えておけば良いでしょう。
試しに、演習05で「ASC」を省略しなかった場合を記載しておきます。
-- 演習10
SELECT * FROM sample
ORDER BY sci ASC, sct DESC, student_id ASCNULLが含まれる場合のレコード表示順の指定方法がある
NULLが未定の値にも関わらずORDER BY句で指定されたために大小を決めなければならない、その時コンピュータ側は、「こういう時はNULLが最も大きい値だったものとして取り扱おう」といった具合に、仮置きの表示場所を決めているわけです。
ただし、環境によって結果が異なるのでご注意ください。
OracleやMySQLなどを使っていると、NULLが最大値扱いになったり、最小値扱いになったりします。
カラム名の後ろに「NULLS FIRST」（NULLは最初に持ってくる）「NULLS LAST」（NULLは最後に持ってくる）を指定することで、NULLの表示順を指定することができます。
降順の場合は「DESC」の後ろに指定します。
-- 演習11
SELECT * FROM sample
ORDER BY eng NULLS FIRST実際にクエリを実行した結果がこちら。
BigQueryにおいて、英語（eng）の昇順ではNULLが最後でしたが、「NULLS FIRST」と指定することで、NULLが最初に表示されるようになっています。
-- 演習12
SELECT * FROM sample
ORDER BY eng DESC NULLS LAST実際にクエリを実行した結果がこちら。
降順であればNULLが最初でしたが、「NULLS LAST」を指定することで、NULLが最後に表示されるようになっています。
余談ですが、欠席で合計点未定扱いになってしまうのも可哀相なので、仮の点数５０点をあててみましょうか。
そうすれば仮の合計点順位を算出できそうです。
ただし、仮の点数で１位になるのも気持ち悪いでしょうから、英語（eng）がNULLだったら最後に表示させます。
同点の場合は生徒IDの昇順にしましょう。
-- 演習13
SELECT *, (jap + mth + sci + sct + IFNULL(eng, 50)) AS total
FROM sample
ORDER BY (jap + mth + sci + sct + eng) DESC NULLS LAST, student_id実際にクエリを実行した結果がこちら。
特殊な操作ですが、こういった調整を行うことも可能だとお分かりいただけたかと思います。
ORDER BY句を使うと処理リソースを多く使う
クエリを書いていると、ORDER BY句を使ったクエリが重たいと感じる場面も出てくるでしょう。
実は、コンピュータにとってORDER BY句での表示順計算は大変で、処理リソースを多く使います。
これが自分のパソコンの中だけで動作していれば良いのですが、ビジネスパーソンがSQLを使う場面では、BigQueryのようにウェブ上のサービス（Saas）を使うことが多く、自分のパソコンの中だけの問題ではなくなります。
他のメンバーも同じ環境で処理を実行しているので、誰かが重たいクエリを実行した時、他のメンバーがクエリを実行するための処理リソースが枯渇してしまいます。
ORDER BY句を使ってキレイな表示順になっていると気持ち良いものですが、必要でないならORDER BY句を使うことは避けましょう。
LIMIT句を使うと抽出する件数を絞ることができる
ORDER BY句のあとにLIMIT句を指定し、最初の何件分のレコードだけを抽出するか指定します。
試しに、国語（jap）の点数上位３名に絞って抽出してみます。
-- 演習14
SELECT * FROM sample
ORDER BY jap DESC
LIMIT 3実際にクエリを実行した結果がこちら。
抽出レコードが３件に絞られています。
システム開発をするエンジニアであれば、画面に一覧表示する機能で、１ページに表示する件数を指定する時に使うことになるでしょう。
WHERE句でなくLIMIT句を使ってレコードを絞るメリット
非エンジニアであれば、メリットはほぼ無いでしょう。
TOP数件だけを可視化したい、といった場面は考えられますが、データ量が多過ぎなければ、とりあえず全部抽出してしまって、可視化の際に削ることが多いと思います。
大容量のテーブルから、試しに数件のレコードを抽出して、実際の値を確認してみる場合に指定することがあります。
しかしBigQueryなどのビッグデータ演算においては、最後にLIMIT句があってもそこまで軽くならないですし、プレビューなどを使用することで十分だったりと、使う場面に中々出くわしません。
OFFSET句を使うとLIMIT句の抽出範囲をもっと指定できる
LIMIT句の後にOFFSET句を指定し、何件分のレコードをskipするか指定します。
試しに、英語（eng）の点数上位３名を抽出してみましょう。
ただし、最初の１件はskipするものとします。
-- 演習15
SELECT * FROM sample
ORDER BY eng DESC
LIMIT 3
OFFSET 1実際にクエリを実行した結果がこちら。
抽出レコードが３件に絞られ、最初の１件はスキップしています。
システム開発をするエンジニアであれば、画面に一覧表示する機能で、何ページ目かを指定する時に使うことになるでしょう。
振り返り
お疲れさまでした。
ビジネスの場面で実際に使うものに絞れば、覚えることは少なく、比較的簡単だったでしょう。
初心者が最初に覚える構文は以上となります。
次は、ビジネスの場面で実際によく使う関数を紹介します。
よく使う関数を紹介する記事を設置予定