Oracle中的分析函数(Analytic Functions)是一类强大的SQL工具,用于执行复杂的计算和分析操作。
Oracle分析函数的特点:
无需分组:分析函数允许在结果集的行之间执行计算,而无需将数据集分组到多个输出行中。
窗口子句:通常与OVER()子句一起使用,以定义窗口或分区内的数据范围。
灵活性强:支持多种计算方式,如排名、累计、移动平均等。
常用分析函数包括:
ROW_NUMBER():为查询结果的每一行分配一个唯一的序号。
1 | SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; |
RANK():根据排序表达式对结果进行排名,相同值的行会获得相同的排名,但后续排名会跳过。
1 | SELECT department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; |
DENSE_RANK():类似于RANK(),但不会跳过后续排名。
1 | SELECT department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank FROM employees; |
NTILE(n):将数据划分为n个桶,并为每个桶中的行分配一个号码。
1 | SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees; |
LAG() 和 LEAD():访问当前行的前一行或后一行的值。
1 | SELECT employee_id, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary FROM employees; |
FIRST_VALUE() 和 LAST_VALUE():返回窗口框架内第一行或最后一行的值。
1 | SELECT department_id, salary, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sal FROM employees; |
SUM(), AVG(), MIN(), MAX() 作为分析函数:计算窗口内的总和、平均值、最小值和最大值。
1 | SELECT employee_id, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary FROM employees; |
窗口子句(Window Clause)包括:
PARTITION BY:将结果集划分为分区,每个分区独立应用分析函数。
ORDER BY:指定窗口内数据的排序顺序。
ROWS BETWEEN ... AND ...:定义窗口框架,即分析函数作用的行范围。
应用场景:
计算运行总和或平均值。
生成排名和百分比排名。
进行时间序列分析。
在报表和数据可视化中提供丰富的统计信息。
注意事项:
分析函数的结果依赖于查询的排序和分区方式,因此必须小心设计ORDER BY和PARTITION BY子句。
使用窗口子句时,确保逻辑上的一致性和性能上的优化。
对于大数据集,某些分析函数可能会消耗大量资源,建议进行性能测试和优化。
通过掌握Oracle分析函数,您可以更高效地处理复杂的数据分析和报告需求,提升数据处理能力和业务洞察力。