美文网首页
Oracle高级分析函数与统计函数结合使用

Oracle高级分析函数与统计函数结合使用

作者: 羁拥_f357 | 来源:发表于2020-05-28 16:17 被阅读0次

    这个blog我们来聊聊Oracle高级分析函数与统计统计函数结合使用

    测试数据:

    DROP TABLE testa;

    CREATE TABLE testa (area VARCHAR2 (20), month VARCHAR2 (20),

    amount NUMBER);

    insert into testa values ('上海', '1', 199); 

    insert into testa values ('上海', '2', 199); 

    insert into testa values ('上海', '3', 155); 

    insert into testa values ('上海', '3', 155); 

    insert into testa values ('上海', '4', 125); 

    insert into testa values ('广州', '1', 75); 

    insert into testa values ('广州', '2', 67); 

    insert into testa values ('北京', '1', 235);

    insert into testa values ('北京', '2', 330); 

    Commit;

    一.keep函数

    keep是Oracle下的另一个分析函数,他的用法不同于通过over关键字指定的分析函数,可以用于这样一种场合下:取同一个分组下以某个字段排序后,对指定字段取最小或最大的那个值。

    keep语法:

    min | max(col1) keep (dense_rank first | lastorder by col2) over (partion by col3); 

    最前是聚合函数,可以是min、max、avg、sum...

    col1为要计算的列;

    dense_rank first,dense_rank last为keep 函数的保留属性,表示分组、排序结果集中第一个、最后一个;

    解释:返回按照col3分组后,按照col2排序的结果集中第一个或最后一个最小值或最大值col1。

    col1和col2列可重复

    需求:求员工表每个员工信息及部门最高薪资、最低薪资

    --传统sql写法,需要嵌套一层临时表

    with tmp1 as

    (

    select e.deptno,max(e.sal) max_sal,min(e.sal) min_sal

    from emp e

    group by e.deptno

    )

    select e2.deptno,

    e2.ename,

    e2.sal,

    max_sal,

    min_sal

    from emp e2

    left join tmp1 

    on e2.deptno = tmp1.deptno

    ORDER BY e2.deptno, e2.sal, e2.ename;

    --排名函数只能进行排名,通过排名可以看出最大和最小薪资,同样需要嵌套临时表来完成此类需求

    --通过keep函数,无需嵌套子查询,代码逻辑更为简单 

    SELECT Deptno,

    Ename,

    Sal,

    MIN(Sal) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY deptno) max_sal,

    MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY deptno) min_sal

    FROM Emp

    ORDER BY deptno, sal, ename;

    SQL> --传统sql写法,需要嵌套一层临时表

    SQL> with tmp1 as

    2 (

    3 select e.deptno,max(e.sal) max_sal,min(e.sal) min_sal

    4 from emp e

    5 group by e.deptno

    6 )

    7 select e2.deptno,

    8 e2.ename,

    9 e2.sal,

    10 max_sal,

    11 min_sal

    12 from emp e2

    13 left join tmp1

    14 on e2.deptno = tmp1.deptno

    15 ORDER BY e2.deptno, e2.sal, e2.ename;

    DEPTNO ENAME SAL MAX_SAL MIN_SAL

    ------ ---------- --------- ---------- ----------

    10 MILLER 1300.00 5000 1300

    10 CLARK 2450.00 5000 1300

    10 KING 5000.00 5000 1300

    20 SMITH 800.00 3000 800

    20 ADAMS 1100.00 3000 800

    20 JONES 2975.00 3000 800

    20 FORD 3000.00 3000 800

    20 SCOTT 3000.00 3000 800

    30 JAMES 950.00 2850 950

    30 MARTIN 1250.00 2850 950

    30 WARD 1250.00 2850 950

    30 TURNER 1500.00 2850 950

    30 ALLEN 1600.00 2850 950

    30 BLAKE 2850.00 2850 950

    14 rows selected

    SQL> --排名函数只能进行排名,通过排名可以看出最大和最小薪资,同样需要嵌套临时表来完成此类需求

    SQL> --通过keep函数,无需嵌套子查询,代码逻辑更为简单

    SQL> SELECT Deptno,

    2 Ename,

    3 Sal,

    4 MIN(Sal) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY deptno) max_sal,

    5 MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY deptno) min_sal

    6 FROM Emp

    7 ORDER BY deptno, sal, ename;

    DEPTNO ENAME SAL MAX_SAL MIN_SAL

    ------ ---------- --------- ---------- ----------

    10 MILLER 1300.00 1300 5000

    10 CLARK 2450.00 1300 5000

    10 KING 5000.00 1300 5000

    20 SMITH 800.00 800 3000

    20 ADAMS 1100.00 800 3000

    20 JONES 2975.00 800 3000

    20 FORD 3000.00 800 3000

    20 SCOTT 3000.00 800 3000

    30 JAMES 950.00 950 2850

    30 MARTIN 1250.00 950 2850

    30 WARD 1250.00 950 2850

    30 TURNER 1500.00 950 2850

    30 ALLEN 1600.00 950 2850

    30 BLAKE 2850.00 950 2850

    14 rows selected

    需求:每月的最高和最低销售额对应的区域(如有多个区域按区域列出最小的一个,如某区域某月无销售额则不做统计

    SELECT t1.month,

    MIN(area) keep(dense_rank FIRST ORDER BY amount DESC) max_area,

    MIN(area) keep (dense_rank FIRST ORDER BY amount) min_area

    FROM testa t1

    GROUP BY t1.month;

    SQL> SELECT t1.month,

    2 MIN(area) keep(dense_rank FIRST ORDER BY amount DESC) max_area,

    3 MIN(area) keep (dense_rank FIRST ORDER BY amount) min_area

    4 FROM testa t1

    5 GROUP BY t1.month;

    MONTH MAX_AREA MIN_AREA

    -------------------- -------------------- --------------------

    1 北京 广州

    2 北京 广州

    3 上海 上海

    4 上海 上海

    二.求累积销售额

    需求:求每个区域每个月的销售额以及累积销售额

    --传统写法,通过表连接 t1.month >= t2.month 及group语句解决

    select t1.area,t1.month,t1.amount,sum(t2.amount) cum_amount

    from testa t1

    left join testa t2

    on t1.area = t2.area

    and t1.month >= t2.month

    group by t1.area,t1.month,t1.amount

    order by t1.area,t1.month;

    --通过sum聚合函数与分析函数配合使用,代码更简洁易懂

    select t1.area,

    t1.month,

    t1.amount,

    sum(t1.amount) over(partition by t1.area order by month) cum_amount

    from testa t1

    order by t1.area,t1.month;

    SQL> --传统写法,通过表连接 t1.month >= t2.month 及group语句解决

    SQL> select t1.area,t1.month,t1.amount,sum(t2.amount) cum_amount

    2 from testa t1

    3 left join testa t2

    4 on t1.area = t2.area

    5 and t1.month >= t2.month

    6 group by t1.area,t1.month,t1.amount

    7 order by t1.area,t1.month;

    AREA MONTH AMOUNT CUM_AMOUNT

    -------------------- -------------------- ---------- ----------

    北京 1 235 235

    北京 2 330 565

    广州 1 75 75

    广州 2 67 142

    上海 1 199 199

    上海 2 199 398

    上海 3 155 1416

    上海 4 125 833

    8 rows selected

    SQL> --通过sum聚合函数与分析函数配合使用,代码更简洁易懂

    SQL> select t1.area,

    2 t1.month,

    3 t1.amount,

    4 sum(t1.amount) over(partition by t1.area order by month) cum_amount

    5 from testa t1

    6 order by t1.area,t1.month;

    AREA MONTH AMOUNT CUM_AMOUNT

    -------------------- -------------------- ---------- ----------

    北京 1 235 235

    北京 2 330 565

    广州 1 75 75

    广州 2 67 142

    上海 1 199 199

    上海 2 199 398

    上海 3 155 708

    上海 3 155 708

    上海 4 125 833

    9 rows selected

    正在跳转(iOS交流裙 密码:123)

    相关文章

      网友评论

          本文标题:Oracle高级分析函数与统计函数结合使用

          本文链接:https://www.haomeiwen.com/subject/gkeaahtx.html