OracleDB组函数

Oracle DB组函数

&# ; 组函数

– 类型和语法

– 使用AVG SUM MIN MAX COUNT

– 在组函数中使用DISTINCT关键字

– 组函数中的NULL值

何谓组函数

组函数会对行集进行计算 为每个组提供一个结果

与单行函数不同 组函数用于对行集进行计算 从而为每个组提供一个结果 这些集合可以包含整个表 也可以包含表分割成的组

组函数的类型

&# ; AVG

&# ; COUNT

&# ; MAX

&# ; MIN

&# ; STDDEV

&# ; SUM

&# ; VARIANCE

每个函数都接受一个参数 下表列出了在语法中可使用的选项

组函数 语法

SELECT group_function(column)

FROM table

[WHERE condition]

[ORDER BY column];

组函数 语法

组函数应放在SELECT关键字之后 可以使用逗号分隔多个组函数

使用组函数的准则

&# ; DISTINCT使函数仅考虑非重复值;ALL使函数考虑每个值(包括重复值) 默认值为ALL 因此无需指定

&# ; 使用expr参数的函数的数据类型可以是CHAR VARCHAR NUMBER或DATE

&# ; 所有组函数都忽略空值 要用一个值替代空值 使用NVL NVL COALESCE CASE或DECODE函数

使用AVG和SUM函数

可以对数字数据使用AVG和SUM函数

hr@TEST > SELECT AVG(salary) MAX(salary) MIN(salary) SUM(salary) FROM employees WHERE job_id LIKE %REP% ;

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)

可以对能够存储数字数据的列使用AVG SUM MIN和MAX函数 示例显示所有销售代表的月薪平均值 最高值 最低值与总和

使用MIN和MAX函数

可以对数字 字符和日期数据类型使用MIN和MAX函数

hr@TEST > SELECT MIN(hire_date) MAX(hire_date) FROM employees;

MIN(HIRE_DATE) MAX(HIRE_DATE)

JAN APR

可以对数字 字符和日期数据类型使用MAX和MIN函数 示例显示任职时间最短和最长的雇员

下面的示例显示在包含所有雇员的列表中 按字母顺序排列姓氏时位于首位及位于末位的雇员姓氏

hr@TEST > SELECT MIN(last_name) MAX(last_name) FROM employees;

MIN(LAST_NAME) MAX(LAST_NAME)

Abel Zlotkey

注 AVG SUM VARIANCE和STDDEV函数仅可用于处理数字数据类型 MAX和MIN函数不能用于处理LOB或LONG数据类型

使用COUNT函数

COUNT()将返回表中的行数

hr@TEST > SELECT COUNT() FROM employees WHERE department_id = ;

COUNT()

示例显示部门 中雇员的数量

COUNT(expr)将返回expr为非空值的行的数量

hr@TEST > SELECT COUNT(mission_pct) FROM employees WHERE department_id = ;

COUNT(MISSION_PCT)

示例显示部门 中可以获得佣金的雇员的数量

COUNT函数有以下三种格式

&# ; COUNT()

&# ; COUNT(expr)

&# ; COUNT(DISTINCT expr)

COUNT()用于返回表中符合SELECT语句标准的行数 包括重复行和在任何列中含有空值的行 如果SELECT语句中包含WHERE子句 则COUNT()会返回符合WHERE子句中条件的行数 相反 COUNT(expr)返回由expr标识的列中非空值的数量 COUNT(DISTINCT expr)返回由expr标识的列中不同非空值的数量

使用DISTINCT关键字

&# ; COUNT(DISTINCT expr)将返回expr的不同非空值的数量

&# ; 要显示EMPLOYEES表中不同部门值的数量 可使用

hr@TEST > SELECT COUNT(DISTINCT department_id) FROM employees;

COUNT(DISTINCTDEPARTMENT_ID)

使用DISTINCT关键字可以避免对某一列中的任何重复值进行计数

示例显示EMPLOYEES表中不同部门值的数量

组函数和空值

组函数将忽略列中的空值

hr@TEST > SELECT AVG(mission_pct) FROM employees;

AVG(MISSION_PCT)

仅根据在表的MISSION_PCT列中存储了有效值的行计算平均值 平均值的计算方法是用付给所有雇员的佣金总和除以获得佣金的雇员数( )

NVL函数会强制组函数包括空值

hr@TEST > SELECT AVG(NVL(mission_pct )) FROM employees;

AVG(NVL(MISSION_PCT ))

根据表中的所有行计算平均值 不考虑MISSION_PCT列中是否存储空值 平均值的计算方法是用付给所有雇员的佣金总和除以公司中的雇员总数( )

lishixinzhi/Article/program/Oracle/201311/18770

使用窗口函数可以方便地实现滚动求和 *** 作。窗口函数是一种特殊的函数，它可以在计算时将数据划分为多个窗口，并在每个窗口内进行计算。

在Oracle数据库中，可以使用窗口函数中的SUM()函数实现滚动求和 *** 作，具体步骤如下：

1 选择需要进行滚动求和计算的列，并按照需要的分组方式进行分组。

2 使用SUM()函数进行滚动求和计算。在SUM()函数中，使用OVER子句指定窗口的大小和偏移量，例如：

SUM(column) OVER (ORDER BY order_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

其中，ORDER BY子句指定按照哪一列进行排序，ROWS BETWEEN子句指定窗口的大小和偏移量，本例中表示当前行及其前两行的和。

3 执行SQL语句，即可得到每个窗口内的滚动求和结果。

需要注意的是，使用窗口函数进行滚动求和计算可以提高计算效率，避免了使用子查询等方式进行计算时的性能问题。此外，Oracle数据库中还支持其他窗口函数，如AVG()、MAX()、MIN()等，可以根据需要进行选择和使用。

除了窗口函数，Oracle数据库还提供了许多其他的高级功能和特性，如分区表、索引、游标等，可以帮助用户更加高效地管理和处理数据。

对时间初始化？

取当前时间：

select sysdate from dual;

截取到日期：

select trunc(sysdate) from dual;

用字符串转换：

select to_date('2016-11-24 10:30:10','yyyy-mm-dd hh24:mi:ss') from dual;

select to_date('20161124103010','yyyymmddhh24miss') from dual;

select to_date('2016/11/24 10:30:10','yyyy/mm/dd hh24:mi:ss') from dual;

select to_date('20161124','yyyymmdd') from dual;

诸如此类

PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序 在SQL中Oracle内建了一系列函数 这些函数都可被称为SQL或PL/SQL语句 函数主要分为两大类: 单行函数; 组函数本文将讨论如何利用单行函数以及使用规则 SQL中的单行函数SQL和PL/SQL中自带很多类型的函数 有字符 数字 日期 转换 和混合型等多种函数用于处理单行数据 因此这些都可被统称为单行函数 这些函数均可用于SELECT WHERE ORDER BY等子句中 例如下面的例子中就包含了TO_CHAR UPPER SOUNDEX等单行函数 SELECT ename TO_CHAR(hiredate day DD Mon YYYY )FROM empWhere UPPER(ename) Like AL% ORDER BY SOUNDEX(ename)单行函数也可以在其他语句中使用 如update的SET子句 INSERT的VALUES子句 DELET的WHERE子句 认证考试特别注意在SELECT语句中使用这些函数 所以我们的注意力也集中在SELECT语句中 NULL和单行函数 在如何理解NULL上开始是很困难的 就算是一个很有经验的人依然对此感到困惑 NULL值表示一个未知数据或者一个空值 算术 *** 作符的任何一个 *** 作数为NULL值 结果均为提个NULL值 这个规则也适合很多函数 只有CONCAT DECODE DUMP NVL REPLACE在调用了NULL参数时能够返回非NULL值 在这些中NVL函数时最重要的 因为他能直接处理NULL值 NVL有两个参数:NVL(x x ) x 和x 都式表达式 当x 为null时返回X 否则返回x 下面我们看看emp数据表它包含了薪水 奖金两项 需要计算总的补偿column name emp_id salary bonuskey type pk nulls/unique nn u nnfk table datatype number number numberlength 不是简单的将薪水和奖金加起来就可以了 如果某一行是null值那么结果就将是null 比如下面的例子:update empset salary=(salary+bonus) 这个语句中 雇员的工资和奖金都将更新为一个新的值 但是如果没有奖金 即 salary + null 那么就会得出错误的结论 这个时候就要使用nvl函数来排除null值的影响 所以正确的语句是:update empset salary=(salary+nvl(bonus ) 单行字符串函数 单行字符串函数用于 *** 作字符串数据 他们大多数有一个或多个参数 其中绝大多数返回字符串ASCII()c 是一字符串 返回c 第一个字母的ASCII码 他的逆函数是CHR()SELECT ASCII( A ) BIG_A ASCII( z ) BIG_z FROM empBIG_A BIG_z CHR()[NCHAR_CS]i是一个数字 函数返回十进制表示的字符select CHR( ) CHR( ) CHR( ) FROM empCHR CHR CHR A z BCONCAT( )c c 均为字符串 函数将c 连接到c 的后面 如果c 为null 将返回c 如果c 为null 则返回c 如果c c 都为null 则返回null 他和 *** 作符||返回的结果相同select concat( slobo Svoboda ) username from dualusernameslobo SyobodaINITCAP()c 为一字符串 函数将每个单词的第一个字母大写其它字母小写返回 单词由空格 控制字符 标点符号限制 select INITCAP( veni vedi vici ) Ceasar from dualCeasarVeni Vedi ViciINSTR( [ [ ]])c c 均为字符串 i j为整数 函数返回c 在c 中第j次出现的位置 搜索从c 的第i个字符开始 当没有发现需要的字符时返回 如果i为负数 那么搜索将从右到左进行 但是位置的计算还是从左到右 i和j的缺省值为 select INSTR( Mississippi i ) from dualINSTR( MISSISSIPPI I ) select INSTR( Mississippi i ) from dualINSTR( MISSISSIPPI I ) INSTRB( [ i[ j])与INSTR()函数一样 只是他返回的是字节 对于单字节INSTRB()等于INSTR()LENGTH()c 为字符串 返回c 的长度 如果c 为null 那么将返回null值 select LENGTH( Ipso Facto ) ergo from dualergo LENGTHb()与LENGTH()一样 返回字节 lower()返回c的小写字符 经常出现在where子串中select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE %white% COLORNAMEWinterwhiteLPAD( [ ])c c 均为字符串 i为整数 在c 的左侧用c 字符串补足致长度i 可多次重复 如果i小于c 的长度 那么只返回i那么长的c 字符 其他的将被截去 c 的缺省值为单空格 参见RPAD select LPAD(answer ) padded answer unpadded from question;PADDED UNPADDED Yes YesNO NOMaybe maybeLTRIM( )把c 中最左边的字符去掉 使其第一个字符不在c 中 如果没有c 那么c 就不会改变 select LTRIM( Mississippi Mis ) from dualLTRppiRPAD( [ ])在c 的右侧用c 字符串补足致长度i 可多次重复 如果i小于c 的长度 那么只返回i那么长的c 字符 其他的将被截去 c 的缺省值为单空格 其他与LPAD相似RTRIM( )把c 中最右边的字符去掉 使其第后一个字符不在c 中 如果没有c 那么c 就不会改变 REPLACE( [ ])c c c 都是字符串 函数用c 代替出现在c 中的c 后返回 select REPLACE( uptown up down ) from dualREPLACEdowntownSTBSTR( [ ])c 为一字符串 i j为整数 从c 的第i位开始返回长度为j的子字符串 如果j为空 则直到串的尾部 select SUBSTR( Message ) from dualSUBSMessSUBSTRB( [ ])与SUBSTR大致相同 只是I J是以字节计算 SOUNDEX()返回与c 发音相似的词select SOUNDEX( dawes ) Dawes SOUNDEX( daws ) Daws SOUNDEX( dawson ) from dualDawes Daws DawsonD D D TRANSLATE( )将c 中与c 相同的字符以c 代替select TRANSLATE( fumble uf ar ) test from dualTEXTrambleTRIM([[]] from c )将c 串中的第一个 最后一个 或者都删除 select TRIM( space padded ) trim from dual TRIMspace paddedUPPER()返回c 的大写 常出现where子串中select name from dual where UPPER(name) LIKE KI% NAMEKING 单行数字函数 单行数字函数 *** 作数字数据 执行数学和算术运算 所有函数都有数字参数并返回数字值 所有三角函数的 *** 作数和值都是弧度而不是角度 oracle没有提供内建的弧度和角度的转换函数 ABS()返回n的绝对值ACOS()反余玄函数 返回 到 之间的数 n表示弧度select ACOS( ) pi ACOS( ) ZERO FROM dualPI ZERO ASIN()反正玄函数 返回 到 n表示弧度ATAN()反正切函数 返回n的反正切值 n表示弧度 CEIL()返回大于或等于n的最小整数 COS()返回n的余玄值 n为弧度COSH()返回n的双曲余玄值 n 为数字 select COSH(< >) FROM dualCOSH( ) EXP()返回e的n次幂 e= FLOOR()返回小于等于N的最大整数 LN()返回N的自然对数 N必须大于 LOG( )返回以n 为底n 的对数MOD()返回n 除以n 的余数 POWER( )返回n 的n 次方ROUND( )返回舍入小数点右边n 位的n 的值 n 的缺省值为 这回将小数点最接近的整数 如果n 为负数就舍入到小数点左边相应的位上 n 必须是整数 select ROUND( ) ROUND( ) FROM dualROUND( ) ROUND( ) SIGN()如果n为负数 返回 如果n为正数 返回 如果n= 返回 SIN()返回n的正玄值 n为弧度 SINH()返回n的双曲正玄值 n为弧度 SQRT()返回n的平方根 n为弧度TAN()返回n的正切值 n为弧度TANH()返回n的双曲正切值 n为弧度TRUNC( )返回截尾到n 位小数的n 的值 n 缺省设置为 当n 为缺省设置时会将n 截尾为整数 如果n 为负值 就截尾在小数点左边相应的位上 单行日期函数 单行日期函数 *** 作DATA数据类型 绝大多数都有DATA数据类型的参数 绝大多数返回的也是DATA数据类型的值 ADD_MONTHS( )返回日期d加上i个月后的结果 i可以使任意 lishixinzhi/Article/program/Oracle/201311/17021

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后基于组计算某种统计值，并且每一组的每一行都可以返回一个统计值。

说白了,分析函数就是 over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，根据什么来呢？over提供一个窗口,使用partition by进行分组，在组内使用order by进行排序。over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

用于合计的函数:

用于排列的函数:

其他:

而这样使用就是分析函数:

它们得出的结果是相同的，都是：

请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回20行数据，即earnings表的每行记录都将返回一行总收入，因为不用distinct的含义是：针对每个打工者计算他/她所在的月份和地区的总收入。

在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。下面通过几个实例来介绍排序分析函数的用途。

问题：统计每个月份,不同地区工资最高的前3名。

利用我们传统的聚合函数max可以方便地取出工资最高的一个员工,但是取出多个就无能为力了,同样，如果不分组我们可以通过排序取出工资最高的前3名，但无法实现对多个月份和地区的分组。而采用rank()分析函数，可以方便地实现我们的要求。

完整的语句如下:

结果为：

我们在开窗函数over()中使用earnmonth(月份)和area(地区)作为分组标志,并按照personincome(收入)倒序排列。

注意:RANK()函数有3组,分别是rank(), dense_rank(), row_number(),它们的区别是：

RANK()如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相同时,rank的结果是1,2,2,4;而dense_rank()则不会跳过这个排名,结果是1,2,2,3;而row_number()哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4

完整的语句如下：

结果为：

完整的语句如下：

结果为：

结果为:

结果为:

在以上例子中,是用rollup()和cube()函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的

grouping函数用法，带一个参数，参数为字段名，如果当前行是由rollup或者cube汇总得来的，结果就返回1，反之返回0

完整语句如下:

结果为:

以上语句统计结果和如下语句使用group by的查询结果一样:

说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦:

语法如下：

其中：

统计每个打工者上个月和下个月有没有赚钱(personincome大于0即为赚钱):

结果为:

在Oracle数据库中，可以使用DBMS_SCHEDULER来实现每天自动执行存储过程的功能。DBMS_SCHEDULER是Oracle提供的一种任务调度工具，可以用来定时执行SQL脚本、存储过程等。

下面是使用DBMS_SCHEDULER实现每天自动执行存储过程的基本步骤：

创建存储过程：首先需要创建一个需要定时执行的存储过程。

创建作业：使用DBMS_SCHEDULER创建一个作业，指定需要执行的存储过程和执行时间。例如，可以创建一个每天固定时间执行的作业。

BEGIN

DBMS_SCHEDULERCREATE_JOB (    job_name        => 'MY_JOB',    job_type        => 'STORED_PROCEDURE',    job_action      => 'MY_PROCEDURE',    start_date      => SYSDATE,    repeat_interval => 'FREQ=DAILY; BYHOUR=12; BYMINUTE=0; BYSECOND=0;',    enabled         => TRUE);END;

在上面的示例中，'MY_PROCEDURE'是需要执行的存储过程名称，'BYHOUR=12; BYMINUTE=0; BYSECOND=0;'表示每天12点执行。

启用作业：使用DBMS_SCHEDULER启用创建的作业。

BEGIN

DBMS_SCHEDULERENABLE('MY_JOB');END;

在上面的示例中，'MY_JOB'是需要启用的作业名称。

检查作业状态：可以使用DBMS_SCHEDULER提供的视图和函数来检查作业的状态和执行历史记录。

-- 查询作业状态SELECT JOB_NAME, STATE FROM USER_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_JOB';-- 查询作业执行历史记录SELECT JOB_NAME, START_DATE, END_DATE FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB';

以上是使用DBMS_SCHEDULER实现每天自动执行存储过程的基本步骤。需要根据实际情况进行调整和优化。

Max(列)， AVG(列)

任意数的阶乘：

CREATE PROCEDURE PROCEDURENAME

@INUPT INT

AS

DECLARE @RESULT INT

WHILE @INTUPT>0

BEGIN

RESULT=@INPUT(@INPUT-1)

SET @INPUT=@INPUT-1

END

SELECT RESULT=@RESULT

GO

如：exec PROCEDURENAME 9

像查询一样调用这个存储过程，就会得到你要的数的阶乘。

以上就是关于OracleDB组函数全部的内容，包括:OracleDB组函数、oracle数据库怎么用窗口函数实现滚动求和、oracle数据库中对时间初始化的函数等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！