# 分析函数

---------------------------------------------------------
select /*+ first_rows */ * from
(
select a.*, rownum rn
from (select * from table_name) a
where rownum <= 40
)
where rn >= 21
---------------------------------------------------------
select /*+ first_rows */ * from
(
select scott.t.*,rownum rum
from scott.t
)
where rum >= 21 and rum <= 40
---------------------------------------------------------
select * from
(select emp.*,rownum rno from emp order by empno asc)
where rno>5 and rno<10
---------------------------------------------------------

select * from tab;
select * from tab where tabtype='TABLE';(TABLE要大写)

select * from dba_tables where wner='SCOTT';(SCOTT要大写)

break on id skip 1
1. row_number() or rank() 新建一列，用以存放按b.dept_no,a.art_grp_no分组后，
再按sum(c.corr_qty*c.sell_pr)排序的序号，
2. over()
3. partition by 按b.dept_no,a.art_grp_no分组

--先partition by col1分组，再分组内order by col2，后在前边分组排序的基础上进行排序

select b.dept_no,
a.art_grp_no,
a.art_no,
a.descr,
sum(c.corr_qty) tot_qty,
sum(c.corr_qty*c.sell_pr) tot_amount,
row_number()over(partition by b.dept_no,a.art_grp_no                                                          order by sum(c.corr_qty*c.sell_pr))  rum
from  article a, art_grp b, stock_corr_hist c
where a.art_grp_no=b.art_grp_no
and a.art_no=c.art_no
and b.dept_no in (14,15,16,17)
and c.corr_cd in (2)
and c.datetime between '20080201' and '20080229'
group by b.dept_no,a.art_grp_no,a.art_no,a.descr
order by b.dept_no,a.art_grp_no )
WHERE rum <11

select * from (
select b.dept_no,
a.art_grp_no,
a.art_no,
a.descr,
sum(c.move_qty) tot_qty,
sum(c.sales_amnt) tot_amount,
row_number()over(partition by b.dept_no,a.art_grp_no
order by sum(c.sales_amnt) desc) rum
from  article a, art_grp b, art_movement c
where a.art_grp_no=b.art_grp_no
and a.art_no=c.art_no
and b.dept_no in (14,15,16,17)
and c.move_kind in (7)
group by b.dept_no,a.art_grp_no,a.art_no,a.descr
order by b.dept_no,a.art_grp_no )
WHERE rum <11

select * from (
select b.dept_no,
a.art_grp_no,
a.art_no,
a.descr,
sum(c.move_qty) tot_qty,
sum(c.sales_amnt) tot_amount,
row_number()over(partition by b.dept_no order by sum(c.sales_amnt) desc) rum
from  article a, art_grp b, art_movement c
where a.art_grp_no=b.art_grp_no
and a.art_no=c.art_no
and c.move_kind in (7)
and c.move_date between '20090801' and '20090831'
group by b.dept_no,a.art_grp_no,a.art_no,a.descr
order by b.dept_no)
WHERE rum <6

DEPT_NO   ART_GRP_NO ART_NO  DESCR     TOT_QTY  TOT_AMOUNT RUM
14   171         83771  牛肉糜     -150.525  -4772.4785 1
.
.
SQL> select * from test;

A
----------
1
2
1

SQL> select a,rank() over(order by a) from test;

A RANK()OVER(ORDERBYA)
---------- --------------------
1                    1
1                    1
2                    3

SQL> select a,row_number() over(order by a) from test;

A ROW_NUMBER()OVER(ORDERBYA)
---------- --------------------------
1                          1
1                          2
2                          3

4. Decode函数的语法结构如下(横向显示列):
decode (expression, search_1, result_1)
decode (expression, search_1, result_1, search_2, result_2)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, result_2, default)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n,            default)

max(decode(b.cardholder_no,1,b.cardholder_name))n1,
max(decode(b.cardholder_no,2,b.cardholder_name))n2,
max(decode(b.cardholder_no,3,b.cardholder_name))n3,
max(decode(b.cardholder_no,4,b.cardholder_name))n4,
max(decode(b.cardholder_no,5,b.cardholder_name))n5
from cust a,cardholder b
where a.cust_no=b.cust_no
and a.reg_date like sysdate-1
group by a.time_zone,a.dis_no,a.cust_no,a.name

5. sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over () 所有员工总和

DEPTNO ENAME SAL DEPT_TT ALL_TT
10 CLARK 2450 8750 29025
10 KING 5000 8750 29025
10 MILLER 1300 8750 29025
20 JONES 2975 10875 29025
20 FORD 3000 10875 29025
20 SMITH 800 10875 29025
20 SCOTT 3000 10875 29025
30 WARD 1250 9400 29025
30 TURNER 1500 9400 29025
30 ALLEN 1600 9400 29025
30 JAMES 950 9400 29025
30 BLAKE 2850 9400 29025
30 MARTIN 1250 9400 29025

6. sum(sal) over (order by deptno) 按部门“连续”求总和

DEPTNO ENAME SAL DEPT_TT ALL_TT
10 CLARK 2450 8750 29025
10 KING 5000 8750 29025
10 MILLER 1300 8750 29025
20 JONES 2975 19625 29025
20 FORD 3000 19625 29025
20 SMITH 800 19625 29025
20 SCOTT 3000 19625 29025
30 WARD 1250 29025 29025
30 TURNER 1500 29025 29025
30 ALLEN 1600 29025 29025
30 JAMES 950 29025 29025
30 BLAKE 2850 29025 29025
30 MARTIN 1250 29025 29025

7. sum(sal) over (partition by deptno order by sal)
按部门求总和,部门内按SAL排序并“连续”求   总和

DEPTNO ENAME SAL DEPT_TT ALL_TT
10 MILLER 1300 1300 29025
10 CLARK 2450 3750 29025
10 KING 5000 8750 29025
20 SMITH 800 800 29025
20 JONES 2975 4875 29025
20 SCOTT 3000 10875 29025
20 FORD 3000 10875 29025
30 JAMES 950 950 29025
30 MARTIN 1250 3450 29025
30 WARD 1250 3450 29025
30 TURNER 1500 4950 29025
30 ALLEN 1600 6550 29025
30 BLAKE 2850 9400 29025

8. rank,dense_rank函数
聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值

from emp

DEPTNO ENAME SAL RR
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 2
20 SMITH 800 4
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
30 MARTIN 1250 4
30 WARD 1250 4
30 JAMES 950 5

9. keep
first,last取基数的分析函数

min(sal) keep (dense_rank first order by sal) over (partition by deptno),
--按部门取出SAL最小的SAL
max(sal) keep (dense_rank last order by sal) over (partition by deptno)
--按部门取出SAL最大的SAL
from emp

min(sal) keep(dense_rank first order by hiredate) over(partition by deptno)
--按部门取出hiredate最早但SAL最小的SAL
from emp

10.ratio_to_report

--计算出每个人占部门薪水总和的比例

12.cume_dist函数

13.grouping函数
grouping sets

from emp
group by grouping sets(deptno,ename)

from emp group by deptno
union all
select null,ename,sum(sal)
from emp group by ename

14.rollup,cube自动汇总函数

from emp
group by rollup(deptno,ename)

decode(grouping(ename),1,'部门总计',ename) ename,sum(sal)
from emp
group by rollup(deptno,ename)

DEPTNO   ENAME  SUM(SAL)
10   KING  5000
10   CLARK  2450
10   MILLER 1300
10   部门总计 8750
20   FORD  3000
20   JONES  2975
20   SCOTT  3000
20   SMITH  800
20   部门总计 10875
30   WARD  1250
30   ALLEN  1600
30   BLAKE  2850
30   JAMES  950
30   MARTIN 1250
30   TURNER 1500
30   部门总计 9400

15.first_value()、last_value()

16.ntile

DEPTNO ENAME SAL AA
10 KING 5000 1
20 FORD 3000 1
20 SCOTT 3000 1
20 JONES 2975 2
30 BLAKE 2850 2
10 CLARK 2450 2
30 ALLEN 1600 3
30 TURNER 1500 3
10 MILLER 1300 3
30 WARD 1250 4
30 MARTIN 1250 4
30 JAMES 950 5
20 SMITH 800 5

select c.art_no,c.t0t1,d.t0t2 from
(select a.art_no,sum(a.move_qty) t0t1
from art_movement a
where a.move_date ='20080410'
group by a.art_no) c,
(select b.art_no,sum(b.move_qty) t0t2
from art_movement b
where b.move_date ='20080412'
group by b.art_no) d
where c.art_no=d.art_no

000.行列拆分问题,插入多条记录
A.
create table test(s_id varchar2(6),e_id varchar2(6),je number(6,2));

B.
insert into test values('890001','890009',20);
insert into test values('891001','891007',30);
insert into test values('892001','892022',50);
insert into test values('893001','893008',60);
insert into test values('894001','894008',40);

C.
select * from test;

S_ID E_ID JE
890001 890009 20
891001 891007 30
892001 892022 50
893001 893008 60
894001 894008 40

D.
SELECT S_ID+ROWNUM-weight,JE FROM
(
select S_ID,RN,E_RN,JE,lag(E_RN,1,0) over(order by rownum)+1 weight from
(
SELECT S_ID,rownum rn,sum(E_ID-S_ID+1) over(order by rownum) E_RN,JE FROM TEST
)
)

S_ID+ROWNUM-WEIGHT JE
890001 20
890002 20
890003 20
890004 20
890005 20
890006 20
890007 20
890008 20
890009 20
891001 30
891002 30
891003 30
891004 30
891005 30
891006 30
891007 30
892001 50
892002 50
892003 50
892004 50
892005 50
892006 50
892007 50
892008 50
892009 50
892010 50
892011 50
892012 50
892013 50
892014 50
892015 50
892016 50
892017 50
892018 50
892019 50
892020 50
892021 50
892022 50
893001 60
893002 60
893003 60
893004 60
893005 60
893006 60
893007 60
893008 60
894001 40
894002 40
894003 40
894004 40
894005 40
894006 40
894007 40
894008 40

E.
create table temp(t_no varchar2(6),je number(6,2));

F.
insert into temp
select * from
(
SELECT S_ID+ROWNUM-weight,JE FROM
(
select S_ID,RN,E_RN,JE,lag(E_RN,1,0) over(order by rownum)+1 weight from
(
SELECT S_ID,rownum rn,sum(E_ID-S_ID+1) over(order by rownum) E_RN,JE FROM TEST
)
)
);

• 博文量
80
• 访问量
72860