DB2 SQL 与 XQuery 教程，第 4 部分: 数据分析

开始之前

关于本系列

本教程为您讲解 SQL 的一些基础和高级话题以及 XQuery 的基础知识，并展示如何使用 SQL 查询或 XQuery 语句将常被问起的业务问题表达为数据库查询。开发人员和数据库管理员可以使用本教程来提高他们的数据库查询技能。Academic Initiative 成员可以使用本教程系列作为他们数据库课程的一部分。

本文中的所有例子都是基于 Aroma，这是一个示例数据库，其中包含了在美国各地的商店中出售的咖啡和茶用品的销售数据。每个例子由以下三部分组成：

以日常用语表达的一个业务问题

以 SQL 或 XQuery 表达的一个或多个例子查询

显示从数据库返回的结果的一个表

本指南是为了让读者学习 SQL 语言和 XQuery 而设计的。和学习任何其他技能一样，重要的是一边学习一边实践。本指南给出的表定义和数据为此提供了便利。

对于使用本指南作为学校课程一部分的学生而言，他们应该向老师学习连接到 Aroma 数据库的操作，并了解本指南的设置与您本地设置的不同之处。

本系列是针对于 DB2 Express-C 9 for UNIX®, Linux® and Windows® （曾用名 Viper）而编写的。

关于本教程

本教程描述如何编写需要进行某种数据分析的查询。很多查询包含连续计算，或对已排序的一组行执行的计算，这些都是在业务分析过程中常遇到的查询。例如：

每月的累加总计是多少？

按周计算的移动平均值是多少？

月销售额之间的排名是怎样的？

当前月的销售额占全年销售额的比例是多少？

DB2 9 中包含的标准 SQL OLAP 函数为回答这些类型的问题提供了有效的途径。通过使用在线分析处理（On-Line Analytical Processing，OLAP）函数，可以在查询结果中以标量值返回排名、行号和已有的列函数信息。OLAP 函数可以包括在一个 select 列表的表达式中或 select 语句的 ORDER BY 子句中。

本教程提供了一系列的例子，每个例子给出了业务查询和相关的语法。

本教程还展示如何使用标量函数从 DATE 列计算和提取信息，例如星期几和月份。

本章中的很多查询都依赖于聚合的销售总额。由于 Sales 表存储每日销售总额，因此数据库设计应包括用于回答这些查询的聚合表。

连接到数据库

在使用 SQL 查询或处理数据之前，需要连接到一个数据库。CONNECT 语句将一个数据库连接与一个用户名相关联。

如果您使用本指南作为学校课程的一部分，那么可以向老师询问要连接到的数据库的名称。对于本系列，数据库名为 aromadb。

要连接到 aromadb 数据库，可以在 DB2 命令行处理器中输入以下命令：

　CONNECT　TO　aromadb　USER　userid　USING　password

注意用老师告诉您的用户 ID 和密码替换 "userid" 和 "password"。如果不需要用户 ID 和密码，那么只需使用以下命令：

　CONNECT　TO　aromadb

如果看到下面的消息，则说明您已经建立一个成功的连接：

Database　Connection　Information　
Database　server　　　=　DB2/NT　9.0.0　
SQL　authorization　ID　=　USERID　
Local　database　alias　=　AROMADB

建立连接后，就可以开始使用数据库了。

累加总数

问题

Aroma Roma coffee 在 2006 年 1 月份的每日销售数字是多少？这个月的累加总销售额和累加总销售量是多少？

OLAP 查询

SELECT　date,　SUM(dollars)　AS　total_dollars,　
　　SUM(SUM(dollars))　OVER(ORDER　BY　date　ROWS　UNBOUNDED　PRECEDING)　AS　run_dollars,　
　　SUM(quantity)　AS　total_qty,　
　　SUM(SUM(quantity))　OVER(ORDER　BY　date　ROWS　UNBOUNDED　PRECEDING)　AS　run_qty　
　FROM　aroma.period　a,　aroma.sales　b,　aroma.product　c　
　WHERE　　a.perkey　=　b.perkey　
　　　AND　c.prodkey　=　b.prodkey　
　　　AND　c.classkey　=　b.classkey　
　　　AND　year　=　2006　
　　　AND　month　=　'JAN'　
　　　AND　prod_name　=　'Aroma　Roma'　
　GROUP　BY　date　
　ORDER　BY　date;

结果

OLAP SUM 函数

OVER() 子句将简单的集合函数（SUM、MIN、MAX、COUNT 和 AVG)与 OLAP 聚合函数区分开来。

当窗口帧指定以下指令时，OLAP SUM 函数产生累加总计：

ROWS　UNBOUNDED　PRECEDING

这个指令告诉系统在结果集中之前的所有行上执行 OLAP 函数，在这个例子中就是 SUM 函数。也可以使用其他限制，例如 GROUP-BETWEEN 来指定所有行的一个子集。

OLAP ORDER BY 子句很关键。在这里指定 OLAP ORDER BY 子句，可以确保 OLAP SUM 函数的输入行是经过正确排序的（在这个例子中是按 Date 排序）。如果没有使用该指令，则输入行的逻辑顺序就可能是混乱的，这会导致累加总计得到的结果没有意义。该查询中的最后一个 ORDER BY 子句只对结果集的显示有影响，它与用于 LOAP 函数的 ORDER BY 子句是不同的。

OLAP ROW_NUMBER 函数

OLAP 函数甚至可用于一些简单的任务，例如在结果集中提供行号，就像下面的查询一样：

SELECT　ROW_NUMBER()　OVER()　AS　row_num,　order_no,　price　
FROM　aroma.orders;

重设累加总计

问题

2006 年 1 月份 Aroma Roma 每个星期的累加销售数字是多少？

OLAP 查询

SELECT　date,　SUM(dollars)　AS　total_dollars,　
　　　SUM(SUM(dollars))　OVER(PARTITION　BY　week　ORDER　BY　date　
　　　ROWS　UNBOUNDED　PRECEDING)　AS　run_dollars,　
　　　SUM(quantity)　AS　total_qty,　
　　　SUM(SUM(quantity))　OVER(PARTITION　BY　week　ORDER　BY　date　
　　　ROWS　UNBOUNDED　PRECEDING)　AS　run_qty　
　FROM　aroma.period　a,　aroma.sales　b,　aroma.product　c　
　WHERE　　a.perkey　=　b.perkey　
　　　AND　c.prodkey　=　b.prodkey　
　　　AND　c.classkey　=　b.classkey　
　　　AND　year　=　2006　
　　　AND　month　=　'JAN'　
　　　AND　prod_name　=　'Aroma　Roma'　
　GROUP　BY　week,　date　
　ORDER　BY　week,　date;

结果