Oracle 数据分析常用函数实战教程Oracle 数据库提供了强大的分析函数Analytic Functions也称为开窗函数能够在不合并数据行的情况下对数据集进行复杂的统计、排名、趋势分析及前后行对比。以下教程将核心函数分类汇总并结合代码示例详解其用法。一、核心语法结构所有分析函数均遵循FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)的通用语法结构。其中PARTITION BY用于定义分组窗口ORDER BY用于定义窗口内的排序规则而窗口帧子句则精确控制计算范围 。子句关键字作用描述是否必填PARTITION BY将结果集划分为多个逻辑分区函数在每个分区内独立计算类似 GROUP BY 但不合并行可选ORDER BY定义分区内数据的排序顺序决定累积计算或排名的基准排名/移位类必填聚合类可选ROWS/RANGE定义窗口的物理行数或逻辑范围如“当前行及前两行”可选默认为整个分区二、排名与序号函数此类函数主要用于生成行号、处理并列排名场景是数据去重和 Top-N 查询的核心工具 。1. 函数对比函数名功能描述并列值处理序号连续性典型应用场景ROW_NUMBER()生成唯一连续序号不并列强制区分连续 (1, 2, 3...)去除重复数据、分页查询RANK()计算排名并列占用后续名次不连续 (1, 1, 3...)允许并列且需体现名次间隔的场景DENSE_RANK()计算密集排名并列不占用后续名次连续 (1, 1, 2...)允许并列但需保持名次紧凑的场景2. 代码示例-- 场景按部门 (dept_id) 对员工薪资 (salary) 进行排名 SELECT emp_id, dept_id, salary, -- 生成唯一行号即使薪资相同序号也不同 ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rn, -- 薪资相同时排名相同但下一名次跳过如 1, 1, 3 RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rk, -- 薪资相同时排名相同下一名次紧接如 1, 1, 2 DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as drk FROM employees;三、前后行移位函数用于访问当前行之前或之后的数据常用于计算环比、同比、移动平均等趋势分析指标 。1. 函数说明LAG(col, n, default)获取当前行向前第n行的值若不存在则返回default。LEAD(col, n, default)获取当前行向后第n行的值若不存在则返回default。2. 代码示例-- 场景计算每位员工薪资与上一位员工的差额按部门内薪资排序 SELECT emp_id, dept_id, salary, -- 获取前一行薪资较低者的薪资若无则补 0 LAG(salary, 1, 0) OVER (PARTITION BY dept_id ORDER BY salary) as prev_salary, -- 计算薪资差值 salary - LAG(salary, 1, 0) OVER (PARTITION BY dept_id ORDER BY salary) as diff_val FROM employees;四、窗口聚合函数标准的聚合函数SUM, AVG, COUNT 等配合OVER子句使用可计算累积值、移动平均值或占比而无需将多行合并为一行 。1. 常见用法需求类型语法模式说明全量统计SUM(col) OVER (PARTITION BY grp)计算分组总和每行显示该组总和累积计算SUM(col) OVER (ORDER BY date_col)按时间顺序累加常用于财务报表移动平均AVG(col) OVER (ORDER BY date_col ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)计算当前行及前两行的平均值占比计算ratio_to_report(col) OVER (...)计算当前值占分组总和的比例2. 代码示例-- 场景计算每日销售额的累计总和及近 3 天移动平均 SELECT sale_date, daily_amount, -- 按日期顺序累加销售额 SUM(daily_amount) OVER (ORDER BY sale_date) as cumulative_sum, -- 计算当前行及前两行共 3 天的平均销售额 AVG(daily_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_avg_3days, -- 计算当日销售额占当月总销售额的比例 ROUND(daily_amount / SUM(daily_amount) OVER (PARTITION BY TO_CHAR(sale_date, YYYY-MM)), 4) as month_ratio FROM sales_table;五、高级统计分析函数Oracle 内置了专门的统计函数可直接计算方差、标准差、相关系数及线性回归斜率/截距适用于深度数据挖掘 。函数类别函数名称功能描述离散程度VAR_POP,VAR_SAMP计算总体方差或样本方差离散程度STDDEV_POP,STDDEV_SAMP计算总体标准差或样本标准差相关性CORR(expr1, expr2)计算两个表达式的相关系数回归分析REGR_SLOPE,REGR_INTERCEPT计算线性回归的斜率和截距分布CUME_DIST,PERCENT_RANK计算累积分布值或百分位排名代码示例-- 场景分析广告投入 (ad_cost) 与销售额 (sales_amt) 的线性关系 SELECT REGR_SLOPE(sales_amt, ad_cost) OVER () as slope, -- 回归斜率 REGR_INTERCEPT(sales_amt, ad_cost) OVER () as intercept, -- 回归截距 CORR(sales_amt, ad_cost) OVER () as correlation_coef, -- 相关系数 STDDEV_SAMP(sales_amt) OVER () as sales_stddev -- 销售额样本标准差 FROM marketing_data;六、综合实战案例Top N 筛选与去重结合排名函数与子查询是解决“每组取前 N 条”及“完全重复数据去重”最高效的方法 。-- 场景1提取每个部门薪资最高的前 3 名员工 SELECT * FROM ( SELECT emp_name, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as drk FROM employees ) WHERE drk 3; -- 场景 2基于 ROW_NUMBER 去除完全重复的记录保留一条 DELETE FROM employees a WHERE rowid NOT IN ( SELECT MIN(rowid) FROM ( SELECT rowid, ROW_NUMBER() OVER (PARTITION BY emp_id, emp_name, hire_date ORDER BY rowid) as rn FROM employees ) WHERE rn 1 );参考来源oracle 常用功能函数汇总oracle分析函数汇总Oracle 之常用分析函数ORACLE ---开窗函数Oracle 开窗函数