トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-194 日暦算の感覚で、前月の同一日までの日数を求める

SQLパズル

DayTable
Day1      Val
--------  ---
07-01-01    1
07-01-02    2
07-01-03    3
07-01-04    4
07-01-05    5
07-01-06    6
07-01-07    7
07-01-08    8
07-01-09    9
07-01-10   10
07-01-11   11
07-01-12   12
07-01-13   13
07-01-14   14
07-01-15   15
07-01-16   16
07-01-17   17
07-01-18   18
07-01-19   19
07-01-20   20
07-01-21   21
07-01-22   22
07-01-23   23
07-01-24   24
07-01-25   25
07-01-26   26
07-01-27   27
07-01-28   28
07-01-29   29
07-01-30   30
07-01-31   31
07-02-01   32
07-02-02   33
07-02-03   34
07-02-04   35
07-02-05   36
07-02-06   37
07-02-07   38
07-02-08   39
07-02-09   40
07-02-10   41
07-02-11   42
07-02-12   43
07-02-13   44
07-02-14   45
07-02-15   46
07-02-16   47
07-02-17   48
07-02-18   49
07-02-19   50
07-02-20   51
07-02-21   52
07-02-22   53
07-02-23   54
07-02-24   55
07-02-25   56
07-02-26   57
07-02-27   58
07-02-28   59
07-03-01   60
07-03-02   61
07-03-03   62
07-03-04   63
07-03-05   64
以下省略

前月同一日のDay1を持つレコードのValを求める。
前月同一日のDay1を持つレコードが存在しない場合は、0とする。

出力結果
DAY1      VAL  PreMonthVal
--------  ---  -----------
07-01-01    1            0
省略
07-01-28   28            0
07-01-29   29            0
07-01-30   30            0
07-01-31   31            0
07-02-01   32            1
07-02-02   33            2
省略
07-02-26   57           26
07-02-27   58           27
07-02-28   59           28
07-03-01   60           32
07-03-02   61           33
省略
07-03-27   86           58
07-03-28   87           59
07-03-29   88            0
07-03-30   89            0
07-03-31   90            0
07-04-01   91           60
07-04-02   92           61
省略
07-04-28  118           87
07-04-29  119           88
07-04-30  120           89
07-05-01  121           91
07-05-02  122           92
省略


データ作成スクリプト

create table DayTable as
select to_date('2007/01/01','yyyy/mm/dd')+RowNum-1 as Day1,RowNum as Val
  from all_objects
 where RowNum <= 370;


SQL

--■■■Lag関数を使う方法■■■
select Day1,Val,
case when extract(DAY from last_day(add_months(Day1,-1))) < extract(DAY from Day1)
     then 0
     else Lag(Val,extract(DAY from last_day(add_months(Day1,-1))),0)
          over(order by Day1) end as PreMonthVal
  from DayTable
order by Day1;

--■■■rangeを使う方法1■■■
select Day1,Val,
case when extract(DAY from last_day(add_months(Day1,-1))) < extract(DAY from Day1)
     then 0
     else nvl(max(Val)
     over(order by DAY1
          range between extract(DAY from last_day(add_months(Day1,-1))) preceding
                    and extract(DAY from last_day(add_months(Day1,-1))) preceding),0)
     end as PreMonthVal
from DayTable
order by Day1;

--■■■rangeを使う方法2■■■
select Day1,Val,
case when extract(DAY FROM last_day(add_months(Day1,-1))) < extract(DAY from Day1)
     then 0
     else nvl(max(Val)
     over(order by Day1 range between interVal '1' month preceding
                                  and interVal '1' month preceding),0)
     end as PreMonthVal
from DayTable
order by Day1;


解説

開始日を日数に含まないならば
5月10日から 6月10日までの日数は、5月の日数に等しい
6月10日から 7月10日までの日数は、6月の日数に等しい
7月10日から 8月10日までの日数は、7月の日数に等しい
8月10日から 9月10日までの日数は、8月の日数に等しい
9月10日から10月10日までの日数は、9月の日数に等しい
という、日暦算の感覚を使っています。

Lag関数は、日に歯抜けがない場合しか使えないのですが、
rangeは、日に歯抜けがあってもなくても使えます。

10-185 範囲内での行間アクセス(歯抜けを考慮しない)
10-186 範囲内での行間アクセス(歯抜けを考慮する)

*****************************************************************************************
range '1' month precedingは、末日を超えていたら末日に変更することを使ってもいいです。

10-306 range '1' month preceding

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

なお、
Lag関数とLead関数の、第2引数と第3引数
と
following指定とpreceding指定
は、分析関数で各行の値が使える箇所だったりします。

--■■■Lag関数とLead関数の、第2引数と第3引数■■■
select ID,Seek,
Lag(ID,Seek,spare)  over(order by ID) as LagID,
Lead(ID,Seek,spare) over(order by ID) as LeadID
from (select 1 as ID,1 as Seek,99 as spare from dual
union select 2,1,88 from dual
union select 3,2,77 from dual
union select 4,1,66 from dual
union select 5,3,55 from dual
union select 6,1,44 from dual
union select 7,2,33 from dual)
order by ID;

ID  Seek  LagID  LeadID
--  ----  -----  ------
 1     1     99       2
 2     1      1       3
 3     2      1       5
 4     1      3       5
 5     3      2      55
 6     1      5       7
 7     2      5      33

--■■■following指定とpreceding指定■■■
select ID,Seek,
max(ID) over(order by ID
              rows between current row
                       and Seek following) as MaxID
from (select 1 as ID,1 as Seek from dual
union select 2,1 from dual
union select 3,2 from dual
union select 4,1 from dual
union select 5,3 from dual
union select 6,1 from dual
union select 7,2 from dual)
order by ID;

ID  Seek  MaxID
--  ----  -----
 1     1      2
 2     1      3
 3     2      5
 4     1      5
 5     3      7
 6     1      7
 7     2      7