• Oracle分析函数FIRST_VALUE、LAST_VALUE


    FIRST_VALUE、LAST_VALUE分析函数可以按照特定分组和排序取出组内首尾值,语法

    FIRST_VALUE 
      { (expr) [ {RESPECT | IGNORE} NULLS ]
      | (expr [ {RESPECT | IGNORE} NULLS ])
      }
      OVER (analytic_clause)
    

    测试下

    10:48:07 SCOTT@study> SELECT EMPNO,
    10:48:15   2         DEPTNO,
    10:48:15   3         SAL,
    10:48:15   4         FIRST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL) AS LOWEST_IN_DEPT,
    10:48:15   5         FIRST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS 1 PRECEDING) AS PRECEDING_IN_DEPT,
    10:48:15   6         LAST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL) AS HIGHEST_IN_DEPT,
    10:48:15   7         LAST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_IN_DEPT
    10:48:15   8    FROM EMP;
    
         EMPNO     DEPTNO        SAL LOWEST_IN_DEPT PRECEDING_IN_DEPT HIGHEST_IN_DEPT HIGHEST_IN_DEPT
    ---------- ---------- ---------- -------------- ----------------- --------------- ---------------
          7934         10       1300           1300              1300            1300            5000
          7782         10       2450           1300              1300            2450            5000
          7839         10       5000           1300              2450            5000            5000
          7369         20        800            800               800             800            3000
          7876         20       1100            800               800            1100            3000
          7566         20       2975            800              1100            2975            3000
          7788         20       3000            800              2975            3000            3000
          7902         20       3000            800              3000            3000            3000
          7900         30        950            950               950             950            2850
          7654         30       1250            950               950            1250            2850
          7521         30       1250            950              1250            1250            2850
          7844         30       1500            950              1250            1500            2850
          7499         30       1600            950              1500            1600            2850
          7698         30       2850            950              1600            2850            2850
    
    14 rows selected.
    
    Elapsed: 00:00:00.00
    10:48:17 SCOTT@study> 
    

    LAST_VALUE的默认写法结果不符合预期,是因为默认的开窗语句"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW",所以需要显性写出正确的开窗语句

  • 相关阅读:
    更快的datetime string to python datetime转换模块 ciso8601
    华为新员工入职培训计划曝光!你留不住员工的原因就在这! zz
    在vmware中安装macos系统 vm12 osx10.11 or higher
    vmware for linux (centos65) 卸载相关
    第八章多态
    第七章 复用类
    第六章 访问权限控制
    Think in Java 第四 五 章
    Think in Java 第三章操作符
    Thank in Java
  • 原文地址:https://www.cnblogs.com/yongestcat/p/11248907.html
Copyright © 2020-2023  润新知