10-247 所属グループの他行の値を横並びで表示

SQLパズル

student
GID    score
-----  -----
A0000   6712
A0000   9352
A0000   9478
A0001   9477
A0006   2410
A0006   5401
A1111   2345
A1111   6789
A3333   1111
A3333   2222
A3333   3333
A3333   4444
A5555   1111
A5555   2222
A5555   3333
A5555   4444
A5555   5555

GIDごとで、
自分の行のscoreを除いて、scoreを昇順にソートした時のそれぞれの値を求める。

出力結果
GID    score  R1    R2    R3    R4
-----  -----  ----  ----  ----  ----
A0000   6712  9352  9478     0     0
A0000   9352  6712  9478     0     0
A0000   9478  6712  9352     0     0
A0001   9477     0     0     0     0
A0006   2410  5401     0     0     0
A0006   5401  2410     0     0     0
A1111   2345  6789     0     0     0
A1111   6789  2345     0     0     0
A3333   1111  2222  3333  4444     0
A3333   2222  1111  3333  4444     0
A3333   3333  1111  2222  4444     0
A3333   4444  1111  2222  3333     0
A5555   1111  2222  3333  4444  5555
A5555   2222  1111  3333  4444  5555
A5555   3333  1111  2222  4444  5555
A5555   4444  1111  2222  3333  5555
A5555   5555  1111  2222  3333  4444

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

データ作成スクリプト

create table student(
GID   char(5),
score number(5));

insert into student values ('A0000',6712);
insert into student values ('A0000',9352);
insert into student values ('A0000',9478);
insert into student values ('A0001',9477);
insert into student values ('A0006',2410);
insert into student values ('A0006',5401);
insert into student values ('A1111',2345);
insert into student values ('A1111',6789);
insert into student values ('A3333',1111);
insert into student values ('A3333',2222);
insert into student values ('A3333',3333);
insert into student values ('A3333',4444);
insert into student values ('A5555',1111);
insert into student values ('A5555',2222);
insert into student values ('A5555',3333);
insert into student values ('A5555',4444);
insert into student values ('A5555',5555);
commit;

SQL

col GID   for a8
col score for 99999
col R1    for 99999
col R2    for 99999
col R3    for 99999
col R4    for 99999

--■■■decode関数を使う方■■■
select GID,score,
decode(rn,1,N1,2,P1,3,P2,4,P3,5,P4) as R1,
decode(rn,1,N2,2,N1,3,P1,4,P2,5,P3) as R2,
decode(rn,1,N3,2,N2,3,N1,4,P1,5,P2) as R3,
decode(rn,1,N4,2,N3,3,N2,4,N1,5,P1) as R4
from (select GID,score,
      Row_Number()    over(partition by GID order by score) as rn,
      Lag(score,1,0)  over(partition by GID order by score) as P1,
      Lag(score,2,0)  over(partition by GID order by score) as P2,
      Lag(score,3,0)  over(partition by GID order by score) as P3,
      Lag(score,4,0)  over(partition by GID order by score) as P4,
      Lead(score,1,0) over(partition by GID order by score) as N1,
      Lead(score,2,0) over(partition by GID order by score) as N2,
      Lead(score,3,0) over(partition by GID order by score) as N3,
      Lead(score,4,0) over(partition by GID order by score) as N4
        from student)
order by GID,score;

--■■■マトリクス表を使う方■■■
select a.GID,a.score,
case when b.R1 > 0 then Lead(a.score,abs(b.R1),0) over(partition by a.GID order by a.score)
     else Lag(a.score,abs(b.R1),0) over(partition by a.GID order by a.score) end as R1,
case when b.R2 > 0 then Lead(a.score,abs(b.R2),0) over(partition by a.GID order by a.score)
     else Lag(a.score,abs(b.R2),0) over(partition by a.GID order by a.score) end as R2,
case when b.R3 > 0 then Lead(a.score,abs(b.R3),0) over(partition by a.GID order by a.score)
     else Lag(a.score,abs(b.R3),0) over(partition by a.GID order by a.score) end as R3,
case when b.R4 > 0 then Lead(a.score,abs(b.R4),0) over(partition by a.GID order by a.score)
     else Lag(a.score,abs(b.R4),0) over(partition by a.GID order by a.score) end as R4
  from (select score,GID,
        Row_Number() over(partition by GID order by score) as JoinKey
          from student) a Join
       (select 1 as JoinKey,1 as R1,2 as R2,3 as R3,4 as R4 from dual union all
        select 2,-1, 1, 2, 3 from dual union all
        select 3,-2,-1, 1, 2 from dual union all
        select 4,-3,-2,-1, 1 from dual union all
        select 5,-4,-3,-2,-1 from dual) b
 using(JoinKey)
order by a.GID,a.score;

解説

導師の本にのっていたマトリクス表を使う方法を意識してみましたが・・・
この場合は、decode関数を使うほうがシンプルだった・・・
(インラインビューで既にLag関数とLead関数を使っているので、負数指定によるエラーをabs関数で防ぐ必要もないし)

---