29.ANY_VALUE

作用：作为聚合函数，返回任意一个值（19c版本才添加的）。
比如常见的一个查询，关联商品基础信息表和销售明细表，对销售明细进行汇总，然后同时又要显示商品编码和商品名称，大多数情况下都是这么写的

select a.item_id,a.item_name,sum(b.sale) 
from item_info a,sale_detail b
where a.item_id=b.item_id
group by a.item_id,a.item_name

但是我们明知在商品信息表中，商品编码是唯一键，一个商品编码肯定只会对应一个商品名称，何必要再group by商品名称呢，这样不会浪费oracle的性能么？
所以，这个sql有了以下变体

select a.item_id,max(a.item_name),sum(b.sale) 
from item_info a,sale_detail b
where a.item_id=b.item_id
group by a.item_id

这样效率会比上面快不少，但是，max这个动作还是进行了一些计算，所以就有了any_value这个函数，这种情况下，使用any_value速度会更快，因为它不需要进行计算，读到哪条就是哪条

select a.item_id,any_value(a.item_name),sum(b.sale) 
from item_info a,sale_detail b
where a.item_id=b.item_id
group by a.item_id

30.APPROX_RANK/APPROX_SUM/APPROX_COUNT

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

GROUP BY ...
HAVING 
  APPROX_RANK (... order by ) [<>=] ?

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

而此函数用近似的算法，先用having的条件把基础数据过滤掉，再进行统计，效率快了很多

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合计

参考
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/APPROX_SUM.html
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/APPROX_COUNT.html
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/APPROX_RANK.html

31.APPROX_MEDIAN

这个实际上是MEDIAN的近似函数，因为上面3个用法差异太大，和这个不是一个套路，所以分开来说

SELECT department_id "Department",
       APPROX_MEDIAN(salary) "Median Salary"
  FROM hr.employees
  GROUP BY department_id
  ORDER BY department_id;

看上去用法几乎和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

实际上ERROR_RATE和CONFIDENCE在其他近似函数中也有些是可以使用的，这里就不展开了。

这里提醒一下，尽管ORACLE官方希望大家优先使用近似函数，但是以我实测的经验来看，小量的数据尽量不要用近似函数，由于样本数不足的原因，偏差会太大，这个时候错误率和置信度都不靠谱

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

Department	MEDIAN	APPROX_MEDIAN_DET	ERROR_RATE_DET	CONFIDENCE_DET	E
10	4400	4400	0.01	0.99	0
20	9500	6000	0.03	0.99	-0.37
30	2850	2800	0.03	0.99	-0.02
40	6500	6500	0.01	0.99	0
50	3100	3100	0.02	0.99	0
60	4800	4800	0.02	0.99	0
70	10000	10000	0.01	0.99	0
80	8900	8800	0.03	0.99	-0.01
90	17000	17000	0.03	0.99	0
100	8000	7800	0.02	0.99	-0.03
110	10154	8300	0.02	0.99	-0.18
7000	7000	0.01	0.99	0

加了一列手动计算的差异百分比，可以明显看到 37% 和函数提供的 3% 相差巨大。

参考
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/APPROX_MEDIAN.html

31.NEXT_DAY

作用:返回指定日期的下个指定星期几是哪一天

  select next_day(date '2021-10-08', '星期一'),
         next_day(date '2021-10-08', 2)
    from dual;

NEXT_DAY(DATE'2021-10-08','星期一')	NEXT_DAY(DATE'2021-10-08',2)
2021-10-11	2021-10-11

第二个参数支持2种表达形式，
第一种是该数据库会话环境对应的星期描述，可能是中文也可能是英文,如果是中文日期环境输入英文描述就会报错，反之亦然。
第二种是一个星期的第几天，星期天是一个星期的第一天，所以传2就是代表星期一了

为了代码兼容性，一般建议不要使用星期描述，应该采用直接传数字的方式，如果一定要传描述，那么建议做如下操作


declare
  l_tmp varchar2(200);
  L date;
begin
  --获取当前会话的日期语言
  select value
    into l_tmp
    from v$nls_parameters H
   WHERE H.PARAMETER = 'NLS_DATE_LANGUAGE';
   --修改成AMERICAN
  execute immediate q'{ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'}';
  --使用英文星期描述进行操作
  select next_day(date '2021-10-08', 'MONDAY') INTO L from dual;
  --将当前会话的日期语言还原
  execute immediate q'{ALTER SESSION SET NLS_DATE_LANGUAGE='}' || l_tmp || '''';
end;

32.NULLIF

作用：比较传入的两个参数，如果相等则返回空，如果不等则返回第一个参数
等价于 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

看上去很难想到使用场景，ORACLE官方举个例子，假设我们要出一张报表，要求给出每个人在本次公司组织架构调整后，之前的职位和当前的职位，如果职位没有调整，则之前的职位或者新的职位显示为空，

SELECT e.last_name, NULLIF(j.job_id, e.job_id) "Old Job ID"
  FROM employees e, job_history j
  WHERE e.employee_id = j.employee_id
  ORDER BY last_name, "Old Job ID";

但是这貌似还是不太实用，那么我举另一个例子，
我们偶尔会要出一张存在多个维度的报表，比如
|省区|门店|昨日销售额|
|-|-|-|
|湖南省区|分店1|10000
|湖南省区|分店2|10000
|湖南省区|分店3|10000
|四川省区|分店4|10000
|四川省区|分店5|10000

前面省区会出现重复的值，在报表中，用户认为只有一个值就够了，其他为空，即类似于透视表合并单元格的效果，像下面这样

省区	门店	昨日销售额
湖南省区	分店1	10000
	分店2	10000
	分店3	10000
四川省区	分店4	10000
	分店5	10000

这个时候NULLIF就有用了，按照官方的例子，一般会局限于一行内的两个值，其实oracle是有函数获取上面一个值或者下面一个值的，即使用开窗函数

select nullif(EMPLOYEE_ID, lag(EMPLOYEE_ID) over(order by EMPLOYEE_ID)) "nullif",
       a.EMPLOYEE_ID
  from hr.job_history a

nullif	EMPLOYEE_ID
101	101
	101
102	102
114	114
122	122
176	176
	176
200	200
	200
201	201

33.RATIO_TO_REPORT

作用：返回每行该值占此列的百分比

一般情况下，我们计算占比的时候，都是先算好汇总值，然后再用每行的数据去除这个汇总，实际上对此表进行两次查询，如果使用RATIO_TO_REPORT则只需要查一次

SELECT last_name,
       salary,
       round(RATIO_TO_REPORT(salary) OVER(), 4) * 100 || '%' AS rr
  FROM hr.employees
 WHERE job_id = 'PU_CLERK'
 ORDER BY last_name, salary, rr

LAST_NAME	SALARY	RR
Baida	2900	20.86%
Colmenares	2500	17.99%
Himuro	2600	18.71%
Khoo	3100	22.3%
Tobias	2800	20.14%

另外，此函数由于是over分析函数，所以同样支持partition by 子句来进行分组求占比。

有举一反三习惯的伙伴们这个时候可能会有点难受，从这个函数名 ratio_to_report来看，ratio意思就是占比了，report是报表或者报告，但一个报告肯定不会局限于看占比，那有没有其他的 to_report函数呢？
答案是：目前没有！
所以这个函数起名就很怪异了。。。

34.SOUNDEX

作用：输入任何英文字符串，返回四个字符，作为一个相似发音的代码

select soundex('an apple'),soundex('an ipol') from dual;

SOUNDEX('ANAPPLE')	SOUNDEX('ANIPOL')
A514	A514

这四个字符中，第一位其实是输入字符串的第一个字母。这个函数其实就是用来比较两个字符串发音是否类似的。

官方给的例子是这样的

SELECT last_name, first_name
     FROM hr.employees
     WHERE SOUNDEX(last_name)
         = SOUNDEX('SMYTHE')
     ORDER BY last_name, first_name;

LAST_NAME  FIRST_NAME
---------- ----------
Smith      Lindsey
Smith      William

想象下，如果和语音识别及人工智能结合，用人声发出自然语言指令，比如说“告诉我，我们这里有几个叫史密斯的”，语音识别部分转化出一个句子，然后openai转化成查询sql，但是可能存在发音和史密斯很像但又不是史密斯这几个字的，比如斯迷斯之类的，这样就可以结合这个soundex函数找到更多“叫”史密斯的了

35.STANDARD_HASH

其实前面有提到hash，但是这个STANDARD_HASH函数用起来更简单，不用转来转去

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

STANDARD_HASH('123','MD5')
202CB962AC59075B964B07152D234B70

呵，熟悉的“202C”，不过别太高兴，这个函数是12C版本才添加的，11g及以下没有

36.WIDTH_BUCKET

作用：设定一个等距的组，返回传入参数所在的组数。

SELECT a.FIRST_NAME,
       a.SALARY,
       WIDTH_BUCKET(a.SALARY, 7000, 10000, 2) sa_group
  FROM hr.employees a
 where a.department_id = 100
 order by SALARY

FIRST_NAME	SALARY	SA_GROUP
Luis	6900	0
Ismael	7700	1
Jose Manuel	7800	1
John	8200	1
Daniel	9000	2
Nancy	12008	3

第一个参数是要分组的值，第二个是分组的最小值，第三个是分组的最大值，第四个是要分几组，如果超出分组范围，比如实际值小于设定的最小值，那么就会归到第0个组去，如果大于，则分到设定组数加1的那个组去。

这里开下脑洞，有个常见的需求场景，要查出多个区间的数据，是不是想起被一堆case when支配的恐惧？后来会了pivot后，就尝试先用除法来做一个分组列，再用pivot给转换上去，但是列数却不能确定，因为简单除法只能实现等距，最大分多少组没有定。

所以这个时候，WIDTH_BUCKET这个函数的出现就解决了这个痛点。

这里我们尝试用常见的函数实现一下WIDTH_BUCKET的效果，方便理解，假设传入的4个参数分别为
expr, min_value, max_value, num_buckets

case
         when :expr < :min_valuethen
          0
         when :expr > :max_valuethen
          :num_buckets + 1
         else
          trunc((:expr - :min_value+1) / ((:max_value- :min_value+ 1) / :num_buckets)) + 1
       end

参考
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/WIDTH_BUCKET

目录CONTENT

【ORACLE】收集一些较为少见但很有用的SQL函数及写法.part5