トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-208 旅人算の感覚を使うクエリ(日付型バージョン)
SQLパズル
salesTable
day1 Val
--------- ----
2007/1/1 200
2007/1/2 300
2007/1/5 900
2007/1/6 700
2007/1/7 600
2007/1/9 800
2007/1/10 400
2007/1/12 500
2007/1/20 1200
2007/1/22 1500
2007/1/25 1400
2007/1/26 1300
2007/1/27 1800
日付が連続した期間での、
開始日と終了日と、
開始日のValと、
終了日のVal
を求める。
出力結果
startDay EndDay FirstVal LastVal
--------- --------- -------- -------
2007/1/1 2007/1/2 200 300
2007/1/5 2007/1/7 900 600
2007/1/9 2007/1/10 800 400
2007/1/12 2007/1/12 500 500
2007/1/20 2007/1/20 1200 1200
2007/1/22 2007/1/22 1500 1500
2007/1/25 2007/1/27 1400 1800
データ作成スクリプト
create table salesTable(day1,Val) as
select to_date('2007/1/1' ,'fmyyyy/mm/dd'), 200 from dual union
select to_date('2007/1/2' ,'fmyyyy/mm/dd'), 300 from dual union
select to_date('2007/1/5' ,'fmyyyy/mm/dd'), 900 from dual union
select to_date('2007/1/6' ,'fmyyyy/mm/dd'), 700 from dual union
select to_date('2007/1/7' ,'fmyyyy/mm/dd'), 600 from dual union
select to_date('2007/1/9' ,'fmyyyy/mm/dd'), 800 from dual union
select to_date('2007/1/10','fmyyyy/mm/dd'), 400 from dual union
select to_date('2007/1/12','fmyyyy/mm/dd'), 500 from dual union
select to_date('2007/1/20','fmyyyy/mm/dd'),1200 from dual union
select to_date('2007/1/22','fmyyyy/mm/dd'),1500 from dual union
select to_date('2007/1/25','fmyyyy/mm/dd'),1400 from dual union
select to_date('2007/1/26','fmyyyy/mm/dd'),1300 from dual union
select to_date('2007/1/27','fmyyyy/mm/dd'),1800 from dual;
SQL
--■■■旅人算の感覚を使う方法1■■■
select min(day1) as startDay,
max(day1) as EndDay,
max(Val) Keep (Dense_Rank First order by day1) as FirstVal,
max(Val) Keep (Dense_Rank Last order by day1) as LastVal
from (select day1,Val,
day1-Row_Number() over(order by day1) as groupID
from salesTable)
group by groupID
order by min(day1);
--■■■旅人算の感覚を使う方法2■■■
select distinct
min(day1) over(partition by partID) as startDay,
max(day1) over(partition by partID) as EndDay,
First_Value(Val) over(partition by partID order by day1) as FirstVal,
Last_Value(Val) over(partition by partID order by day1
Rows between Unbounded Preceding and Unbounded Following) as LastVal
from (select day1,Val,
day1-Row_Number() over(order by day1) as partID
from salesTable)
order by startDay;
--■■■旅人算の感覚を使わない方法■■■
select min(day1) as startDay,
max(day1) as EndDay,
max(Val) Keep (Dense_Rank First order by day1) as FirstVal,
max(Val) Keep (Dense_Rank Last order by day1) as LastVal
from (select day1,Val,
sum(willSum) over(order by day1) as groupID
from (select day1,Val,
case when day1 = 1+ Lag(day1) over(order by day1)
then 0 else 1 end as willSum
from salesTable))
group by groupID
order by min(day1);
解説
旅人算の感覚を使うクエリの、
日付型バージョンです。
インラインビューの中の、
SQLのイメージは以下のようになります。
select day1,Row_Number() over(order by day1) as Rn,
day1-Row_Number() over(order by day1) as groupID,Val
from salesTable
order by day1;
day1 Rn groupID Val
---------- -- ---------- ----
2007/01/01 1 2006/12/31 200
2007/01/02 2 2006/12/31 300
2007/01/05 3 2007/01/02 900
2007/01/06 4 2007/01/02 700
2007/01/07 5 2007/01/02 600
2007/01/09 6 2007/01/03 800
2007/01/10 7 2007/01/03 400
2007/01/12 8 2007/01/04 500
2007/01/20 9 2007/01/11 1200
2007/01/22 10 2007/01/12 1500
2007/01/25 11 2007/01/14 1400
2007/01/26 12 2007/01/14 1300
2007/01/27 13 2007/01/14 1800
day1を旅人A、Rnを旅人Bとして
以下の図をイメージすると分かりやすいかもしれません。
旅人Aが、毎回1日以上進む旅人 (単位は、date型)
旅人Bが、毎回1進む旅人 (単位は、数値型)
と考えてます。
date型-数値は、date型ですが
その値は広義の単調増加となります。
そして、同じ値は、同じグループなので、
day1-Row_Number() over(order by day1) as groupID
として、groupIDを求めています。
--------------- 1- 2- 3- 4- 5- 6- 7- 8- 9-10-11-12-13-
2006/12/31 0 | | | | | | | | | | | | | |
2007/01/01 1 |AB| | | | | | | | | | | | |
2007/01/02 2 | |AB| | | | | | | | | | | |
2007/01/03 3 | | | B| | | | | | | | | | |
2007/01/04 4 | | | | B| | | | | | | | | |
2007/01/05 5 | | |A | | B| | | | | | | | |
2007/01/06 6 | | | |A | | B| | | | | | | |
2007/01/07 7 | | | | |A | | B| | | | | | |
2007/01/08 8 | | | | | | | | B| | | | | |
2007/01/09 9 | | | | | |A | | | B| | | | |
2007/01/10 10 | | | | | | |A | | | B| | | |
2007/01/11 11 | | | | | | | | | | | B| | |
2007/01/12 12 | | | | | | | |A | | | | B| |
2007/01/13 13 | | | | | | | | | | | | | B|
2007/01/14 14 | | | | | | | | | | | | | |
2007/01/15 15 | | | | | | | | | | | | | |
2007/01/16 16 | | | | | | | | | | | | | |
2007/01/17 17 | | | | | | | | | | | | | |
2007/01/18 18 | | | | | | | | | | | | | |
2007/01/19 19 | | | | | | | | | | | | | |
2007/01/20 20 | | | | | | | | |A | | | | |
2007/01/21 21 | | | | | | | | | | | | | |
2007/01/22 22 | | | | | | | | | |A | | | |
2007/01/23 23 | | | | | | | | | | | | | |
2007/01/24 24 | | | | | | | | | | | | | |
2007/01/25 25 | | | | | | | | | | |A | | |
2007/01/26 26 | | | | | | | | | | | |A | |
2007/01/27 27 | | | | | | | | | | | | |A |
9-52 最大のリージョンを求める(境界なし)