[小ネタ] SQLの GROUP BY / ORDER BY には数字 (1, 2...) を指定しよう

この記事で紹介しているSQLの書き方は、その場で使い捨てる用途を想定しています。（プロダクションコードは想定していません。）

こんなSQL書いてしまっていませんか？

よくこんな集計用のSQLを即興で書くことが多いと思いますが、

--------------------------------------------------------
-- users テーブルについて、部署・役職・作成日ごとに件数を集計する
-- (MySQL用)
--------------------------------------------------------
SELECT
  u.department_code `部署コード`,
  u.role_code `役職コード`,
  DATE_FORMAT(u.created_at, '%Y-%m-%d') `作成日`,
  COUNT(*) `人数`
FROM users u
GROUP BY
  u.department_code,
  u.role_code,
  DATE_FORMAT(u.created_at, '%Y-%m-%d')
ORDER BY
  u.department_code ASC,
  u.role_code DESC,
  DATE_FORMAT(u.created_at, '%Y-%m-%d') ASC

GROUP BY / ORDER BY で SELECT に出現している項目 (DATE_FORMAT(u.created_at, '%Y-%m-%d')) を繰り返し書いているのが非常に煩雑ですね。

GROUP BY セレクトリストのカラム位置 / ORDER BY セレクトリストのカラム位置 の構文を使ってスッキリとしたSQLを書こう

GROUP BY / ORDER BY では SELECT 上のカラムや式について 数字で参照することができます。

--------------------------------------------------------
-- users テーブルについて、部署・役職・作成日ごとに件数を集計する
-- (MySQL用)
--------------------------------------------------------
SELECT
  u.department_code `部署コード`,
  u.role_code `役職コード`,
  DATE_FORMAT(u.created_at, '%Y-%m-%d') `作成日`,
  COUNT(*) `人数`
FROM users u
GROUP BY 1, 2, 3
ORDER BY 1 ASC, 2 DESC, 3 ASC

さっき繰り返し現れていたDATE_FORMAT(u.created_at, '%Y-%m-%d') が 1箇所で済むようになりました。

即興で集計する場合は、表の左側に集計キーを置き、それらで集約・ソートしたいことが8割なので、数字を書くだけで集計キーを参照できるのは、非常に理にかなった構文と言えます。

ちなみに、初心者は誤解しやすいのですが、これは数字による特殊な構文でのカラム参照であって、数字による式の結果で集計やソートをしているわけではありません。

たとえば、MySQLのマニュアルであれば、以下の column position にあたる数字のみが出てきた場合の特別な構文です。

Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1
MySQLマニュアル

PostgreSQLなどMySQL以外の対応状況について

この構文はMySQL以外でもサポートされています。

実際に各DBMSのマニュアルとWeb上のプレイグラウンド で確認してみたところ¹、以下のようになっているようです。

DBMS	GROUP BY 数字	ORDER BY 数字
MySQL	OK	OK
PostgreSQL	OK	OK
SQLite	OK	OK
SQL Server	❌ NG	OK
Oracle	❌ NG	OK

OracleとSQL Serverについては GROUP BY 数字 がサポートされていないのが残念です。（マニュアルでも確認できませんでした）

1. 2024/05/22 時点で選べる比較的新しいバージョンを用いています。厳密なバージョンは確認していないので参考程度としてください。 ↩