使用DB2 UDB OLAP函数

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

在线分析处理函数非常灵活，也很强大。通过使用这些函数，您可以为一些问题找到简单的解决方案，而这些问题本来要么是要迭代一个或者多个游标，要么是要进行递归。

来源：IT专家网 2008年6月10日

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

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


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.