ソフトウェア開発者の日常

こだわりなく書きたいことを書いていきます。

Postgre SQL:値の設定ルールが変わったために、ソートの条件を見直し

スポンサーリンク

今年からルールが変わったために対応していました。
f:id:AJYA:20141024053859j:plain
photo credit: therefromhere via photopin cc

昨年まではあるカラムに対して、A-01~99/B-01~99/C-01~99という値を割り振られていました。
今年はお客さんの都合で、AA-CC-01~99/AA-DD-01~99/AB-CC-01~99/AB-DD-01~99/AC-01~99という値が割り振られました。

システム上の動作は問題はありませんでしたが、ソートの順序だけ正しくありませんでした。

昨年までの値は、以下のSQLでA-01~99→B-01~99→C-01~99の順番にソートしていました。

SELECT
SUBSTRING( order FROM '[a-zA-Z]+') AS col1,
CAST( SUBSTRING( order FROM '[0-9]+') AS INTEGER) AS col2,
FROM table1
ORDER BY col1 ASC, col2 ASC;

今年の値では、上記SQLではAA-CC-01がAAと01、AA-DD-01がAAと01でソートしてしまうため、
AA-CC-01
AA-DD-01
AA-CC-02
AA-DD-02
AC-01
の順番でソートされていました。

ソートを
AA-CC-01
AA-CC-02
AA-DD-01
AA-DD-02
AC-01
の順番にするためには、1個目の-の後の部分(CC/DDの部分)を取り出して、数値だったらダミーの値に置き換えて、そうでなければそのままソートに使う方法を思いついたので、実装したのが以下のSQLです。

SELECT
SUBSTRING( order FROM '[a-zA-Z]+') AS col1,
CASE 
    WHEN CAST( SUBSTRING( split_part(order, '-', 2) FROM '[0-9]+') AS INTEGER) IS NOT NULL THEN 'ZZ'
    ELSE split_part(order, '-', 2)
END AS col3,
CAST( SUBSTRING( order FROM '[0-9]+') AS INTEGER) AS col2,
FROM table1
ORDER BY col1 ASC, col3 ASC, col2 ASC;

これで希望通りにソートができました。
元のSQLを自分で書いていなかったので、理解するまでちょっと悩みましたが、実行結果を見て納得していました。