仓库源文站点原文


title: "[Database] Advanced SQL" date: 2022-04-29 21:35:00 tags:

- database

Advanced SQL

{% raw %} $$ \boxed{@} % Color % \newcommand\c[2]{\textcolor{#1}{#2}} \newcommand\r[1]{\textcolor{red}{#1}} \newcommand\g[1]{\textcolor{green}{#1}} \newcommand\b[1]{\textcolor{blue}{#1}} \newcommand\red[1]{\textcolor{red}{#1}} \newcommand\blue[1]{\textcolor{blue}{#1}} \newcommand\green[1]{\textcolor{green}{#1}} \newcommand\black[1]{\textcolor{black}{#1}} \newcommand\white[1]{\textcolor{white}{#1}} \newcommand\cyan[1]{\textcolor{cyan}{#1}} \newcommand\magenta[1]{\textcolor{magenta}{#1}} \newcommand\yellow[1]{\textcolor{yellow}{#1}} \newcommand\orange[1]{\textcolor{orange}{#1}} \newcommand\lime[1]{\textcolor{lime}{#1}} \newcommand\pink[1]{\textcolor{pink}{#1}} \newcommand\darkgray[1]{\textcolor{darkgray}{#1}} \newcommand\gray[1]{\textcolor{gray}{#1}} \newcommand\lightgray[1]{\textcolor{lightgray}{#1}} \newcommand\brown[1]{\textcolor{brown}{#1}} \newcommand\olive[1]{\textcolor{olive}{#1}} \newcommand\purple[1]{\textcolor{purple}{#1}} \newcommand\teal[1]{\textcolor{teal}{#1}} \newcommand\violet[1]{\textcolor{violet}{#1}} \newcommand\hotpink[1]{\textcolor{hotpink}{#1}} \newcommand\blueviolet[1]{\textcolor{blueviolet}{#1}} \newcommand\navyblue[1]{\textcolor{navyblue}{#1}} \newcommand\peach[1]{\textcolor{Peach}{#1}} \newcommand\orangeRed[1]{\textcolor{OrangeRed}{#1}} \newcommand\salmon[1]{\textcolor{Salmon}{#1}} \newcommand\skyblue[1]{\textcolor{SkyBlue}{#1}} \newcommand\springreen[1]{\textcolor{SpringGreen}{#1}} \newcommand\aqua[1]{\textcolor{aqua}{#1}} \newcommand\navy[1]{\textcolor{navy}{#1}} \newcommand\silver[1]{\textcolor{silver}{#1}} \newcommand\fuchsia[1]{\textcolor{fuchsia}{#1}} \newcommand\maroon[1]{\textcolor{maroon}{#1}} \definecolor{luo}{RGB}{102,204,255} \definecolor{miku}{RGB}{57,197,187} \newcommand\luo[1]{\textcolor{luo}{#1}} \newcommand\miku[1]{\textcolor{miku}{#1}}

% Typography % \newcommand\a[1]{\begin{aligned}#1\end{aligned}} \newcommand\t[1]{\text{#1}} \newcommand\tb[1]{\text{\textcolor{blue}{#1}}} \newcommand\lb[1]{\left{\begin{aligned} #1 \end{aligned}\right.} \newcommand\lrb[1]{\lb{\rb{#1}}} \newcommand\rb[1]{\left.\begin{aligned} #1 \end{aligned}\right}} \newcommand\env[2]{\begin{#1}#2\end{#1}} \newcommand\step[1]{\textbf{ (#1) }}

% Misc % \newcommand\s[1]{{#1}} \newcommand\qed{\quad\square} \newcommand\define{\dot{=}} \newcommand\then{\implies} \newcommand\rounddown[1]{\lfloor{#1}\rfloor} \newcommand\roundup[1]{\lceil{#1}\rceil} \newcommand\graph[4]{#1 = (#2, #3) \quad |#2| = #4} \newcommand\G{G = (V, E) \quad |V| = n} \newcommand\so{\therefore} \newcommand\comment[1]{\quad\text{(#1)}} \newcommand\note[1]{\quad\text{(#1)}} \newcommand\bt[1]{\boxed{\text{#1}}} \newcommand\max[1]{\textbf{ max } {#1} } \newcommand\min[1]{\textbf{ min } {#1} } \newcommand\IF{\textbf{ IF }} \newcommand\if{\textbf{ if }} \newcommand\IS{\textbf{ IS }} \newcommand\is{\textbf{ is }} \newcommand\do{\textbf{ do }} \newcommand\dowhile{\textbf{ do while }} \newcommand\dountil{\textbf{ do until }} \newcommand\find{\textbf{ find }} \newcommand\until{\textbf{ until }} \newcommand\thereisa{\textbf{ There is a }} \newcommand\thereisan{\textbf{ There is an }} \newcommand\hasno{\textbf{ has no }} \newcommand\has{\textbf{ has }} \newcommand\but{\textbf{ but }} \newcommand\however{\textbf{ however }} \newcommand\AND{\textbf{ AND }} \newcommand\OR{\textbf{ OR }} \newcommand\NOT{\textbf{ NOT }} \newcommand\THEN{\textbf{ THEN }} \newcommand\IN{\textbf{ in }} \newcommand\NOTIN{\textbf{ NOT-IN }} \newcommand\assume{\textbf{ Assuming that: }} \newcommand\contradictory{\textbf{ Thus lead to contradiction }} \newcommand\proof{\textbf{Proof: }} \newcommand\st{\textbf{ such that }} \newcommand\hold{\text{ holds }} \newcommand\lhs{\text{ LHS }} \newcommand\rhs{\text{ RHS }} \newcommand\wlg{\text{ Without loss of generality }} \newcommand\nb{\text{ nota bene }} \newcommand\analogously{\text{ analogously }} \newcommand\viceversa{\textbf{ viceversa }} \newcommand\let{\textbf{ let }} \newcommand\as{\textbf{ as }} \newcommand\for{\textbf{ As for }} \newcommand\select{\textbf{ SELECT }} \newcommand\m[1]{\mathit{#1}} \newcommand+[1]{\mathcal{#1}} \newcommand\warnning[1]{\colorbox{Blue}{\textcolor{Yellow}{#1}}} \newcommand\error[1]{\colorbox{Black}{\textcolor{White}{#1}}} $$

{% endraw %}

Access DB From PL

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


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

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
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 这样的语言,可能会导致 安全问题

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

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

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

触发器类型

过渡变量 (Transition Variable)

触发时机

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

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

-- 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 FIRSTNULL 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

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 时, PARTIONGROUP 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. 如果 某个年份的元组 具有 多个,则 为某个年份到底选择哪个元组取决于具体实现

窗口 的大小界定参数:

OLAP

Introduction

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


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

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

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


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

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

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

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

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

clothes_sizeall值 是对 :smallmediumlarge 进行 聚集 得到的。


转轴 (Pivot):改变 交叉表 的操作

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

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

上卷 (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)$

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

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

item_name clothes_size dark pastel white
skirt small 2 11 2
... ... ... ... ...
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维的数据立方体

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子句 产生的 我们不想要的分组