零点起飞学Oracle之数据的复杂处理.pptx

第8章数据的复杂处理 到目前为止 前面章节已经讲解了如何从数据库选择 select 记录 where子句如何限定返回满足所定义条件的行的数量 但有时 在访问数据库时 经常需要对表中的某列数据进行统计分析 比如求其最大值 最小值 平均值 方差等 所有这些针对其中一列或者多列数据的分析就叫做聚合分析 Oracle中的聚合函数就是可以是实现数据的聚合分析 8 1统计数据 聚合函数返回基于多个行的单一结果 聚集函数可以在select或者select的having子句中使用 当用于select子句时 常常与groupby子句一起使用 如果要处理表中的数据 可以使用聚合函数 8 1 1求最大值 求最大值表示在一组数据中找到其中最大的数 在实际查询中 经常需要获取一组记录中特定列的最大值 Oracle中提供max函数来解决该问题 根据列的数据类型 可以分为以下三种情况 1 max 函数应用于数值型max 函数应用于数值型时 是按照数值的大小顺序来获得最大值 示例8 1 为了获得薪资最高的员工的薪资情况 可以利用如下所示的SQL语句 8 1 1求最大值 2 max 函数应用于字符型当max 函数应用于字符类型数据 则会按照字母的编码值找出最大值 示例8 2 在表employees中 列first name为字符型 可以利用max 函数来获得其最大值 3 max 函数应用于日期型日期型的实质也是数值型 因此 max 函数应用于日期型与数值型具有完全相同的效果 获得最晚日期 示例8 3 在表employees中 列hire date为日期型 可以利用max 函数来获得公司最晚来的员工的进入公司的时间 8 1 2求最小值 求最小值表示在一组数据中找到其中最小的数 Oracle中 min 函数可以获得最小值 与max 函数相反 min 函数同样可应用于数值型 字符型和日期型 示例8 3 在表employees中 可以利用min 函数来获得last name的最小值 8 1 3求平均值 平均值表示对一组数据求平均值的运算 Oracle中 avg 函数用于获得一组数据的平均值 该函数只能应用于数值型 示例8 4 为了获得表employees中所有员工的平均工资 可以利用如下SQL语句 8 1 4求和 求和的含义是依次对数据进行累加 得到数据的总和 Oracle中 sum 函数用于获得一组数据的和 该函数同样只能应用于数值型 示例8 5 为了获得表employees中所有员工薪资之和 可以利用如下SQL语句 8 1 5统计记录数 统计是用来对一组数据进行统计个数的 Oracle中 count 函数用于计算表中记录的个数或者列中值的数目 计算内容用select语句指定 使用count函数时 必须指定一个列的名称 其语法结构如下 count count column 第一行表示计算表中的行的总数 即使表中的行的数据是null 也被计入在内 第二行表示计算列包含的行的数目 如果该列中的某行数据位null 则该行不计入统计总数 8 1 5统计记录数 该函数的常见使用场景有三种 1 统计单列统计单列表示将一个列名作为count 函数的参数 当列值不为空时 将被统计在内 否则将不统计 示例8 6 我们可以首先向表employees中插入新的数据 并比较空值和非空值的统计情况 目前 表employees中的数据中 employee id为 207 的员工的first name的列值为空 可以利用count 函数来比较存在空值与不存在空值在统计数目时的区别 2 统计所有列统计所有列表示将表的所有列被作为count 函数的参数 这种情况下 即使所有列值均为空 Oracle仍将进行计数 8 1 5统计记录数 3 利用count 1 进行统计对于count 函数来说 count 1 与统计所有列count 表示的含义的相同的 示例8 8 利用count 1 的形式统计表employees中的记录 8 2Oracle中常用技巧 Oracle除了针对基本数据类型的各种函数之外 还有一些特殊函数 如为空值重新赋值 nvl 函数 结果集的行号 rownum 函数 强制转换数据类型 cast 函数 8 2 1空值处理 nvl 函数 数据库中的数据有时出现空值 nvl 函数可以判断表达式的值是否为空 如果为空 则可以返回该函数设置的新值 若不为空 则返回原值 其使用语法如下所示 nvl 表达式 新值 表达式 nvl 函数首先判断第一个表达式参数的值是否为空 如果为空 则返回第二个参数的值 如果不为空 则返回第一个参数表达式的值 示例8 10 表employees表中存储了员工工资信息 我们可以通过查询获得员工编号以及薪资信息 8 2 2结果集的行号 rownum 函数 rownum函数可以返回结果集的行号 返回第一行 分配的是1 返回第二行 分配的是2 1 利用rownum为搜寻结果添加一列 示例8 11 rownum的最简单应用场景是为搜寻结果添加一列 如下所示 2 rownum与orderby子句 示例8 12 对表中数据排序 然后获得排名在某个范围之内的记录是一种常见需求 例如 用户可能要求获得表employees中 按员工姓名排序前7位的员工信息 一种常见的思路为综合利用rownum与orderby子句来排序并增加过滤条件 rownum小于等于7 8 2 2结果集的行号 rownum 函数 3 rownum与比较运算符 rownum是oracle系统顺序分配为从查询返回的行的编号 返回的第一行分配的是1 第二行是2 依此类推 rownum可以与比较运算符 的结合使用 示例8 13 现需要获得表employees中 行号为1的员工信息 最容易想到的SQL语句如下所示 8 2 3强制转换数据类型 cast 函数 Oracle中的cast 函数可以强制转换列或变量的数据类型 即将内部类型或集合类型的值转换为另一种集合类型或者内部类型 其使用语法如下所示 cast 原数据as新的数据类型 示例8 15 利用一个已有表来创建一个新表 在创建过程中 使用cast 函数将列的数据类型进行转换 例如 表jobs存储了员工职位信息 其数据结构如下所示 8 3常见数据运算 运算符是一种符号 用来指定要在一个或者多个表达式中执行的操作 执行列 常量或者变量之间的数学运算和比较操作 表达式是标识符 值和运算符的组合 简单的表达式可以是一个常量 函数 列名 变量与子查询 可以用运算符将两个或更多的简单表达式连接起来组成复杂的表达式 运算就是根据数学法则进行计算 Oracle中的常用运算包括数学运算 逻辑运算 比较运算和按位运算 本节将就这几种运算进行简要介绍 8 3 1数学运算 数学运算是用于执行数字型表达式的算术运算 Oracle中的数学运算包括加 减 乘 除 四种 无论操作数是何种数据类型 都将首先转换为数值型 然后才参与运算 示例8 16 本示例演示四种基本数学运算及不同数据类型的操作数 8 3 2逻辑运算 逻辑运算符用来对逻辑条件进行测试 已获得真实情况 它与比较运算符一样 根据测试结果返回布尔值true null false Oracle中的逻辑运算包括 大于运算 可用于数值型 日期型和字符串类型的比较 大于等于运算 可用于数值型 日期型和字符串类型的比较 不等于 可用于数值型 日期型和字符串类型的比较 与用法相同 NOT 取反操作 AND 布尔值的与操作 OR 布尔值的或操作 示例8 16 需要注意的是 无论哪种逻辑运算 只要其中一个操作数据为null 运算结果一定为假 8 3 3按位运算 按位运算即允许按照位来操作整型变量 Oracle仅仅提供了bitand 函数来实现按位与运算 按位或与按位异或 则可以利用bitand 函数间接获得 8 3 3按位运算 1 按位与Oracle中最常用的按位运算为按位与运算 按位与运算是双目运算符 它的作用是使参与运算的两数各对应的二进位相与 只有对应的两个二进位均为1时 结果位才为1 否则为0 每个十进制整数都可以转换为二进制 按位与运算应该使用bitand 函数 该函数有两个参数 其使用语法如下所示 bitand 数值1 数值2 当然 如果数值参数不为整数 Oracle总是先将其转换为整数 转换规则为直接截取整数部分 然后才进行运算 8 3 3按位运算 示例8 17 与运算的本质是 如果两个运算位均为1 将返回为1 否则返回为0 对于一个整数 我们有时很想知道其二进制形式下某位为1还是0 那么 可以利用bitand来实现 例如 判断数字189二进制形式的第6位是0或者1 我们可以通过分解数字189为二进制 并与32的二进制形式进行与运算 如图所示 8 3 3按位运算 2 按位或Oracle并未提供专门的函数来实现按位或运算 但是我们可以通过bitand 函数来间接实现 示例8 18 对于表达式x y 其本质如图所示 以随机数字189 57为例 8 3 3按位运算 3 按位异或异或运算规则为 两个值不相同 则异或结果为真 反之 为假 概括为 不同为1 相同为0 示例8 19 Oracle同样未提供直接计算按位异或的函数 而利用已有知识 我们同样可以很容易的推导出按位异或的运算公式 我们首先利用随机数189和57来查看按位异或的计算方式 如图所示 8 4特殊数据运算 Oracle进行条件查询时 除了使用比较表达式作为搜索条件之外 还可以使用简单的特殊判式 在where子句中可以使用多个搜索条件选择记录 这些判式包括 BETWEEN 范围测试 IN 集合成员测试 LIKE 模糊匹配 ISNULL 空值判断 EXITS 存在性判断 ALL SOME ANY 数量判断 8 4 1范围测试 在Oracle中的where子句中 使用between关键字可以方便的限制查询数据的范围 使用between关键字查询时包含了边界值 使用notbetween关键字查询限定数据范围之外的记录 其使用语法如下所示betweenxandy其中 x和y限定了范围的临界值 示例8 20 利用between判式获得表employees中薪资处于2000 5000之间的员工信息 8 4 2集合成员测试 同between关键字一样 in判式也是为了更好更方便地检查数据的范围 使用in关键字一般是在子查询中 示例8 21 使用in关键字 结合表employees departments查询所有部门在某一地区 1400 的员工信息 表employees测试哪些员工在该集合中 8 4 3模糊匹配 在实际应用中 用户不会总是能给出精确的查询条件 因此 经常需要根据一些并不确切的线索来搜索信息 这种情况下 即可使用模糊查询 Oracle中提供了like判式来实现模糊匹配 通常情况下 like子句会和通配符配合使用 使用通配符可以实现更为复杂的模糊查询条件 以下主要讲解这些通配符的使用 1 匹配任意字符串 对于like判式 最常用的通配符为 该通配符可替代个数不确定的字符 示例8 22 在表employees中获得所有员工的的姓是以 K 开头的员工信息 那么可以利用匹配符 8 4 3模糊匹配 2 匹配单个字符 对于like判式 另外一个常用通配符为 该通配符可用于匹配任意的单个字符 示例8 23 对于示例8 22 使用通配符 来代替 3 原义字符在like判式中 通配符 和 有着特殊的意义 但是有时候用户希望判断标准中出现了这两个字符 那么将需要二者的原义字符 示例8 24 对于一个字符串 例如百分比 30 用户希望判断是否该字符串以 结尾 那么直接在判式中使用 无法达到预期要求 8 4 4空值判断 在数据库中 null值是一个特定的术语 用来描述记录中没有定义内容的字段值 通常 我们称之为空 在Oracle中 判断某个条件的值时 不能使用 来判断一个列或表达式的值是否为空 而是利用特殊判式isnull 示例8 25 查询表employees中 员工的department id为空的员工信息 8 4 5存在性判断 在一般情况下 在查询时 如果只是需要考虑是否满足条件 而对数据本身没有要求的话 就可以用exists判式 exists判式的操作对象是结果集 当结果集中记录数大于0时 将返回为真 否则 将返回为假 exists判式一般也用在子查询中 示例8 26 exists往往可以作为in判式的替代语法 结合表employees departments查询所有部门在某一地区 1400 的员工信息 表employees测试哪些员工在该集合中 8 4 6集合判断 在查询过程 有时往往需要针对集合进行逻辑运算 此时就需要用some any和all对子查询中返回的多行结果进行处理 Some在此表示满足其中一个的意义 any也表示满足其中一个的意义 All则表示满足其中所有的查询结果的含义 使用and串起来的比较从句 8 4 6集合判断 示例8 27 在表employees中存储了员工薪资 salary 现需要获得薪资大于所有IT部门的员工信息 IT部门的department id是60 此时 可以使用all判式 来将列salary与集合进行比较 利用all判式来实现列与集合进行逻辑判断 示例8 28 除了all判式之外 Oracle还提供了另外一个类似的判式some和any 从语义上分析可以很容易得出结论 只要对集合中的某个值的逻辑运算为真 那么条件即可成立 相当于对多个逻辑判式使用了or连接运算 而some和any可以实现完全相同的功能 在示例8 27中 利用some或any代替all判式 相当于获得表employees中年龄大于任一位IT部门的薪资 搜寻条件即可成立 8 5分析函数与窗口函数 Oracle提供了强大的分析函数与窗口函数 这极大增强了Oracle的查询功能 分析函数和窗口函数也为报表统计等复杂查询提供了更多的解决方案 本节将讲述分析函数与窗口函数在Oracle中的应用 主要内容包括 分析函数与窗口简介 窗口详解 主要的分析函数 8 5 1分析函数与窗口函数简介 在前面所讲述的函数中 普通函数总是针对单条记录 而聚合函数则针对整个结果集 而对于分析函数和窗口函数 它们总是结合使用 所作用的对象与普通函数和聚合函数有着极大的不同 它们总是为每条记录准备与之相关的一个新的结果集 分析函数就作用于结果集之上 而这些结果集就类似于 窗口 对于每条记录 窗口函数可以为其提供一个结果集 类似于窗口 当然 图中所演示的结果集窗口只是一种示例 至于窗口的真实大小 则由窗口函数的实际定义决定 8 5 2排名函数 最常用的分析函数是排名函数 排名函数用于返回当前记录在窗口中的位置 常用的排名函数包括 rank dense rank 和row number 因为排名函数的返回值总是依赖于记录的次序 因此 排名函数所对应的窗口函数必须指定排序规则 orderby子句 而Oracle为每条记录提供的默认窗口为 表中第一条记录开始 直至与当前记录具有相同排名的所有记录 8 5 2排名函数 1 rank 函数的使用 示例8 29 现欲获得表employees中的员工 按照年龄大小升序排列 每位员工的排名情况 2 dense rank 函数的使用 示例 30 与rank 函数不同 dense rank 函数返回的排名则不具有跳跃性 3 row number 函数的使用 示例8 31 row number 函数单纯返回当前记录在窗口中的所处位置的行号 这里的行号具有唯一性 利用row number 函数来代替dense rank 函数 8 5 3默认窗口 在上面示例中 只使用了orderby来定义窗口 此时的窗口为 按照排序规则 第一条记录至当前排名的所有记录 这里值得注意的是 由于排序时关键字相同 造成多条记录具有相同排名 此时将出现多条记录具有相同的窗口 我们可以通过另外一个函数count 来查看窗口中实际的记录数目来验证这以情况 8 5 4分区窗口 对于分析函数来说 默认窗口往往并不常用 而另外一种窗口 分区窗口则相对常用的多 分区窗口是指与当前记录拥有相同的分区标准的所有记录 创建分区窗口的语法如下所示 partitionby列名partitionby语句首先根据列名获得当前记录的列值 接着获得表中具有相同列值的所有记录 并将该记录集合作为当前记录的窗口 8 5 4分区窗口 示例8 32 对于表employees中的员工记录 我们希望获得员工姓名和薪资的同时 也获得该部门员工的平均薪资 即可使用分区窗口 示例8 33 在分区窗口中 同样可以对其中的记录进行排序 例如 在获得员工信息的同时 获得在同职位中 按薪资大小排序的位置 则可以同时利用partitionby与orderby来定义窗口 8 5 5窗口子句 对于每条记录 一旦使用了窗口函数 都有对应的窗口记录集合 而使用窗口子句则可以进一步限制窗口范围 而这样的窗口子句一般以两种方式出现 利用rows子句进行限制利用range子句进行限制 8 5 5窗口子句 1 rows子句rows子句的使用前提为窗口已经利用orderby进行了排序 当窗口中的记录经过排序 当前记录一定处于某个位置 那么可以利用rows子句 按照位置向前或向后追溯 以进一步限制窗口大小 其使用语法如下所示 over orderby列名rowsbetween位移量precedingand位移量following 在rows子句中 利用preceding向前追溯 排序之后 处于当前记录之前的记录 利用following向后追溯 排序之后 处于当前记录之后的记录 示例8 34 在表employees中 除了可以获得当前员工信息 还希望获得当前薪资和相邻的两位员工的薪资总和 则可以综合利用rows子句进行限制 8 5 5窗口子句 2 range子句rows子句以相对位置作为获取记录的标准 而range子句则以相对列值作为筛选记录的标准 其使用语法如下所示 over orderby列名rangebetween差值precedingand差值following 在range子句中 利用preceding向前追溯 排序之后 处于当前记录之前的记录 利用following向后追溯 排序之后 处于当前记录之后的记录 追溯的范围为当前列值 差值 示例8 35 在表employees中 除了可以获得当前员工信息 还希望获得与当前员工薪资相差1000之内的员工数目 则可以利用range子句 8 5 5窗口子句 3 currentrow与unbounded窗口子句中 除了可以利用确定的数值来限定窗口之外 还可以之间使用currentrow来指定当前记录 使用unbounded来代替数值 表示不受限制的窗口范围 示例8 36 currrentrow将直接定位当前记录 例如 在表employees中 可以利用综合利用unbounded和currentrow获得排序之后 第一条记录至当前记录的窗口大小 示例8 37 当然 也可以对rows子句的前后位移均不进行限制 这样 每条记录所获得的窗口均为表中所有记录 8 5 6常用分析函数 分析函数的实质非常类似于聚合函数 因此很多聚合函数均可与窗口函数结合使用 如max min sum 等 除此之外 Oracle还提供了另外几种常用的分析函数 本小节将简要讲述Oracle中常用的几种分析函数 fist value 函数 last value 函数 lead 函数 lag 函数 1 first value 函数first value 函数用于返回已排序窗口中第一条记录相关的信息