利用Oracle中的分析函数进行数据统计分析（oracle 中分析函数）
利用Oracle中的分析函数进行数据统计分析
随着企业数据量的不断增加，数据分析相关的工作也越来越重要。Oracle数据库是企业中最流行的数据库之一，因此对于数据分析师而言，掌握Oracle中的分析函数将能够极大地提高工作效率和数据分析的准确性。
一、“在线分析处理”简介
在 Oracle 中，有很多可以用来统计分析数据的工具，其中最常用的就是分析函数。分析函数可以将多行数据形成一个结果集，也可以根据需要将结果分组，以便进行各种聚合函数的操作。
二、Oracle中的一些常见分析函数
1、 RANK, DENSE_RANK, ROW_NUMBER
这些函数可以用于返回结果的行数，来表明某一行的排名。
例如：
SELECT department_name, AVG(salary) AS department_average_salary, RANK() OVER(ORDER BY AVG(salary) DESC) AS department_ranking FROM employees GROUP BY department_name;
以上 SQL语句将返回各个部门的平均工资和排名。
2、LAG 和 LEAD
LAG 和 LEAD 分别用于比较某一行与它之前或之后的行。
例如：
SELECT last_name, hire_date, salary, LAG(salary) OVER(ORDER BY hire_date) AS lag_salary, LEAD(salary) OVER(ORDER BY hire_date) AS lead_salary FROM employees;
以上 SQL语句将返回雇员上一次工资调整时间和下一次工资调整时间，以及调整前后的工资变化。
3、 PERCENTILE_DISC 和 PERCENTILE_CONT
这些函数用于计算一个数据集中某一分位数的值。
例如：
SELECT department_name, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY salary) AS percentile_75_salary FROM employees GROUP BY department_name;
以上 SQL 语句将返回各个部门的中位数和第75个百分位数。
三、应用分析函数的时机
从使用效果来看，应用分析函数通常是在需要分组或排序的时候，以及当分析要求不仅仅是简单的计数或聚合时使用。
经典的例子就是排除掉每个类别中价格最低的商品，显示给定的商品列表，其 SQL 语句如下：
SELECT product_name, category, list_price FROM ( SELECT product_name, category, list_price, ROW_NUMBER() OVER(PARTITION BY category ORDER BY list_price) AS row_number FROM products ) WHERE row_number > 1;
可以看到，这个语句使用了 ROW_NUMBER 分析函数，其结果是将 product_name、category 以及 list_price 按 category 分成若干部分，按 list_price 排序后返回一个包含新 row_number 列的结果集，新列值代表每个 category 内部的价格排名。仅选出 row_number 大于 1 的行之后形成最终结果集，这样就“排除”了每个类别中价格最低的商品。
四、小结
如此实用的分析函数在 Oracle 中，使用分析函数除了为分析师带来更准确，更高效的工作方式外，也能够使数据分析的结果更加准确和有效。掌握 Oracle 中的分析函数可以为你在工作中带来不少收益，推荐各位数据分析师多加学习和掌握。
附：代码展示
1. ROW_NUMBER
用法：ROW_NUMBER() OVER(PARTITION BY column ORDER BY column)
功能：行号，可以在多个排序维度的情况下提供唯一的行编号
SELECT empno, deptno, sal, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rn FROM emp;
2. RANK
用法：RANK() OVER(PARTITION BY column ORDER BY column)
功能：返回组内排名，并跳过相等的排名
SELECT empno, deptno, sal, RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) rnk FROM emp;
3. DENSE_RANK
用法：DENSE_RANK() OVER(PARTITION BY column ORDER BY column)
功能：返回组内排名，不跳过相等的排名
SELECT empno, deptno, sal, DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) drnk FROM emp;
4. AVG
用法：AVG(column) OVER(PARTITION BY column)
功能：计算组内均值
SELECT sal, deptno, AVG(sal) OVER(PARTITION BY deptno) avg_sal FROM emp;
5. COUNT
用法：COUNT(*) OVER(PARTITION BY column)
功能：计算分组中元素总数
SELECT deptno, COUNT(*) OVER(PARTITION BY deptno) count_dept FROM emp;
6. MAX
用法：MAX(column) OVER(PARTITION BY column)
功能：计算组内的最大值
SELECT sal, deptno, MAX(sal) OVER(PARTITION BY deptno) max_sal FROM emp;
7. MIN
用法：MIN(column) OVER(PARTITION BY column)
功能：计算组内的最小值
SELECT sal, deptno, MIN(sal) OVER(PARTITION BY deptno) min_sal FROM emp;
8. SUM
用法：SUM(column) OVER(PARTITION BY column)
功能：计算组内的总和
SELECT sal, deptno, SUM(sal) OVER(PARTITION BY deptno) sum_sal FROM emp;
9. FIRST_VALUE
用法：FIRST_VALUE(column) OVER(PARTITION BY column ORDER BY column)
功能：根据指定的排序方式和查询条件，返回每个分组的第一条的值
SELECT sal, deptno, FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) first_sal FROM emp;
10. LAST_VALUE
用法：LAST_VALUE(column) OVER(PARTITION BY column ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
注意：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 可以得到每个分组的最后一个值
功能：返回每个分组的最后一行的值
SELECT sal, deptno, LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_sal FROM emp;
