前言

经常做数据分析的伙伴，sql是必要掌握的技能之一，然而目前大多数学校教材仅限于一些基本函数的用法。oracle数据库每个版本都有一些新的函数被引入，如果能熟练掌握相关函数的使用方法，应该会对工作有所帮助。

墨天轮已上线oracle数据库最新版本21c的在线实训环境，开箱即用，很适合用来做这些新函数的测试。

下面我会在每个版本中挑出一些比较实用的函数进行测试。

准备测试环境

Oracle 21c 实训环境

请先按照 墨天轮Oracle 21c实训环境操作手册 初始化HR Schema Demo，并登陆hr用户。

12c

1.APPROX_COUNT_DISTINCT

作用接近于count(distinct ),但效率更快，因为是取的近似值，在数据量很大且不要求数据绝对准确时可以使用

SELECT department_id,
       APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
  FROM employees
 group by department_id;

2.TO_APPROX_COUNT_DISTINCT /APPROX_COUNT_DISTINCT_AGG/APPROX_COUNT_DISTINCT_DETAIL

这三个函数其实是APPROX_COUNT_DISTINCT的分解，需要组合使用，来达到和APPROX_COUNT_DISTINCT类似的效果。
如果你只对一个维度进行去重统计，当然用count(distinct )或者APPROX_COUNT_DISTINCT就好了，但是如果你要对同一个数据的多个维度进行去重统计，按照常规的方式，每个维度的去重都需要对整个数据重新进行一次扫描。
比如统计 部门+职位的不同薪水个数 和 部门的不同薪水个数，常规写法是这样的

SELECT DEPARTMENT_ID,
        JOB_ID,
        count(distinct SALARY) SALARY_COUNT
   FROM employees
  group by DEPARTMENT_ID, JOB_ID;

SELECT DEPARTMENT_ID,
        count(distinct SALARY) SALARY_COUNT
   FROM employees
  group by DEPARTMENT_ID;

你无法基于上一个结果来得到下一个结果，只能从原始数据重新查。但是ORACLE新增了这三个函数后，你可以这样使用

--先创建一张 基于部门加职位的去重薪水个数 的临时表，或者用视图/物化视图/with as 都行
CREATE TABLE TEST_APPROX_T AS 
 SELECT DEPARTMENT_ID,
        JOB_ID,
        APPROX_COUNT_DISTINCT_DETAIL(SALARY) SALARY_COUNT
   FROM employees
  group by DEPARTMENT_ID, JOB_ID;

--使用 TO_APPROX_COUNT_DISTINCT 将数据转化成可识别的数据  
select DEPARTMENT_ID,
       JOB_ID,
       TO_APPROX_COUNT_DISTINCT(SALARY_COUNT) SALARY_COUNT
  from TEST_APPROX_T;

--根据上一个结果，使用APPROX_COUNT_DISTINCT_AGG聚合，统计到部门的去重薪水个数
with t1 as
 (select DEPARTMENT_ID, APPROX_COUNT_DISTINCT_AGG(SALARY_COUNT) SALARY_COUNT
    from TEST_APPROX_T
   GROUP BY DEPARTMENT_ID)
select DEPARTMENT_ID, TO_APPROX_COUNT_DISTINCT(SALARY_COUNT) from T1;

这个函数之所以能向上继续聚合，是因为APPROX_COUNT_DISTINCT_DETAIL和APPROX_COUNT_DISTINCT_AGG并没有把最终结果计算出来，而是把明细数据都转换成了二进制数据，该结构有利于数据库进行快速计算，所以在向上聚合时，它几乎保留了完整的原始数据但数据行数却大大减小，省去了普通count(distinct)的重复开销

3.APPROX_MEDIAN

这个其实是MEDIAN的近似函数，作为一个聚合函数，它能快速返回一个中位值

SELECT department_id "Department",
         MEDIAN(salary) "MEDIAN",
         APPROX_MEDIAN(salary) "APPROX_MEDIAN",
         APPROX_MEDIAN(salary DETERMINISTIC) "APPROX_MEDIAN_DET ",
         APPROX_MEDIAN(salary , 'ERROR_RATE') "ERROR_RATE",
         APPROX_MEDIAN(salary DETERMINISTIC, 'ERROR_RATE') "ERROR_RATE_DET",
         APPROX_MEDIAN(salary , 'CONFIDENCE') "CONFIDENCE",
         APPROX_MEDIAN(salary DETERMINISTIC, 'CONFIDENCE') "CONFIDENCE_DET"
    FROM hr.employees
   GROUP BY department_id
   ORDER BY department_id;

这里出现了 DETERMINISTIC 、ERROR_RATE 、CONFIDENCE 三个词，其中
加DETERMINISTIC的时候，统计的列只能为数字或者能to_number成数字的，如果是要取非数字的中位值，比如日期，就不能加上 DETERMINISTIC；
ERROR_RATE意为错误率，返回的值表示这种近似统计的中位值错误率大概为多少
CONFIDENCE意为置信度，返回的值表示这种近似统计的可信度有多高，其实满足 置信度+错误率=1

4.STANDARD_HASH

标准哈希

select STANDARD_HASH('123','MD5') FROM DUAL;

第二个参数为要使用的算法，支持SHA1, SHA256, SHA384, SHA512, MD5

5.TO_BLOB/TO_CLOB/TO_CHAR

在12c版本以前，要对CLOB/BLOB/BFILE/RAW/VARCHAR2等类型的数据进行转换时，经常要用dbms_lob包写一大段转换逻辑，而12c中新加的这些函数，大大方便了这些类型的转换

TO_BLOB (bfile)
TO_BLOB (raw)
TO_CHAR (bfile|blob)
TO_CLOB (bfile|blob)
TO_CLOB (character)

举一个例子：

--以sys重新连接
sqlplus / as sysdba
--创建目录
create directory test_images as 
'/opt/oracle/product/21c/dbhomeXE/python/lib/python3.9/site-packages/sklearn/datasets/images';
--查询
SELECT TO_BLOB(bfilename('TEST_IMAGES','china.jpg'), 'JPEG') FROM dual;

6.VALIDATE_CONVERSION

校验一个字符串是否可以转换成其他类型，如果返回1，则说明可以转换；返回0则说明不能转换

SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('1234.56' AS BINARY_FLOAT)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('July 20, 1969, 20:18' AS DATE,
    'Month dd, YYYY, HH24:MI', 'NLS_DATE_LANGUAGE = American')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('200 00:00:00' AS INTERVAL DAY TO SECOND)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('P1Y2M' AS INTERVAL YEAR TO MONTH)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('$100,00' AS NUMBER,
    '$999D99', 'NLS_NUMERIC_CHARACTERS = '',.''')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('29-Jan-02 17:24:00' AS TIMESTAMP,
    'DD-MON-YY HH24:MI:SS')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('1999-12-01 11:00:00 -8:00'
    AS TIMESTAMP WITH TIME ZONE, 'YYYY-MM-DD HH:MI:SS TZH:TZM')
  FROM DUAL;

SELECT VALIDATE_CONVERSION('11-May-16 17:30:00'
    AS TIMESTAMP WITH LOCAL TIME ZONE, 'DD-MON-YY HH24:MI:SS')
  FROM DUAL;

下面是不能转换的例子

SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT)
  FROM DUAL;

SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT, '$99D99')
  FROM DUAL;

18c

1.APPROX_RANK/APPROX_SUM/APPROX_COUNT

这几个函数带上了approx前缀，所以他们分别是RANK（排行）、sum（求和）、count（计数）这几个函数对应的近似函数，但是实际使用又有很大的区别。
首先，使用这几个函数时必须是如下结构

select 
...
APPROX_SUM(...),---或APPROX_COUNT(...)
...
from 
...
GROUP BY ...
HAVING 
  APPROX_RANK (... order by ) [<>=] ? 

这是因为，这几个函数存在的意义就是，在做聚合统计数据时，我们有时候只想看到排行前几名的统计值，而常规写法就是先对所有数据进行聚合，然后排名，再根据排名过滤，这样实际上有大量的计算并不会在最终的结果体现,我们也并不会关心前几名以外的统计数据。

而此函数用近似的算法，先用having的条件把基础数据过滤掉，再进行统计，效率快了很多。ORACLE把这个叫 “近似TOP-N”。

SELECT department_id, job_id, 
       APPROX_SUM(salary) 
FROM   hr.employees
GROUP BY department_id, job_id
HAVING 
  APPROX_RANK ( 
  PARTITION BY department_id 
  ORDER BY APPROX_SUM(salary) 
  DESC ) <= 10;

比如此例就是每个department_id 合计salary排行前十的job_id，对应的每个job_id的salary。

2.ROUND_TIES_TO_EVEN

四舍五入，具有和round(number)函数相同的输入参数（但不支持BINARY_FLOAT和BINARY_DOUBLE这两种类型），目的补充round(number)函数不能实现的场景，它不仅判断要舍去部分的四舍五入，还会判断要保留的有效数字的最后一位是大于等于5还是小于5

--小数点往右一位四舍五入
SELECT ROUND_TIES_TO_EVEN (0.05, 1) from DUAL;
--小数点往左一位四舍五入
SELECT ROUND_TIES_TO_EVEN(45.177,-1) FROM DUAL;
--第二个参数不传时默认为0，即保留整数部分
select ROUND_TIES_TO_EVEN(4.5) from dual;
--☆☆注意和round返回的区别☆☆
select ROUND_TIES_TO_EVEN(4.5) A,
       round(4.5) B,
       ROUND_TIES_TO_EVEN(5.5) C,
       round(5.5) D
  from dual;
--round支持binary_double和BINARY_FLOAT类型的数值传入
select round(cast(3.14 as binary_double)) from dual;
--下例返回报错
select ROUND_TIES_TO_EVEN(cast(3.14 as binary_double)) from dual;

3.LISTAGG

LISTAGG可以将一列数据用指定的分隔符拼接成一个长字符串，以达到行列转换的效果，它不是18c添加的函数，但是有修改语法，可以省去“within GROUP”子句

with t as 
(select 'A' NAME,-2 VALUE FROM DUAL UNION ALL
select 'B',3 from dual union all
select 'A',-1 from dual union all
select 'A',5 from dual union all
select 'B',2 from dual 
)
SELECT LISTAGG(NAME)  res,LISTAGG(NAME,',') res2  FROM t ;

19c

1.ANY_VALUE

作为聚合函数，返回任意一个值
以前我们可能会这么写

select A.EMPLOYEE_ID, 
       B.FIRST_NAME, 
       MIN(A.START_DATE) MIN_START_DATE
  from JOB_HISTORY A, EMPLOYEES B
 WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID
 GROUP BY A.EMPLOYEE_ID, B.FIRST_NAME;

或者这么写,目的是减少聚合的维度来提高sql执行效率，因为一个EMPLOYEE_ID肯定对应一个FIRST_NAME，没必要再放在group by 后面

select A.EMPLOYEE_ID, 
       max(B.FIRST_NAME) FIRST_NAME, 
       MIN(A.START_DATE) MIN_START_DATE
  from JOB_HISTORY A, EMPLOYEES B
 WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID
 GROUP BY A.EMPLOYEE_ID;

而现在可以这么写了，因为上例的MAX还是得计算，而使用ANY_VALUE则会取扫描到的第一个记录，效率比上面更快

select A.EMPLOYEE_ID,
       ANY_VALUE(B.FIRST_NAME) FIRST_NAME,
       MIN(A.START_DATE) MIN_START_DATE
  from JOB_HISTORY A, EMPLOYEES B
 WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID
 GROUP BY A.EMPLOYEE_ID;

21c

1.CHECKSUM

用于检查表中的数据是否发生变化，比如

select job_id, checksum(a.salary)
  from EMPLOYEES a
 where job_id in ('AC_ACCOUNT', 'AC_MGR')
 group by job_id;

update EMPLOYEES set SALARY=SALARY*2 where EMPLOYEE_ID=206;
commit;

select job_id, checksum(a.salary)
  from EMPLOYEES a
 where job_id in ('AC_ACCOUNT', 'AC_MGR')
 group by job_id;

还可以这样

select  checksum(a.first_name||a.last_name||a.salary)
  from EMPLOYEES a;

以此来快速定位数据是否有被修改，这在某些增量传输数据的场景下很有用

2.BIT_AND_AGG/BIT_OR_AGG/BIT_XOR_AGG

位聚合运算（与、或、异或）
其实这3个函数和BITAND/BITOR/BITXOR的区别在于，
BITAND/BITOR/BITXOR只能对传入的两个参数进行位运算，
BIT_AND_AGG/BIT_OR_AGG/BIT_XOR_AGG是对传入的列进行位运算，返回一个值，支持group by 聚合

3.SKEWNESS_POP/SKEWNESS_SAMP

两个聚合函数，SKEWNESS_POP为总体偏度，SKEWNESS_SAMP为样本偏度，用于确定给定数据分布的对称性

select a.department_id, SKEWNESS_POP(salary)
  from hr.employees a
 group by department_id
;
select a.department_id, SKEWNESS_SAMP(salary)
  from hr.employees a
 group by department_id
;

4.KURTOSIS_POP/KURTOSIS_SAMP

两个聚合函数，KURTOSIS_POP为总体峰度，KURTOSIS_SAMP为样本峰度，用于确定给定数据分布的异常值特征

select a.department_id, KURTOSIS_pop(salary)
  from hr.employees a
 group by department_id
;
select a.department_id, KURTOSIS_SAMP(salary)
  from hr.employees a
 group by department_id
;

以上均为基于墨天轮oracle21c的在线实训环境做的相关测试。

由于机器学习模型、APEX应用、文本处理、图形二进制数据处理等在本环境下不方便操作，因此暂不包含相关内容。

参考Oracle官方文档SQL Language Reference

如有错误，请联系作者更正

• 本文作者： DarkAthena
• 版权许可协议：CC BY-NC-SA 3.0 。转载请注明出处！