• 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",所以需要显性写出正确的开窗语句

  • 相关阅读:
    《ASP.NET 本质论》源码下载
    将 Excel 导入到 SharePoint 列表
    使用 jQuery dataTables 3 解析请求参数
    数据库表的转置
    翻译:使用 Entity Framework 4.x 进行代码优先 (CodeFirst) 开发
    转贴:是K2,還是WF(Workflow Foundation)?
    EF CodeFirst 自定义表映射
    CRC原理及其逆向破解方法
    空间坐标转换
    Post Process
  • 原文地址:https://www.cnblogs.com/yongestcat/p/11248907.html
Copyright © 2020-2023  润新知