Oracle 23ai新特性:使用列别名的 GROUP BY 和 HAVING 子句

摘要

随着数据库技术的不断发展，SQL 语言也在不断进化，以更好地满足数据查询和分析的需求。本文将探讨如何在 SQL 查询中使用列别名（column alias）或列位置（column position）来简化 GROUP BY 和 HAVING 子句，并提高查询的可读性和维护性。

一、引言

在 SQL 查询中，GROUP BY 子句用于将具有相同值的行分组到汇总行中，而 HAVING 子句则用于过滤这些分组的结果。传统上，GROUP BY 和 HAVING 子句要求使用表中的实际列名。然而，在一些情况下，我们希望使用计算字段或表达式的结果作为分组标准或条件。这时，使用列别名或列位置可以显著简化查询并增强其灵活性。

二、列别名的应用

2.1 在 SELECT 和 GROUP BY 中使用列别名

从 Oracle Database 23ai 开始，可以在 SELECT 子句中定义的列别名直接用于 GROUP BY 子句。这意味着你可以先为复杂的表达式指定一个简单易记的名字，然后用这个名字来进行分组操作。

考虑以下示例，假设我们有一个名为 dept 的部门表和一个名为 emp 的员工表，其中包含如下数据：

-- 创建部门表
CREATE TABLE dept (
    deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
    dname VARCHAR2(14),
    loc VARCHAR2(13)
);

-- 插入部门数据
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

-- 创建员工表
CREATE TABLE emp (
    empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
    ename VARCHAR2(10),
    job VARCHAR2(9),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7, 2),
    comm NUMBER(7, 2),
    deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept
);

-- 插入员工数据（部分）
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
commit;

现在，如果我们想要获取每个部门的大写名称以及该部门的员工数量，并且只显示那些员工数超过3个的部门，我们可以这样写查询：

SELECT INITCAP(d.dname) AS department, COUNT(*) AS amount
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY INITCAP(d.dname)
HAVING COUNT(*) > 3;

输出结果可能是这样的：

DEPARTMENT   AMOUNT
------------ ------
Research     5
Sales        6

2.2 在 HAVING 子句中使用列别名

同样的规则也适用于 HAVING 子句。如果查询中包含了聚合函数，并且你想根据这些聚合结果进行过滤，你可以先定义一个别名，然后在 HAVING 中使用该别名。

SELECT INITCAP(d.dname) AS department, COUNT(*) AS amount
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY department
HAVING amount > 3;

这将产生与上面相同的输出。

三 使用列位置

3.1 GROUP BY 和 HAVING 中的列位置

除了使用列别名外，Oracle Database 23ai 还允许我们在 GROUP BY 子句中通过列的位置（即它们在 SELECT 子句中的顺序）来引用列。需要注意的是，这种方法对于 HAVING 子句并不适用，因为数据库无法区分列位置和数值字面量。

还是以上面的示例，无法直接使用数值做聚合操作

SELECT INITCAP(d.dname) AS department, COUNT(*) AS amount
    FROM dept d
    JOIN emp e ON d.deptno = e.deptno
    GROUP BY 1 -- 1 表示 SELECT 子句中的第一个表达式
    HAVING COUNT(*) > 3;
SELECT INITCAP(d.dname) AS department, COUNT(*) AS amount
               *
ERROR at line 1:
ORA-03162: "D"."DNAME": must appear in the GROUP BY clause or be used in an
aggregate function as 'group_by_position_enabled' is FALSE
Help: https://docs.oracle.com/error-help/db/ora-03162/
SQL> 

为了启用这一特性，必须首先设置会话参数：

ALTER SESSION SET group_by_position_enabled = TRUE;

然后，我们可以简化之前的查询如下：

SELECT INITCAP(d.dname) AS department, COUNT(*) AS amount
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY 1 -- 1 表示 SELECT 子句中的第一个表达式
HAVING COUNT(*) > 3;

这段代码同样会产生上述的输出结果。这段代码同样会产生上述的输出结果。

请注意，虽然使用列位置可以减少重复输入长表达式的需要，但它降低了查询的可读性，特别是当查询变得复杂时。因此，建议只在简单的查询中使用列位置，并且在团队协作环境中保持一致性。

四、底层原理

让我们看看当我们使用这个新语法时底层原理是什么。

通过10053事件，可以跟踪到底层的调用逻辑

oradebug setmypid
alter session set events '10053 trace name context forever';
SELECT INITCAP(d.dname) AS department, COUNT(*) AS amount
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY department
HAVING amount > 3;
alter session set events '10053 trace name context off';
oradebug close_trace;
oradebug tracefile_name

trc日志如下

GPUA: Checking validity of union-all group-by pushdown  for query block SEL$58A6D7F6 (#1)
GPUA: kkqgpuaCheckValidity - final check SEL$58A6D7F6 (#1)
GPUA: kkqgpuaCheckValidity : qb SEL$58A6D7F6 (#1):******* UNPARSED QUERY IS *******
SELECT INITCAP("D"."DNAME") "DEPARTMENT",COUNT(*) "AMOUNT" FROM "SYS"."DEPT" "D","SYS"."EMP" "E" WHERE "D"."DEPTNO"="E"."DEPTNO" GROUP BY INITCAP("D"."DNAME") HAVING COUNT(*)>3
GPUA: Failed validity - no view SEL$58A6D7F6 (#1)

可以看到底层仍然调用的未加别名的列。

五、结论

使用列别名或列位置可以在一定程度上简化 GROUP BY 和 HAVING 子句的编写，特别是在处理复杂表达式时。然而，这种便利性不应该牺牲查询的清晰度和维护性。开发者应当权衡利弊，选择最适合项目需求的方法。同时，了解你的 RDBMS 的具体行为也很重要，因为不同数据库系统可能对这一特性的支持程度有所不同。