【環境】
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
