Oracle Database 19c 新特性之 ANY_VALUE 聚合函数

Oracle ANY_VALUE 函数

最新推荐文章于 2024-06-26 12:18:52 发布

原创最新推荐文章于 2024-06-26 12:18:52 发布 · 2.5k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #sql

Oracle 专栏收录该内容

54 篇文章

订阅专栏

介绍了 Oracle19c 中新增的 ANY_VALUE 聚合函数，它可以为分组后的每个组返回一个任意值，解决了查询字段不属于 GROUP BY 字段的问题。与 MIN 或 MAX 函数相比，ANY_VALUE 函数性能更优。

ANY_VALUE 函数是 Oracle 19c 新增的一个聚合函数，可以为分组操作之后的每个组返回一个任意值，可以解决查询字段不属于 GROUP BY 字段的问题。

假如我们想要获取每个部门中的员工数量，可以使用 COUNT 函数和 GROUP BY 子句（示例数据）：

SELECT d.dept_id, d.dept_name, COUNT(e.emp_id)
FROM department d
LEFT JOIN employee e 
ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;

DEPT_ID|DEPT_NAME|COUNT(E.EMP_ID)|
-------+---------+---------------+
      1|行政管理部|              3|
      2|人力资源部|              3|
      3|财务部   |              2|
      4|研发部   |              9|
      5|销售部   |              8|
      6|保卫部   |              0|

我们需要将所有非聚合函数中的字段（d.dept_id 以及 d.dept_name）写在 GROUP BY 子句中，否则查询将会返回错误。例如：

SELECT d.dept_id, d.dept_name, COUNT(e.emp_id)
FROM department d
LEFT JOIN employee e 
ON e.dept_id = d.dept_id
GROUP BY d.dept_id;

SQL 错误 [979] [42000]: ORA-00979: 不是 GROUP BY 表达式

在以上查询中，d.dept_name 字段由 d.dept_id 字段唯一决定，逻辑上并不需要出现在 GROUP BY 子句中，但是 SQL 语法上必须这样写。另外，GROUP BY 中的字段会带来额外的性能开销。一个常用的解决方法就是使用 MIN 或者 MAX 函数。例如：

SELECT d.dept_id, MAX(d.dept_name), COUNT(e.emp_id)
FROM department d
LEFT JOIN employee e 
ON e.dept_id = d.dept_id
GROUP BY d.dept_id;

DEPT_ID|MAX(D.DEPT_NAME)|COUNT(E.EMP_ID)|
-------+----------------+---------------+
      1|行政管理部        |              3|
      2|人力资源部        |              3|
      3|财务部           |              2|
      4|研发部           |              9|
      5|销售部           |              8|
      6|保卫部           |              0|

这种方法可以避免在 GROUP BY 子句中使用额外的字段，但是新增的 MIN 或者 MAX 函数仍然会带来一定的性能开销。

为了彻底解决这个问题，我们可以使用 ANY_VALUE 聚合函数。该函数的使用方法和 MIN 或者 MAX 相同，但是它的开销更小，因为它会直接返回组内第一个非空的数据。例如：

SELECT d.dept_id, ANY_VALUE(d.dept_name), COUNT(e.emp_id)
FROM department d
LEFT JOIN employee e 
ON e.dept_id = d.dept_id
GROUP BY d.dept_id;

DEPT_ID|ANY_VALUE(D.DEPT_NAME)|COUNT(E.EMP_ID)|
-------+----------------------+---------------+
      1|行政管理部              |              3|
      2|人力资源部              |              3|
      3|财务部                 |              2|
      4|研发部                 |              9|
      5|销售部                 |              8|
      6|保卫部                 |              0|

除此之外，当我们想要为每个部门返回一个任意的员工姓名，也可以使用 ANY_VALUE 函数实现：

SELECT d.dept_id, ANY_VALUE(d.dept_name), ANY_VALUE(e.emp_name), COUNT(e.emp_id)
FROM department d
LEFT JOIN employee e 
ON e.dept_id = d.dept_id
GROUP BY d.dept_id;

DEPT_ID|ANY_VALUE(D.DEPT_NAME)|ANY_VALUE(E.EMP_NAME)|COUNT(E.EMP_ID)|
-------+----------------------+---------------------+---------------+
      1|行政管理部              |张飞                  |              3|
      2|人力资源部              |诸葛亮                |              3|
      3|财务部                 |孙尚香                |              2|
      4|研发部                 |赵氏                  |              9|
      5|销售部                 |法正                  |              8|
      6|保卫部                 |                     |              0|