本文共 3168 字，大约阅读时间需要 10 分钟。

Oracle OVER??????

?Oracle?????OVER?????????????????????????????????????Partition By??????????Order By????????????????OVER???????????????????????????????

1. OVER???????

OVER??????????

SELECT column_name,        sum(column_name) OVER(partition_by_clause order_by_clause) FROM table_name;

??

SELECT name, class, s,        sum(s) OVER(partition_by class order_by s desc) mm FROM t2WHERE mm = 1;

??

2. Partition By?Order By???

OVER???????Partition By?Order By????Partition By???????????????Order By????????????????

??1?Partition By deptno Order By salary

SELECT deptno, ename, sal,        rank() OVER(partition_by deptno order_by sal) r,       (SELECT COUNT(1) n FROM emp GROUP BY deptno) bFROM empWHERE deptno IN (30, 60);

??2?Partition By class Order By s desc

SELECT class, s,        rank() OVER(partition_by class order_by s desc) mm FROM t2WHERE mm = 1;

3. Range?Rows???

OVER????Range?Rows???????????Range?????????Rows????N??

???Range between 5 preceding and 5 following

SELECT name, class, s,        sum(s) OVER(order_by s range between 5 preceding and 5 following) mm FROM t2WHERE mm = 1;

???Rows between 2 preceding and 2 following

SELECT name, class, s,        sum(s) OVER(order_by s rows between 2 preceding and 2 following) mm FROM t2WHERE mm = 1;

4. Unbounded Preceding and Following

?Range?Rows???unbounded????????????????

???Unbounded preceding and following

SELECT opr_id, res_type,        first_value(res_type) OVER(partition_by opr_id order_by res_type) low,       last_value(res_type) OVER(partition_by opr_id order_by res_type rows BETWEEN unbounded preceding AND unbounded following) highFROM rm_circuit_routeWHERE opr_id IN ('000100190000000000021311', '000100190000000000021355', '000100190000000000021339')ORDER BY opr_id;

5. rank()?dense_rank()?percent_rank()

rank()

rank()????????????????????????

dense_rank()

dense_rank()???rank()????????????????

percent_rank()

percent_rank()????????????????????

???percent_rank

SELECT deptno, ename, sal,        (a.r-1)/(n-1) pr1,       percent_rank() OVER(partition_by deptno order_by sal) pr2FROM (    SELECT deptno, ename, sal,            rank() OVER(partition_by deptno order_by sal) r    FROM emp    WHERE deptno IN (30, 60)) a,(SELECT deptno, COUNT(1) n FROM emp GROUP BY deptno) bWHERE a.deptno = b.deptno;

6. cume_dist()?percentile_cont()

cume_dist()

cume_dist()?????????????CDF???????????????????

percentile_cont()

percentile_cont()??????????????????

???percentile_cont(0.7)

SELECT ename, sal, deptno,        percentile_cont(0.7) within group(order_by sal) over(partition_by deptno) "Percentile_Cont",       percent_rank() OVER(partition_by deptno order_by sal) "Percent_Rank"FROM empWHERE deptno IN (30, 60);

7. PERCENTILE_DISC()

PERCENTILE_DISC()????????????????

???

SELECT ename, sal, deptno,        percentile_disc(0.7) within group(order_by sal) over(partition_by deptno) "Percentile_Disc",       cume_dist() over(partition_by deptno order_by sal) "Cume_Dist"FROM empWHERE deptno IN (30, 60);

???????????Oracle?OVER??????????????????????????????????????????????????