Oracle教程FG057-SQL分析函数

1. 分析函数基础

分析函数是Oracle SQL中强大的功能，用于计算基于一组行的聚合值，同时保留详细的行数据。分析函数可以在不使用GROUP BY子句的情况下执行复杂的计算。更多学习教程www.fgedu.net.cn

分析函数语法：function_name([arguments]) OVER (PARTITION BY partition_expression ORDER BY order_expression [ASC|DESC]);

— 创建示例表
CREATE TABLE fgsales (
sales_id NUMBER(6),
employee_id NUMBER(6),
department_id NUMBER(4),
sale_date DATE,
amount NUMBER(10,2)
);– 插入数据
INSERT INTO fgsales VALUES (1, 100, 90, TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), 10000);INSERT INTO fgsales VALUES (2, 100, 90, TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), 15000);INSERT INTO fgsales VALUES (3, 101, 90, TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), 8000);INSERT INTO fgsales VALUES (4, 101, 90, TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), 12000);INSERT INTO fgsales VALUES (5, 103, 60, TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), 5000);INSERT INTO fgsales VALUES (6, 103, 60, TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), 7000);SELECT * FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID SALE_DATE AMOUNT
———- ———– ————- ———- ———-
1 100 90 01-JAN-26 10000
2 100 90 02-JAN-26 15000
3 101 90 01-JAN-26 8000
4 101 90 02-JAN-26 12000
5 103 60 01-JAN-26 5000
6 103 60 02-JAN-26 7000

2. RANK函数

RANK函数用于计算每行的排名，相同值会获得相同的排名，但会跳过后续的排名。

— 使用RANK函数计算销售金额排名
SELECT sales_id, employee_id, department_id, amount,
RANK() OVER (ORDER BY amount DESC) AS rank
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT RANK
———- ———– ————- ———- ———-
2 100 90 15000 1
4 101 90 12000 2
1 100 90 10000 3
3 101 90 8000 4
6 103 60 7000 5
5 103 60 5000 6

— 按部门分区计算排名
SELECT sales_id, employee_id, department_id, amount,
RANK() OVER (PARTITION BY department_id ORDER BY amount DESC) AS dept_rank
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT DEPT_RANK
———- ———– ————- ———- ———-
6 103 60 7000 1
5 103 60 5000 2
2 100 90 15000 1
4 101 90 12000 2
1 100 90 10000 3
3 101 90 8000 4

3. DENSE_RANK函数

DENSE_RANK函数用于计算每行的排名，相同值会获得相同的排名，但不会跳过后续的排名。

— 使用DENSE_RANK函数计算销售金额排名
SELECT sales_id, employee_id, department_id, amount,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT DENSE_RANK
———- ———– ————- ———- ———-
2 100 90 15000 1
4 101 90 12000 2
1 100 90 10000 3
3 101 90 8000 4
6 103 60 7000 5
5 103 60 5000 6

— 按部门分区计算排名
SELECT sales_id, employee_id, department_id, amount,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY amount DESC) AS dept_dense_rank
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT DEPT_DENSE_RANK
———- ———– ————- ———- ————–
6 103 60 7000 1
5 103 60 5000 2
2 100 90 15000 1
4 101 90 12000 2
1 100 90 10000 3
3 101 90 8000 4

4. ROW_NUMBER函数

ROW_NUMBER函数为结果集中的每行分配一个唯一的序号，即使值相同也会分配不同的序号。

— 使用ROW_NUMBER函数分配序号
SELECT sales_id, employee_id, department_id, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT ROW_NUM
———- ———– ————- ———- ———-
2 100 90 15000 1
4 101 90 12000 2
1 100 90 10000 3
3 101 90 8000 4
6 103 60 7000 5
5 103 60 5000 6

— 按部门分区分配序号
SELECT sales_id, employee_id, department_id, amount,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY amount DESC) AS dept_row_num
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT DEPT_ROW_NUM
———- ———– ————- ———- ————
6 103 60 7000 1
5 103 60 5000 2
2 100 90 15000 1
4 101 90 12000 2
1 100 90 10000 3
3 101 90 8000 4

5. SUM和AVG分析函数

SUM和AVG分析函数用于计算分区内的总和和平均值。

— 计算每个部门的销售总额和平均值
SELECT sales_id, employee_id, department_id, amount,
SUM(amount) OVER (PARTITION BY department_id) AS dept_total,
AVG(amount) OVER (PARTITION BY department_id) AS dept_avg
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT DEPT_TOTAL DEPT_AVG
———- ———– ————- ———- ———- ———-
6 103 60 7000 12000 6000
5 103 60 5000 12000 6000
2 100 90 15000 45000 11250
4 101 90 12000 45000 11250
1 100 90 10000 45000 11250
3 101 90 8000 45000 11250

— 计算累计销售金额
SELECT sales_id, employee_id, sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum
FROM fgsales;SALES_ID EMPLOYEE_ID SALE_DATE AMOUNT CUMULATIVE_SUM
———- ———– ———- ———- ————–
1 100 01-JAN-26 10000 10000
3 101 01-JAN-26 8000 18000
5 103 01-JAN-26 5000 23000
2 100 02-JAN-26 15000 38000
4 101 02-JAN-26 12000 50000
6 103 02-JAN-26 7000 57000

6. LEAD和LAG函数

LEAD和LAG函数用于访问当前行之前或之后的行数据。

— 使用LEAD和LAG函数
SELECT sales_id, employee_id, sale_date, amount,
LAG(amount, 1, 0) OVER (PARTITION BY employee_id ORDER BY sale_date) AS previous_amount,
LEAD(amount, 1, 0) OVER (PARTITION BY employee_id ORDER BY sale_date) AS next_amount
FROM fgsales;SALES_ID EMPLOYEE_ID SALE_DATE AMOUNT PREVIOUS_AMOUNT NEXT_AMOUNT
———- ———– ———- ———- ————— ———–
1 100 01-JAN-26 10000 0 15000
2 100 02-JAN-26 15000 10000 0
3 101 01-JAN-26 8000 0 12000
4 101 02-JAN-26 12000 8000 0
5 103 01-JAN-26 5000 0 7000
6 103 02-JAN-26 7000 5000 0

— 计算销售金额的变化
SELECT sales_id, employee_id, sale_date, amount,
amount – LAG(amount, 1, amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS amount_change
FROM fgsales;SALES_ID EMPLOYEE_ID SALE_DATE AMOUNT AMOUNT_CHANGE
———- ———– ———- ———- ————–
1 100 01-JAN-26 10000 0
2 100 02-JAN-26 15000 5000
3 101 01-JAN-26 8000 0
4 101 02-JAN-26 12000 4000
5 103 01-JAN-26 5000 0
6 103 02-JAN-26 7000 2000

7. FIRST_VALUE和LAST_VALUE函数

FIRST_VALUE和LAST_VALUE函数用于获取分区内的第一个和最后一个值。

— 使用FIRST_VALUE和LAST_VALUE函数
SELECT sales_id, employee_id, department_id, amount,
FIRST_VALUE(amount) OVER (PARTITION BY department_id ORDER BY amount DESC) AS first_value,
LAST_VALUE(amount) OVER (PARTITION BY department_id ORDER BY amount DESC) AS last_value
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT FIRST_VALUE LAST_VALUE
———- ———– ————- ———- ———– ———-
6 103 60 7000 7000 7000
5 103 60 5000 7000 5000
2 100 90 15000 15000 15000
4 101 90 12000 15000 12000
1 100 90 10000 15000 10000
3 101 90 8000 15000 8000

— 使用窗口子句
SELECT sales_id, employee_id, department_id, amount,
FIRST_VALUE(amount) OVER (PARTITION BY department_id ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
LAST_VALUE(amount) OVER (PARTITION BY department_id ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM fgsales;SALES_ID EMPLOYEE_ID DEPARTMENT_ID AMOUNT FIRST_VALUE LAST_VALUE
———- ———– ————- ———- ———– ———-
6 103 60 7000 7000 5000
5 103 60 5000 7000 5000
2 100 90 15000 15000 8000
4 101 90 12000 15000 8000
1 100 90 10000 15000 8000
3 101 90 8000 15000 8000

8. 最佳实践

风哥提示：SQL分析函数的最佳实践：
1. 合理使用分析函数，简化复杂的查询逻辑
2. 正确使用PARTITION BY子句，确保数据分组正确
3. 合理使用ORDER BY子句，确保排序顺序正确
4. 对于大型表，考虑使用分析函数替代自连接和子查询
5. 注意分析函数的性能影响，避免在大型结果集上使用复杂的分析函数
6. 合理使用窗口子句，控制分析函数的计算范围
7. 结合其他SQL功能，如CASE语句，增强分析函数的功能
8. 定期分析查询性能，优化分析函数的使用
9. 遵循SQL编码规范，保持分析函数的可读性
10. 测试分析函数的结果，确保计算逻辑正确

生产环境建议：在生产环境中，应合理使用分析函数，避免在大型表上使用过于复杂的分析函数。对于需要频繁执行的分析查询，考虑使用物化视图或结果缓存来提高性能。同时，为分析函数中的排序列创建适当的索引，优化查询性能。

更多视频教程www.fgedu.net.cn

学习交流加群风哥微信: itpux-com

学习交流加群风哥QQ113257174

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html