CodeZine(コードジン)

特集ページ一覧

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

CodeZineに掲載されたSQLを分析関数で記述する 6

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2008/10/31 14:00

ダウンロード SourceCode (3.0 KB)

目次

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の次に入社した人はTigerでその入社日は2001-10-12。Tigerの次に入社した人はKimでその入社日は2003-04-01。Kimの次に入社した人はJohnでその入社日は2004-05-30。となります。

出力結果
名前 入社日 次の入社日
Scott 2000-12-23 2001-10-12
Tiger 2001-10-12 2003-04-01
Kim 2003-04-01 2004-05-30
Tom 2003-04-01 2004-05-30
Wendy 2003-04-01 2004-05-30
Joe 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

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

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

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

 次の入社日は、自分より大きい入社日の中で最小の入社日だと考えて、答えは下記となります。

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

 range指定のmin関数を使って、自分より大きい入社日の中で最小の入社日(最小上界)を求めてます。以下のように解釈すると分かりやすいでしょう。

min(入社日) over(  --以下の範囲で最小の入社日を求める。
order by 入社日    --入社日の昇順で、
range between      --行の範囲は、
      1 following  --小さいほうは、1日後から
  and unbounded following) --大きいほうは際限なし

 SQLのイメージは下記となります。

SQLのイメージ
SQLのイメージ

  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5