create table MakeUniqVal(ID,Val) as
select '01',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '01',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '01',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '01',to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
select '01',to_date('20101012 10:30','yyyymmdd hh24:mi') from dual union all
select '01',to_date('20101012 10:30','yyyymmdd hh24:mi') from dual union all
select '01',to_date('20101012 10:30','yyyymmdd hh24:mi') from dual union all
select '02',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '02',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual;
alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';
--■■■インラインビューを何度も使う方法■■■
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val from(
(select ID,Val+NumToDsInterVal(-1+Row_Number() over(partition by ID,Val order by 1),'Minute') as Val
from MakeUniqVal)
))))))))))));
--■■■model句を使う方法(10g以降)■■■
select ID,Val
from MakeUniqVal
model
partition by(ID)
dimension by(row_number() over(partition by id order by Val) as rn)
measures(Val)
rules(Val[rn > 1] order by rn
= greatest(Val[cv()-1]+interVal '1' minute,
Val[cv()]));
--■■■パイプライン表関数を使う方法■■■
create or replace Package Pack10_320 Is
type ReturnType is record(
ID MakeUniqVal.ID%type,
Val MakeUniqVal.Val%type);
type ReturnTypeSet is table of ReturnType;
end;
/
create or replace function MinuteInc return Pack10_320.ReturnTypeSet PipeLined IS
hasChange boolean;
outR Pack10_320.ReturnType;
cursor cur is select * from MakeUniqVal;
type saveDataDef is table of cur%rowType index by binary_integer;
saveData saveDataDef;
begin
open cur;
fetch cur bulk collect into saveData;
close cur;
for I in 1..saveData.Count Loop
hasChange := false;
Loop
for J in 1..saveData.Count Loop
continue when I = J;
if saveData(I).ID = saveData(J).ID
and saveData(I).Val = saveData(J).Val then
saveData(I).Val := saveData(I).Val+ InterVal '1' minute;
hasChange := true;
end if;
end Loop;
exit when hasChange=false;
hasChange := false;
end Loop;
end Loop;
for I in 1..saveData.Count Loop
outR.ID := saveData(I).ID;
outR.Val := saveData(I).Val;
pipe row(outR);
end Loop;
end;
/
sho err
select ID,Val from table(MinuteInc) order by ID,Val;
--■■■パイプラインでない表関数を使う方法■■■
create or replace type ReturnType as object(
ID number(1),
Val date);
/
create or replace type ReturnTypeSet as table of ReturnType;
/
create or replace function MinuteInc return ReturnTypeSet IS
hasChange boolean;
outR ReturnTypeSet;
begin
select ReturnType(ID,Val) bulk collect into outR from MakeUniqVal;
for I in 1..outR.Count Loop
hasChange := false;
Loop
for J in 1..outR.Count Loop
continue when I = J;
if outR(I).ID = outR(J).ID
and outR(I).Val = outR(J).Val then
outR(I).Val := outR(I).Val+ InterVal '1' minute;
hasChange := true;
end if;
end Loop;
exit when hasChange=false;
hasChange := false;
end Loop;
end Loop;
return outR;
end;
/
sho err
select ID,Val from table(MinuteInc) order by ID,Val;