test

CREATE TABLE TEST 
(
  NO VARCHAR2(20) NOT NULL 
, NAME VARCHAR2(20) 
, DEPT_NO VARCHAR2(20) 
, SAL number ,
job
, CONSTRAINT TEST_PK PRIMARY KEY 
  (
    NO 
  )
  ENABLE 
);
INSERT INTO "DBTRAIN1"."TEST" (NO, NAME, DEPT_NO, SAL) VALUES ('1', '1', '1', '100','a')
INSERT INTO "DBTRAIN1"."TEST" (NO, NAME, DEPT_NO, SAL) VALUES ('2', '2', '1', '200','a')
INSERT INTO "DBTRAIN1"."TEST" (NO, NAME, DEPT_NO, SAL) VALUES ('3', '3', '1', '300','a')
INSERT INTO "DBTRAIN1"."TEST" (NO, NAME, DEPT_NO, SAL) VALUES ('4', '4', '2', '50','b')
INSERT INTO "DBTRAIN1"."TEST" (NO, NAME, DEPT_NO, SAL) VALUES ('5', '5', '2', '100','b')
INSERT INTO "DBTRAIN1"."TEST" (NO, NAME, DEPT_NO, SAL) VALUES ('7', '7', '4', '200','c')
INSERT INTO "DBTRAIN1"."TEST" (NO, NAME, DEPT_NO, SAL) VALUES ('6', '6', '3', '10','c')

select no,name,dept_no,sal,sum(sal)over(partition by dept_no ) a from test; --分区统计
select no,name,dept_no,sal,sum(sal)over(partition by dept_no order by sal) a from test;逐个分区统计
select no ,name ,dept_no,sal,sum(sal)over  (order by no)   from test;-按主鍵累加
select no ,name ,dept_no,sal,sum(sal)over  (order by no  rows between 1 preceding and 1 following )from test;  --前一行后一行加本身一行累加
select no ,name ,dept_no,sal,sum(sal)over  (order by no  range between 1 preceding and 1 following )from test;--值在相同范圍內合計
select no ,sal ,job,rank() over(partition by job order by sal desc ) from test where  job='b';--分区分级排序，可并列,跳序列
select no ,sal ,job,row_number() over(partition by job order by sal desc ) from test where  job='b';--分区分级排序，不并列。
select no, sal ,job , dense_rank()over(partition by job order by sal desc) from test;--分区分级排序，可并列,不跳序列
select no,sal,job  ,first_value(sal) over(partition by job order by sal desc),last_value(sal) over(partition by job order by sal desc) from test;   --范圍內第一條記錄和最后一條
記錄。

with a as 
(select 1 id,'a' name from dual
 union
 select 2 id,'b' name from dual
 union
 select 3 id,'c' name from dual
 union
 select 4 id,'d' name from dual
 union
 select 5 id,'e' name from dual
) 
select id,name,lag(id,1,'')over(order by name ) from a;
--從后面截取N位，空就在前面，




with a as 
(select 1 id,'a' name from dual
 union
 select 2 id,'b' name from dual
 union
 select 3 id,'c' name from dual
 union
 select 4 id,'d' name from dual
 union
 select 5 id,'e' name from dual
) 
select id,name,lead(id,2,'')over(order by name) from a;
--從前面截取N位，空在后面

with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over(partition by a) b from a 
order by a; 
--ratio_to_report(a)函数用法 Ratio_to_report() 括号中就是分子，over() 括号中就是分母

with a as (select 1 a from dual
           union all
select 1 a from dual
           union  all
select 1 a from dual
           union all
select 2 a from dual
           union all 
select 3 a from dual
           union all
select 4 a from dual
           union all
select 4 a from dual
           union all
select 5 a from dual
           )
select a, ratio_to_report(a)over() b from a
group by a order by a;--分组后的占比