First_ValueとLast_Valueとnth_Value

指定したソートキーでの、最初や最後やn番目の行の値を求める

指定したソートキーでの、最初の行の値を求めるのが、First_Value関数。
指定したソートキーでの、最後の行の値を求めるのが、Last_Value関数。
指定したソートキーでの、(Row_Numberな順位が)n番目の行の値を求めるのが、nth_Value関数となります。
Oracle11gR2でnth_Value関数が追加されました。

create table FirstLastSample(ID,SortKey,Val) as
select 111,1,20 from dual union all
select 111,3,60 from dual union all
select 111,9,40 from dual union all
select 222,2,90 from dual union all
select 222,4,70 from dual union all
select 333,5,80 from dual;

IDごとでSortKeyの昇順で、最初の行のValと最後の行のValを求めてみます。

select ID,SortKey,
First_Value(Val)
over(partition by ID order by SortKey) as FirVal,
Val,
Last_Value(Val)
over(partition by ID order by SortKey
     Rows between Unbounded Preceding
              and Unbounded Following) as LastVal
from FirstLastSample;

出力結果
 ID  SortKey  FirVal  Val  LastVal
---  -------  ------  ---  -------
111        1      20   20       40
111        3      20   60       40
111        9      20   40       40
222        2      90   90       70
222        4      90   70       70
333        5      80   80       80

SQLのイメージは下記です。partition by IDで、IDごとに区切る赤線を引いて、First_Value関数で青線、Last_Value関数で黄緑線を引いてます。

▲ ページTOPに戻る

First_ValueとLast_Valueのignore nulls

ある条件を満たす、最初や最後の行の値を求める

Oracle10gR1から、First_Value関数とLast_Value関数で、ignore nullsを指定できます。
Oracle11gR2からは、Lag関数とLead関数でもignore nullsを指定できます。

Last_Value(値 ignore nulls) over句 が基本的な使い方ですが、Last_Value(case when 条件 then 値 end ignore nulls) over句 というふうに、case式を組み合わせて使うほうが多いです。

create table IgnoreNullsSample1(ID,SortKey,Val) as
select 555,1, 600 from dual union all
select 555,3, 300 from dual union all
select 555,5,null from dual union all
select 555,9,null from dual union all
select 666,2, 400 from dual union all
select 666,3,null from dual union all
select 666,4,null from dual union all
select 666,5, 600 from dual union all
select 777,1,null from dual union all
select 777,3,null from dual union all
select 777,5, 900 from dual union all
select 777,6,null from dual;

IDごとでSortKeyの昇順で、最初のnullでないValと、最後のnullでないValを求めてみます。

select ID,SortKey,
First_Value(Val ignore nulls)
over(partition by ID order by SortKey
     Rows between Unbounded Preceding
              and Unbounded Following) as FirVal,
Val,
Last_Value(Val ignore nulls)
over(partition by ID order by SortKey
     Rows between Unbounded Preceding
              and Unbounded Following) as LastVal
from IgnoreNullsSample1;

出力結果
 ID  SortKey  FirVal   Val  LastVal
---  -------  ------  ----  -------
555        1     600   600      300
555        3     600   300      300
555        5     600  null      300
555        9     600  null      300
666        2     400   400      600
666        3     400  null      600
666        4     400  null      600
666        5     400   600      600
777        1     900  null      900
777        3     900  null      900
777        5     900   900      900
777        6     900  null      900

SQLのイメージは下記です。partition by IDで、IDごとに区切る赤線を引いて、
First_Value関数で青線、Last_Value関数で黄緑線を引いてます。

ignore nullsの、別の使い方として、その行以降で最初のnullでないValや、その行までで最後のnullでないValを求めるといった使い方もあります。

create table IgnoreNullsSample2(SortKey,Val) as
select  1,null from dual union all
select  2, 500 from dual union all
select  3,null from dual union all
select  5,null from dual union all
select  6, 300 from dual union all
select 10,null from dual union all
select 11,null from dual union all
select 12, 700 from dual union all
select 13,null from dual;

select SortKey,
First_Value(Val ignore nulls)
over(order by SortKey
Rows between current row
         and Unbounded Following) as FirVal,
Val,
Last_Value(Val ignore nulls)
over(order by SortKey) as LastVal
  from IgnoreNullsSample2;

出力結果
SortKey  FirVal   Val  LastVal
-------  ------  ----  -------
      1     500  null     null
      2     500   500      500
      3     300  null      500
      5     300  null      500
      6     300   300      300
     10     700  null      300
     11     700  null      300
     12     700   700      700
     13    null  null      700

SQLのイメージは下記です。First_Value関数で青線、Last_Value関数で黄緑線を引いてます。

▲ ページTOPに戻る

Rows 2 Preceding

移動累計を求める (行数を指定)

Rows 2 Precedingといった指定は、移動平均や移動累計を求める時などに使われます。

create table MoveSumSample(SortKey,Val) as
select 1, 10 from dual union all
select 2, 20 from dual union all
select 5, 60 from dual union all
select 7,100 from dual union all
select 8,200 from dual union all
select 9,600 from dual;

SortKeyの昇順での、前の2行と自分の行を加算対象とした移動累計を求めてみます。

select SortKey,Val,
sum(Val) over(order by SortKey Rows 2 Preceding) as moveSum
  from MoveSumSample;

出力結果
SortKey  Val  moveSum
-------  ---  -------
      1   10       10
      2   20       30
      5   60       90
      7  100      180
      8  200      360
      9  600      900

SQLのイメージは下記です。sum(Val) over(order by SortKey Rows 2 Preceding)で黄緑線を引いてます。

▲ ページTOPに戻る

Range 2 Preceding

移動累計を求める (ソートキーの範囲を指定)

Range 2 Precedingといった指定は、移動平均や移動累計を求める時などに、使われます。
Rows 2 Precedingとの違いは、Rowsが行数の指定なのに対して、Rangeはソートキーの範囲の指定という点です。

SortKeyが自分の行より2小さい行から、自分の行までを加算対象とした移動累計を求めてみます。

select SortKey,Val,
sum(Val) over(order by SortKey Range 2 Preceding) as moveSum2
  from MoveSumSample;

出力結果
SortKey  Val  moveSum2
-------  ---  --------
      1   10        10
      2   20        30
      5   60        60
      7  100       160
      8  200       300
      9  600       900

SQLのイメージは下記です。sum(Val) over(order by SortKey Range 2 Preceding)で黄緑線を引いてます。

▲ ページTOPに戻る

• ＜ 前のページへ
• 1
• 2
• 3
• 4
• 5
• 次のページへ ＞