LightDB支持group by常量

machete701

于 2024-04-08 15:34:17 发布

阅读量203

点赞数 1

文章标签： oracle postgresql

本文链接：https://blog.csdn.net/u012796067/article/details/137509038

版权

本文讲述了LightDB在Oracle模式下处理常量分组的功能，以及其在动态SQL中的应用场景，包括统计和无分组操作的实用性。

摘要由CSDN通过智能技术生成

功能概述

从24.1版本开始，LightDB oracle兼容模式下支持group by常量，即支持按常量分组聚集，其中分组字段可以有一个或多个常量字段，也可以常量字段和变量以及表达式混合使用。

常量字段用于分组，实际上并没有什么作用，基于常量字段分组，实际上就相当于未分组。例如select count(*) from t group by 'aa'在oracle中完全等价于select count(*) from t。在LightDB的oracle兼容模式下，我们也是这么处理的，即将group by子句中的常量分组字段直接丢弃掉，并在日志信息中记录sql中group by的常量字段已自动丢弃。值得一提的是只是丢弃了常量字段，group by子句中的常量表达式或者函数调用还是保留下来了。

也许你会觉得这样的功能很鸡肋，这里我用一个小例子来说明这个功能存在的意义：
假如要做一个基于不同维度进行统计的功能，前台传入一个分组字段A，后台根据传入字段构造一个动态sql对表进行分组统计即group by A，而当前台传入的分组字段为空时，表示需要对表进行朴素聚集，此时只需将动态sql语句的group by子句拼接为group by 0即可，无需其它修改。

使用案例

CREATE TABLE EMP
(   EMPNO    NUMBER(4, 0),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4, 0),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2, 0),
    DNAME    VARCHAR2(100),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH',  'CLERK',    7902, to_date('1980-12-17','yyyy-mm-dd'), 800.00,  null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN',  'SALESMAN', 7698, to_date('1981-02-20','yyyy-mm-dd'), 1600.00, 300.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD',   'SALESMAN', 7698, to_date('1981-02-22','yyyy-mm-dd'), 1250.00, 500.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES',  'MANAGER',  7839, to_date('1981-04-02','yyyy-mm-dd'), 2975.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','yyyy-mm-dd'), 1250.00, 1400.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7698, 'BLAKE',  'MANAGER',  7839, to_date('1981-05-01','yyyy-mm-dd'), 2850.00, null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7782, 'CLARK',  'MANAGER',  7839, to_date('1981-06-09','yyyy-mm-dd'), 2450.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7788, 'SCOTT',  'ANALYST',  7566, to_date('1987-04-19','yyyy-mm-dd'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7839, 'KING',   'PRESIDENT',null, to_date('1981-11-17','yyyy-mm-dd'), 5000.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','yyyy-mm-dd'), 1500.00, 0.00,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7876, 'ADAMS',  'CLERK',    7788, to_date('1987-05-23','yyyy-mm-dd'), 1100.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7900, 'JAMES',  'CLERK',    7698, to_date('1981-12-03','yyyy-mm-dd'), 950.00,  null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7902, 'FORD',   'ANALYST',  7566, to_date('1981-12-03','yyyy-mm-dd'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7934, 'MILLER', 'CLERK',    7782, to_date('1982-01-23','yyyy-mm-dd'), 1300.00, null,    10, null);



lightdb@oracle=# select 0,
lightdb@oracle-#         count(*) as rowcount ,
lightdb@oracle-#         sum(sal) as sum_salary,
lightdb@oracle-#         sum(comm) as sum_comm
lightdb@oracle-# from emp group by -100;
 ?column? | rowcount | sum_salary | sum_comm 
----------+----------+------------+----------
        0 |       14 |      29025 |     2200
(1 row)

lightdb@oracle=# select 0,
        count(*) as rowcount ,
        sum(sal) as sum_salary,
        sum(comm) as sum_comm,deptno
from emp group by -100,deptno,1,23,'aa';
 ?column? | rowcount | sum_salary | sum_comm | deptno 
----------+----------+------------+----------+--------
        0 |        3 |       8750 |          |     10
        0 |        6 |       9400 |     2200 |     30
        0 |        5 |      10875 |          |     20
(3 rows)

lightdb@oracle=# select mgr,count(*) from emp group by 100,-11111111111.23232423543545657,mgr;
 mgr  | count 
------+-------
      |     1
 7566 |     2
 7782 |     1
 7902 |     1
 7788 |     1
 7839 |     3
 7698 |     5
(7 rows)