PostgreSQLでハイフン入り数字(string型)をソートする

はじめに

表題のことを実現するためにちょっと調べたりしたのでメモ。

前提

例えば、以下のようなデータがあるとする。

SELECT task_id FROM tasks;

| task_id |
|---------|
| 1-1     |
| 1-2     |
| 1-10    |
| 1-20    |
| 2-1     |

単純にtask_idに対してORDER BYをかけると、以下のような並びになってしまう。

SELECT task_id FROM tasks ORDER BY task_id DESC;

| task_id |
|---------|
| 2-1     |
| 1-20    |
| 1-2     |
| 1-10    |
| 1-1     |

子番号のソートがいけてない

解決策

実現したいことは 「親番号の降順 と 子番号の昇順 で取得したい」 なので、整理すると以下の2ステップで実現できそう。

1. 親番号と子番号を取得する
2. それぞれを降順と昇順で並べる

1.親番号と子番号を取得する

ハイフンを起点にその前と後ろを取得すれば実現できそうということでちょっと調べてみる。
結果、文字列の分割はsubstr、ハイフン位置の特定はstrposが使えそう。

substr('対象の文字列', '開始位置', '抽出する文字数') -- 第3引数は省略可。省略した場合は開始位置から一番最後までの文字列を取得する。
strpos('対象の文字列', '検索する文字列')

親番号と子番号を取得してみる。

SELECT 
  task_id 
  substr(task_id, 1, strpos(task_id, '-') - 1) -- 親番号(1文字目〜ハイフンの1つ前)の取得
  substr(task_id, strpos(task_id, '-') + 1) -- 子番号(ハイフンの1つ後〜最後まで)の取得
FROM tasks

取得できた

2.それぞれを降順と昇順で並べる

文字列のままではソートできないので、::integerでキャスト(CAST ASでも可)してからORDER BYで並び替える。

SELECT task_id 
FROM tasks
ORDER BY
  substr(task_id, 1, strpos(task_id, '-') - 1)::integer DESC, -- 親番号を降順
  substr(task_id, strpos(task_id, '-') + 1)::integer; -- 子番号を昇順(ASCは省略可)

| task_id |
|---------|
| 2-1     |
| 1-20    |
| 1-10    |
| 1-2     |
| 1-1     |

無事並べ替えできた

その他

substr()の第2引数の開始位置について

プログラムとは異なり、1文字目は1になる。
仮に0を指定すると空文字が取得できてしまい、意図した文字を抽出できない。