create table gradeTable(name,mydate,grade) as
select 'Bill',to_date('2008-01-28','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2007-12-03','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2007-10-08','yyyy-mm-dd'),'B' from dual union
select 'Bill',to_date('2007-08-13','yyyy-mm-dd'),'B' from dual union
select 'Bill',to_date('2007-06-18','yyyy-mm-dd'),'B' from dual union
select 'Bill',to_date('2007-04-23','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2007-02-26','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2007-01-01','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-11-06','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-09-11','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2006-07-17','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2006-05-22','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-03-27','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-01-30','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2008-02-08','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2007-12-28','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2007-11-16','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2007-10-05','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2007-08-24','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-07-13','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-06-01','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-04-20','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-03-09','yyyy-mm-dd'),'A' from dual union
select 'Jane',to_date('2007-01-26','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-12-15','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2006-11-03','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2006-09-22','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-08-11','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-06-30','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-05-19','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-04-07','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2006-02-24','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2006-01-13','yyyy-mm-dd'),'C' from dual union
select 'Mike',to_date('1999-11-22','yyyy-mm-dd'),'F' from dual union
select 'Mike',to_date('1999-10-20','yyyy-mm-dd'),'F' from dual union
select 'Mike',to_date('1999-09-18','yyyy-mm-dd'),'F' from dual union
select 'Tom' ,to_date('1970-05-18','yyyy-mm-dd'),'F' from dual union
select 'Tom' ,to_date('1970-04-18','yyyy-mm-dd'),'A' from dual union
select 'Tom' ,to_date('1970-03-18','yyyy-mm-dd'),'A' from dual;
--■■■分析関数を使う方法1(10g以降)■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
First_Value(case when grade = 'F' then mydate end ignore nulls)
over(partition by name order by mydate desc) as targetDate,
Row_number() over(partition by name order by mydate desc)
- Row_number() over(partition by name,grade order by mydate desc) as distance
from gradeTable)
where grade = 'F'
group by name,distance
having max(case when mydate = targetDate then 1 else 0 end) = 1
order by name;
--■■■分析関数を使う方法2■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
max(decode(grade,'F',mydate)) over(partition by name) as targetDate,
Row_number() over(partition by name order by mydate desc)
- Row_number() over(partition by name,grade order by mydate desc) as distance
from gradeTable)
where grade = 'F'
group by name,distance
having max(case when mydate = targetDate then 1 else 0 end) = 1
order by name;
--■■■分析関数を使う方法3■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
max(mydate) over(partition by name,grade) as targetDate,
Row_number() over(partition by name order by mydate desc)
- Row_number() over(partition by name,grade order by mydate desc) as distance
from gradeTable)
where grade = 'F'
group by name,distance
having max(case when mydate = targetDate then 1 else 0 end) = 1
order by name;
--■■■分析関数を使う方法4■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
max(mydate) over(partition by name,grade) as targetDate,
Row_number() over(partition by name order by mydate desc)
- Row_number() over(partition by name,grade order by mydate desc) as distance
from gradeTable)
where grade = 'F'
group by name,distance
having max(mydate) = max(targetDate)
order by name;