第九章 Oracle 中的复杂数据处理

第九章 Oracle 中的复杂数据处理

利用聚合函数统计数据

• 求最大值——max()函数

  • 应用于数值型：按照数值大小顺序来获取最大值

  • 应用于字符型：

    • Oracle 会依照字母表顺序进行排序，如果列值中英文同时存在时，中文将排在英文之后，来获取最大值

  • 应用于日期型：表示获取最晚的日期。

• 求最小值——min()函数

  • 与max()相反，同样应用于数值型，字符型，日期型

• 求平均数——avg()函数

  • 用于获取一组数据的平均值，只能应用于数值型！！

• 求和——sum()函数

  • 用于获取一组数据之和，只能应用于数值型！！！

• 统计记录数——count([参数])函数：用于统计记录数目

  • 统计列值不为空的列：count(列名)

    • 列名作为count()函数的参数，当列值不为空时，将计数1，否则计数0；

  • 统计符合条件的所有列:

    • count(*)

      • “*”号表示将该表的所有列作为了count()参数，即使所有列值均为空，Oracle仍将进行计数。

    • count(1)

      • count(1)与count(*)返回的值相同，无论列值是否为空，都将对符合条件的数据进行计数。

Oracle中的常用技巧

• 多值判断——decode()

  • 使用语法如下：

    • decode(表达式，比较值1，结果值1，比较值2，结果值2，…，默认值)
    • 类似于编程语言中的switch case 语句

• 为空值重新赋值——nvl()

  • nvl()函数可以判断表达式的值是否为空，如果为空，则返回新值,新值可以是表达式；如果不为空，则返回原值

  • 使用语法如下：

    • nvl(表达式，新值/表达式)
    • nvl()首先判断第一个表达式参数的值是否为空：如果为空，则返回第二个参数的值，如果不为空，则返回第一个参数表达式的值。

• 结果集的行号——rownum()

  • rownum()函数用于返回结果集的行号

  • 例如：

    • 

• 强制转换数据类型——cast()

  • 使用语法如下：

    • cast(原数据 as 新的数据类型 )
    • 使用建议：当需要使用原有数据来创建新表，但数据类型需要改变时使用；

Oracle中的运算

• 数学运算

  • Oracle中的数学运算包括加(+)减(-)乘(*)除(/)四种，无论操作数是何种数据类型，都将首先转换为数值型，然后参与运算。当null参与数学运算时，运算结果始终为null；

• 逻辑运算

  • 用于数值型、日期型和字符串类型的比较

    • >：大于运算

    • >=:大于等于运算

    • <:小于运算

    • <=：小于等于运算

    • =:等于

    • <>或!=：不等于

  • NOT:取反操作

  • 布尔值操作：

    • AND:与操作
    • OR：或操作

• 按位运算

  • 参加运算的两个数，按二进制位进行运算。

  • 按位与

    • 规则：只有两个数的二进制同时为1，结果才为1，否则为0
    • bitand(数值1，数值2)
    • 如果数值参数不为整数，Oracle总是先将其转换为整数——转换规则为直接截取整数部分，然后才进行运算。

  • 按位或

    • 规则：参加运算的两个数只要有两个数中一个为1，结果就为1

    • 按位或未提供专门的函数实现，可以通过按位与函数，间接实现

      • 数值1+数值2-bitand(数值1，数值2)

  • 按位异或

    • 规则：两个二进制数不同时，返回1；相同时，返回0

    • 按位异或同样未提供专门的函数实现，可以通过按位与函数，间接实现

      • 数值1+数值2-2*bitand(数值1，数值2)

  • 列的乘积

    • 可通过指数运算ln(),求和运算sum(),乘方运算exp()，间接实现

      • exp(sum(ln(列名)))

      • sum(ln(data))是获取data列所有数值的自然对数的和，利用exp(sum(ln(data)))对自然对数的和做一次自然对数的乘方运算，最后获取的结果就是列的乘积。

      • 例如：

        • 获取student表中id的乘积，仅限数值运算
        • 

• Oracle 中的特殊判式

  • between … and …——范围测试

    • 用于判断列或表达式的值是否处于某个范围之内
    • select 列名1，列名2，… from 表名 where 列名 between x and y;
    • x和y限定了范围的临界值
    • 等同于 ：select 列名1，列名2，… from 表名 where 列名 >= x and 列名 <=y;

  • in——集合成员测试

    • 用于判断某个列或表达式的值是否处于某个集合之内
    • select 列名1，列名2，… from 表名 where 列名 in (集合/子查询);

  • like——模糊匹配

    • 匹配任意字符串——“%”

      • 该通配符用于任意长度的任意字符.

      • 例如：

        • 

    • 匹配单个字符——“_”

      • 该统配符可用于匹配任意的单个字符

      • 例如：

        • 

    • 转义字符

      • 特殊字符：%，_，都具有特殊含义，当对特殊字符进行模糊查询时，需要用到转义字符，转义字符由escape 指定

      • 例如：

      • 

    • is null——空值判断

      • oracle 不能直接使用“=”判断一个列或表达式的值是否为空，而是使用特殊判式 is null.
      • is null 判式的对立面为 is not null

    • exists——存在性判断

      • exists用于判断记录的存在性。exists判式的操作对象是结果集，当结果集中记录大于0时，将返回真；否则返回为假；

      • 例如：

        • 

  • 数量判断——all,some,any

    • 大于集合中的所有数：all

      • 

    • 大于集合中的任意一个数即可：

      • some

        • 

      • any

        • 

      • some和any 功能相同

Oracle中分析函数与窗口函数

以下部分来自于脚本之家

1、什么是窗口函数？

窗口函数也属于分析函数。Oracle从8.1.6开始提供窗口函数，窗口函数用于计算基于组的某种聚合值，
窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
与聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行

基本语法: ‹分析函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)。
语法中的‹分析函数›主要由序列函数（rank、dense_rank和row_number等组成）
与聚合函数（sum、avg、count、max和min等）作为窗口函数组成。

从窗口函数组成上看，它是group by 和 order by的功能组合，group by分组汇总后改变了表的行数，一行只有一个类别，而partiition by则不会减少原表中的行数。
恰如窗口函数的组成，它同时具有分组和排序的功能，且不减少原表的行数。
OVER 关键字表示把函数当成窗口函数而不是聚合函数。SQL 标准允许将所有聚合函数用做窗口函数，使用 OVER 关键字来区分这两种用法。

2、窗口函数——开窗

OVER 关键字后的括号中经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则窗口函数会对结果集中的所有行进行聚合运算。

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

为什么叫开窗呢？

因为在over()括号中的，partition() 函数可以将查询到的数据进行单独开一个窗口处理。譬如，查询每个班级的学生的排名情况，查询每个国家的历年人口等，诸如此类，都是在查询到的每一个班级、每一个国家中都开一个窗口，单独去执行命令。

rows和range分别表示选择后几行、选择数据范围
理解 rows between 含义,也叫做window子句：

preceding：往前following：往后current row：当前行unbounded：无边界，unbounded precending 表示从最前面的起点开始， unbounded following：表示到最后面的终点注：不加 partition by 的话则把整个数据集当作一个分区，不加 order by的话会对某些函数统计结果产生影响，如sum()

3、一些分析函数的使用方法

1.聚合函数

聚合函数	定义
sum()	求和
max()	求最大值
min()	求最小值
avg()	求平均值
count()	统计数

2.序列函数

序列函数	定义
row_number()	按照值排序时产生一个自增编号，值相等时不会重复，不会产生空位
rank()	按照值排序时产生一个自增编号，值相等时会重复，会产生空位
dense_rank()	按照值排序时产生一个自增编号，值相等时会重复，不会产生空位

row_number()

select * ，row_number()over(oder by 成绩 desc) as 排名 from 班级表;

查询结果：

+------------+--------+------+------+------+
| 姓名       | 性别   | 班级  | 成绩 | 排名 |
+------------+--------+------+------+------+
| 张三       | 男      | 1    | 100  | 1    |
| 李四       | 女      | 3    | 100  | 2    |
| 张三       | 女      | 1    | 100  | 3    |
| 王五       | 女      | 2    | 99   | 4    |
| 赵四       | 男      | 2    | 90   | 5    |
| 孙六       | 男      | 2    | 90   | 6    |
| 喜羊羊     | 男      | 3    | 85   | 7    |
| 美羊羊     | 女      | 4    | 82   | 8    |
| 懒洋洋     | 女      | 1    | 80   | 9    |
| 慢羊羊     | 女      | 2    | 70   | 10   |
+------------+--------+------+------+------+

rank()

select * ，rank()over(oder by 成绩 desc) as 排名 from 班级表

查询结果：

+------------+--------+------+------+------+
| 姓名       | 性别   | 班级  | 成绩 | 排名 |
+------------+--------+------+------+------+
| 张三       | 男      | 1    | 100  | 1    |
| 李四       | 女      | 3    | 100  | 1    |
| 张三       | 女      | 1    | 100  | 1    |
| 王五       | 女      | 2    | 99   | 4    |
| 赵四       | 男      | 2    | 90   | 5    |
| 孙六       | 男      | 2    | 90   | 5    |
| 喜羊羊     | 男      | 3    | 85   | 7    |
| 美羊羊     | 女      | 4    | 82   | 8    |
| 懒洋洋     | 女      | 1    | 80   | 9    |
| 慢羊羊     | 女      | 2    | 70   | 10   |
+------------+--------+------+------+------+

dense_rank()

select * ，row_number()over(oder by 成绩 desc) as 排名 from 班级表

查询结果：

+------------+--------+------+------+------+
| 姓名       | 性别   | 班级  | 成绩 | 排名 |
+------------+--------+------+------+------+
| 张三       | 男      | 1    | 100  | 1   |
| 李四       | 女      | 3    | 100  | 1   |
| 张三       | 女      | 1    | 100  | 1   |
| 王五       | 女      | 2    | 99   | 2   |
| 赵四       | 男      | 2    | 90   | 3   |
| 孙六       | 男      | 2    | 90   | 3   |
| 喜羊羊     | 男      | 3    | 85   | 4   |
| 美羊羊     | 女      | 4    | 82   | 5   |
| 懒洋洋     | 女      | 1    | 80   | 6   |
| 慢羊羊     | 女      | 2    | 70   | 7   |
+------------+--------+------+------+------+

3.其他类

其他类	定义
percent_rank()	分组内当前行的rank值-1/分组内总行数-1
lag()	用于统计窗口内往上第n行值 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL
lead()	用于统计窗口内往下第n行值 第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL
ntile()	用于将分组数据按照顺序切分成n片，返回当前切片值
first_value()	取分组内排序后，截止到当前行，第一个值
last_value()	取分组内排序后，截止到当前行，最后一个值
cume_dist()	返回小于等于当前值的行数/分组内总行数

4、OVER()参数——分组函数

partition by 子句：

窗口函数的 over 关键字后括号中的可以使用 partition by 子句来定义行的分区来供进行聚合计算。
与 group by 子句不同，partition by 子句创建的分区是独立于结果集的，创建的分区只是供进行
聚合计算的，而且不同的窗口函数所创建的分区也不互相影响。

5、OVER()参数——排序函数

order by 子句：

窗口函数中可以在over关键字后的选项中使用order by 子句来指定排序规则，而且有的窗口函数还
要求必须指定排序规则。使用order by 子句可以对结果集按照指定的排序规则进行排序，并且在一个
指定的范围内进行聚合运算。
语法：ORDER BY字段名  RANGE|ROWS  BETWEEN边界规则1  AND  边界规则2

PARTITION BY子句和ORDER BY 可以共同使用，从而可以实现更加复杂的功能