【環境】
CentOS 6/PostgreSQL 8.4
CentOS 7/PostgreSQL 9.2
例えば、文字列型のカラムにて、文字列と数値が混じったデータの場合にソートすると、次のようになる。
db=> SELECT * FROM sample_data ORDER BY os;
os
-----------------
Mac OS X 10.12
Mac OS X 10.13
Mac OS X 10.9
Windows NT 10.0
Windows NT 6.3
いい感じにソートするには、少し工夫する。
db=> SELECT * FROM sample_data
ORDER BY
SUBSTRING(os FROM '^[A-Za-z ]+'),
TO_NUMBER(SUBSTRING(os FROM '[0-9.]+$'), '9999999999');
os
-----------------
Mac OS X 10.9
Mac OS X 10.12
Mac OS X 10.13
Windows NT 6.3
Windows NT 10.0
考え方としては、以下の通り。
・SUBSTRING を使い、ソートするカラムの先頭から英字を抜き出して、ソートの第一条件とする。
・SUBSTRING を使い、ソートするカラムの後ろから数値とドット（小数点）を抜き出して、to_numberで適当な桁数（上記では10桁）のnumeric型にキャストして、ソートの第二条件とする。
ソートの条件をSELECTで抽出してみると、どうなっているか分かりやすい。
db=> SELECT
os,
SUBSTRING(os FROM '^[A-Za-z ]+'),
TO_NUMBER(SUBSTRING(os FROM '[0-9.]+$'), '9999999999')
FROM
sample_data;
os | substring | to_number
------------------+-------------+-----------
Windows NT 10.0 | Windows NT | 100
Windows NT 6.3 | Windows NT | 63
Mac OS X 10.12 | Mac OS X | 1012
Mac OS X 10.9 | Mac OS X | 109
Mac OS X 10.13 | Mac OS X | 1013
ここに、'Mac OS X 10.12.1' というデータが入ってくると、うまくいかない（´・ω・｀）
db=> SELECT * FROM sample_data
ORDER BY
SUBSTRING(os FROM '^[A-Za-z ]+'),
TO_NUMBER(SUBSTRING(os FROM '[0-9.]+$'), '9999999999');
os
------------------
Mac OS X 10.9
Mac OS X 10.12
Mac OS X 10.13
Mac OS X 10.12.1
Windows NT 6.3
Windows NT 10.0
バージョンの区切りになっているドットの出現回数が違うのが原因。
この場合、出現回ごとに桁数を揃えて（ドットで区切って0詰めする）、ソート条件に追加すると意図したようにソートされる。
db=> SELECT * FROM sample_data
ORDER BY
SUBSTRING(os FROM '^[A-Za-z ]+'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 1), 3, '0'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 2), 3, '0'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 3), 3, '0');
os
------------------
Mac OS X 10.9
Mac OS X 10.12
Mac OS X 10.12.1
Mac OS X 10.13
Windows NT 6.3
Windows NT 10.0
試しに、'Mac OS X 11.0' や 'Mac OS X 9.99' という架空のデータを入れて試してみる。
db=> SELECT * FROM sample_data
ORDER BY
substring(os from '^[A-Za-z ]+'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 1), 3, '0'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 2), 3, '0'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 3), 3, '0');
os
------------------
Mac OS X 9.99
Mac OS X 10.9
Mac OS X 10.12
Mac OS X 10.12.1
Mac OS X 10.13
Mac OS X 11.0
Windows NT 6.3
Windows NT 10.0
いい感じでソートされる。
ソート条件がどうなっているか、SELECTで抽出してみると、桁毎に0詰めになっている事が確認できる。
db=> SELECT
os,
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 1), 3, '0'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 2), 3, '0'),
LPAD(SPLIT_PART(SUBSTRING(os FROM '[0-9.]+$'), '.', 3), 3, '0')
FROM sample_data;
os | lpad | lpad | lpad
------------------+------+------+------
Windows NT 10.0 | 010 | 000 | 000
Windows NT 6.3 | 006 | 003 | 000
Mac OS X 10.12 | 010 | 012 | 000
Mac OS X 10.9 | 010 | 009 | 000
Mac OS X 10.13 | 010 | 013 | 000
Mac OS X 10.12.1 | 010 | 012 | 001
Mac OS X 11.0 | 011 | 000 | 000
Mac OS X 9.99 | 009 | 099 | 000
但し、前半に文字列があり、後半に数字（ドットも可）がある、という前提で、バージョン部分が「x.y.z」というフォーマットしか判別できない。
使える場面が限られるけど、とりあえずメモ。
UserAgentから取り出したOSのソートには使えそう。
参考；
https://www.postgresql.jp/document/9.2/html/functions-string.html
https://www.postgresql.jp/document/9.2/html/functions-formatting.html
