分析函数应用

最新推荐文章于 2024-03-04 23:37:56 发布

elimago

最新推荐文章于 2024-03-04 23:37:56 发布

阅读量896

点赞数

分类专栏： oracle 文章标签： null pivot windows sql

oracle 专栏收录该内容

48 篇文章

订阅专栏

1.累计计算

本例中对某部门的工资进行逐行计算，每行包括之前所有行中工资的合计。

SELECT ename "Ename", deptno "Deptno", sal "Sal",
SUM(sal)
    OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL)
    OVER (PARTITION BY deptno
          ORDER BY ename) "Dept Total",
ROW_NUMBER()
    OVER (PARTITION BY deptno
          ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
/

2.前N条查询

从多到少排列销售人员的工资，取前三行。如果该部门少于三人，则返回的记录少于三个。

SELECT * FROM (
SELECT deptno, ename, sal, ROW_NUMBER()
OVER (
PARTITION BY deptno ORDER BY sal DESC
) Top3 FROM emp
)
WHERE Top3 <= 3
/

我需要工资为前三位的销售人员名字——即查找工资金额、排序、取最高的三项金额、给我领取这些工资的人员的名字。

SELECT * FROM (
SELECT deptno, ename, sal,
DENSE_RANK()
OVER (
PARTITION BY deptno ORDER BY sal desc
) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC
/

3.Windows窗口

可根据两种规则对窗口进行设置：数据值的范围或当前行指定区距的行。分析函数中的ORDER BY会默认添加一条窗口语句：RANGE UNBOUNDED PRECEDING。即按照ORDER BY语句取得划分中的当前之前的所有行。

以下例子为一个分组中的滑动窗口，计算该组中当前行与其前两行的SAL列的和。如我们需要计算当前员工的工资和其之前的两人工资的和，如下例所示。

SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM(SAL)
    OVER (PARTITION BY deptno
          ORDER BY ename
          ROWS 2 PRECEDING) "Sliding Total"
FROM emp
ORDER BY deptno, ename
/

4.Range Windows范围窗口

范围窗口根据WHERE语句对行进行收集。例如“之前5”将会生成一个滑动视窗，包括该分组中当前行之前的5个单位所有行。这些单位可以是数值或日期，使用数字或日期以外的其他数据类型表示的范围无效。

计算当前雇佣日期之前100天内雇佣的员工的数量。范围窗口返回当前行雇佣日期100天之前并在这个范围内计算行数。

SELECT ename, hiredate, hiredate-100 hiredate_pre,
       COUNT(*)
       OVER (
          ORDER BY hiredate ASC
          RANGE 100 PRECEDING
       ) cnt
FROM emp
ORDER BY hiredate ASC
/

计算每个员工雇佣之前100天内雇佣员工的平均工资。查询如下：

SELECT ename, hiredate, sal,
       AVG(sal)
       OVER (
          ORDER BY hiredate ASC
          RANGE 100 PRECEDING
       ) avg_sal
FROM emp
ORDER BY hiredate ASC
/

5.Row Windows行窗口

行窗口为实际单位，是包括在窗口中实际行数。例如可以计算一给定记录的平均工资，该记录包括其之前或之后雇佣的员工（至多5名），具体如下：

SELECT ename, hiredate, sal,
AVG(sal)
OVER (ORDER BY hiredate ASC ROWS 5 PRECEDING) AvgAsc,
COUNT(*)
OVER (ORDER BY hiredate ASC ROWS 5 PRECEDING) CntAsc,
AVG(sal)
OVER (ORDER BY hiredate DESC ROWS 5 PRECEDING) AvgDes,
COUNT(*)
OVER (ORDER BY hiredate DESC ROWS 5 PRECEDING) CntDes
FROM emp
ORDER BY hiredate
/

6.访问当前行前后的行

我们常常不仅想访问当前行，还想访问“之前”或“之后”行中的数据。例如，某份报告需要表明各部门的所有员工、员工雇佣日期、距上一雇佣的天数、距下一雇佣的天数。

y.直接编写SQL会比较困难，其执行性能必然存在问题。过去我常用的方法是“select a select”或编写PL/SQL函数，从当前行得到数据，并“找到”之前以及之后行中的数据。这样可以达到目的，查询的开发与运行会带来很大开销。

SELECT deptno, ename, hiredate,
LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
        ORDER BY hiredate, ename) last_hire,
hiredate - LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
        ORDER BY hiredate, ename) days_last,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
        ORDER BY hiredate, ename) next_hire,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
        ORDER BY hiredate, ename)-hiredate days_next
FROM emp
ORDER BY deptno, hiredate

7.LAG

LAG无须自合并就可以获取同一个表格中的多行记录。知道来自查询中的多行以及光标的位置，LEAD就可以进入位置指定区距之前的行。

如果不指定区距，默认值为1。如果区距超出窗口范围，则返回可指定默认值。如不指定默认值，则默认值为null。

下例中，EMP表中的每个人之前雇佣员工的工资：

SELECT ename,hiredate,sal,

LAG(sal, 1, 0)

  OVER (ORDER BY hiredate) AS PrevSal

FROM emp

WHERE job = 'CLERK';

8.LEAD

LEAD不用自合并就可同时进入一个表格中的多行。知道来自查询中的多行以及光标的位置，LEAD就可以进入位置指定区距之后的行。

如不指定区距，则默认值为1。如区距超出窗口范围则返回任意默认值。如不指定默认值，默认值为0。

下例，EMP表中的每个员工，他们之后雇佣员工的雇佣日期：

SELECT ename, hiredate,
LEAD(hiredate, 1)
OVER (ORDER BY hiredate) AS NextHired
FROM emp WHERE deptno = 30;

9.确定组中的第一个值/最后一个值

FIRST_VALUE与LAST_VALUE函数可以选择一组中的第一行和最后一行。这些行很有用，因为它们经常用作计算的基线。

下例为每个部门的每名员工、最低工资员工的姓名。

SELECT deptno, ename, sal,
FIRST_VALUE(ename)
OVER (PARTITION BY deptno
ORDER BY sal ASC) AS MIN_SAL_HAS
FROM emp
ORDER BY deptno, ename;

下例中为每个部门的每名员工、最高工资员工的姓名。

SELECT deptno, ename, sal,

  FIRST_VALUE(ename)

  OVER (PARTITION BY deptno

        ORDER BY sal DESC) AS MAX_SAL_HAS

FROM emp

ORDER BY deptno, ename;

下例为第30个部门中的每名员工、工资最低员工的姓名，使用内联视图。

SELECT deptno, ename, sal,
  FIRST_VALUE(ename)
  OVER (ORDER BY sal ASC) AS MIN_SAL_HAS
FROM (SELECT * FROM emp WHERE deptno = 30)

10.交叉表或Pivot查询

交叉表查询，或者pivot查询，用稍微不同的方法将这些数据分组。交叉表查询可根据三行（一行代表一个项目），每行有三列（第一列列出项目，然后一列代表一年）得出结果——如下：

如果需要以列的形式显示每个部门中工资最多的3个人，查询为每个部门返回一行，一行有4列。DEPTNO，该部门中工资最高的人，工资第二高人的名字，依次类推。使用分析函数很容易做到，不采用有分析函数这将是不可能的。

SELECT deptno,
  MAX(DECODE(seq,1,ename,null)) first,
  MAX(DECODE(seq,2,ename,null)) second,
  MAX(DECODE(seq,3,ename,null)) third
FROM (SELECT deptno, ename,
       row_number()
       OVER (PARTITION BY deptno
             ORDER BY sal desc NULLS LAST) seq
       FROM emp)
WHERE seq <= 3
GROUP BY deptno
/

DEPTNO FIRST      SECOND     THIRD
---------- ---------- ---------- ----------
        10 KING       CLARK      MILLER
        20 SCOTT      FORD       JONES
        30 BLAKE      ALLEN      TURNER