时间戳

首页 > 解决方案 > SQL DB2：如何使用“运行总计算”动态加入 2 个表

sql - SQL DB2：如何使用“运行总计算”动态加入 2 个表

问题描述

我正在服务器上学习 SQL：IBM V7R1M0、DB2。

我正在尝试构建 SQL 报告。在找了几天类似的例子后，我在知识的海洋中推出了这个瓶子......

上下文：商店从仓库请求货物。这些货物是在托盘上挑选的。这些托盘在装载到卡车之前将被放在临时通道上。

规则 1：我们只希望一个商店的货盘在集结通道上（我们不想混合来自不同商店的货盘）

规则2：商店将占用附近的集结通道。

规则 3：暂存通道按那里的 ID 排序（有间隙）

表格1：

|-----|-----|-----------------|
| ID  |store|pallet_estimation|
|-----|-----|-----------------|
| 1   | A   | 35              |
| 2   | C   |  2              |
| 3   | B   | 30              |
|-----|-----|-----------------|

SELECT * FROM (
    VALUES (1, 'A', 35), (2, 'C', 2), (3, 'B', 30)
          ) T1(ID, store, pallet_estimation)

表 2：

|---------------|---------------|
|ID_staging_lane|pallet_capacity|
|---------------|---------------|
| 201           | 10            |
| 202           | 10            |
| 204           | 30            |
| 205           | 40            |
| 208           | 30            |
| 210           | 30            |
|---------------|---------------|

SELECT * FROM(
  VALUES (201, 10), (202, 10), (204, 30), (205, 40), (208, 30), (210, 30)
     ) T2(ID_staging_lane, pallet_capacity)

预期结果：

|-----------|--------|--------------------|---------------|------------------|
|T1_sequence|T1_store|T1_pallet_estimation|T2_staging_lane|T2_pallet_capacity|
|-----------|--------|--------------------|---------------|------------------|
| 1         | A      | 35                 | 201           | 10               |
| 1         | A      | 35                 | 202           | 10               |
| 1         | A      | 35                 | 204           | 30               |
| 2         | C      |  2                 | 205           | 40               |
| 3         | B      | 30                 | 208           | 30               |
|-----------|--------|--------------------|---------------|------------------|

谢谢你，查尔斯，给你时间。我会努力改善我的需求。

如果需要，我想按照顺序在几个分段通道上拆分/划分托盘估计

例子：

For store A which has 35 pallets, 
I want to use staging lane 201 then it remains 35 - 10 = 25 , 
then I want to use staging lane 202 then it remains 25 - 10 = 15, 
then I want to use staging lane 204 then it remains 15 - 30 = -15

then I want to continue with the store C on the next staging lane 205 then it remains 2 - 40 = -38

then I want to continue with the store B on the next staging lane 208 then it remains 30 - 30 = 0

您将如何开始构建它？- 有窗口功能？SUM() OVER() - 使用递归 SQL 吗？DECLARE FETCH - 是否可以在 SQL 中构建动态 JOIN？- 其他想法？

提前致谢，

雷诺

标签： sqldb2-400

解决方案

首先，v7r1 已经很老了……确切地说是 10 年……

其次，我不明白您要加入什么...我看不出任何可以解释为什么存储 A 最终在您的结果中有 3 行的原因。

第三，在任何 RDBMS 中都没有“动态连接”之类的东西。您可以有一个动态语句，其中可能包括一个联接。或者你可以有一个静态语句，它也可以包括一个连接。对于 IBM i 上的 Db2，只有将语句合并到 RPG/COLBOL 程序或 SQL 存储过程/函数中才重要。

现在说了这么多，让我向您介绍公用表表达式（CTE）。基本上与嵌套表表达式 (NTE) 相同，但 IMO 更易于遵循，并且 CTE 在 i 上也比 NTE 具有性能优势。

with T1 as (
  SELECT * FROM (
    VALUES (1, 'A', 35), (2, 'C', 2), (3, 'B', 30)
          ) T1(ID, store, pallet_estimation)
), T2 as (
SELECT * FROM(
  VALUES (201, 10), (202, 10), (204, 30), (205, 40), (208, 30), (210, 30)
     ) T2(ID_staging_lane, pallet_capacity)
), fitment as (
select T1.*, T2.*, row_number() OVER(partition by ID_STAGING_LANE) as rowNbr
from T1 join T2 on pallet_estimation <= pallet_capacity
)
select * from fitment where rowNbr = 1;

是with T1 as (<select statement>)公用表表达式；T2和fitment. _ 该with关键字仅用于第一个 CTE。

CTE 连接 T1 和 T2基于fitment哪个估计适合车道描述，为每个可能性分配一个 row_number。最终选择对每个车道进行第一次拟合。

CTE 的好处是您可以轻松构建它们并在进行过程中查看结果。您可以随时添加select * from MYCTE并查看到目前为止的内容。

请注意，如图所示，一个 CTE 可以引用另一个 CTE。（fitment参考T1和T2）

编辑
您需要在结果集中向前或向后使用的函数命名为LAG()and LEAD()。它们是Db2 for i 中内置的OLAP 功能的一部分。不幸的是，它们是在 7.3 中添加的。

您将需要使用用户定义的函数 (UDF) 来滚动您自己的版本，该函数使用所谓的scatchpad在对每一行的函数的调用之间保存数据。

我在 SQL 的 Scratchpad 上发现了一篇非常古老的文章Scribble，展示了如何在 RPG 中使用便笺簿。您也可以在 SQL 定义的 UDF 中使用它。

做一些谷歌搜索，看看你是否可以开始。如果您遇到问题，请在此处创建一个新问题。（或查看中端邮件列表

推荐阅读