服务器 频道

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

  【IT168 服务器学院】使用 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. 

 


  

  
 

0
相关文章