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

核心提示：开始之前关于本系列本教程为您讲解 SQL 的一些基础和高级话题以及 XQuery 的基础知识，并展示如何使用 SQL 查询或 XQuery 语句将常被问起的业务问题表达为数据库查询，DB2 SQL 与 XQuery 教程，第 4 部分: 数据分析，开发人员和数据库管理员可以使用本教程来提高他们的数据库查询技能，Acad

开始之前

关于本系列

本教程为您讲解 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;

结果

Date	Total_Dollars	Run_Dollars	Total_Qty	Run_Qty
2006-01-02	855.50	855.50	118	118
2006-01-03	536.50	1392.00	74	192
2006-01-04	181.25	1573.25	25	217
2006-01-05	362.50	1935.75	50	267
2006-01-06	667.00	2602.75	92	359
2006-01-07	659.75	3262.50	91	450
2006-01-08	309.50	3572.00	54	504
2006-01-09	195.75	3767.75	27	531
2006-01-10	420.50	4188.25	58	589
2006-01-11	547.50	4735.75	78	667
2006-01-12	536.50	5272.25	74	741
2006-01-13	638.00	5910.25	88	829
2006-01-14	1057.50	6967.75	150	979
2006-01-15	884.50	7852.25	122	1101
2006-01-16	761.25	8613.50	105	1206
2006-01-17	455.50	9069.00	66	1272
2006-01-18	768.50	9837.50	106	1378
2006-01-19	746.75	10584.25	103	1481
2006-01-20	261.00	10845.25	36	1517
2006-01-21	630.75	11476.00	87	1604
2006-01-22	813.75	12289.75	115	1719
...

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;

ROW_NUM	ORDER_NO	PRICE
1	3600	1200.46
2	3601	1535.94
3	3602	780.00
...

重设累加总计

问题

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;

结果

Date	Total_Dollars	Run_Dollars	Total_Qty	Run_Qty
2006-01-02	855.50	855.50	118	118
2006-01-03	536.50	1392.00	74	192
2006-01-04	181.25	1573.25	25	217
2006-01-05	362.50	1935.75	50	267
2006-01-06	667.00	2602.75	92	359
2006-01-07	659.75	3262.50	91	450
2006-01-08	309.50	3572.00	54	504
2006-01-09	195.75	195.75	27	27
2006-01-10	420.50	616.25	58	85
2006-01-11	547.50	1163.75	78	163
2006-01-12	536.50	1700.25	74	237
2006-01-13	638.00	2338.25	88	325
2006-01-14	1057.50	3395.75	150	475
2006-01-15	884.50	4280.25	122	597
2006-01-16	761.25	761.25	105	105
2006-01-17	455.50	1216.75	66	171
2006-01-18	768.50	1985.25	106	277
2006-01-19	746.75	2732.00	103	380
2006-01-20	261.00	2993.00	36	416
2006-01-21	630.75	3623.75	87	503
2006-01-22	813.75	4437.50	115	618
...

OLAP 窗口分割

当分割列中的值发生变化时，通过 OVER() 子句中的 OLAP PARTITION BY 子句可以重新设置计算。您可以按一个列或多个列对 OLAP 计算进行分割。

在这个查询中，虽然 PARTITION BY 子句中用到了 WEEK 列，但是显示的结果中不包含这一列。系统从 DATE 列得出某一天属于第几周。但是，如果在 select 列表中包括 WEEK 值，并使结果表的格式与之前的结果一致，那么查询的结果就更容易解释了。

下面是在 select 语句中包括 week 列的查询，以及该查询的输出。

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,　week　
　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

结果

DATE	TOTAL_DOLLARS	RUN_DOLLARS	TOTAL_QTY	RUN_QTY	WEEK
2006-01-02	855.50	855.50	118	118	2
2006-01-03	536.50	1392.00	74	192	2
2006-01-04	181.25	1573.25	25	217	2
2006-01-05	362.50	1935.75	50	267	2
2006-01-06	667.00	2602.75	92	359	2
2006-01-07	659.75	3262.50	91	450	2
2006-01-08	309.50	3572.00	54	504	2
2006-01-09	195.75	195.75	27	27	3
2006-01-10	420.50	616.25	58	85	3
2006-01-11	547.50	1163.75	78	163	3
2006-01-12	536.50	1700.25	74	237	3
2006-01-13	638.00	2338.25	88	325	3
2006-01-14	1057.50	3395.75	150	475	3
2006-01-15	884.50	4280.25	122	597	3
2006-01-16	761.25	761.25	105	105	4
2006-01-17	455.50	1216.75	66	171	4
2006-01-18	768.50	1985.25	106	277	4
2006-01-19	746.75	2732.00	103	380	4
2006-01-20	261.00	2993.00	36	416	4
2006-01-21	630.75	3623.75	87	503	4
2006-01-22	813.75	4437.50	115	618	4
...

使用算术运算符

问题

2004 年每种产品的平均售价是多少？按照将总销售额除以总销售量的公式计算平均值。

例子查询

SELECT　prod_name,　SUM(dollars)　AS　total_sales,　SUM(quantity)　AS　total_qty,　
　　　　DEC(sum(dollars)/sum(quantity),　7,　2)　AS　price　
　FROM　aroma.product　a,　aroma.sales　b,　aroma.period　c　
　WHERE　　a.prodkey　=　b.prodkey　
　　　AND　a.classkey　=　b.classkey　
　　　AND　c.perkey　=　b.perkey　
　　　AND　year　=　2004　
　GROUP　BY　prod_name　
　ORDER　BY　price;

结果

Prod_Name	Total_Sales	Total_Qty	Price
Gold Tips	38913.75	11563	3.36
Special Tips	38596.00	11390	3.38
Earl Grey	41137.00	11364	3.61
Assam Grade A	39205.00	10767	3.64
Breakfast Blend	42295.50	10880	3.88
English Breakfast	44381.00	10737	4.13
Irish Breakfast	48759.00	11094	4.39
Coffee Mug	1054.00	213	4.94
Darjeeling Number 1	62283.25	11539	5.39
Ruby's Allspice	133188.50	23444	5.68
Assam Gold Blend	71419.00	11636	6.13
Colombiano	188474.50	27548	6.84
Aroma Roma	203544.00	28344	7.18
La Antigua	197069.50	26826	7.34
Veracruzano	201230.00	26469	7.60
Expresso XO	224020.00	28558	7.84
Aroma baseball cap	15395.35	1953	7.88
Lotta Latte	217994.50	26994	8.07
Cafe Au Lait	213510.00	26340	8.10
Aroma Sounds Cassette	5206.00	620	8.39
Xalapa Lapa	251590.00	29293	8.58
NA Lite	231845.00	25884	8.95
Demitasse Ms	282385.25	28743	9.82
Aroma t-shirt	20278.50	1870	10.84
Travel Mug	1446.35	133	10.87
Darjeeling Special	127207.00	10931	11.63
Spice Sampler	6060.00	505	12.00
Aroma Sounds CD	7125.00	550	12.95
French Press, 2-Cup	3329.80	224	14.86
Spice Jar	4229.00	235	17.99
French Press, 4-Cup	3323.65	167	19.90
Tea Sampler	13695.00	550	24.90
...

使用算术运算符：( ), +, -, *, /

可以在 select 列表或搜索条件中执行算术运算符。下面的表中完整地列出了所有算术运算符。在这个表中，算术运算符按计算优先顺序从高到低（从上到下）排列，同一级别上则从左到右排列：

操作符	名称
( )	强制确定计算顺序
+, -	正和负
*, /	乘和除
+, -	加和减

如果对一个表达式的计算顺序不确定，那么可以用括号来组织这个表达式。例如，对于表达式 (4 + 3 * 2)，服务器算出它的值为 10，但是对于使用了括号的表达式 ((4 + 3) * 2)，服务器算出它的值为 14。

使用中的注意事项

DEC 函数用于将 Price 值裁剪到只剩两个小数位：

　　　　　　　　　　dec(sum(dollars)/sum(quantity),　7,　2)　AS　price

用 OLAP 比较累加总计

问题

在 2006 年 3 月份，西部和南部的累加日销售额相差多少？

OLAP 查询

SELECT　t1.date,　sales_cume_west,　sales_cume_south,　
　sales_cume_west　-　sales_cume_south　AS　west_vs_south　
　FROM　
　　　(SELECT　date,　SUM(dollars)　AS　total_sales,　
　　　　　SUM(SUM(dollars))　OVER(ORDER　BY　date　
　　　　　ROWS　UNBOUNDED　PRECEDING)　AS　sales_cume_west　
　　　FROM　aroma.market　a,　
　　　　　aroma.store　b,　
　　　　　aroma.sales　c,　
　　　　　aroma.period　d　
　　　WHERE　　a.mktkey　=　b.mktkey　
　　　　　AND　b.storekey　=　c.storekey　
　　　　　AND　d.perkey　=　c.perkey　
　　　　　AND　year　=　2006　
　　　　　AND　month　=　'MAR'　
　　　　　AND　region　=　'West'　
　　　GROUP　BY　date)　AS　t1　
　JOIN　
　　　(SELECT　date,　SUM(dollars)　AS　total_sales,　
　　　　　SUM(SUM(dollars))　OVER(ORDER　BY　date　
　　　　　ROWS　UNBOUNDED　PRECEDING)　AS　sales_cume_south　
　　　FROM　aroma.market　a,　
　　　　　aroma.store　b,　
　　　　　aroma.sales　c,　
　　　　　aroma.period　d　
　　　WHERE　　a.mktkey　=　b.mktkey　
　　　　　AND　b.storekey　=　c.storekey　
　　　　　AND　d.perkey　=　c.perkey　
　　　　　AND　year　=　2006　
　　　　　AND　month　=　'MAR'　
　　　　　AND　region　=　'South'　
　　　GROUP　BY　date)　AS　t2　
　ON　t1.date　=　t2.date　
　ORDER　BY　date;

结果

DATE	Sales_Cume_West	Sales_Cume_South	WEST_VS_SOUTH
2006-03-01	2529.25	2056.75	472.50
2006-03-02	6809.00	4146.75	2662.26
2006-03-03	9068.75	6366.55	2702.20
...
2006-03-29	100513.85	62891.35	37622.50
2006-03-30	104267.40	65378.75	38888.65
2006-03-31	107222.15	68100.75	39121.40

OLAP ORDER BY 子句

使用 OLAP 进行连续计算的一个优点是可以将这些计算放在子查询中。OLAP 排序是函数本身的一部分，每个 OLAP 函数都有它自己的 ORDER BY 子句，这种子句独立于查询最终的 ORDER BY 子句。

在这个查询中，每个 OLAP 函数中都包括 ORDER BY DATE 子句，这是为了确保使用正确的值来计算累加总计。在查询的最后，有一个外层的 ORDER BY DATE 子句，该子句用于控制结果集的显示。

关于该查询要注意的地方

西部和南部累加总计销售额之间的比较是通过在 SELECT 语句中用简单的算术计算实现的。

移动平均值

销售数字会随着时间而波动。当销售数字剧烈波动时，会给观察深层次的、长远的趋势造成困扰。移动平均值就是用来克服这些波动的影响的。例如，一个三周的移动平均值就是将最近连续三周的总计除以 3 得到的。

问题

在 2005 年第 3 季度，圣何塞和迈阿密产品销售额的三周移动平均值是多少？

OLAP 查询

SELECT　city,　week,　SUM(dollars)　AS　sales,　
　　　DEC(AVG(SUM(dollars))　OVER(partition　by　city　
　　　ORDER　BY　city,　week　ROWS　2　PRECEDING),7,2)　AS　mov_avg,　
　　　SUM(SUM(dollars))　OVER(PARTITION　BY　city　
　　　ORDER　BY　week　ROWS　unbounded　PRECEDING)　AS　run_sales　
　FROM　aroma.store　a,　
　　　aroma.sales　b,　
　　　aroma.period　c　
　WHERE　　a.storekey　=　b.storekey　
　　　AND　c.perkey　=　b.perkey　
　　　AND　qtr　=　'Q3_05'　
　　　AND　city　IN　('San　Jose',　'Miami')　
　GROUP　BY　city,　week;

在下面的结果集中，注意每个城市的前两行中的平均值不是三周移动平均值，因为此处还没有足够的数据来完成那样的计算。相反，这两个平均值分别是在第一行（一周平均值）和前两行（两周平均值）上计算出来的。

结果

City	Week	Sales	Mov_avg	Run_sales
Miami	27	1838.55	1838.55	1838.55
Miami	28	4482.15	3160.35	6320.70
Miami	29	4616.70	3645.80	10937.40
Miami	30	4570.35	4556.40	15507.75
Miami	31	4681.95	4623.00	20189.70
...
Miami	38	5500.25	5235.00	49493.35
Miami	39	4891.40	5346.71	54384.75
Miami	40	3693.80	4695.15	58078.55
...
San Jose	27	3177.55	3177.55	3177.55
San Jose	28	5825.80	4501.67	9003.35
San Jose	29	8474.80	5826.05	17478.15
San Jose	30	7976.60	7425.73	25454.75
San Jose	31	7328.65	7926.68	32783.40
San Jose	32	6809.75	7371.66	39593.15
San Jose	33	7116.35	7084.91	46709.50
...

DEC 函数用于定义在返回的结果中，Mov_Avg 列的值显示多少位数。

OLAP AVG 函数

OLAP AVG 函数是和下面的窗口帧一起使用的：

ROWS　n　PRECEDING

其中 n 是表示滑动因子的一个数字。在前面几个查询中，这个值通常是用关键字 unbounded 设置的。在这个查询中，由于需要基于三个行来计算移动平均值，因此插入值 2，表明是当前行加上前两行。

OLAP ORDER BY 子句确保 AVG 函数被应用到正确的移动连续行上（在这个例子中就是 WEEK）。PARTITION BY 子句标识出用于重设 AVG 函数的值（在这个例子中就是 CITY）。

OLAP SUM 函数

当窗口帧指定以下指令时，OLAP SUM 函数将产生移动总和：

ROWS　n　PRECEDING

这里也可以使用 FOLLOWING 关键字。

OLAP ORDER BY 子句很关键。这个子句用于确保 OLAP SUM 函数的输入行是经过正确排序的（在这个例子中是按 Date 的升序排列）。

排名

问题

西部各商店在销售总额方面的排名是怎样的？

OLAP 查询

SELECT　store_name,　district,　SUM(dollars)　AS　total_sales,　
　　　RANK()　OVER(ORDER　BY　SUM(dollars)　DESC)　AS　sales_rank　
　FROM　aroma.market　a,　
　　　aroma.store　b,　
　　　aroma.sales　c,　
　　　aroma.period　d　
　WHERE　　a.mktkey　=　b.mktkey　
　　　AND　b.storekey　=　c.storekey　
　　　AND　d.perkey　=　c.perkey　
　　　AND　year　=　2005　
　　　AND　month　=　'MAR'　
　　　AND　region　=　'West'　
　GROUP　BY　store_name,　district;

结果

Store_Name	District	Total_Sales	Sales_Rank
Cupertino Coffee Supply	San Francisco	18670.50	1
Java Judy's	Los Angeles	18015.50	2
Beaches Brew	Los Angeles	18011.55	3
San Jose Roasting Company	San Francisco	17973.90	4
Instant Coffee	San Francisco	15264.50	5
Roasters, Los Gatos	San Francisco	12836.50	6

首先计算 Sales 表中符合 WHERE 子句中搜索条件的每日销售额的总数，然后进行排名。

OLAP RANK 函数

IBM DB2 数据库支持一组 OLAP 排名函数，包括 RANK() 和 DENSE_RANK()。 这些函数不需要任何参数，因为 OLAP ORDER BY 子句定义了要排名的列或表达式。注意，OLAP 函数的默认排序顺序是升序（ASC），而业务查询常常不是按这个顺序排序的。为了从高到低排名（其中 1 是最高名次），必须在 OLAP ORDER BY 子句中指定 DESC 关键字。

DENSE_RANK 函数与 RANK 函数在一个方面存在区别：当使用 RANK 函数时，如果有两行或多行获得相同名次，那么接下来的行的名次就会跳跃下降。例如，如果有两个行的名次都是 2，那么这两行之后的那一行的名次就是 4（即 1,2,2,4）。当使用 DENSE_RANK 时，名次之间是连续的，不存在跳跃下降的现象。例如，如果有两个行的名次都是 8，那么这两行之后的那一行的名次仍然是 9，如下面的例子中所示：

SELECT　prod_name,　dollars,　DENSE_RANK()　OVER(ORDER　BY　dollars　DESC)　AS　dense_rank,　
　　　RANK()　OVER(ORDER　BY　dollars　DESC)　AS　tie_rank　
　FROM　aroma.product　a,　
　　　aroma.sales　b,　
　　　aroma.period　c　
　WHERE　　a.prodkey　=　b.prodkey　
　　　AND　a.classkey　=　b.classkey　
　　　AND　c.perkey　=　b.perkey　
　　　AND　date　=　'01-03-2006';

结果

PROD_NAME	DOLLARS	DENSE_RANK	TIE_RANK
Espresso Machine Italiano	499.75	1	1
Cafe Au Lait	392.00	2	2
Veracruzano	360.00	3	3
Lotta Latte	328.00	4	4
NA Lite	306.00	5	5
Colombiano	283.50	6	6
Darjeeling Special	207.00	7	7
Colombiano	202.50	8	8
Colombiano	202.50	8	8
Expresso XO	201.50	9	10
Xalapa Lapa	195.50	10	11
...

使用 DATE 运算

问题

计算给定日期前 90 天的日期和后 90 天的日期。

例子查询

SELECT　date　-　90　DAYS　AS　due_date,　
　　　date　AS　cur_date,　
　　　date　+　90　DAYS　AS　past_due　
　FROM　aroma.period　
　WHERE　year　=　2004　
　　　AND　month　=　'JAN';

结果

Due_Date	Cur_Date	Past_Due
2003-10-03	2004-01-01	2004-03-31
2003-10-04	2004-01-02	2004-04-01
2003-10-05	2004-01-03	2004-04-02
...

加减日期

可以对一个日期进行加减运算。为此，必须指定以下信息：

被加或被减的值（列名或日期时间表达式）

指定增量度量的日期单位。可以是天、月、年或这些值的组合。

增量的正或负。

关于该查询

这个例子查询计算一个给定日期的前 90 天的日期和后 90 天的日期。该函数以 ANSI SQL 92 日期时间格式返回值。

一定要弄清应加减哪个值。例如，在大多数情况下 "90 days" 并不等于 "3 months"。如果将前面的查询改为 3 months，那么很可能会得到不同的结果集：

SELECT　date　-　3　MONTHS　AS　due_date,　
　　　date　AS　cur_date,　
　　　date　+　3　MONTHS　AS　past_due　
　FROM　aroma.period　
　WHERE　year　=　2004　
　　　AND　month　=　'JAN';

结果

Due_Date	Cur_Date	Past_Due
2003-10-01	2004-01-01	2004-04-01
2003-10-02	2004-01-02	2004-04-02
2003-10-03	2004-01-03	2004-04-03
...

如果不小心的话，这些差异会变得令人费解。为确保能得到需要的信息，您应该注意一些特殊情况，例如闰年。

还可以加减几个值的组合，例如：

SELECT　date　-　3　MONTHS　-　4　DAYS　AS　due_date,　
　　　date　AS　cur_date,　
　　　date　+　3　MONTHS　+　4　DAYS　AS　past_due　
　FROM　aroma.period　
　WHERE　year　=　2004　
　　　AND　month　=　'JAN';

结果

Due_Date	Cur_Date	Past_Due
2003-09-27	2004-01-01	2004-04-05
2003-09-28	2004-01-02	2004-04-06
2003-09-29	2004-01-03	2004-04-07
...

类似地，您可以加减任何 TIME 数据类型的各个部分（HOURS、MINUTES 和/或 SECONDS）或任何 TIMESTAMP 数据类型的各个部分（YEARS、MONTHS、DAYS、HOURS、MINUTES 和/或 SECONDS）。

使用 HAVING 子句排除分组

问题

2005 年有哪些产品的总销售额低于 $25000？

例子查询

SELECT　prod_name,　sum(dollars)　AS　total_sales　
　FROM　aroma.product　a,　aroma.sales　b,　aroma.period　c　
　WHERE　　a.prodkey　=　b.prodkey　
　　　AND　a.classkey　=　b.classkey　
　　　AND　c.perkey　=　b.perkey　
　　　AND　year　=　2005　
　GROUP　BY　prod_name　
　HAVING　sum(dollars)　<　25000　
　ORDER　BY　total_sales　DESC;

结果

Prod_Name	Total_Sales
Aroma t-shirt	21397.65
Espresso Machine Royale	18119.80
Espresso Machine Italiano	17679.15
Coffee Sampler	16634.00
Tea Sampler	14907.00
Aroma baseball cap	13437.20
Aroma Sheffield Steel Teapot	8082.00
Spice Sampler	7788.00
Aroma Sounds CD	5937.00
Aroma Sounds Cassette	5323.00
French Press, 4-Cup	4570.50
Spice Jar	4073.00
French Press, 2-Cup	3042.75
Travel Mug	1581.75
Easter Sampler Basket	1500.00
Coffee Mug	1258.00
Christmas Sampler	1230.00

分组上的条件：HAVING 子句

虽然将数据划分到分组可以减少返回的信息量，但是查询常常仍然会返回不需要的信息。您可以使用 HAVING 子句来排除不满足指定条件的分组，例如以销售总额小于或大于某个数作为条件。

这个查询计算每种产品在 2005 年的销售总额，然后只保留销售总额低于 $25000 的那些查询。

HAVING 子句的语法

SELECT　column　name(s)　
　FROM　table　name(s)　
　[WHERE　search_condition]　
　[GROUP　BY　group_list]　
　[HAVING　condition]　
　[ORDER　BY　order_list];

condition	一个 SQL 条件，其中可以包括集合函数

HAVING 子句与 WHERE 子句有以下区别：

WHERE 子句	HAVING 子句
对分组之前的行有影响	对分组之后的结果集有影响
条件不能用集合函数（例如 SUM 或 AVG）表达，但是可以为非聚合表达式使用列别名	条件可以用任何集合函数和列别名表达

使用中的注意事项

HAVING 子句中的条件可以引用任何集合函数。带有 HAVING 子句的查询必须包含一个 GROUP BY 子句，惟一的例外是 select 列表只包含集合函数。例如：

SELECT　MIN(prodkey),　MAX(classkey)　
　FROM　aroma.product　
　HAVING　MIN(prodkey)　=　0;

结束语

本教程是系列教程中的第 4 部分，本教程描述了如何：

使用 SQL OLAP 函数执行数据分析；

使用 DATE 标量函数和 DATE 运算从 DATETIME 列计算和提取信息。

分析函数

SQL OLAP 函数可用于回答要求计算排名、比例、移动总和和移动平均值等诸多业务问题。本教程中的例子主要集中在可以由 OLAP 函数处理的一些计算上面。一般来说，OLAP 函数非常灵活，可以大大简化复杂的 SQL。

本文示例源代码或素材下载