over partition by

题目一：

现有这么一批数据，现要求出：
每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数

 建表

create table TABLE_0111
(
  NAME  VARCHAR2(20),
  MONTH VARCHAR2(20),
  PV    INTEGER
)

准备数据

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-01', 5);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-01', 15);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('B', '2015-01', 5);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-01', 8);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('B', '2015-01', 25);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-01', 5);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-02', 4);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-02', 6);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('B', '2015-02', 10);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('B', '2015-02', 5);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-03', 16);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('A', '2015-03', 22);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('B', '2015-03', 23);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('B', '2015-03', 10);

insert into TABLE_0111 (NAME, MONTH, PV)
values ('B', '2015-03', 11);

 

第一种思路：通过内连接的方式得出答案

create or replace view tab_01 as
  select name, month, sum(pv) as total
    from TABLE_0111
   group by name, month
   order by name, month;

create or replace view tab_02 as
  select *
    from (select t1.name  as t1name,
                 t1.month as t1month,
                 t1.total as t1total,
                 t2.name  as t2name,
                 t2.month as t2month,
                 t2.total as t2total
            from tab_01 t1
            join tab_01 t2 on t1.name = t2.name)
   where t2month >= t1month;

select * from tab_01;
select * from tab_02;
select t2name, t2month, t2total, max(t1total), sum(t1total)
  from tab_02
 group by t2name, t2month, t2total
 order by t2name, t2month;

 

第二种思路：通过分析函数

select * from TABLE_0111;

select name,
       month,
       total,
       max(total) over(partition by name order by name, month rows between unbounded preceding and current row) as maxpv,
       sum(total) over(partition by name order by name, month rows between unbounded preceding and current row) as sumpv
  from tab_01;