分析函数(窗口函数)

# 分析函数(窗口函数)

```SQL
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             DEPTNO,
             SAL,
             ROUND(AVG(SAL) OVER(), 2) AVG_ALL_SAL,
             ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO), 2) AVG_DEPT_SAL,
             ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO
                                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),2) AVG_SAL1,
             ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO
                                    ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),2) AVG_SAL2
    FROM EMP;

SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             DEPTNO,
             SAL,
             COMM,
             COUNT(1) OVER() QTY1,
             COUNT(*) OVER() QTY2,
             COUNT(COMM) OVER() QTY3,
             COUNT(DISTINCT DEPTNO) OVER() QTY4,
             COUNT(1) OVER(PARTITION BY DEPTNO) QTY5
    FROM EMP;

SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             ROW_NUMBER() OVER(ORDER BY SAL) RN1, --并列序号不一样
             ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL) RN2,
             RANK() OVER(ORDER BY SAL) RN3, --并列序号一样，跳号，如：1，1，3，4
             RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) R4,
             DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) R5 --并列序号一样，不跳号，如：1，1，2，3，4
    FROM EMP;

SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL,
             FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) SAL2
    FROM EMP;

--
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL1,
             LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SAL2,
             LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SAL3,
             LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) SAL4,
             LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SAL5,
             LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SAL6
    FROM EMP;

--
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             LAG(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL1,
             LAG(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL2,  --sal向下移动1行，超出用NULL
             LAG(SAL, 2, 0) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL3  --sal向下移动2行，超出用0
    FROM EMP;

--
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             LEAD(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL1,
             LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL2,
             LEAD(SAL, 2, 0) OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL3
    FROM EMP;

--
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY EMPNO) OVER(PARTITION BY DEPTNO) NAME1
    FROM EMP;

--
SELECT DEPTNO,
             DBMS_LOB.SUBSTR(SUBSTR(RTRIM(XMLCAST(XMLAGG(XMLELEMENT(E,ENAME || ',') ORDER BY EMPNO) AS CLOB),','),1,100)) NAME
    FROM EMP
 GROUP BY DEPTNO;

--
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             MAX(SAL) OVER() SAL1,
             MAX(SAL) OVER(PARTITION BY DEPTNO) SAL2,
             MAX(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) SAL3,
             MIN(SAL) OVER() SAL4,
             MIN(SAL) OVER(PARTITION BY DEPTNO) SAL5,
             MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) SAL6,
             SUM(SAL) OVER() SAL7,
             SUM(SAL) OVER(PARTITION BY DEPTNO) SAL8,
             SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) SAL9
    FROM EMP;

--
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             CUME_DIST() OVER(ORDER BY SAL) P1,
             CUME_DIST() OVER(PARTITION BY DEPTNO ORDER BY SAL) P2
    FROM EMP
 ORDER BY DEPTNO, SAL;

--
SELECT EMPNO,
             ENAME,
             JOB,
             MGR,
             HIREDATE,
             SAL,
             DEPTNO,
             PERCENT_RANK() OVER(ORDER BY SAL) P1,
             PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) P2
    FROM EMP
 ORDER BY DEPTNO, SAL;

--
select DEPTNO,sum(sal) from emp group by DEPTNO;
select JOB,DEPTNO,sum(sal) from emp group by rollup(JOB,DEPTNO);

SELECT EMPNO,
             ENAME,
             ENAME,
             HIREDATE,
             SAL,
             DEPTNO,
             RATIO_TO_REPORT(SAL) OVER() AS PCT1L,
             RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO) AS PCT2,
             ROUND(RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO), 4) * 100 || '%' AS PCT3
    FROM EMP;
```

## ROWS BETWEEN ... AND ... 物理窗口

rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）

```SQL
/*
ROWS BETWEEN ... AND ...

UNBOUNDED PRECEDING  分区内第一行
1 PRECEDING          分区内当前行前一行
CURRENT ROW          分区内当前行
1 FOLLOWING          分区内当前行后一行
UNBOUNDED FOLLOWING  分区内最后一行
*/

SELECT mid,mdate,msum,
       MAX(msum) OVER(PARTITION BY mid ORDER BY mdate ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) max_msum1,  --当前行前第5行到当前行前一行,共5行
       MAX(msum) OVER(PARTITION BY mid ORDER BY mdate ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) max_msum2,  --当前行前第5行到当前行,共6行
       MAX(msum) OVER(PARTITION BY mid ORDER BY mdate ROWS 5 PRECEDING) max_msum3,  --与上面等价，当前行前第5行到当前行,共6行
    MAX(msum) OVER(PARTITION BY mid ORDER BY mdate ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) max_msum4  --当前行和后一行共2行
FROM leity_test_3
WHERE mdate <= '20190105'
```

## RANGE

range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内

order by 的字段只能有一个（假设字段名叫A），选择行是由A字段值决定的。range 5 preceding and

```SQL
SELECT mid,mdate,msum,
       MAX(msum) OVER(PARTITION BY mid ORDER BY to_date(mdate,'yyyymmdd') RANGE 1 PRECEDING) max_msum1
FROM leity_test_3
WHERE mdate <= '20190105'
```