聚合函数,分析函数

null

表示一种不确定的状态或值
任何值与其做加减乘除等操作，结果均为NULL,字符串连接不受影响
判断：IS[ NOT NULL
X IS NULL,返回TRUE,说明X是NUL,返回 FALSE,说明X不是NUL
X IS NOT NULL,反之
只有 DECODE函数可以直接对NUL做等值判断
decode(x,1,'one,2,'two,null"NUL,3, three,4,four,数不过来了)

 

SQL分析函数功能一排名
几种不同的排名需求
排名无并列，且每个排名与紧接着的下一个排名都是连续的
排名有并列，且并列的排名与紧接着的下一个排名不连续
排名有并列，且并列的排名与紧接着的下一个排名连续

with t as(select rownum*10 cnt from dual connect by rownum<5 union all select rownum*40-10 from dual connect by rownum<3)
select cnt,row_number()over(order by cnt) rn,rank()over(order by cnt) rk,dense_rank()over(order by cnt)drk from 

 

相邻类分析函数
LAG和LEAD用于获取相邻行的数据，以便于进行相关计算
LAG是取到排序后当前记录之前的记录
LEAD是取到排序后当前记录之后的记录

相邻类分析函数-LEAD和LAG
查出同部门按字母正序姓名比自己大和小的雇员姓名各是啥？没有比自己姓名小的设为A
,没有比自己姓名大的设为ZZZ。

select deptno,
ename,
lag(ename, 1, 'AAA') over(partition by deptno order by ename) lower_name,
lead(ename, 1, 'ZZZ') over(partition by deptno order by ename) higher_name
from emp;

相邻类分析函数的要点
LAG/LEAD(vn,dV)里的n表示位移，必须是0或正整数，dv是在没有取到对应值时的默认值
n默认是1,dv默认是null
相邻类分析函数后面 order by子句是必须的
partition by和 order by后均可跟多列

相关统计分析函数-SUM
求出每个部]按月的累计销售额

with t as
 (select dept_id,
         to_char(sale_date, 'yyyy-mm') sale_month,
         sum(sale_cnt) month_sale_cnt
    from lw_sales
   group by dept_id, to_char(sale_date, 'yyyy-mm'))
select dept_id,
       sale_month,
       month_sale_cnt,
       sum(month_sale_cnt) over(partition by dept_id order by sale_month) cum_month_sale_cnt
  from t;

相关统计分析函数-SUM
求出每个部门按售出货物类别的累计销售额（按货物类別代码正序排列）以及每个货物按部门
的累计销售额（按部门编号正序排列）

with t as
 (select dept_id, good, sum(sale_cnt) goods_sale_cnt
    from lw_sales
   group by dept_id, good)
select dept_id,
       good,
       goods_sale_cnt,
       sum(goods_sale_cnt) over(partition by dept_id order by good) cum_gsc_goods,
       sum(goods_sale_cnt) over(partition by good order by dept_id) cum_gsc_dept
  from t;

相关统计分析函数-AVG
求出每个部]每种货物的销售额与该货物在公司各部门平均销售额之间的差值

with t as
 (select dept_id, good, sum(sale_cnt) goods_sale_cnt, avg(sale_cnt) s
    from lw_sales
   group by dept_id, good)
select dept_id,
       good,
       goods_sale_cnt,
       s,
       round(avg(goods_sale_cnt) over(partition by good), 2) avg_goods_sale_cnt,
       goods_sale_cnt -
       round(avg(goods_sale_cnt) over(partition by good), 2) dv_goods_sale_cnt
  from t;

传统写法

with t as
 (select dept_id, good, sum(sale_cnt) goods_sale_cnt, avg(sale_cnt) s
    from lw_sales
   group by dept_id, good),
t1 as
 (select good, round(avg(goods_sale_cnt), 2) avg_goods_sale_cnt
    from t
   group by good)
select a.dept_id,
       a.good,
       goods_sale_cnt,
       avg_goods_sale_cnt,
       goods_sale_cnt - avg_goods_sale_cnt dv_goods_sale_cnt
  from t a, t1 b
 where a.good = b.good
 order by 2, 1;

相关统计分析函数-MAX/MIN
货物G01每月的最高和最低销售额对应的部门（如有多个部门按部D列出最小的个，如某
门某月无销售额则不做统计）

with t as
 (select dept_id,
         to_char(sale_date, 'yyyy-mm') sale_month,
         sum(sale_cnt) goods_sale_cnt
    from lw_sales
   where good = 'G01'
   group by dept_id, to_char(sale_date, 'yyyy-mm')),
t1 as
 (select sale_month,
         max(goods_sale_cnt) max_gsc,
         min(goods_sale_cnt) min_gsc
    from t
   group by sale_month)
select a.sale_month,
       min(case
             when goods_sale_cnt = max_gsc then
              dept_id
           end) max_dept_id,
       min(case
             when goods_sale_cnt = min_gsc then
              dept_id
           end) min_dept_id
  from t a, t1 b
 where a.sale_month = b.sale_month
   and (goods_sale_cnt = min_gsc or goods_sale_cnt = max_gsc)
 group by a.sale_month;

 

with t as
 (select dept_id,
         to_char(sale_date, 'yyyy-mm') sale_month,
         sum(sale_cnt) goods_sale_cnt
    from lw_sales
   where good = 'G01'
   group by dept_id, to_char(sale_date, 'yyyy-mm'))
select a.sale_month,
       min(dept_id) keep(dense_rank first order by goods_sale_cnt desc) max_dept_id,
       min(dept_id) keep(dense_rank first order by goods_sale_cnt) min_dept_id
  from t a
 group by a.sale_month;

查出货物G03在销售当天及前十二天的最大销售额（按部门和整个公司分别求出）