sql语句--分析函数

1. 常见的分析函数包括：

评级函数：可计算等级、排名、百分点、 n 分片（三分片、四分片等）。

窗口函数：计算一定的记录范围内或者一定时间内的和、平均值、最大值、最小值等。

分析函数的语法如下：

analytic_function([ arguments ]) over(analytic_clause)

其中：

1) analytic_function ：分析函数的名称。

2) arguments ：分析函数所带参数，内置分析函数一般带 0-3 个参数。参数可以是任何数字

类型或是可以隐式转换为数字类型的数据类型。 Oracle 根据最高数字优先级别确定函数参数，

并且隐式地将需要处理的参数转换为数字类型。

3) over ：用以标识函数是一个分析函数，对于既可作为聚集函数又可作为分析函数的函数，必

须用 over 来标识此函数为分析函数。 over 关键字是必须的，是分析函数就必须使用。 over

后面小括号中是 analytic_clause, 即使 analytic_clause 的三个部分都不使用，小括号

也不能省略，否则 oracle 因不能识别函数为分析函数而报错。

4) analytic_clause 的语法如下：

[query_partition_clause] [order_by_clause [ windowing_clause]]

query_partition_clause 是查询分组子句；

order_by_clause 是分组排序子句；

windowing_clause 是窗口范围子句。

1. 常见的评级函数

• 根据不同的排名方式：

SELECT t.*, RANK() OVER(ORDER BY salary DESC) rk

FROM hr.employees t;

SELECT t.*, DENSE_RANK() OVER(ORDER BY salary DESC) drk

FROM hr.employees t;

• 为每一条记录返回一个数字（需要在数据中增加序列时，可使用 row_number()函数。）

SELECT t.*, ROW_NUMBER() OVER(ORDER BY salary DESC) rn

FROM employees t;

• 数据中有 id 相同的两条记录时，进行去重

CREATE TABLE hr.emp_test

AS SELECT * FROM employees WHERE employee_id=105;

INSERT INTO hr.emp_test VALUES

('105','Jack','Zhang','AHUNOLD','515.131.4369',

to_date('2015-9-30','yyyy-mm-dd'),

'FI_MGR',4200.00,null,100,50);

SELECT * FROM

( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY employee_id

ORDER BY hire_date) rn

FROM hr.emp_test t)

WHERE rn = 1;

说明： 表中有两位员工的员工号均为 105，后入职的员工工号分配错误，按照员工工号进行分 组，并按入职时间进行排序，选出排序为 1 的记录，就是员工号正确的员工信息。 row_number() over 函数的一种常见用法是当数据中有 id 相同的两条记录时，可按照时 间等字段进行排序，然后继续记录筛选，从而完成去重操作。

2.窗口函数

窗口可以结合这 些函数使用：SUM()、AVG()、MAX()、MIN()、COUNT()等。窗口也可以和 FIRST_VALUE 和 LAST_VALUE 结合使用，用来返回窗口中的第一个值和最后一个值。

SELECT t.*,

SUM(salary) OVER(PARTITION BY department_id) sum_sal_dept,

AVG(salary) OVER(PARTITION BY department_id) avg_sal_dept ，

SUM(salary) OVER(PARTITION BY 1) sum_sal

FROM hr.employees t;

说明：当需要不同类型的计算值，以及不同维度的计算值，并要求使用一条语句实现时，就

可以在语句中使用窗口函数，不同窗口中的分组排序互不影响。