ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分析函数

分析函数

原创 Linux操作系统 作者:hjianping 时间:2011-04-26 19:40:29 0 删除 编辑

---------------------------------------------------------
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要大写)

联合:union,union all
交  :intersect
差  :minus

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分组

注:select over(partition by col1 order by col2) from test order by ...
          --先partition by col1分组,再分组内order by col2,后在前边分组排序的基础上进行排序

例:select * from (
   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)


例:select a.time_zone,a.dis_no,a.cust_no,a.name,
          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 () 所有员工总和

例:select deptno,ename,sal,sum(sal) over (partition by deptno) dept_tt,sum(sal) over()           all_tt      from emp order by 1

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 ADAMS 1100 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) 按部门“连续”求总和

例:select deptno,ename,sal,sum(sal) over (order by deptno) dept_tt,sum(sal) over() all_tt      from emp order by 1

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 ADAMS 1100 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排序并“连续”求   总和

例:select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_tt,sum(sal)           over() all_tt from emp

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 ADAMS 1100 1900 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 主要的功能是计算一组数值中的排序值

例:select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rr
     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 ADAMS 1100 3
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取基数的分析函数

例:select deptno,ename,sal,
          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
  
例:select deptno,ename,empno,sal,hiredate,
          min(sal) keep(dense_rank first order by hiredate) over(partition by deptno)
          --按部门取出hiredate最早但SAL最小的SAL
     from emp


10.ratio_to_report
  
例:select deptno,ename,sal,100*round(ratio_to_report(sal) over(partition by deptno),3) rr      from emp
          --计算出每个人占部门薪水总和的比例

 

11.lag(col,n,default)、lead(col,n,default) --取前后边N条数据


12.cume_dist函数


13.grouping函数
   grouping sets
例:select deptno,ename,sum(sal)
     from emp
    group by grouping sets(deptno,ename)

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

14.rollup,cube自动汇总函数

例:select deptno,ename,sum(sal)
     from emp
    group by rollup(deptno,ename)

例:select decode(grouping(deptno),1,'所有部门',deptno) deptno,
          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   ADAMS  1100
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
所有部门  部门总计 29025

 

15.first_value()、last_value()

 

16.ntile
把14行数据分为5份
例:select deptno,ename,sal,ntile(5) over(order by sal desc) aa from emp

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
20 ADAMS 1100 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
  )
)
start with rn=1 CONNECT BY ROWNUM<=e_rn;

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
  )
)
start with rn=1 CONNECT BY ROWNUM<=e_rn
);

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-693794/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 按周统计
下一篇: 重复记录
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72860