Oracle如何使用窗口函数实现这样的查询

Question

表记录
======================================
name        date        score
Fei Zhang   1           100
Fei Zhang   2           99
Fei Zhang   3           100
Fei Zhang   4           100
Fei Zhang   5           98
Fei Zhang   6           100
Fei Zhang   7           100
Fei Zhang   8           100
Fei Zhang   9           100
Fei Zhang   10           60现要求统计张飞同学各次测验的得满分的情况，包括持续满分次数和总满分次数，需要得到游标如下
======================================
name       date        score        uninterrupted_100        total_100 
Fei Zhang   1           100         1                        1
Fei Zhang   3           100         1                        2
Fei Zhang   4           100         2                        3
Fei Zhang   6           100         1                        4
Fei Zhang   7           100         2                        5
Fei Zhang   8           100         3                        6
Fei Zhang   9           100         4                        7
帮帮忙，要则们写才行？

Answer 1

select name,date,score,row_number() over(partition by group_flag order by date )  uninterrupted_100,row_number() over(order by date )  total_100 
from(
select *,sum(temp_flag) over(order by date) group_flag
from(
select *,case when date-lag(date,1,0) over(order by date)>1 then 1 else 0 end temp_flag
from table
where score=100))

Answer 2

所有的分析函数里再加个partition by name不就结了

Answer 3

  借鉴别人的:create table b(name varchar(30),
            dt number,
            score number);select name,dt,score,row_number()over(partition by score,rn order by dt) uninterrupted_100,
row_number()over(order by dt) total_100
from(
select b.*,row_number()over(order by dt)-row_number()over(partition by score order by dt) rn from b)
where score=100;

Answer 4

1楼的可行
我也写个
with tt as(select 'feiz' name,1 date1,100 score from dual union all
  select 'feiz' name,2 ,99 score from dual union all
  select 'feiz' name,3 ,100 score from dual union all
  select 'feiz' name,4 ,100 score from dual union all
  select 'feiz' name,5 ,98 score from dual union all
  select 'feiz' name,6 ,100 score from dual union all
  select 'feiz' name,7 ,100 score from dual union all
  select 'feiz' name,8 ,100 score from dual union all
  select 'feiz' name,9 ,100 score from dual union all
  select 'feiz' name,10 ,60 score from dual)
select name,date1,score,
  dense_rank()over(partition by name,date1-rownum order by date1)uninterrupted_100,
  row_number()over(partition by name order by date1)total_100
from tt
where score=100
如果date字段是日期型的，改成下面的比较保险
select name,date,score,
  dense_rank()over(partition by name,date-rn order by date)uninterrupted_100,
  row_number()over(partition by name order by date)total_100
from (
  select tt.*,row_number()over(partition by name order by date)rn from tt
where score=100)

Answer 5

说句题外话,在内存不是问题的情况下,用window函数是个不错的选择,代码结构可以得到简化,性能也能有所提升.
不过如果内存比较吃紧,rollup和window函数是要消耗额外的排序工作区的,不建议使用,毕竟磁盘排序的成本是比较高的.