PostgreSQLの分析関数の衝撃6
（window関数の応用例）

複数列のdistinctなcountなど

3. 次の入社日を求める

　続きまして、次の入社日を求めるSQLです。まずは、テーブルのデータと出力結果を考えます。

入社

名前	入社日
Scott	2000-12-23
Tiger	2001-10-12
Kim	2003-04-01
Tom	2003-04-01
Wendy	2003-04-01
Joe	2003-04-01
John	2004-05-30
Hideyoshi	2004-07-30
Ieyasu	2004-07-30
Nobunaga	2004-07-30
Mithuhide	2005-12-30

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

出力結果

名前	入社日	次の入社日
Scott	2000-12-23	2001-10-12
Tiger	2001-10-12	2003-04-01
Joe	2003-04-01	2004-05-30
Kim	2003-04-01	2004-05-30
Tom	2003-04-01	2004-05-30
Wendy	2003-04-01	2004-05-30
John	2004-05-30	2004-07-30
Hideyoshi	2004-07-30	2005-12-30
Ieyasu	2004-07-30	2005-12-30
Nobunaga	2004-07-30	2005-12-30
Mithuhide	2005-12-30	null

　この問題のポイントは、どのようにしてwindow関数で行間アクセスを行うかです。もし、入社日に重複がないなら、単純に下記のようにLead関数を使えばいいです。

入社日に重複がないならこれで可

select 名前,入社日,
Lead(入社日) over(order by 入社日) as 次の入社日
  from 入社
order by 入社日,名前;

間違った出力結果

名前	入社日	次の入社日
Scott	2000-12-23	2001-10-12
Tiger	2001-10-12	2003-04-01
Joe	2003-04-01	2004-05-30
Kim	2003-04-01	2003-04-01
Tom	2003-04-01	2003-04-01
Wendy	2003-04-01	2003-04-01
John	2004-05-30	2004-07-30
Hideyoshi	2004-07-30	2004-07-30
Ieyasu	2004-07-30	2004-07-30
Nobunaga	2004-07-30	2005-12-30
Mithuhide	2005-12-30	null

　上記の結果から分かりますがLead関数を使っても、入社日に重複があるので正しい結果を得ることはできません。Lead関数とLag関数は、ソートキーによって行が一意にならない場合は、実質、使い道がないのです。

　答えは、ソートキーによって行を一意にしつつ、Lead関数の第2引数に、同一入社日の中での名前の逆順位を指定すればいいと考えて、下記となります。

逆順位を使う方法

select 名前,入社日,
Lead(入社日,RevRank::integer) over(order by 入社日,名前) as 次の入社日
from (select 名前,入社日,
      Row_Number() over(partition by 入社日 order by 名前 desc) as RevRank
        from 入社) a
order by 入社日,名前;

　SQLのイメージは下記となります。Row_Number() over(partition by hireDate order by ename desc)に対応する赤線と黄緑線と、Lead関数に対応する青線を引いてます。

SQLのイメージ

　なお、『分析関数の衝撃6 （応用編）』では、range指定のmin関数を使った下記のSQLを使いましたが、PostgreSQL 8.4では文法エラーになるので使えません。

PostgreSQL 8.4では文法エラー

select 名前,入社日,
min(入社日) over(
order by 入社日
range between 1 following
          and unbounded following) as 次の入社日
  from 入社
order by 入社日,名前;