8-7 次の入社日を求める

SQLパズル

入社テーブル
名前       入社日
---------  --------
Scott      20001223
Tiger      20011012
Kim        20030401
Tom        20030401
Wendy      20030401
Joe        20030401
John       20040530
Hideyoshi  20040730
Ieyasu     20040730
Nobunaga   20040730
Mithuhide  20051230

それぞれの、
次に入社した人の、入社日を求める。

出力結果
名前       入社日    次の入社日
---------  --------  ----------
Scott      20001223  20011012
Tiger      20011012  20030401
Kim        20030401  20040530
Tom        20030401  20040530
Wendy      20030401  20040530
Joe        20030401  20040530
John       20040530  20040730
Hideyoshi  20040730  20051230
Ieyasu     20040730  20051230
Nobunaga   20040730  20051230
Mithuhide  20051230  null

SQLクックブックのレシピ8.7を参考にさせていただきました

データ作成スクリプト

create table 入社 as
select 'Scott' as 名前,20001223 as 入社日 from dual
union select 'Tiger',20011012 from dual
union select 'Kim',20030401 from dual
union select 'Tom',20030401 from dual
union select 'Wendy',20030401 from dual
union select 'Joe',20030401 from dual
union select 'John',20040530 from dual
union select 'Hideyoshi',20040730 from dual
union select 'Ieyasu',20040730 from dual
union select 'Nobunaga',20040730 from dual
union select 'Mithuhide',20051230 from dual;

SQL

--■■■分析関数を使う方法1■■■
select 名前,入社日,
Lead(入社日,RevRank) over(order by 入社日,名前) as 次の入社日
from (select 名前,入社日,
      Row_Number() over(partition by 入社日 order by 名前 desc) as RevRank
      from 入社);

--■■■分析関数を使う方法2■■■
select 名前,入社日,
max(入社日) over(order by Rank
                    range between 1 following
                              and 1 following) as 次の入社日
from (select 名前,入社日,
      Dense_Rank() over(order by 入社日) as Rank
        from 入社)
order by 入社日,名前;

--■■■分析関数を使う方法3■■■
select 名前,入社日,
min(入社日) over(order by 入社日
                 range between 1 following
                           and unbounded following) as 次の入社日
  from 入社
order by 入社日,名前;

--■■■相関サブクエリを使う方法■■■
select 名前,入社日,
(select min(b.入社日)
   from 入社 b
  where b.入社日 > a.入社日) as 次の入社日
from 入社 a
order by 入社日,名前;

解説

分析関数を使う方法1では、逆ソートの考え方を使ってます。

SQLクックブックでは、Lead関数の引数に
件数 - 正順位 + 1
を指定してますが、

正順位 + 逆順位 = 件数 + 1
を移項すると
逆順位 = 件数 - 正順位 + 1
なので、同じ意味となります。

2-3-6 メジアン(中央値)を取得
2-3-24 逆ソート(リバースソート)
2-3-26 正順位と逆順位

分析関数を使う方法2のように、
Dense_Rank関数とrangeを組み合わせてもいいです。

分析関数を使う方法3であれば、
インラインビューは不要となります。

分析関数の衝撃3 (後編)
分析関数の衝撃6 (応用編)

---