こっぱみぢんこ

並べ

webシステムで一覧画面を表示するのに、表示するデータの並べ替えのキー項目と昇順／降順を自由に指定できるようにしたい場合が有る。割とよく有る。例えば文庫本で

書籍	著者	発行	初版
幻視街	半村良	角川書店	1980/12/30
瑠璃の方舟	夢枕獏	文藝春秋	1998/04/10
俺に関する噂	筒井康隆	新潮社	1978/05/25
山月記	中島敦	岩波書店	1994/07/18

となっているとき、ヘッダの項目名をクリックすると、その項目をキーに昇順で、もう一度クリックすると降順で並べ替えるような場合。

件数が少なければクライアント側でやればいい。そのためのjQueryのプラグインなんかはたくさん出回っているし、自前で作るのも大して難しくない。でも件数が多いと、全部を画面表示はできないので上位N件を抽出することになり、サーバーに、と言うかデータベースに問い合わせなければならなくなってしまう。

これをどう実装するか。

すぐに思いつくのは、SQLの order by を条件指定に応じて動的に作るというやり方。でも、これだと大抵の場合コードが汚くなったり見通しが悪くなったりするんだよな。 SQLの作成をJavaやVBの側でできるならまだいいけど、PL/SQLでやらなきゃいけないとなると、読み難さもより一層。シングルクォーテーション地獄。地獄は大袈裟か。しかし、多少はマシなJavaやVBでも、SQLの要素が細切れでコード中に存在して、PL/SQLとはまた違う方向で見難かったりもするんだよな。

ということで、順序指定をなるべくSQLの範囲でなんとかできないか考えてみたのだが。

1

case って式だから order by に組み込めるのでは？と思って試してみた。


select *
from ( select 書籍
            , 著者
            , 発行
            , 初版
       from 文庫本
       order by case :key
                  when 1 then 書籍
                  when 2 then 著者
                  when 3 then 発行
                  else        初版
                  end )
where rownum <= :num

:key を1にすれば書籍で、2にすれば著者でソートされる。

あっさり上手くいったと思ったが、直後に挫折。これだと、ASC/DESCが固定になってしまうんだよな。 ASC/DESCは、ソート条件式（上の例なら case 〜 end）に付け加えるものだから当然なんだけどさ。

ASC/DESCが式中に含められないので、ASCまたはDESCは固定しておいて条件分岐の方を工夫することを考えるのだが。これが数値や日付であれば、正負の符号をひっくり返すことで何とかできる。文庫本なら、例えばこんな感じ。


select *
from ( select 書籍
            , 著者
            , 発行
            , 初版
       from  文庫本
       order by case :key
                  when 0 then 初版 - to_date('1900/01/01')
                  else        to_date('1900/01/01') - 初版
                  end )
where rownum <= :num

:key を0にすれば初版の昇順で、1にすれば降順でソートされる。

でもこれ、数値や日付だからできることで、可変長文字列だと途方に暮れて終わり。できる範囲であっても、コードをぱっと見ただけでは何がしたいのか判らず、最初の目的からは遠く外れた状態。ソートのキーが一つでこの状態なのに、キーが二つ三つと増えたらもうどうしようもない。

2

row_number も式だから、caseに組み込めるのでは？この結果で order by すればいいのでは？と思いついて試してみた。


select 書籍
     , 著者
     , 発行
     , 初版
     , case :key
         when 10 then row_number() over (order by 書籍 asc )
         when 11 then row_number() over (order by 書籍 desc)
         when 20 then row_number() over (order by 著者 asc )
         when 21 then row_number() over (order by 著者 desc)
         when 30 then row_number() over (order by 発行 asc )
         when 31 then row_number() over (order by 発行 desc)
         when 40 then row_number() over (order by 初版 asc )
         else         row_number() over (order by 初版 desc)
         end 順番
from  文庫本
where 順番 <= :num
order by 順番

:key を10にすれば書籍の昇順で、21にすれば著者の降順でソートされる。順番が列として明示的に定義されるので、先頭N件を取得するのも判り易い。

と、文庫本の例では一応は上手くいくのだが、しかしこれ、見通しが良いのか？何だか文字の密度が濃くて黒いし。ソートキーを二つ三つと追加するのは、組み合わせが決まっているなら over の中の order by に追加すれば良いので簡単だが、組み合わせが特定できない場合はどうすればいいのか。いくら何でも、取り得る組み合わせを全部書くわけにはいかないよな。

3

パラメーターを一つで色々やろうとするから組み合わせに悩まなければならないのだ。それならばと、一カ所であれこれやるのを諦めて、ソート用の値の取得とその優先順位設定を分けてみた。


select *
from ( select 書籍
            , 著者
            , 発行
            , 初版
       from ( select 書籍
                   , 著者
                   , 発行
                   , 初版
                   , case :bookDir
                       when 0 then row_number() over (order by 書籍 asc )
                       else        row_number() over (order by 書籍 desc)
                       end 書籍順
                   , case :authorDir
                       when 0 then row_number() over (order by 著者 asc )
                       else        row_number() over (order by 著者 desc)
                       end 著者順
                   , case :publisherDir
                       when 0 then row_number() over (order by 発行 asc )
                       else        row_number() over (order by 発行 desc)
                       end 発行順
                   , case :dateDir
                       when 0 then row_number() over (order by 初版 asc )
                       else        row_number() over (order by 初版 desc)
                       end 初版順
              from  文庫本 )
       order by case :key1
                  when 1 then 書籍順
                  when 2 then 著者順
                  when 3 then 発行順
                  else        初版順
                  end
              , case :key2
                  when 1 then 書籍順
                  when 2 then 著者順
                  when 3 then 発行順
                  else        初版順
                  end
              , case :key3
                  when 1 then 書籍順
                  when 2 then 著者順
                  when 3 then 発行順
                  else        初版順
                  end
              , case :key4
                  when 1 then 書籍順
                  when 2 then 著者順
                  when 3 then 発行順
                  else        初版順
                  end )
where rownum <= :num

:xxxDirは昇順降順を決める。

:key1〜4はソートのキー項目を決める。また、この並びが優先順位になっている。

一番内側で各項目に対する順番値を列として取得しておき、その外側でこの順番を適用する優先順を決めるのだな。そして脳内会議。

「パラメーターがいっぱいあるよね？」

「それは変更可能な項目と同じ数になっただけだし、パラメーターと設定する値との対応はむしろ整理されてるし…」

「同じようなコードがずらっと並んでるよね？」

「それは…その…」

「これこそ自動生成すれば良いんじゃないの？」

「そうすると、何をやっているのかパッと見に判り難いし…」

「じゃあこれは見通しが良いの？」

「…いい…とは言えないような…」

まあ、条件が複雑だとどうしたって面倒になるんだけど、order by のところは、我ながらちょっとどうかと思う。文庫本の例だと項目数が4つだからまだ見れなくもないけど、たぶんこれが限界だろう。結局のところ、SQLのレベルで何とかなるのは簡単な場合だけってことか。

4

ソートのキーと方向を order by の文字列に置き換えるのは、java だとこんな感じか。


import java.util.LinkedHashMap;
import java.util.Map;

public class SQLUtil {

    public enum Order {
        ASC("ASC"),
        DESC("DESC");

        private final String _order;

        Order(final String order) {
            _order = order;
        }

        public String toString() {
            return _order;
        }
    }

    public static String toOrderBy(final Map<String, Order> keys) {
        StringBuilder sb = new StringBuilder();
        for (Map.Entry<String, Order> e : keys.entrySet()) {
            sb.append(", ");
            sb.append(e.getKey());
            sb.append(" ");
            sb.append(e.getValue());
        }
        sb.replace(0, 2, "");
        return sb.toString();
    }

    // test
    public static void main(String[] args) {
        String s = SQLUtil.toOrderBy(new LinkedHashMap<String, Order>() {{
            this.put("書籍", Order.ASC);
            this.put("著者", Order.ASC);
            this.put("発行", Order.ASC);
            this.put("初版", Order.DESC);
        }});
        assert "書籍 ASC, 著者 ASC, 発行 ASC, 初版 DESC".equals(s);
        System.out.println(s);
    }
}

ロジックの中核である toOrderBy() は、ソートする項目の数にも値にも依存しない。変更に対する強さは、もう圧倒的にこっちなんだよな。

せっかくだから、メソッドtoOrderByをjava8っぽく書いてみる。


    public static String toOrderBy(final Map<String, Order> keys) {
        return keys.entrySet().stream()
                   .map(e -> e.getKey() + " " + e.getValue())
                   .reduce((a, b) -> a + ", " + b)
                   .get();
    }

各要素を変換して繋げて返すという流れがはっきりしている分、慣れるとこっちの方が判り易いような気がするな。最初のカンマを消すといった、地味に面倒な記述が不要なのもいい。

こうして作った文字列を、上のパターン2相当の


select 書籍
     , 著者
     , 発行
     , 初版
     , row_number() over ( order by :order ) 順番
from 文庫本
where 順番 <= :num
order by 順番

に :order として組み込む。何かもう圧倒的にすっきりしてるな。

しかし、今日も暇だったなぁ。

なんでこんなことを考えてるかというと、1時間以上も帰ってこないSQLを待ってて暇だったから。 SQLが帰ってこないのはサーバーが頑張っているからで、PCの側は本当に待っているだけ。なので、こっちはこっちでExcelでテストデータを作らせたら、これがまたCPU使用率が100%に振り切れた状態で1時間以上帰ってこない。もう本当に何もできないのだった。

こんな暇な状態なのに、実は忙しいと言うのが恐ろしいところ。今日が暇な分だけ、明日忙しくなるんだよな。

2014 08 01

並べ

1

2

3

4