利用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;