トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-245 5分以上未来の最小データで経路作成
SQLパズル
TimeTree
ID timeValue
-- -------------------
1 2008/06/11 05:39:59 ←出力対象
2 2008/06/11 05:40:10
3 2008/06/11 05:46:27 ←出力対象
4 2008/06/11 05:49:41
5 2008/06/11 05:50:41
6 2008/06/11 05:54:30 ←出力対象
7 2008/06/11 05:54:36
8 2008/06/11 05:58:10
9 2008/06/11 06:01:03 ←出力対象
10 2008/06/11 06:04:42
11 2008/06/11 06:11:18 ←出力対象
12 2008/06/11 06:21:55 ←出力対象
13 2008/06/11 06:25:44
14 2008/06/11 06:25:50
15 2008/06/11 06:37:53 ←出力対象
16 2008/06/11 06:50:20 ←出力対象
17 2008/06/11 06:51:42
18 2008/06/11 06:58:12 ←出力対象
19 2008/06/11 07:09:38 ←出力対象
20 2008/06/11 07:09:50
21 2008/06/11 07:27:27 ←出力対象
22 2008/06/11 07:30:28
timeValueが最小のレコードから、
timeValueが5分以上未来の最小データをたどった結果を出力する。
出力結果
ID timeValue
-- -------------------
1 2008/06/11 05:39:59
3 2008/06/11 05:46:27
6 2008/06/11 05:54:30
9 2008/06/11 06:01:03
11 2008/06/11 06:11:18
12 2008/06/11 06:21:55
15 2008/06/11 06:37:53
16 2008/06/11 06:50:20
18 2008/06/11 06:58:12
19 2008/06/11 07:09:38
21 2008/06/11 07:27:27
データ作成スクリプト
create table TimeTree(ID,timeValue) as
select 1,timestamp '2008-06-11 05:39:59' from dual union
select 2,timestamp '2008-06-11 05:40:10' from dual union
select 3,timestamp '2008-06-11 05:46:27' from dual union
select 4,timestamp '2008-06-11 05:49:41' from dual union
select 5,timestamp '2008-06-11 05:50:41' from dual union
select 6,timestamp '2008-06-11 05:54:30' from dual union
select 7,timestamp '2008-06-11 05:54:36' from dual union
select 8,timestamp '2008-06-11 05:58:10' from dual union
select 9,timestamp '2008-06-11 06:01:03' from dual union
select 10,timestamp '2008-06-11 06:04:42' from dual union
select 11,timestamp '2008-06-11 06:11:18' from dual union
select 12,timestamp '2008-06-11 06:21:55' from dual union
select 13,timestamp '2008-06-11 06:25:44' from dual union
select 14,timestamp '2008-06-11 06:25:50' from dual union
select 15,timestamp '2008-06-11 06:37:53' from dual union
select 16,timestamp '2008-06-11 06:50:20' from dual union
select 17,timestamp '2008-06-11 06:51:42' from dual union
select 18,timestamp '2008-06-11 06:58:12' from dual union
select 19,timestamp '2008-06-11 07:09:38' from dual union
select 20,timestamp '2008-06-11 07:09:50' from dual union
select 21,timestamp '2008-06-11 07:27:27' from dual union
select 22,timestamp '2008-06-11 07:30:28' from dual;
SQL
--■■■階層問い合わせを使う方法■■■
with WorkView as (
Select ID,timeValue,RowID as Row_ID,min(timeValue) over() as startTimeValue,
first_value(RowID) over(order by timeValue
range between interval '5' minute following
and unbounded following) as childRowID
from TimeTree)
select ID,to_char(timeValue, 'YYYY/MM/DD HH24:MI:SS') as timeValue
from WorkView
Start With timeValue = startTimeValue
connect by prior childRowID = Row_ID;
--■■■PL/SQLを使う方法■■■
declare
saveVal date;
begin
for rec in (select ID,timeValue from TimeTree order by ID) Loop
if saveVal + interval '5' minute > rec.timeValue then
null;
else
saveVal := rec.timeValue;
DBMS_Output.Put_Line('ID=' || rec.ID || ',Time=' ||
to_char(rec.timeValue,'yyyy/mm/dd hh24:mi:ss'));
end if;
end Loop;
end;
/
解説
分析関数のrange指定の便利な使い方のひとつです。
ソートキーにdate型またはtimestamp型を指定し、rangeで期間リテラルを指定できるのです。
マニュアル(分析関数)より引用
RANGEを指定した場合、次のことがいえます。
value_exprは論理オフセットになります。
これは、正数値または期間リテラルに評価する定数または式である必要があります。
期間リテラルの詳細は、「リテラル」を参照してください。
マニュアル(期間リテラル)より引用
INTERVAL '10' MINUTE
は、10分を示します。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
数値バージョンは、下記となります。
create table treeValue(Val) as
select 10 from dual union
select 14 from dual union
select 15 from dual union
select 30 from dual union
select 36 from dual union
select 40 from dual union
select 45 from dual union
select 50 from dual union
select 51 from dual union
select 52 from dual union
select 53 from dual union
select 54 from dual union
select 55 from dual union
select 61 from dual union
select 65 from dual union
select 66 from dual union
select 67 from dual;
--■■■階層問い合わせを使う方法■■■
select Val
from (select Val,min(Val) over() as StartVal,
min(Val) over(order by Val
range between 5 following
and unbounded following) as childVal
from treeValue)
Start With Val = StartVal
connect by prior childVal = Val;
Val
---
10
15
30
36
45
50
55
61
66
--■■■PL/SQLを使う方法■■■
declare
saveVal pls_Integer;
begin
for rec in (select Val from treeValue order by Val) Loop
if saveVal + 5 > rec.Val then
null;
else
saveVal := rec.Val;
DBMS_Output.Put_Line(to_char(rec.Val));
end if;
end Loop;
end;
/
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
8-15 2日前のデータも出力
range query --- I solved same question