トップページに戻る
次の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