読者です 読者をやめる 読者になる 読者になる

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

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

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して思い通りの順番に並べ替える方法

PostgreSQL SQL
スポンサーリンク

あるデータにおいて、当初は文字列の先頭と特定の文字の後に数字がついていました。
途中から仕様が変わって、文字列にはまったく数字が付かなくなり、以下のようなデータになりました。

番号(no) 名前(name)
1 大沼 直輝/金谷 凌/青井 敬三/表 博康
2 八尾 功二/春日井 伸次/鴨川 克成/川平 将大
3 有沢 竜平/神埼 修吾/その他/その他
4 飯塚 裕二/石野 猛/若月 理/その他
5 汲田 貴史/その他/その他/その他
6 大南 浩和/武原 悟/その他/その他
7 その他/その他/その他/その他
8
注:名前はジェネレータでランダム生成しています。

このような空のデータもある状態で、並べ替えをしなければならず、このまま単純に並べ替えたのでは、空のデータが1番目になり、その他が2番目になってしまいます。
並べ替えた後は、空のデータが1番最後で、その他が2番目になってほしいです。

f:id:AJYA:20170318001054j:plain
photo credit: tomasvondra DSC05340 via photopin (license)

クエリでの対処方法

クエリでどうすれば対処できるか考え、以下のようにしました。

select DISTINCT name,
  CASE 
    WHEN SPLIT_PART(name,'/', 1) = 'その他' THEN 98 
    WHEN name='' THEN 99 
    ELSE 1
  END AS tmp1,

  SPLIT_PART(name,'/', 1) AS tmp2,

  CASE 
    WHEN SPLIT_PART(name,'/', 2) = 'その他' THEN 98 
    WHEN name='' THEN 99 
    ELSE 1
  END AS tmp3,

  SPLIT_PART(name,'/', 2) AS tmp4,

  CASE 
    WHEN SPLIT_PART(name,'/', 3) = 'その他' THEN 98 
    WHEN name='' THEN 99 
    ELSE 1
  END AS tmp5,

  SPLIT_PART(name,'/', 3) AS tmp6,

  CASE 
    WHEN SPLIT_PART(name,'/', 4) = 'その他' THEN 98 
    WHEN name='' THEN 99 
    ELSE 1
  END AS tmp7,

  SPLIT_PART(name,'/', 4) AS tmp8
FROM data_table 
ORDER BY tmp1, tmp2, tmp3, tmp4, tmp5, tmp6, tmp7, tmp8;

PostgreSQLの文字列関数SPLIT_PARTを使って、特定の区切り文字(/)で文字列を分割して、指定したフィールドを取得しています。
指定したフィールドが「その他」なら98、空なら99、どちらにも当てはまらなければ1にしています。
この値でソートして、次にフィールドの値を使って、先に1になったデータ同士のソートを行います。
これを4回繰り返すことで、思ったとおりの順番に並び替えができました。