lead lag rank（）over（）dense_rank()row_number()over（）rollupcube幾大分析函式

 

----幾大分析的函式

分析分析函式之前先來介紹一下開窗子句，開窗子句分為兩種：range以及row即是範圍開窗以及行開窗兩種

range表示以偏移量來開窗,與當前行的偏移量,只能用number和date型別，而且只能有一列   

rows表示以行來開窗,什麼型別都行,可以多列

第一行是 unbounded preceding,

當前行是 current row,

最後一行是 unbounded following.

range between unbounded preceding and current row         ----從開始的到當前行，如果sum則累加

range between unbounded preceding and unbounded following ----所有資料，如果sum則只有一個總和

range between 500 preceding and 500 following             ----與當前行數值偏移量為+-500的所有值

rows between 1 preceding and  current row                 ----前一行到當前行

rows between 1 preceding and  5 following                 ----前一行到當前行後面的五行

rows between unbounded preceding and  5 following         ----第一行到後五行

order by 開窗列時，碰到連續相同數值的行的累加當一行處理

----------------------------

select deptno,ename,sal,

  first_value(sal) over(partition by deptno) deptsal,

  last_value(sal) over(partition by deptno) deptsal2

from emp;

select sal,ratio_to_report(sal) over() from emp;

--------------------求第二大值

create table t (x int);

insert into t values(8);

insert into t values(10);

insert into t values(10);

insert into t values(11);

insert into t values(7);

insert into t values(5);

commit;

----一

select x,

       max(x) over(partition by null order by x range between 2 preceding and current row) deptsal,

       x,

       min(x) over(partition by null order by x range between 2 preceding and 2 following) deptsa2

  from t;

---二 

  select x, max(x) over() deptsal, x, min(x) over() deptsa2 from t;

1.max()預設是所有行最大值,min()預設是所有行最小值，可以結合開窗子句

2.lead,lag

lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)

select x,

       lead(x,1) over(partition by null order by x ) deptsal,

       x,

       min(x) over(partition by null order by x range between 2 preceding and 2 following) deptsa2

  from t;

 

  select x,

       lag(x,1) over(partition by null order by x ) deptsal,

       x,

       min(x) over(partition by null order by x range between 2 preceding and 2 following) deptsa2

  from t;

 

3.rank()over()

 select x,

       rank() over(partition by null order by x ) deptsal,

       x,

       min(x) over(partition by null order by x range between 2 preceding and 2 following) deptsa2

  from t;

 select x,

      dense_rank() over(partition by null order by x ) deptsal,

       x,

       min(x) over(partition by null order by x range between 2 preceding and 2 following) deptsa2

  from t;

4.row()number() 

 select x,

      dense_rank() over(partition by null order by x ) deptsal,

       x,

       min(x) over(partition by null order by x range between 2 preceding and 2 following) deptsa2

  from t;

select deptno, ename, sal, row_number() over(order by null) rn from emp;

select deptno,ename,sal,row_number() over(order by dbms_random.value) rn  ----dbms_random.value()也可以

from emp;

5.加強型分析函式

rollup(deptno,job) = (deptno,job)(deptno)()

cube(deptno,job) = (deptno,job)(deptno)(job)()

---------------------------加強型函式

group by rollup(a,b,c)

轉化為

group by (a,b,c)

union all

group by (a,b)

union all

group by (a)

union all

group by()

-------------------ege:

select deptno,sum(sal) from emp group by rollup(deptno,sal);-----最後一行彙總

------------------------

select deptno,sum(sal) from emp group by deptno,sal

union all

select deptno,sum(sal) from emp group by deptno

union all

select null,sum(sal)  from emp

order by deptno;

---------------------------

select deptno,job,grouping(deptno),grouping(job),sum(sal) from emp group by rollup(deptno,job);----以0和1區分彙總資料

select deptno,job,sum(sal) from emp group by cube(deptno,job);--- cube(a,b)= (a,b) (a) (b) ()

select deptno,sum(sal) from emp group by rollup(deptno);

select deptno,job,sum(sal) from emp group by rollup(deptno,job);--rollup(a,b) = (a,b)(a)()