CHECKSUM 分析函数
本文概述了Oracle database 21c中引入的 CHECKSUM 分析函数。可以用于检查表的内容是否已变更。
一、初始化示例数据
--drop table emp purge;
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)
);
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;二、CHECKSUM 作为聚合函数
CHECKSUM 函数返回一个确定的8字节有符号长整型校验和，并转换为Oracle number 类型。这对于检查表的内容是否已更改非常有用。校验和基于一组行的传入表达式，该表达式不受行顺序的影响。表达式可以是列、常量、绑定变量或组合它们的表达式。它支持除ADT和JSON之外的大多数数据类型。我们可以选择对所有行或不同的行执行操作。
作为聚合函数，它减少了行数，因此称为“聚合”。如果数据没有分组，我们将EMP表中的14行转换为具有聚合值的单行。
select checksum(sal) as checksum_total
from emp;
CHECKSUM_TOTAL
--------------
251201
SQL>DISTINCT、UNIQUE 和 ALL 关键字也可用于分析函数。
我们可以通过包含 GROUP BY 子句，获得更细粒度的信息。在下面的示例中，我们可以对每个部门的薪水求校验和。
select deptno,
checksum(sal) as checksum_dept
from emp
group by deptno
order by deptno;
DEPTNO CHECKSUM_DEPT
---------- -------------
10 47845
20 350390
30 838098
SQL>分析函数允许我们在保留原始行数据的同时返回这些聚合值。
我们向表加增加一行部门编号为“10”的数据，查看它如何影响校验和，并回滚更改。
insert into emp (empno, ename, sal, deptno) values (9999, 'HALL', 1000, 10);
select checksum(distinct sal) as checksum_dept
from emp;
CHECKSUM_DEPT
-------------
826243
SQL>
select deptno,
checksum(sal) as checksum_by_dept
from emp
group by deptno
order by deptno;
DEPTNO CHECKSUM_BY_DEPT
---------- ----------------
10 345922
20 350390
30 838098
SQL>
rollback;我们可以看到这会影响表的整体校验和，并且按部门编号分组查看他其实只影响 group by 查询中部门编号为“10”的校验和。
使用 DISTINCT 或 UNIQUE 关键字意味着仅使用表达式中的唯一值进行计算。ALL 关键字与默认操作相同。
-- ALL : The default action.
select checksum(sal) as checksum_total,
checksum(all sal) as checksum_total_all
from emp;
CHECKSUM_TOTAL CHECKSUM_TOTAL_ALL
-------------- ------------------
251201 251201
SQL>
-- DISTINCT or UNIQUE values.
select checksum(distinct sal) as checksum_total_distinct,
checksum(unique sal) as checksum_total_unique
from emp;
CHECKSUM_TOTAL_DISTINCT CHECKSUM_TOTAL_UNIQUE
----------------------- ---------------------
216548 216548
SQL>DISTINCT、UNIQUE 和 ALL 关键字也可用于分析函数。
三、CHECKSUM 分析函数
CHECKSUM 分析函数的基本定义描述如下。
CHECKSUM "(" [ DISTINCT | ALL ] expr ")" [ OVER "(" analytic_clause ")" ]从 OVER 子句中省略分区子句意味着将整个结果集视为单个分区。在下面的示例中，我们显示薪水校验和以及所有原始数据。
select empno,
ename,
deptno,
sal,
checksum(sal) over () as checksum_total
from emp;
EMPNO ENAME DEPTNO SAL CHECKSUM_TOTAL
---------- ---------- ---------- ---------- --------------
7369 SMITH 20 800 376934
7499 ALLEN 30 1600 376934
7521 WARD 30 1250 376934
7566 JONES 20 2975 376934
7654 MARTIN 30 1250 376934
7698 BLAKE 30 2850 376934
7782 CLARK 10 2450 376934
7788 SCOTT 20 3000 376934
7839 KING 10 5000 376934
7844 TURNER 30 1500 376934
7876 ADAMS 20 1100 376934
7900 JAMES 30 950 376934
7902 FORD 20 3000 376934
7934 MILLER 10 1300 376934
9999 HALL 10 1000 376934
SQL>添加分区子句允许我们返回分区内的校验和。
select empno,
ename,
deptno,
sal,
checksum(sal) over (partition by deptno) as checksum_by_dept
from emp;
EMPNO ENAME DEPTNO SAL CHECKSUM_BY_DEPT
---------- ---------- ---------- ---------- ----------------
7782 CLARK 10 2450 345922
9999 HALL 10 1000 345922
7839 KING 10 5000 345922
7934 MILLER 10 1300 345922
7566 JONES 20 2975 350390
7369 SMITH 20 800 350390
7788 SCOTT 20 3000 350390
7902 FORD 20 3000 350390
7876 ADAMS 20 1100 350390
7844 TURNER 30 1500 838098
7499 ALLEN 30 1600 838098
7900 JAMES 30 950 838098
7521 WARD 30 1250 838098
7654 MARTIN 30 1250 838098
7698 BLAKE 30 2850 838098
SQL>本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2022-03-08，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读