Oracle有哪些和百分比相关的函数总结

原创于 2025-08-26 15:32:49 发布 · 423 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

数据库同时被 2 个专栏收录

124 篇文章

订阅专栏

Oracle

46 篇文章

订阅专栏

Oracle数据库中与百分比相关的函数主要分为两类：标准标量函数和开窗函数（分析函数）。开窗函数在这方面功能更强大，是处理百分比计算的首选。

以下是详细的分类和说明：

一、标准标量函数 (Scalar Functions)

这类函数通常用于简单的数值百分比格式转换或基础计算。

TO_CHAR (配合数字格式模型)
- 功能：将数字转换为特定格式的字符串，其中包含百分比符号。
- 原理：它并不进行计算，只是对现有数字进行格式化和显示。数字 0.25 经过格式化后会显示为 25%。
- 语法示例：
```
SELECT TO_CHAR(0.25, 'FM9990.00%') AS formatted_percent FROM dual;
-- 结果： '25.00%'

SELECT TO_CHAR(commission_pct, 'FM990.00%') AS comm_pct FROM employees;
-- 将数值类型的佣金比例字段显示为百分比格式
```
ROUND, TRUNC, 等算术函数
- 功能：常用于百分比计算过程中的精度控制。
- 场景：先进行百分比计算，再用这些函数处理结果。
- 语法示例：
```
SELECT ROUND((achieved / target) * 100, 2) AS achievement_pct
FROM sales_data;
-- 计算达成率并四舍五入到小数点后2位
```

二、开窗函数 (Analytic Functions) - 核心工具

这是进行复杂百分比计算最常用和强大的工具。它们通过对一个“窗口”内的数据进行计算来得出结果。

RATIO_TO_REPORT

功能： 计算某个值在其所属窗口分区内总和所占的比例。结果是一个介于 0 到 1 之间的小数，要得到百分比通常需要乘以 100。
常用场景：计算每个人的销售额占全组/全公司的百分比。

语法示例：

SELECT department_id,
       last_name,
       salary,
       -- 计算每个员工薪水在其部门总薪水的占比
       ROUND(RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id), 4) * 100 AS pct_in_dept
FROM employees;
-- 注意：结果已经是比例，乘以100后才是百分比数值

PERCENT_RANK

功能：计算一行数据在其所在窗口分区中的百分位排名。公式为：(当前行的排名 - 1) / (分区内的总行数 - 1)。
结果范围：返回 0 到 1 之间的值。表示有多少比例的数据排在该行之前。
常用场景： “你的薪水超过了公司百分之多少的人？”

语法示例：

SELECT department_id,
       last_name,
       salary,
       -- 计算部门内薪水的百分位排名
       ROUND(PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary), 4) * 100 AS percentile_rank
FROM employees;
-- 如果某行的结果为 0.95，意味着它的薪水超过了部门内95%的人。

CUME_DIST (累积分布)

功能：计算一行数据在其所在窗口分区中的累积分布。即值小于等于当前行值的行数在分区总行数中所占的比例。
结果范围：返回 (0, 1] 之间的值。
与 PERCENT_RANK 的区别：
- PERCENT_RANK：关注的是排名位置的比例。
- CUME_DIST：关注的是数据值的分布比例。

语法示例：

SELECT department_id,
       last_name,
       salary,
       -- 计算部门内薪水的累积分布
       ROUND(CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary), 4) * 100 AS cumulative_distribution
FROM employees;
-- 如果某行的结果为 0.60，表示部门中60%的人的薪水小于或等于他的薪水。

NTILE

功能：将一个分区内的数据有序地平均划分到指定数量的桶（Bucket）中，并返回每行所属的桶编号。
常用场景：进行百分位数分段，如将数据按成绩分为4个四分位（Quartile）、10个十分位（Decile）或100个百分位（Percentile）。

语法示例：

SELECT last_name,
       salary,
       -- 将全体员工按薪水高低分为4个组（即四分位）
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
       -- 将全体员工按薪水高低分为100个组（即百分位）
       NTILE(100) OVER (ORDER BY salary DESC) AS percentile
FROM employees;
-- 如果某行的percentile结果为1，表示它位于前1%（最高的一档）。

PERCENTILE_CONT 和 PERCENTILE_DISC (逆分布函数)
- 功能：与上述函数相反，它们根据指定的百分位值来返回对应的数据值。通常用于计算中位数、四分位数等。
- 区别：
  - PERCENTILE_CONT：基于连续分布模型，如果指定的百分位介于两个值之间，会进行线性插值返回一个结果。
  - PERCENTILE_DISC：基于离散分布模型，返回在累计分布中第一个大于等于指定百分位的值。
- 常用场景： “公司薪资的中位数（50%分位点）是多少？”
- 语法示例：
```
SELECT department_id,
       -- 计算部门薪水中位数（连续）
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) AS median_cont,
       -- 计算部门薪水中位数（离散）
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) AS median_disc
FROM employees;
```