《数据库系统概念》学习笔记——第五章高级SQL

动态SQL：通用程序设计语言可以通过函数（非过程式语言）或者方法（对于面向对象的语言）来连接数据库并与之交互。
- JDBC和ODBC是两个通用的连接到SQL数据库并执行查询和更新的标准。
嵌入式SQL：嵌入式SQL必须在编译时全部确定，并交给预处理器。预处理程序提交SQL到数据库系统进行预编译和优化，然后它把应用程序中的SQL语句替换成相应的代码和函数，最后调用程序语言的编译器进行编译。

5.1.1 JDBC

JDBC标准：定义了Java程序连接数据库服务器的应用程序接口（Application program interface，API）。

应用程序连接数据库的过程：

连接到数据库: getConnection
向数据库发送SQL语句用于执行: createStatement
获取查询结果: executeQuery/executeUpdate...
关闭连接: close

5.1.1.1 连接到数据库

// 必须在数据库链接之前完成 驱动程序加载
Class.forName("oracle.jdbc.driver.OracleDriver");

// 创建了一个数据库链接的句柄
Connection conn = DriverManager.getConnection(url, username, password);
/*
URL - 指明服务器所在的主机名称（db.yale.edu）；
      与数据库通信所用的协议（jdbc:oralce:thin:);
      数据库系统用来通信的端口号1521；
      服务器端使用的特定数据库（univdb）
username - 指定数据库用户标识
password - 指定密码
*/

5.1.1.2 向数据库系统传递SQL语句

// 通过Statement实例向数据库发送SQL语句用于执行
Statement stmt = conn.createStatement();
stmt.executeQuery()/executeUpdate().....

5.1.1.3 获取查询结果

ResultSet可以接收stmt.executeQuery() 的返回结果。
使用迭代器访问元组。可以利用属性名或者属性位置提取元组的属性。

注意：执行完之后一定要关闭statement句柄和数据库连接。

5.1.1.4 预备语句

// 使用PreparedStatement提交SQL用于编译。
// 注意：其优势在于防止SQL注入。对设置的参数值中的特殊字符会进行转义。
PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?,?,?,?)");
pStmt.setString(1,"8877");
pStmt.setString(2,"Perry");
pStmt.setString(3,"Finance");
pStmt.setInt(4,125000);
pStmt.executeUpdate();

pStmt.setString(1,"8878");
pStmt.executeUpdate();

5.1.1.5 可调用语句

// CallableStatement可以调用SQL的存储过程和函数
CallableStatement  cStmt1 = conn.prepareCall("{?=call some_function(?)}");
CallableStatement  cStmt2 = conn.prepareCall("{?=call some_procedure(?,?)}");

5.1.1.6 元数据特性

// ResultSet有一个getMetaData()方法，它可以返回包含结果集元数据的ResultSetMetaData对象。
// ResultSetMetaData进一步包含查找元数据信息的方法，如：结果的列数、特定列的名称、或者特定列的数据类型。
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 0;i<rsmd.getColumnCount();i++){ 
    System.out.println(rsmd.getColumnName(i));
    System.out.println(rsmd.getColumnTypeName(i));
}

// DatabaseMetaData提供了查找数据库元数据的机制 
// conn.getMetaData()返回DatabaseMetaData对象
// DatabaseMetaData又进一步包括大量方法可以查询所连接的数据库和数据库系统的元数据
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, "univdb","department","%");
    //getColumns的参数：类别，模式名，表名，列名
    //返回值:每列返回一行，包含一系列属性，如：COLUMN_NAME，TYPE_NAME
while(rs.next()){
    System.out.println(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"));
}

5.1.1.7 其他特性

可更新的结果集（updatable result set): 可以从一个在数据库关系上执行选择和/或投影操作的查询中创建一个可更新的结果集。对结果集中的元组的更新将引起数据库关系中相应元组的更新。
setAutoCommit(false/true): 打开或者关闭自动提交（即：每个SQL语句作为一个自动提交的独立事务对待）。
处理大对象的接口：ResultSet提供getBlob(), getClob()返回Blob或者Clob对象。这些对象不存储整个大对象，而是存储这些大对象的定位器，即：指向数据库中实际大对象的逻辑指针。
反向存储大对象的接口：PreparedStatement.setBlob(int parameterIndex, InputStream inputStream)把一个类型为blob的数据库列与一个输入流关联起来。当预备语句执行时，数据从输入流被读取，然后写入数据库的二进制大对象中。setClob方法类似。
提供行集（row set）特性：允许把结果集打包起来发送给其他应用程序。行集可以向后也可以向前扫描，并且可以被修改。

5.1.2 ODBC

开放数据库互连（Open DataBase connectivity，ODBC）标准定义了一个API，应用程序用它来连接数据库，并与之进行交互。

创建SQL环境变量，和数据库连接句柄：SQLAllocEnv()，SQLAllocConnect()
建立数据库连接：SQLConnect()
把SQL语句发送到数据库执行：SQLAllocStmt()，SQLExecDirect（）
处理结果集合：SQLFetch（）遍历结果集；SQLBindCol（）提取属性值
释放句柄，关闭连接，释放环境句柄：SQLFreeStmt(), SQLDisconnect(), SQLFreeConnect(), SQLFreeEnv()

其他特性：

开启和关闭自动提交：SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0)
提交事务/回滚事务：SQLTransact(conn, SQL_COMMIT), SQLTransact(conn, SQL_ROLLBACK)
ODBC定义了符合性级别（conformance level）：用于指定标准定义的功能的自己。
- 一个ODBC的实现可以提供核心级特性，也可以以提供更多的高级特性（level1和level2）
  - level1需要支持取得目录的有关信息，如：什么关系存在，它们属性的类型等
  - level2需要更多的特性，例如发送和提取参数值数组以及检索有关目录更详细信息的能力。

5.1.3 嵌入式SQL

SQL标准定义了可以将SQL嵌入到不同的语言中，如：C++，C，Java等。SQL查询所嵌入的语言成为宿主语言，宿主语言中使用的SQL结构被称为嵌入式SQL。

嵌入式SQL和动态SQL的区别：

一个使用嵌入式SQL的程序在编译前必须先经过一个特殊的预处理器进行处理。嵌入式的SQL请求被宿主语言的声明以及允许运行时刻执行数据库访问的过程调用所代替。然后，所产生的程序由宿主语言编译器进行编译。当使用嵌入式SQL时，一些SQL相关的错误可以再编译时就被发现。
而JDBC或者ODBC中，SQL语句是在运行时被解释的。

// 使预处理器识别嵌入式SQL请求
EXEC SQL <嵌入式SQL语句>；

// 表明预处理器应该在此处插入特殊变量以用于程序和数据库系统间的通信 
EXEC SQL INCLUDE SQLCA;

// 在执行SQL之前，需要连接数据库
EXEC SQL connect to <server> user <user-name> using <password>;

// 在嵌入的SQL中可以使用宿主语言的变量，但是需要加上“:”,以区别SQL变量
// 这类变量必须在DECLARE SECTIION块中进行声明，不过变量声明的语法取决于宿主语言
EXEC SQL BEGIN DECLARE SECTIION;
int credit_amount;
EXEC SQL END DECLARE SECTIION;

// 声明游标（declare cursor),此时并不计算查询结果，程序必须使用open和fetch语句得到元组
EXEC SQL 
    declare c cursor for
    select ID, name 
    from student
    where tot_cred>:credit_amount;
// 当open子句被执行时，宿主变量credit_amount的值就会被应用到查询中，
// 数据库系统会将该语句的执行结果存储在一个临时关系中。
EXEC SQL open c;

// 如果查询及诶过存在错误，则数据库系统会在SQL通信区域（SQLCA——）的变量中存储一个错误诊断信息。

// 利用一系列的fetch子句把结果赋值给宿主语言的变量
// 单条fetch子句每次只能获得一个元组
// 可以使用while循环，遍历全部元组
EXEC SQL fetch c into :si,:sn;

// 必须使用close，告诉数据库系统删除临时关系c
EXEC SQL close c;

// insert/update/delete的嵌入式SQL不返回结果
EXEC SQL <update/insert/delete子句>；

// 也可以通过游标来更新数据库关系
EXEC SQL 
    declare c cursor for
    select *
    from instructor
    where dept_name = 'Music'
    for update;
// 利用fetch操作进行迭代，每取到一个元组，都执行如下代码
EXEC SQL 
    update instructor
    set salary = salary+100
    where current of c;

// 提交事务
EXEC SQL COMMIT；

//回滚事务
EXEC SQL ROLLBACK；

SQLJ：Java的嵌入式SQL。SQLJ与其他嵌入式SQL语法不通，使用了#sql代替EXEC SQL标识，并且不使用游标，而是使用Java的迭代器接口来获得查询结果。

5.2 函数与过程

5.2.1 声明和调用SQL函数和过程

// 定义一个普通函数
create function dept_count(dept_name varchar(20))
    return integer
    begin
    declare d_count inetger;
        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))
    return table(
        ID varchar(5),
        name varchar(20),
        dept_name varchar(20),
        salary numberic(8,2))
    return table(
        select ID, name, dept_name, salary
        from instructor
        where instructor.dept_name = instructor_of.dept_name);
        
// 定义一个过程
// SQL允许多个过程同名，只要同名过程的参数个数不同。名称和参数个数用于表示一个过程。
// SQL也允许多个韩树同名，只要这些函数的参数个数不同，或者参数类型不同。
create procedure dept_count_proc(int 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
    
// 调用过程
declare d_count integer
call dept_count_proc('Physics',d_count);

5.2.2 支持过程和函数的语言构造

持久存储模块（Persistent Storage Module, PSM):

// declare用来声明；set用来赋值
// 复合语句
begin...end
//确保其中包含的所有语句作为单一的事务执行
begin atomic ... end 

// while, repeat，for语句
while <布尔表达式> do
    语句序列；
end while

repeat
    语句序列；
until <布尔表达式>
end repeat

declare n integer default 0;
for r as
    select budget from department;
do 
    set n = n - r.budget
end for    

// if else
if <布尔表达式>
    then 语句/符合语句
elseif <布尔表达式>
    then 语句/符合语句
else
    语句/符合语句
end if

// case语句

// 异常处理:声明异常条件和句柄（handler）来处理异常
declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_sears
begin
    sequence of statements
end

// begin...end中间可以用signal out_of_classroom_seats触发异常

5.2.3 外部语言过程

SQL允许使用程序设计语言定义函数和过程，如：C++，java。这种方式定义的函数和过程会比SQL中定义的函数高效，并执行一些SQL中无法实现的计算。

外部过程和函数可以这样指定：

create procedure dept_count_proc(in dept_name varchar(20), out count integer)
language C
external name '/usr/avi/bin/dept_count_proc'

create function dept_count_proc(dept_name varchar(20))
return integer
language C
external name '/usr/avi/bin/dept_count'

外部语言需要处理参数（in和out参数），已经返回值中的控制，还需要传递操作失败/成功的状态，以方便对异常进行处理。可用如下参数表示：

一个指明失败/成功状态的sqlstate值
一个存储函数返回值的参数
一些指明每个参数/函数结果的值是否为空的指示器变量

具体如何处理，取决于不同数据库系统的具体实现。

如果函数不想关注这些问题，可以声明的时候添加：

// 指明外部过程或函数只使用说明的变量，而不处理空值和异常
parameter style general

用程序设计语言定义并在数据库系统之外编译的函数可以由数据库系统代码来加载和执行。不过这么做会引入一些危险：程序中的错误可能破坏数据库内部的结构，并绕过数据库系统的访问-控制功能。若系统关心执行的效率胜过安全性，则可以采用这种方式执行过程。
若系统关心安全性胜过效率，则可以将这些代码作为一个单独进程的一部分来执行，通过进程间通信，传入参数的值，取回结果。但是代价很高。
如果用Java/C#这种”安全“的语言书写，则可能会存在第三种可能：在数据库进程本身的沙盒(sandbox)中执行代码。沙盒允许java/C#访问它的内存区域，但阻止代码直接在查询执行过程的内存中做任何读操作或者更新操作，或者访问文件系统中的文件。（C语言无法创建沙盒，因为可以通过指针不加限制的访问内存）

Oracle支持外部语言例程在查询执行过程中的沙盒运行，允许java作为数据库过程中的一部分运行。

5.3 触发器

触发器（trigger）是一条语句，当数据库修改时，它自动被系统执行。要设置触发器必须满足以下两个条件：

指明什么条件下执行触发器：引起触发器检测的事件+触发器执行的条件
指明触发器执行的动作

5.3.1 对触发器的需求

可以用来实现未被SQL约束机制指定的某些完整性约束
可以用来设置满足特定条件时对用户发出警报或自动开始执行某项任务
- 例如：某种商品库存低于最小值时，自动发出一个订单

注意：触发器不能执行数据库以外的更新，上述例子中是在订购表中添加一条记录，而不是直接去外界下单。

5.3.2 SQL中的触发器

本节介绍SQL标准定义的语法，但是实际的数据库系统在实现触发器时不完全遵照该标准。

触发器可以在事件（insert, delete, update）之前或者之后触发。

// referencing new row as nrow - 创建一个过渡变量（transition variable）
// for each row - 显式的在每一个指定的行进行迭代
// when 语句 - 指定一个条件，系统仅对满足条件的元组执行触发器的其余部分
// begin...end - 将多行SQL作为一个复合语句进行执行

// 案例：检查插入时的完整性
create trigger timeslot_check1 after insert on section
    referencing new row as nrow
    for each row
    when (nrow.time_slot_id not in (
        select time_slot_id
        from time_slot /*time_slot中不存在该time_slot_id*/
    ))
    begin
        rollback
    end;

// 案例：检查删除时的完整性
// 被删除的元组的time_slot_id要么还在time_slot中，要么不在section中，否则将违背参照完整性
create trigger timeslot_check2 after delete on time_slot
    referencing old row as orow
    for each row
    when (orow.time_slot_id not in (
            select time_slot_id
            from time_slot /*time_slot刚刚被删除的time_slot_id*/
            )  
          and orow.time_slot_id in (
              select time_slot_id
              from section) /*在section中仍然含有该time_slot_id的引用*/
          ) 
    begin
        rollback
    end;

// 案例：检查更新
// after update of takes on grade  -- 可以指定只在更新特定属性时被触发 更新takes中的grade属性时才出发
// 当takes中元组的grade属性被更新时，需要用触发器维护student元组的tot_cred属性，使其保持实时更新
// 当grade从空或者‘F’被更新为代表课程已经完成的具体分数时，触发器才被触发
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;
        
// 案例：使用set来更改插入值
create trigger setnull before update of takes
    referencing new row as nrow
    for each row
        when (nrow.grade='')
        begin atomic
            set nrow.grade = null;
        end;

// 其他：
// for each statement - 我们还可以对引起插入、删除或者更新的SQL语句执行操作，而不是对被影响的行操作
// 过渡表（transition table） - referencing old table as / referencing new table as
// 过渡表只能用于after触发器，不能用于before触发器

// 设置触发器无效 - 取决于数据系统的实现
alter trigger trigger_name disable;

// 删除触发器
drop trigger trigger_name;

5.3.3 何时不用触发器

触发器有很多合适的用途，但是也有很多不适合的情况。触发器很好用，但是如果有其他解决方法时尽量避免触发器。

用触发器替代on delete cascade ---- 缺点：会导致大量的工作量
用触发器维护物化视图 ---- 缺点：但是现在许多数据库能够自动维护物化视图
通过触发器复制或备份数据库: 当每个关系插入、更新或者删除上时设置触发器，将变更记录存储在change或delta的关系上，再通过一个单独的进程将这些改变的数据复制到数据库的副本中。 ---- 缺点：现在的数据库提供内置的数据库复制工具，不需要手工操作
触发器的非故意执行 - 例如：
- 当数据从一个备份的拷贝中加载，或者一个站点上的数据库更新复制到备份站点时，触发器非故意执行。在该种情况下，触发器已经执行过了，不需要再次执行。因此，应该在加载数据时触发器应当被设置为无效。等到备份结束后，再设置为有效
- 作为取代的方法，
  - 有些数据库系统支持触发器定义为 not for replication, 保证不会在备份站点执行。
  - 有些数据库提供系统变量指明该数据库是一个副本，不需要触发触发器。
还应该避免触发器的级联触发，避免无限循环触发链。

5.4 递归查询

举例： CS301是CS347的先修课程，并且CS201是CS301的先修课，CS101是CS201的先修课程，那么CS101，CS201和CS301都是CS347的先修课程。

关系的prereq的传递闭包（transitive closure）:是一个包含所有(cid，pre)对的关系，pre是cid的一个直接或者间接的先修课程。

有很多要求计算与此类似的层次（hierarchy）的传递闭包的应用，如：自行车中的所有部件（自行车-轮子+车身，轮子-轮骨+轮胎....，如此类推）

5.4.1 用迭代来计算传递闭包

// create temporary table table_name 创建临时表，这些表仅在执行查询的事务内部才可用，事务结束后会被删除
// repeat循环

// 计算给定课程cid的全部先修课程，包括直接和间接
// 注意：关系prerequisite（course_id, prereq_id）已经存在，并指明哪一门课是另一门课的直接先修课程
create funciton findAllPrereqs(cid varchar(8))
return table(course_id varchar(98))
begin
    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))  -- 存储中间结果
    
    // 查找当前cid的全部直接先修课程，并插入new_c_prereq
    insert into new_c_prereq (select prereq_id from prereq where course_id = cid)；
    
    // 循环
    repeat
        // 将new_c_prereq插入c_prereq
        insert into c_prereq (select course_id from new_c_prereq);
        // 查找new_c_prereq表中所有课程的直接先修课程, 并去除那些已经在在c_prereq存在的先修课程
        // 将结果插入到temp
        insert into temp (
            (select prereq_id from new_c_prereq, prereq where new_c_prereq.course_id = prereq.course_id)
            except
            (select course_id  from c_prereq)  --except可以避免环的出现 a->b,b->c,c->a
        );
        // 更新new_c_prereq
        delete from new_c_prereq;
        insert into new_c_prereq (select * from temp);
        
        // 更新temp
        delete from temp;
        
        until not exists(select * from new_c_prereq);  -- 直到找不到新的先修课程，则循环终止
    end repeat;
    return table c_prereq;
end

5.4.2 SQL中的递归

用递归视图定义传递闭包.

SQL标准用with recursive子句来支持有限形式的递归，在递归中一个视图（或临时视图）用自身表达自身

create recursive view - 可以创建永久的递归视图

任何递归视图都必须定义为两个子查询的并：

一个非递归的基查询（base query)
一个使用递归视图的递归查询（recursive query）

可以理解为：

首先计算基查询并把所有结果元组添加到视图关系（初始为空）中；
然后用当前视图关系中的元组计算递归查询，并把所有结果元组加到视图关系中。
重复上述步骤，知道没有新的元组添加到视图关系为止。

得到的视图关系实例就称为递归视图定义中的不动点（fixed point）。

/*
CS247课程的先修课程：
   - CS247的先修课程
   -CS247的（直接和间接）先修课程的先修课程  ----这是个递归过程
 */
with recursive rec_preqreq(course_id, prereq_id) as (
        select course_id, prereq_id
        from prereq
    union
        select rec_prereq.course_id, prereq.prereq_id
        from rec_prereq, prereq
        where rec_prereq.prereq_id = prereq.course_id
)

select * from rec_preqreq;

递归视图中的递归查询是有一些限制的：该递归查询必须是单调的（monotonic），即：如果视图关系实例V1是实例V2的超集，那么它在V1上的结果必须是它在V2上结果的超集。

递归查询不能用于下列场景，因为它们会导致非单调

递归视图上的聚集
在递归视图的子查询上的not exists语句
右端使用递归视图的集合差except运算
- 如：递归查询是r-v，其中v是递归视图，那么我们在v中添加一个元组，会导致查询结果变小，破坏单调性。

==》只要递归查询是单调的，递归视图的含义就可以用迭代过程定义；如果递归查询时非单调的，那么就很难确定视图的含义

5.5 高级聚集特性

5.5.1 排名

计算排名，分位数等查询可以用SQL完成，但是会比较复杂。通常编程人员会借助SQL+程序设计语言共同实现。

本节，只介绍如何用SQL实现。

/* 
rank()函数：对order by属性上所有具有相同值的元组赋予相同的排名：
        例如：A和B都具有最高的GAP，则A和B的名词都是1，C具有次高的GPA，C的名次为3
dense_rank()函数：与rank()函数类似，不过它不在等级排序中产生隔断。
        例如：A和B都具有最高的GAP，则A和B的名词都是1，C具有次高的GPA，C的名次为2
*/
// student_grades(ID, GPA) - 给出每个学生的平均绩点，
// 计算GPA排名  -  但是不排序
select ID rank(), over (order by GPA desc) as s_rank
from student_grades;
// 计算GPA排名  -  排序
select ID, rank() over (order by GPA desc) as s_rank
from student_grades
order by s_rank;

// 等价于下面的实现 - 但是下面实现的缺点是计算代价随着关系的大小线性增长
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;

// 分区 partition by
// dept_grades(ID, dept_name, GPA)
// 计算每个学生在各自系的排名
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;

//注意：
一个select语句中可以使用多个rank函数
当rank（可能带有分区）与group by同时出现时，group by先执行，分区和排名在group by的结果上执行。

// limit子句 - limit 10，获取前10个   // 注意：limit子句不支持分区
select ID, GPA from student_grades order by GPA limit 10;

// 一些其他函数
percent_rank: 以分数的方式给出元组的名词。（r-1）/(n-1) - 其中r是元组的名词，n为分区中包含的元组的个数。如果分区中只有一个元组，则定义为null。
cume_dist: 累积分布的简写, p/n。其中p是分区中排序值小于或等于该元祖排序值的元组数，n为分区中包含的元组的个数。
row_number: 对行进行排序，并且按照行在排序中所处的位置给每行一个唯一行号，具有相同排序的不同行将按照非确定的方式得到不同的行号
ntile(n): 按照给定的顺去取的每个分区中的元组，并把他们分成n个具有相同元祖数目的桶 -=>可用于构造基于百分比的直方图
    select ID, ntile(4) over (order by GPA desc) as quartile from student_grades;
nulls first/nulls last: 指定空值的位置
    select ID, rank() over(order by GPA desc nulls last) as s_rankfrom student_grades;

5.5.2 分窗

窗口查询用来对一定范围内的元组计算聚集函数。如：趋势分析，股票市场的趋势分析等。

// preceding, following, unbounded preceding, unbounded following
// range between ... and ...

// tot_credits(year, num_credits) - 给出每年学生选课的总学分
// 计算每年中[当前年-2，当前年]窗口的平均总学分 
select year, avg(num_credits) over (order by year rows 3 preceding) as avg_total_credits
from tot_credits;

// 计算每年中 当前年之间的所有年 的窗口的平均总学分 
select year, avg(num_credits) over (order by year rows unbounded preceding) as avg_total_credits
from tot_credits;

select year, avg(num_credits) over (order by year rows between 3 preceding and 2 following) as avg_total_credits
from tot_credits;

// [当前年-4，当前年]，包括边界
select year, avg(num_credits) over (order by year rows between year-4 and year) as avg_total_credits
from tot_credits;

// 分区+分窗
// tot_credits_dept(dept_name, year, num_credits)
select dept_name, year, 
     avg(num_credits) over (partition by dept_name order by year rows between 3 preceding and current row) as avg_total_credits
from tot_credits_dept;

5.6 OLAP

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

联机 - 指的是针对数据分析人员提出的新的汇总数据的请求，能够几秒钟之内在线得到响应，而无需等待很长时间。

5.6.1 联机处理分析

sales(item_name, color, clothes_size, quantity)

item_name: skirt, dress, shirt, pants
color: white, dark, pastel
clothes size: small, medium, large
quantity: 整数值，表示商品的数量

统计分析需要对多个属性进行分组。

度量属性（measure attribute)：这些属性可以用于度量某个值，并在其上做聚集操作。例如：quantity属性就可以看成度量属性。

维属性（dimension attribute）：维属性定义了度量属性以及度量属性的汇总在这些维属性上进行观察的各个维度。例如：item_name，color，clothes size就是维属性。

交叉表（cross-tablulation/cross-tab）: 一个交车表是从一个关系（如R）中导出的，由关系的一个属性（A）的值构成其行表头，另一个属性（B）的值构成其列表头。

交叉表还可已包含汇总行和汇总列

转轴表（pivot-table）：交叉表也叫转轴表。

数据立方体（data cube）：将二维的交叉表推广到n维，可视作一个数据立方体。如图5-18所示，该立方体的大小为(3+1)*(4+1)*(3+1) = 80，为每个属性可能的属性取值个数加一的乘积（加一是因为增加了all）。

每个交叉表是一个多维数据立方体的二维视图。数据分析人员可以交互的选择交叉表的属性进行查看，改变交叉表中的维的操作叫做转轴（pivoting）。
OLAP系统允许对一个固定的clothes_size值来查看一个在item_name和color上的交叉表。这样的操作叫做切片（slicing）。该操作有时也可以叫做切块(dicing)。
上卷(rollup)和下钻(drill down):
- 由较细粒度到较粗粒度的操作叫做上卷
- 由较粗粒度到较细粒度的操作叫做下钻。
一个属性的不同的细节层次可以组织为一个层次结构（hierarchy），如：region-country-state-city。分析人员可以以沿着层次结构进行上卷和下钻。

all：表示当前属性的全部的值。SQL标准使用null值替代all。

5.6.2 交叉表和关系表

交叉表与数据库的关系表的区别：

交叉表的列的数目依赖于书籍的数据。数据值的变化可能导致增加更多的列。

OLAP的实现

OLAP的类型：
- 多维OLAP（Multi地mentional OLAP， MOLAP）系统：使用内存中的多维数组存储数据立方体
- 关系OLAP（Relational OLAP，ROLAP）系统：使用关系数据库存储汇总和基表数据
- 混合OLAP（Hybrid OLAP，HOLAP）系统：复合系统将一些汇总数据存在内存中，基表数据和另一些汇总数据存储到关系数据库中。

许多OLAP系统实现为空户-服务系系统。服务器端包含关系数据库和MOLAP数据立方体。客户端系统通过与服务器通信获得数据的视图。

5.6.3 SQL中的OLAP

pivot子句；在group by子句中使用rollup和cube操作；

案例一：pivot子句

// sales(item_name, color,clothes_size, quantity)
select * 
from sales
pivot (
    sum(quantity)
    for color in ('dark','pastel','white) -- 指定color属性中的哪些值可以再转轴的结果中作为属性名出现
)
order by item_name;

输出结果：案例二：cube操作

select item_name, color, clothes_size, sum(quantity)
from sales
group by cube(item_name, color, clothes_size);
/*
此时，生成的(item_name, color，null,sum(quantity)) 表示在该item_name,color下全部的clothes_size的聚集。用null表示了之前介绍的all。
group by cube(item_name, color, clothes_size)操作：
    会在{(item_name,color,clothes_size)，(item_name,color),(item_name,clothes_size),(color, clothes_size),(item_name),
(color),(clothes_size),()}这些上做group by操作，产生八个分组。
*/

// 为了便于理解，还可以用DECODE函数 将null替换成all ： 下图仅对item_name和color作为维度属性
// decode (value, match-1, replacement-1, match-2, replacement-2,..., match-n, replacement-n, default-replacement);
// grouping函数，当参数是cube或rollup产生的null值时，它将返回1，否则返回0
select decode(grouping(item_name),1,'all',item_name) as item_name,
      decode(grouping(color),1,'all',color) as color,
      sum(quantity) as quantity
from sales
group by cube(item_name, color);

第二个sql的输出结果就是：

案例三：rollup操作

/*
rollup操作与cube操作类似，只是产生的结果会少于cube。rollup中属性的顺序会影响计算结果
group by rollup(item_name,color,clothes_size) ：
    仅会在{(item_name,color,clothes_size), (item_name, color), (item_name),()}上做group_by运算来产生四个分组。

应用场景：对类似（region，country，state，city）这种层次结构上的数据的下钻和上卷非常有用。
*/

/*多个rollup和cube可以再一个单独的group by中使用 
下面的SQL可以产生如下分组：
{(item_name, color, clothes_size),(item_name,color),(item_name),(color,clothes_size),(color),()}
这是因为：
rollup(item_name) ==> {(item_name),()}
rollup(color, clothes_size) ==> {(color, clothes_size),(color),()}
二者取笛卡尔积，就得到了这个结果。
*/
select item_name, color, clothes_size, sum(quantity)
from sales
group by rollup(item_name),rollup(color,clothes_size);

//注意：我们也可以使用having来限制rollup和cube的分组的产生

总结

SQL查询可以从宿主语言通过嵌入和动态SQL激发。ODBC和JDBC标准给C、Java等语言的应用程序定义了接入SQL数据库的应用程序接口。程序员可以通过这些API访问数据库
函数和过程可以用SQL提供的过程扩展来定义，它允许迭代和条件（if-then-else）语句
触发器定义了当某个事件发生而且满足相应条件时自动执行的动作。触发器有很多用处，例如：实现业务规则，审计日志，甚至执行数据库系统外的操作。大多数数据库都支持触发器。
一些查询，如：传递闭包，既可以用迭代表示，也可以通过递归SQL查询表示。递归可以用递归视图，或者用递归的with子句定义
SQL支持高级的聚集特性，如：排名和分窗查询
OLAP工具帮助分析人员从不同的维度查看汇总数据：
- OLAP工具工作在以维属性和度量属性为特性的多维数据之上
- 数据立方体由以不同方式汇总的多维数据构成。预先计算数据立方体有助于提高汇总数据的查询速度
- 交叉表的显示允许用户一次查看多维数据的两个维及其汇总数据
- 上卷、下钻、切片和切块是用户使用OLAP工具时常用的一些操作
从SQL-1999标准开始，SQL提供了cube和rollup操作符，有些系统还支持了pivot子句。