oracle 函数

详细学习请参考oracle数据库官方文档

Oracle 数据库文档

oracle中的greatest 函数和 least函数

数值函数

ORACLE分析函数(8)-WIDTH-BUCKET 会根据参数设置，返回当前记录所属的份数

WIDTH_BUCKET点击查看详情

CUME_DIST
下面的示例计算采购部门中每个员工的薪水百分比。例如，40％的文员的薪水低于或等于Himuro。
SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees

ORDER BY job_id, last_name, salary, cume_dist;

FIRST
LAST
以下示例在示例表的每个部门中返回hr.employees佣金最低的员工的最低薪水和佣金最高的员工的最高薪水：
SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) “Worst”,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) “Best”
FROM employees
GROUP BY department_id
ORDER BY department_id;

下一个示例进行与上一个示例相同的计算，但是返回部门内每个员工的结果：
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) “Worst”,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) “Best”
FROM employees

GROUP_ID的用法

GROUPING_ID的用法

GROUP_ID
GROUPING_ID
Grouping
grou cude
Grou by grouping sets都是用来分组求统计的
select
DEPARTMENT_ID,job_id,
grouping(DEPARTMENT_ID) as div_grp,
grouping(job_id) as job_grp,
grouping_id(DEPARTMENT_ID,job_id) as grp_id,
sum(salary)
from employees
group by cube(DEPARTMENT_ID,job_id)
having grouping_id(DEPARTMENT_ID,job_id) > 0
order by DEPARTMENT_ID,job_id;

oracle中range/rows的区别

SELECT last_name, salary,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND
150 FOLLOWING) AS mov_count
FROM employees

PERCENT_RANK
SELECT department_id, last_name, salary, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
FROM employees


LNNVL
得到与条件相反的结果 空值包含所有范围 要用is null 进行过滤

NTH_VALUE
SELECT DEPARTMENT_ID, MANAGER_ID, MIN(SALARY),
NTH_VALUE(MIN(SALARY), 1) OVER (PARTITION BY DEPARTMENT_ID ORDER BY MANAGER_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
FROM employees
GROUP BY DEPARTMENT_ID, MANAGER_ID
order by DEPARTMENT_ID

NTILE
SELECT last_name, salary, NTILE(7) OVER (ORDER BY salary DESC) AS quartile
FROM employees

PERCENT_RANK

PRESENTNNV
PRESENTV
在原有SQL的 每个月的销售额 的基础上，进一步追加 每个季度 与 全年的销售额。
介绍了根据日期进行操作的方法


TRANSLATE

oracle中CAST函数