Advanced SQL

Access DB From PL

通过 编程语言 (Programming Language) 访问 数据库 的方式：

• 动态SQL：使用 数据库API 来连接 数据库
  • JDBC
  • ODBC
  • ADO.NET
• 嵌入式SQL：可以将 SQL语句 嵌入到 宿主语言 中，通过 预处理器 对 嵌入宿主语言的SQL 进行 转化。

这里简单地讨论几点 JDBC 的内容。

• 主要涉及的对象：Connection，Statement，ResultSet

• JDBC 并不是仅仅支持 MySQL 的，它可以 指定 用于 建立数据库连接 的 数据库通信的API：如 jdbc:mysql，jdbc:oracle 等来连接 多种类型的数据库

  更准确地说，JDBC 仅 指定通信所用的API，而不是 指定通信所用的协议。因为一个JDBC驱动器 可能同时支持 多种数据库通信协议

• Statement 并不是 SQL语句对象本身，但可以用 Statement 来 执行语句

• 使用 PreparedStatement 而不是 拼接字符串的方式 来 构造SQL语句！
• 数据库 返回的 ResultSet 不仅仅包含 元组数据，还包含许多 元数据。在需要时使用 ResultSet#getMetaData 获得 元数据，而不是 硬编码 它们！
• 对 Updatable Result Set 的 修改性操作 会 反映到数据库
• 如果需要将 接下来的多条SQL语句作为事务，而不是 每条语句都被视为独立的事务，则请用 Connection#setAutoCommit(false)

Function and Procedure

函数 (Function) 和 过程 (Procedure) 允许 业务逻辑 作为 存储过程 记录在 数据中。

这使得 业务逻辑代码 是 编程语言独立 和 应用独立 的。

Function

• 一个简单的 函数 的例子

CREATE FUNCTION dept_count(dept_name varchar(20)) RETURNS integer
BEGIN
    DECLARE d_count integer
    SELECT count(*) INTO d_count
    FROM instructor
    WHERE instructor.dept_name = dept_name
    RETURN d_count
END

• 带参数的视图 (Parameterized View)

CREATE FUNCTION instructor_of(dept_name varchar(20)) RETURNS TABLE(ID varchar(5),
                                                                  name varchar(20),
                                                                  dept_name varchar(20),
                                                                  salary numeric(8,2))
RETURN TABLE(SELECT ID, name, dept_name, salary
             FROM instructor
             WHERE instructor.dept_name = instructor_of.dept_name
            );

我们将 这种函数 视为 广义上的视图。

Procedure

对于 函数 (Function) 和 过程 (Procedure) 如果做 详细区分，则我们这样定义：

• 函数：带有 返回值
• 过程：不带有 返回值

从 组成体系结构 的角度看，过程 是比 函数 更加 底层的概念

CREATE PROCEDURE dept_count_proc(IN dept_name varchar(20), OUT d_count integer)
BEGIN
    SELECT count(*) INTO d_count
    FROM instructor
    WHERE instructor.dept_name = dept_count_proc.dept_name
END

IN 表示 输入参数，而 OUT 表示输出参数。

这种形式的 传参方式 在某些 底层接口调用 中常见。其中 OUT 经常传递的是 指针类型，以便 调用者 可以通过 变量 来 接收 到 过程的结果

Persistent Storage Module

持久存储模块 (Persistent Storage Module) ：用于处理 构造

这里不详细讨论 PSM 的 语法，只给出一个 大号的例子

DECLARE out_of_classroom_seats CONDITION
DECLARE EXIT HANDLER FOR out_of_classroom_seats
BEGIN
    sequence of statements
END

CREATE FUNCTION registerStudnet(
    IN s_id varchar(5),
    IN s_courseid varchar(8),
    IN s_secid varchar(8),
    IN s_semester varchar(6),
    IN s_year numeric(4,0),
    OUT errorMsg varchar(100)
    RETURNS integer
BEGIN
    DECLARE currEnrol int;
    SELECT count(*) INTO currEnrol
    FROM takes
    WHERE course_id = s_courseid AND sec_id = s_secid
    AND semester = s_semester AND year = s_year;

    DECLARE limit int;
    SELECT capacity INTO limit
    FROM capacity NATURAL JOIN section
    WHERE course_id = s_courseid AND sec_id = s_secid
    AND semester = s_semester AND year = s_year;

    IF (currEnrol < limit) 
        BEGIN
            INSERT INTO takes VALUES (s_id, s_courseid, s_secid, s_semester, s_year, null);
            RETURN(0);
        END
    -- capacity overflow !
    SET errorMsg = 'Enrollment limit reached for course' || s_courseid || 'section' || s_secid;
    RETURN(-1);
END    
)

External Language Procedure

数据库 可以将 过程 委托给 外部语言程序 来执行，像这样的 过程 称作 外部语言过程

CREATE PROCEDURE dept_count_proc(IN dept_name varchar(20), OUT count integer)
LANGUAGE C
EXTERNAL NAME '/usr/avi/bin/dept_course_proc'

如果为了 性能 而将 任务 委托给 C 这样的语言，可能会导致 安全问题。

而如果委托给 Java，C# 这样的语言，通过 进程间通信 和 沙盒执行 则会导致 效率低下。

此外，编写能 正确处理 数据库数据 的 外部例程 的 编码量 通常较多。

Trigger

触发器 (Trigger)：用于在 数据库 发生 指定事件 时，自动 被 执行的语句

CREATE TRIGGER credits_earned AFTER UPDATE OF takes ON(grade)
    REFERENCING NEW ROW AS nrow
    REFERENCING OLD ROW AS orow
    FOR EACH ROW
    WHEN nrow.grade <> 'F' AND nrow.grade IS NOT NULL
        AND (orow.grade = 'F' OR orow.grade IS NULL)
    BEGIN ATOMIC
        UPDATE student
        SET tot_cred = tot_cred + 
            (SELECT credits
            FROM course
            WHERE course.course_id = nrow.course_id)
        WHERE student.id = nrow.id;
    END

触发器类型：

• 语句级触发器 (FOR EACH STATEMENT)：对于 引起修改的整个SQL语句 仅执行1次 触发器
• 行级触发器 (FOR EACH ROW)：对 每个受影响的行 都执行1次 触发器

过渡变量 (Transition Variable)：

• REFERENCING OLD ROW AS symbol：存储 已经更新或删除的行的旧值
• REFERENCING NEW ROW AS symbol：可用于 插入 或 删除 的 行的引用

触发时机：

• BEFORE：用于 避免非法更新，附加额外约束
• AFTER：用于 为某些修改做善后处理，维护某些修改性操作的合法性

如果可以使用 存储过程 的话，则尽量不要使用 触发器。

Recursive Query

递归查询 (Recursive Query) 的典型例子是寻找 传递闭包 (Transitive Closure) ，比如 找出某个课程的所有前置课程。

寻找 传递闭包

Iteration

CREATE FUNCTION findAllPrereqs(cid varchar(8))
RETURN TABLE(course_id varchar(8))
BEGIN
    -- define variables
    CREATE TEMPORARY TABLE c_prereq(course_id varchar(8));
    CREATE TEMPORARY TABLE new_c_prereq(course_id varchar(8));
    CREATE TEMPORARY TABLE temp(course_id varchar(8));
    -- init and continue
    INSERT INTO new_c_prereq
        SELECT prereq_id
        FROM prereq
        WHERE course_id = cid;
    -- loop until not new course is added
    REPEAT
        INSERT INTO c_prereq
            SELECT INTO temp
            FROM new_c_prereq;
        INSERT INTO temp
            (SELECT prereq.course_id
            FROM new_C_prereq, prereq
            WHERE new_c_prereq.course_id = prereq.course_id)
            EXCEPT
            (SELECT course_id
            FROM c_prereq);
        DELETE FROM new_c_prereq;
        INSERT INTO new_c_prereq
            SELECT *
            FROM temp;
        DELETE FROM temp;
    UNTIL NOT EXISTS (SELECT * FROM new_c_prereq)
    END REPEAT
    -- return the result table
    RETURN TABLE c_prereq;
END

Recursion

-- define recursive query
WITH RECURSIVE rec_prereq(course_id, prereq_id) AS (SELECT course_id, prereq_id
                                                   FROM prereq)
                                                   UNION 
                                                   (SELECT rec_prereq, course_id, prereq.prereq_id
                                                   FROM prereq, rec_prereq
                                                   WHERE prereq.course_id = rec_prereq.prereq_id)
-- call recursive query
SELECT *
FROM rec_prereq;

上述的SQL中，首先通过 基查询 找到 每门课程的先修课程，然后 递归查询 在 每次迭代过程 中 增加一层课程，直到到达 课程-先修课程的最大层次。

通过 FROM子句 对 递归视图 prc_prereq 进行 递归查询。

WITH RECURSIVE 用于声明 递归视图，它会使得 递归 在到达 不动点 时 自动被终止

SQL标准 要求 递归过程 返回的 结果 是 单调递增的，并且最终到达 不动点 (Fixed Point)

也就是说，每次 递归过程 返回的 结果 必须是 之前的结果的超集

只要 递归过程是递增的，就可以使用 迭代过程来定义递归过程。

任何的 递归查询 (Recursive Query) = 基查询 (Base Query) UNION 递归查询 (Recursive Query)

Advanced Aggregate

Rank

Sparse Rank & Dense Rank

• rank()

-- SQL 1
SELECT ID, rank() OVER (ORDER BY (GPA) DESC) AS s_rank
FROM student_grades
ORDER BY 's_rank'

单纯的 rank 并不会对 结果关系中的元组 按rank进行排序。

故添加 ORDER BY 's_rank' 来对 结果关系的元组 进行 排序

其他类型的 排序函数：percent_rank()，cume_dist()，row_number()

关于 空值 的处理，可以设置策略：NULL FIRST 或 NULL LAST

SELECT ID, rank() OVER (ORDER BY (GPA) DESC NULL LAST) AS s_rank
FROM student_grades
ORDER BY 's_rank'

$SQL 1 = SQL 2$

-- SQL 2
SELECT ID, (1 + (SELECT count(*)
                FROM student_grades B
                WHERE B.GPA > A.GPA)) AS s_rank
FROM student_grades A
ORDER BY s_rank

• dense_rank()

rank() 产生的 排名 是 间断的：如果 分数最高的人 有2人，则 这两个人的排名 均为 1，但 分数次高的人 的 排名 则为 3

相反的，如果使用 dense_rank()，则产生 不间断 的 排名：分数最高的两人 排名均为 1，但 分数次高的人 的 排名 则为 2

-- SQL 3
SELECT ID, dense_rank() OVER (ORDER BY (GPA) DESC) AS s_rank
FROM student_grades
ORDER BY 's_rank'

Partition before Rank

-- SQL 4
SELECT ID, dept_name, rank() OVER (PARTITION BY dept_name ORDER BY GPA DESC) AS DEPT_RANK
FROM dept_grades
ORDER BY dept_name, dept_rank;

当存在 GROUP BY 时， PARTION 在 GROUP BY 之后执行。

可以认为，如果在 聚集 时没有 显示地指定 PARTITION，则认为 将所有的集合 分为 唯一的一个分区

Grading

ntile(n) 按 给定顺序 取得 每个分区 (Partion) 中的元组，然后将 元组 分成 n个具有相同元组数目的桶

SELECT ID, ntile(4) OVER (ORDER BY (GPA DESC)) AS quartile
FROM student_grades;

可用于构造 百分比直方图

Window Query

窗口查询：可用于对 一定范围内的元组 进行 聚集

不同于 分区查询 中的 1个元组 只对 1个分区 (Partion) 有贡献，分窗查询 中的 窗口 (Window) 是可以 重叠的。

SELECT year, avg(num_credits) OVER(ORDER BY year ROWS 3 PRECEDING) AS avg_total_credits
FROM tot_credits;

n.b. 如果 某个年份的元组 具有 多个，则 为某个年份到底选择哪个元组 将 取决于具体实现。

窗口 的大小界定参数：

• ROWS n PRECEDING/FOLLOWING
• ROWS UNBOUNDED PRECEDING/FOLLOWING
• ROWS BETWEEN a PRECEEDING AND b FOLLOWING
• RANGE BETWEEN a AND b

OLAP

Introduction

联机分析处理 (OLAP) 是一个 交互式系统，允许 分析人员 查看 多维数据 的 不同种类的汇总数据

假如我们有 模式 $sales(item_name, color, clothes_size, quantity)$

可以对 关系中的某些属性 分组 (Group) 为 度量属性 和 维属性：

• 度量属性 (Measure Attribute)：对 某个值 进行 度量，并且可以对 这个值 进行 聚集操作
• 维属性 (Dimension Attribute)：剩余的属性 称为 维属性

能够 模式化 为 度量属性 和 维属性 的 数据 统称为 多维数据 (Multi-Dimensional Data)

数据立方体 (Data Cube)：可以用于描述 n维数据。

交叉表 (Cross-Tabulation / Pivot Table)：可以用于描述 二维属性，是 数据立方体 的 2维情形

某些 交叉表 可能还含有 汇总行 (Total Row) 和 汇总列 (Total Column)

单元 (Cell)：n维的数据单元 可用 n维向量 进行 定位，每个 单元 存储 1个值

当 某个维度 的 取值 为 all 时，则表示 对该维度的数据进行聚集，即 对该维度进行压缩！

如 clothes_size 的 all值 是对 ：small，medium 和 large 进行 聚集 得到的。

• Select attribute_list

转轴 (Pivot)：改变 交叉表 中 维 的操作

切片 (Slice) / 切块 (Dicing)：固定 某个维度，观察 其余的维度

一般将 切片 用于 固定1个维度时，切块 用于 固定多个维度 时。

• Change observation granularity

上卷 (RollUp) ：即 粒度变粗

下钻 (Drill Down)：即 粒度变细

较粗粒度的数据 可以由 较细粒度的数据 所产生，反之则不能。

即 高维数据 可以产生 低维数据，反之不能。

一个 属性 可以有不同的 粒度，这些 不同粒度 组成 层次结构，如 Datetime的层次结构：

graph TD;
date_time(DateTime) --> hour_of_day(Hour of Day)
date_time --> date(Date)
date --> day_of_week(Day of Week)
date --> month(Month)
month --> quarter(Quarter)
quarter --> year(Year)

当我们讨论 属性的粒度 时， 我们应当把 粒度树 倒过来看，这样正好符合 上卷 和 下钻。

计算机的 树 就是 倒过来的自然界的树，而 粒度树 是 倒过来的树，也就正好符合 自然界的树。

OLAP in SQL

模式 $sales(item_name, color, clothes_size, quantity)$

• pivot()

SELECT *
FROM sales
pivot(
    sum(quantity)
    FOR color IN ('dark', 'pastel', 'white')
)

pivot中的for 的 语义 是：解包 指定属性的指定值 ，将 这些值 作为 新属性 插入。

• cube()

SELECT item_name, color, clothes_size, sum(quantity)
FROM sales
GROUP BY cube(item_name, color, clothes_size)

如果需要处理 all值，则可以使用 decode() 和 grouping()。

该查询返回的是一个 关系 $ (item_name, color, clothes_size, quantity)$

该关系表示1个 3维的数据立方体

• rollup()

SELECT item_name, color, clothes_size, sum(quantity)
FROM sales
GROUP BY rollup(item_name, color, clothes_size)

从 返回的结果关系的模式 来看，rollup() 和 cube() 返回的 结果关系 具有 相同的模式。

实际上，rollup() 生成的是：1个 0维立方体，1个 1维立方体，1个 2维立方体， 1个 3维立方体

无论是用 cube() 还是 rollup()，我们都无法 精准控制分组的产生，即无法 精准指定分组具有哪些属性。

但我们可以通过 HAVING子句 来去除 GROUP子句 产生的 我们不想要的分组