10-224 バケットソートで列をソート

SQLパズル

BukketSortTest
 A   B   C   D   E
--  --  --  --  --
15  20  10  18  99
16  17  13  20  11
18  23  40  29  33
30  31  32  33  34
44  43  42  41  40
54  51  52  53  50
63  64  62  61  60
74  73  72  70  71
88  88  88  88  99
99  99  99  99  99

同じ行で、列の昇順にソートする。

出力結果
 A   B   C   D   E
--  --  --  --  --
10  15  18  20  99
11  13  16  17  20
18  23  29  33  40
30  31  32  33  34
40  41  42  43  44
50  51  52  53  54
60  61  62  63  64
70  71  72  73  74
88  88  88  88  99
99  99  99  99  99

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table BukketSortTest(A,B,C,D,E) as
select 15,20,10,18,99 from dual union
select 16,17,13,20,11 from dual union
select 18,23,40,29,33 from dual union
select 30,31,32,33,34 from dual union
select 44,43,42,41,40 from dual union
select 54,51,52,53,50 from dual union
select 63,64,62,61,60 from dual union
select 74,73,72,70,71 from dual union
select 88,88,88,88,99 from dual union
select 99,99,99,99,99 from dual;

create or replace type BukketDataType as object(
A number(2),
B number(2),
C number(2),
D number(2),
E number(2));
/

create or replace type BukketDataTypeSet as table of BukketDataType;
/

SQL

create or replace function BukketData return BukketDataTypeSet PipeLined IS
    type BukketArrayDefine is table of number(2) index by binary_integer;
    BukketArray BukketArrayDefine;

    MinValue constant pls_Integer :=   0;
    MaxValue constant pls_Integer := 100;

    out_rec BukketDataType;
begin
    for rec in (select A,B,C,D,E
                  from BukketSortTest
                 order by A,B,C,D,E) Loop

        for i in MinValue..MaxValue Loop
            BukketArray(i) := 0;
        end Loop;

        BukketArray(rec.a) := BukketArray(rec.a) + 1;
        BukketArray(rec.b) := BukketArray(rec.b) + 1;
        BukketArray(rec.c) := BukketArray(rec.c) + 1;
        BukketArray(rec.d) := BukketArray(rec.d) + 1;
        BukketArray(rec.e) := BukketArray(rec.e) + 1;

        out_rec := BukketDataType(NULL,NULL,NULL,NULL,NULL);

        for i in MinValue..MaxValue Loop
            while BukketArray(i) > 0 Loop
                if out_rec.a is null then
                    out_rec.a := i;
                elsif out_rec.b is null then
                    out_rec.b := i;
                elsif out_rec.c is null then
                    out_rec.c := i;
                elsif out_rec.d is null then
                    out_rec.d := i;
                elsif out_rec.e is null then
                    out_rec.e := i;
                end if;

                BukketArray(i) := BukketArray(i) - 1;
            end loop;
        end Loop;

        pipe row(out_rec);
    end Loop;
end;
/

sho err

select * from table(BukketData);

解説

バケットソートを使って、ソートしてます。

バケットソート - Wikipedia

---