使用 OLAP 函数的一些实用技巧

　　在线分析处理（online analytical processing，OLAP）函数非常灵活，也很强大。通过使用这些函数，您可以为一些问题找到简单的解决方案，而这些问题本来要么是要迭代一个或者多个游标，要么是要进行递归。在其他某些情况下，编写查询时，使用 OLAP 函数或辅助表比起不使用它们来要容易得多。

　　本文没有对 OLAP 函数作什么描述，而是描述了实际生活中的一些情况，在这些情况中，往往是通过使用 OLAP 函数得出了简单的解决方案。

　　使用连续数或日期生成辅助表

　　辅助表不包含用户的数据。相反，这些表用于简化查询的编写。我们将使用两个辅助表：

　　顺序表，即连续整数的列表，从 1 开始，以某个最大数结束。
日历表，即介于开始日期和结束日期之间的连续日期的列表。

　　本章中，我们将学习如何使用 OLAP 函数填充辅助表。

　　注意：Joe Celko 撰写的“SQL for Smarties”一书对辅助表作了详细讨论。

　　让我们创建并填充一个顺序表。使用一个 OLAP 函数 ROW_NUMBER() 为结果集中的各行提供连续数（consecutive number），像这样：

select ROW_NUMBER() OVER(), TABNAME FROM SYSCAT.TABLES where TABSCHEMA=’DB2ADMIN’           1TABNAME          ——————————————-         1 CONSECUTIVE_NUMS         2 DATES         3 EXPLAIN_ARGUMENT         4 EXPLAIN_INSTANCE         5 EXPLAIN_OBJECT         6 EXPLAIN_OPERATOR         7 EXPLAIN_PREDICATE         8 EXPLAIN_STATEMENT         9 EXPLAIN_STREAM         10 SALES_DETAIL         11 SALES_DETAIL_CLS         11 record(s) selected.

        
      

　　注意，即使没有被选中的列，也会提供连续数。例如，让我们使用从 1 开始的连续数来填充一个表。方法如下：

create TABLE NUMBER_SEQ(NUM INT NOT NULL);          insert INTO NUMBER_SEQ select ROW_NUMBER() OVER() FROM           SYSCAT.COLUMNS;

　　从 1 开始的连续数将被插入到表 NUMBER_SEQ 中，在系统视图 SYSCAT.COLUMNS 中，每个数字对应一行（我选择了 SYSCAT.COLUMNS，是因为它出现在每个数据库中，并且总是有 1,000 多行。您可以使用任何表，只要这个表有足够多（但不要太多）的记录）。在 NUMBER_SEQ 中会有 SYSCAT.COLUMNS 表中那么多的行：

select MIN(NUM) AS MIN_NUM, MAX(NUM) AS MAX_NUM, count(*) AS NUM_REC FROM NUMBER_SEQ   MIN_NUM     MAX_NUM     NUM_REC      ———– ———– ———–                      1        3197        3197     1 record(s) selected.

 

　　使用连续日期来填充一个日历表很容易：

create TABLE DATE_SEQ(SOME_DATE DATE NOT NULL);          insert INTO DATE_SEQ select DATE(’01/01/2003’) + (ROW_NUMBER()           OVER() – 1) DAYS FROM SYSCAT.COLUMNS;

　　我们已经看到了如何使用 OLAP 函数 ROW_NUMBER() 来创建和填充辅助表。显然，有两种方法可以完成这一任务。例如，我们可以使用递归或者在一个循环中插入记录。在这种解决方案中，使用 ROW_NUMBER 的最大好处是简化了编程。

　　在接下来的三章中，我们将使用 NUMBER_SEQ 和 DATE_SEQ 这两个辅助表来简化某些本来比较复杂的查询。

　　使用顺序表打印发票

　　假设我们需要存储某杂货店一条收银线上的所有销售额。还需要能够打印任何一笔买卖的发票，像这样：

ITEM_NAME                      PRICE_PER_ITEM          —————————— ———————— NESCAFE CLASSIC                  +6.49000000000000E+000  ENGLISH BAGELS 6-PACK            +1.49000000000000E+000  ENGLISH BAGELS 6-PACK            +1.49000000000000E+000

 　　由于某些原因，我们选择不在数据库中存储重复的记录。也就是说，我们希望只存储一条记录 (’ENGLISH BAGELS 6-PACK’, 1.49, 2) ，而不是两条记录 (’ENGLISH BAGELS 6-PACK’, 1.49) 。

　　不存储重复行通常可以带来更多方便。因此，我们应该使用下面这样的表：

create TABLE SALE_ITEM( SALE_ID INT NOT NULL, ITEM_NAME VARchar(30) NOT NULL, ITEM_QUANTITY SMALLINT NOT NULL, PRICE_PER_ITEM FLOAT NOT NULL); insert INTO SALE_ITEM VALUES (1, ’NESCAFE CLASSIC’, 1, 6.49), (1, ’ENGLISH BAGELS 6-PACK’, 2, 1.49), (1, ’BABY CARROTS’, 3, 0.99);

 

　　我们需要编写一个查询，该查询的输出应该是这样的（实际上就是打印一张发票）：

ITEM_NAME                      PRICE_PER_ITEM            —————————— ————————  NESCAFE CLASSIC                  +6.49000000000000E+000  ENGLISH BAGELS 6-PACK            +1.49000000000000E+000  ENGLISH BAGELS 6-PACK            +1.49000000000000E+000  BABY CARROTS                     +9.90000000000000E-001  BABY CARROTS                     +9.90000000000000E-001  BABY CARROTS                     +9.90000000000000E-001

　　下面是一个非常简单的查询，这个查询可以实现上述功能：

select ITEM_NAME, PRICE_PER_ITEM FROM SALE_ITEM join NUMBER_SEQ
        
ON NUMBER_SEQ.NUM <= SALE_ITEM.ITEM_QUANTITY ORDER BY
        
PRICE_PER_ITEM DESC
      

　　注意：在不相等的条件下，联结两个表是完全合法的，像这个例子中就是如此。

　　如前一章所述，我们也可以通过使用递归来得到所需的结果。在某些情况下，递归或许更加有效。不过，使用了顺序表的代码看上去更简单，更易于理解，并且不必为避免可能由递归招致的无限循环而担忧。因此，在这种情况下，比起使用递归来，使用 ROW_NUMBER 是一种简单的替代方法。
如果不能创建辅助表，那么使用表表达式并生成连续数会比较容易，像这样：

select ITEM_NAME, PRICE_PER_ITEM FROM SALE_ITEM  join  (select ROW_NUMBER() OVER() AS NUM FROM SYSCAT.TABLES) AS NUMBER_SEQ ON NUMBER_SEQ.NUM <= SALE_ITEM.ITEM_QUANTITY ORDER BY PRICE_PER_ITEM DESC

　　结果完全一样，不过性能上慢了一点。

　　如何使用日历表简化查询

　　我从 [article on Pivot Tables] 中摘出了这个例子。首先，让我们创建一个表，并插入一些数据：

create TABLE BUSINESS_TRIP(EMPLOYEE_ID INT NOT NULL, DATE_FROM DATE NOT NULL, DATE_TO DATE NOT NULL); insert INTO BUSINESS_TRIP VALUES (1, DATE(’01/06/2003’), DATE(’01/10/2003’)), (1, DATE(’01/13/2003’), DATE(’01/17/2003’)), (1, DATE(’01/20/2003’), DATE(’01/24/2003’)), (1, DATE(’01/27/2003’), DATE(’01/31/2003’)), (2, DATE(’01/07/2003’), DATE(’01/08/2003’)), (3, DATE(’01/08/2003’), DATE(’01/09/2003’));

　　假设有一个简单的任务：“选择 2003 年 1 月份没有雇员出差的所有日子”，这时日历表 DATE_SEQ 就很好用了。

select SOME_DATE AS NOBODY_ON_TRIP FROM DATE_SEQ where SOME_DATE BETWEEN DATE(’01/01/2003’) AND DATE(’01/31/2003’) AND NOT exists(select * FROM BUSINESS_TRIP where SOME_DATE BETWEEN DATE_FROM AND DATE_TO); NOBODY_ON_TRIP  ————–  01/01/2003      01/02/2003      01/03/2003      01/04/2003      01/05/2003      01/11/2003      01/12/2003      01/18/2003      01/19/2003      01/25/2003      01/26/2003         11 record(s) selected.

　　这个查询非常简单。在[article on Pivot Tables]中讨论了一些肯定是更复杂的替代方案。

　　假设有一个类似的任务：“选择 2003 年 1 月份有两名以上雇员在出差的所有日子”，同样，这里日历表 DATE_SEQ 也提供了一个非常容易的方法：

select SOME_DATE AS THREE_OR_MORE_ON_TRIP FROM DATE_SEQ where SOME_DATE BETWEEN DATE(’01/01/2003’) AND DATE(’01/31/2003’) AND (select count(*) FROM BUSINESS_TRIP where SOME_DATE BETWEEN DATE_FROM AND DATE_TO) > 2; THREE_OR_MORE_ON_TRIP  ——————-  01/08/2003

 

　　同样，如果不能创建辅助表，我们就可以使用表表达式：

select SOME_DATE AS THREE_OR_MORE_ON_TRIP  FROM  (select DATE(’01/01/2003’) + ROW_NUMBER() OVER() DAYS AS SOME_DATE FROM SYSCAT.TABLES) AS DATE_SEQ where SOME_DATE BETWEEN DATE(’01/01/2003’) AND DATE(’01/31/2003’) AND (select count(*) FROM BUSINESS_TRIP where SOME_DATE BETWEEN DATE_FROM AND DATE_TO) > 2;

 

　　使用顺序表将两条记录布置到一行上
　　假设该表的结构和数据如下：

create TABLE VEHICLE_ACCIDENT( ACCIDENT_ID INT NOT NULL, TAG_NUMBER char(10) , TAG_STATE char(2) );  insert INTO VEHICLE_ACCIDENT VALUES(1,’123456’,’IL’),(1,’234567’,’IL’),(1,’34567TT’,’WI’);

 

　　（为了简单起见，这里省略了其他列）。注意，在一起事故中可能牵涉到不止两辆车。这就要求将两条记录布置到一行上，像这样（当牵涉到 3 辆车时）：

TAG_NUMBER_1 TAG_STATE_1 TAG_NUMBER_2 TAG_STATE_2  ———— ———– ———— ———–  123456       IL          234567       IL        3456TT       WI

　　通过使用 ROW_NUMBER() ，这一点很容易实现：

WITH VEHICLE_ACCIDENT_RN(ACCIDENT_ID, ROWNUM, TAG_NUMBER, TAG_STATE) AS (select ACCIDENT_ID, ROW_NUMBER() OVER() AS ROWNUM, TAG_NUMBER, TAG_STATE FROM VEHICLE_ACCIDENT) select LEFT_SIDE.TAG_NUMBER AS TAG_NUMBER_1, LEFT_SIDE.TAG_STATE AS TAG_STATE_1, RIGHT_SIDE.TAG_NUMBER AS TAG_NUMBER_2, RIGHT_SIDE.TAG_STATE AS TAG_STATE_2 FROM (select L.*, (L.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN L where MOD(ROWNUM,2)=1)AS LEFT_SIDE LEFT OUTER join (select R.*, (R.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN R where MOD(ROWNUM,2)=0)AS RIGHT_SIDE ON LEFT_SIDE.PAGENUM = RIGHT_SIDE.PAGENUM  where LEFT_SIDE.ACCIDENT_ID=1 AND (RIGHT_SIDE.ACCIDENT_ID=1 OR RIGHT_SIDE.ACCIDENT_ID IS NULL)

 

　　不管事故涉及的车辆是奇数还是偶数，该查询都给出了正确的结果。可以随意添加记录并进行检验。同样，如上一章所述，也可以通过一些其他的方法来解决这个问题。通过使用 ROW_NUMBER() ，我们可以得到一个很简单的解决方案，这个方案可以快捷地开发出来，并且易于理解。

　　使用 ROW_NUMBER() 为客人分配房间

　　让我们考虑一个简单的问题。假设有两个表：一个是宾馆可用房间的列表，一个是刚到的宾馆客人的列表。我们需要将可用的房间分配给刚到的客人，那么如果有空房间的话，应该尽量使每个客人得到一个房间。为了解决这样的问题，通常需要打开两个游标，一个是用于房间的游标，一个是用于客人的游标，然后迭代这两个游标，直到其中一个游标或者两个游标同时到达结尾处。下面是这个表的定义和一些样本数据：

create TABLE ROOM( ROOM_ID INT NOT NULL PRIMARY KEY, SMOKING char(1) NOT NULL CHECK(SMOKING IN (’N’,’Y’))); insert INTO ROOM VALUES (121,’Y’),(139,’N’),(142,’N’),(201,’Y’),(202,’N’); create TABLE GUEST( GUEST_ID INT NOT NULL PRIMARY KEY, SMOKER char(1) NOT NULL CHECK(SMOKER IN (’N’,’Y’))); insert INTO GUEST VALUES(321, ’N’),(17,’Y’),(57,’Y’),(91,’Y’),(2,’N’),(444,’N’); create TABLE GUEST_ASSIGNMENT( GUEST_ID INT NOT NULL PRIMARY KEY, ROOM_ID INT NOT NULL UNIQUE, FOREIGN KEY(GUEST_ID) REFERENCES GUEST(GUEST_ID), FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ROOM_ID));

 

　　如果使用 OLAP 函数 ROW_NUMBER() ，就不需要打开两个游标并迭代房间和客人。相反，我们可以简单地联结这两个表，并在房间与客人之间取得 1 对 1 的对应关系。为了理解其工作原理，让我们首先看看这个选择查询及其输出：

select ROOM_NUMBER, GUEST_NUMBER, ROOM_ID, GUEST_ID FROM (select ROW_NUMBER() OVER(ORDER BY ROOM_ID) AS ROOM_NUMBER, ROOM_ID FROM ROOM) AS R join (select ROW_NUMBER() OVER(ORDER BY GUEST_ID) AS GUEST_NUMBER, GUEST_ID FROM GUEST) AS G ON ROOM_NUMBER=GUEST_NUMBER ROOM_NUMBER          GUEST_NUMBER         ROOM_ID     GUEST_ID     ——————– ——————– ———– ———–                    1                    1         121           2                    2                    2         139          17                    3                    3         142          57                    4                    4         201          91                    5                    5         202         321

 

　　这个例子查询将 ROOM 表中的最多一条记录与 GUEST 表中的最多一条记录相联结。注意，您可以不像我那样指定排序（OVER(ORDER BY GUEST_ID)），而是宣称排序不重要（OVER()），甚至请求使用随机排序（OVER(ORDER BY RAND())）。在这种情况下，一个客人得不到一个房间，因为没有足够的空房间。可以随意添加记录到 ROOM 表中，以检验该查询在其他情况下（例如有客人那么多的房间，或者没有客人那么多的房间）的工作情况。

　　如果理解了联结的工作原理，填充 GUEST_ASSIGNMENT 表就比较容易了：

insert INTO GUEST_ASSIGNMENT select GUEST_ID, ROOM_ID FROM (select ROW_NUMBER() OVER(ORDER BY ROOM_ID) AS ROOM_NUMBER, ROOM_ID FROM ROOM) AS R join (select ROW_NUMBER() OVER(ORDER BY GUEST_ID) AS GUEST_NUMBER, GUEST_ID FROM GUEST) AS G ON ROOM_NUMBER=GUEST_NUMBER

 

　　我们已经看到，在这种情况下使用 ROW_NUMBER() 函数可以为我们的开发节省很多力气。我们不必打开两个游标并迭代它们。

　　使用 ROW_NUMBER() OVER(PARTITION … ) 将不抽烟的客人分配到“无烟”房间
前一章的示例过于简单。这里让我们更接近现实一点。让我们确保吸烟的客人住进允许吸烟的房间，而不吸烟的客人则住进无烟房间。下面的查询就实现了这一点：

select ROOM_NUMBER, GUEST_NUMBER, SMOKER, SMOKING, ROOM_ID, GUEST_ID FROM (select ROW_NUMBER() OVER(PARTITION BY SMOKING ORDER BY ROOM_ID) AS ROOM_NUMBER, ROOM_ID, SMOKING FROM ROOM) AS R join (select ROW_NUMBER() OVER(PARTITION BY SMOKER ORDER BY GUEST_ID) AS GUEST_NUMBER, GUEST_ID, SMOKER FROM GUEST) AS G ON ROOM_NUMBER=GUEST_NUMBER AND SMOKER=SMOKING ROOM_NUMBER          GUEST_NUMBER         SMOKER SMOKING ROOM_ID     GUEST_ID     ——————– ——————– —— ——- ———– ———–                     1                    1 N      N               139           2                     2                    2 N      N               142         321                     3                    3 N      N               202         444                     1                    1 Y      Y               121          17                     2                    2 Y      Y               201          57

 

　　同样，如果理解了如何联结 GUEST 和 ROOM 表中的记录，就可以通过一条简单的 insert 语句填充 GUEST_ASSIGNMENT 表。这种方法显然比迭代客人上的游标和房间上的游标要容易得多。

　　使用累加和将货物箱分配给卡车

　　本章中要讨论的这个问题非常类似于前面的两个问题。这次我将演示累加和是如何简化查询的开发的。

　　假设需要将一些大小一致的箱子装载到几辆容量不等的卡车上。实际上这是一个非常常见的资源分配问题，这种问题通常使用游标来解决。

　　下面是表的定义以及一些样本数据：

create TABLE TRUCK( TRUCK_ID INT NOT NULL PRIMARY KEY, CAPACITY SMALLINT NOT NULL); insert INTO TRUCK VALUES(11,3), (22, 2), (33,3); create TABLE CARGO_BOX( CARGO_BOX_ID INT NOT NULL PRIMARY KEY, DESCRIPTION VARchar(40)); insert INTO CARGO_BOX VALUES(101,’PEACHES’),(102,’POTATOES’),(103,’TOMATOES’),(104,’TOMATOES’),(105,’TOMATOES’), (106,’PINEAPPLES’),(107,’PINEAPPLES’); create TABLE BOX_IN_TRUCK( TRUCK_ID INT NOT NULL, CARGO_BOX_ID INT NOT NULL, FOREIGN KEY(TRUCK_ID) REFERENCES TRUCK(TRUCK_ID),  FOREIGN KEY(CARGO_BOX_ID) REFERENCES CARGO_BOX(CARGO_BOX_ID));

 

　　问题是恰当地填充 BOX_IN_TRUCK 表，意即将箱子分配给卡车，使得没有卡车超载。通常需要使用游标来完成这一任务。不过，如果使用 OLAP 函数，即使没有游标也能完成这一任务。

insert INTO BOX_IN_TRUCK select TRUCK_CUMULATIVE.TRUCK_ID, CARGO_BOX.CARGO_BOX_ID FROM (select SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) – CAPACITY + 1 AS BOX_FROM,  SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) AS BOX_TO, CAPACITY, TRUCK_ID FROM TRUCK) AS TRUCK_CUMULATIVE join (select ROW_NUMBER() OVER() AS ROW_NUMBER, CARGO_BOX_ID FROM CARGO_BOX) AS CARGO_BOX ON ROW_NUMBER BETWEEN BOX_FROM AND BOX_TO;

 

　　让我们验证该查询是否产生了所需的结果：

select * FROM BOX_IN_TRUCK   TRUCK_ID    CARGO_BOX_ID  ———– ————  11          101           11          102           11          103           22          104           22          105           33          106           33          107

　　为了理解其工作原理，让我们检索查询中涉及的所有列：

select TRUCK_CUMULATIVE.TRUCK_ID, BOX_FROM, BOX_TO, CARGO_BOX.CARGO_BOX_ID, ROW_NUMBER FROM (select SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) – CAPACITY + 1 AS BOX_FROM,  SUM(CAPACITY) OVER(ORDER BY TRUCK_ID) AS BOX_TO, CAPACITY, TRUCK_ID FROM TRUCK) AS TRUCK_CUMULATIVE join (select ROW_NUMBER() OVER() AS ROW_NUMBER, CARGO_BOX_ID FROM CARGO_BOX) AS CARGO_BOX ON ROW_NUMBER BETWEEN BOX_FROM AND BOX_TO; TRUCK_ID    BOX_FROM    BOX_TO      CARGO_BOX_ID ROW_NUMBER            ———– ———– ———– ———— ——————–           11           1           3          101                    1           11           1           3          102                    2           11           1           3          103                    3           22           4           5          104                    4           22           4           5          105                    5           33           6           8          106                    6           33           6           8          107                    7     7 record(s) selected.