[大数据]第九章 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.聚合函数

2.序列函数

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.其他类

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 可以共同使用，从而可以实现更加复杂的功能

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖