sql - 使用 Oracle SQL 窗口函数以部分数量满足多个需求的 SQL 查询
问题描述
我想解决一个在我的 SQL 历史中多次提出的抽象问题。如果我们想象我们是一家销售公司并谈论我们想要销售的零件(产品),那么这个抽象问题可以更好地实现,因此在不同的日期需要不同的数量。另一方面,我们有我们的“填充可能性”,例如来自库存、生产、在不同日期和数量的采购的数量。
我想仅使用一个查询(无程序,无临时表)来满足按所需日期和可用日期排序的需求。
作为技术基础,您可以假设有两个表:
- NEED_TABLE,列出多个需求、所需日期和数量。
- FILL_TABLE,列出多个“填充”、可用日期和数量。
在我的示例中,有两个需求:
- 需要 A:我们需要 2019 年 1 月 2 日第 4 部分的零件编号 123
- 需要 B:我们需要 2019 年 6 月 2 日第 2 部分的零件编号 123
我们有两个不同数量的“填充物”:
- 填写 X:我们在采购订单中的 2 数量中有 123 零件,可于 2019 年 1 月 1 日
- 填写 Y:我们在采购订单中有 4 个零件编号 123,可用 2019 年 6 月 1 日
结果应该是:
- 我需要 2019 年 1 月 2 日数量为 4 的零件编号 123(“需要 A”),由数量 2 中的采购订单(“填充 X”)和数量 2 中的另一个采购订单(“填充 Y” -部分的)。
- 我需要 2019 年 6 月 2 日数量为 2 的零件编号 123(“需要 B”),由数量为 2 的采购订单填充(“填充 Y”- 部分)。
SQL查询:
with
NEED_TABLE
as
(select 'A' NEED_ID, 123 partno, to_date('01/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 4 NEED_QTY from dual
union all
select 'B' NEED_ID, 123 partno, to_date('06/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 2 NEED_QTY from dual),
FILL_TABLE
as
(select 'X' FILL_ID, 123 partno, to_date('01/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 2 FILL_QTY from dual
union all
select 'Y' FILL_ID, 123 partno, to_date('06/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 4 FILL_QTY from dual)
select NEED_TABLE.NEED_ID
, NEED_TABLE.PARTNO
, NEED_TABLE.DATE_NEEDED
, NEED_TABLE.NEED_QTY
, FILL_TABLE.FILL_ID
, FILL_TABLE.DATE_AVAILABLE
, FILL_TABLE.FILL_QTY
/* all following is wrong/incomplete */
, lag(need_QTY - fill_QTY, 1, need_QTY)
over(
partition by NEED_ID
order by DATE_NEEDED, DATE_AVAILABLE) REAL_NEED_QTY
, greatest(
lag(need_QTY - fill_QTY, 1, need_QTY)
over(
partition by NEED_ID
order by DATE_NEEDED, DATE_AVAILABLE)
- FILL_QTY
, 0) LEFT_NEED_QTY
, abs(
least(
lag(need_QTY - fill_QTY, 1, need_QTY)
over(
partition by NEED_ID
order by DATE_NEEDED, DATE_AVAILABLE)
- FILL_QTY
, 0)) LEFT_FILL_QTY
from NEED_TABLE, FILL_TABLE
order by DATE_NEEDED, DATE_AVAILABLE;
如果您检查该查询的结果,第一个 NEED_ID“A”似乎一切正常。但是当它继续使用 NEED_ID“B”时,它不记得在填充需求“A”时 FILL_ID X 和 Y 已经减少了。
我期望结果如下:
NEED_ID A is filled by FILL_ID X qty 2
NEED_ID A is filled by FILL_ID Y qty 2
(NEED_ID A is filled by FILL_ID X qty 0)
NEED_ID B is filled by FILL_ID Y qty 2
需要表:
| NEED_ID | PARTNO | DATE_NEEDED | NEED_QTY |
|---------|--------|-------------|----------|
| A | 123 | 01/02/2019 | 4 |
| B | 123 | 06/02/2019 | 2 |
填充表:
| FILL_ID | PARTNO | DATE_AVAILABLE | FILL_QTY |
|---------|--------|----------------|----------|
| X | 123 | 01/01/2019 | 2 |
| Y | 123 | 06/01/2019 | 4 |
预期查询结果:
| NEED_ID | PARTNO | DATE_NEEDED | NEED_QTY | FILL_ID | DATE_AVAILABLE | FILL_QTY | ***REAL_FILL*** | "WHY?" |
|---------|--------|-------------|----------|---------|----------------|----------|-----------------|---------------------------------------------------------------------|
| A | 123 | 01/02/2019 | 4 | X | 01/01/2019 | 2 | 2 | A needs 4, gets partially filled by X by 2 |
| A | 123 | 01/02/2019 | 4 | Y | 06/01/2019 | 4 | 2 | A still needs 2, gets completely filled by Y by 2 |
| B | 123 | 06/02/2019 | 2 | X | 01/01/2019 | 2 | 0 | B needs 2, can't get filled by X, because A already used that qty |
| B | 123 | 06/02/2019 | 2 | Y | 06/01/2019 | 4 | 2 | B still needs 2, gets completely filled by remaining qty of Y, by 2 |
非常感谢任何帮助-谢谢!
解决方案
这是我的尝试:
with
need(rn, nid, nq) as (
select 1, 'A', 4 from dual union all
select 2, 'B', 2 from dual ),
fill(rf, fid, fq) as (
select 1, 'X', 2 from dual union all
select 2, 'Y', 4 from dual ),
u as (
select rn, nid, -nq nq, null rf, null fid, null fq from need
union all select null, null, null, rf, fid, fq from fill),
c (crn, cnid, cnq, crf, cfid, cfq, rest, amt) as (
select rn, nid, nq, 0, fid, fq, nq, 0 from u where rn = 1
union all
select nvl(rn, crn), nvl(nid, cnid), nvl(nq, cnq),
nvl(rf, crf), nvl(fid, cfid), nvl(fq, cfq), rest + nvl(nq, fq),
least(abs(rest), abs(nvl(nq, fq)))
from c
join u on rest >= 0 and rn = crn + 1
or rest < 0 and rf = crf + 1 )
select cnid, cfid, amt from c where amt <> 0
我简化了数据,但partno
可以很容易地添加到连接中,并且partition by
正确的 row_numbers 和日期仅对排序行很重要。如果它们有更多含义,现在可以添加它们,但让我们从更清晰的东西开始。
这个怎么运作。need
并且fill
是我们的数据源。u
是这些表与需要的联合,并在单独的列中填充数据。需要这个联合才能使下一个查询正常工作。
c
是递归 CTE,从 first fill开始。它是我们的锚。在下一步中,我添加(加入)填充或需要行,具体取决于我们在之前获得的内容rest
。如果剩余值低于零,则意味着我们必须寻找下一个填充行。如果它更大,则意味着我们从填充中获得了盈余,我们可以寻找下一个需求。在每个步骤中,都会计算交易金额,该金额等于先前休息和当前加入的填充/需要的较低值。
最后我取交易的金额和双方。测试了一些例子。
推荐阅读
- autodesk-forge - MarkupsCore Extension 自定义代码后无法与 Autodesk View 交互
- python - 在熊猫中按组检查5个不同年份的值
- java - 如何在 Hibernate Criteria API 中使用 row_number(或任何具有相同行为的东西)?
- html - 如何将此代码仅转换为 HTML?
- javascript - 具有多个标题的多个图像模式
- php - 菜单滚动问题
- react-native - React Native:屏幕顶部的额外空白空间
- c# - 调用 HttpContext.Response.WriteAsync() 后,ASP.NET Core Web API 无法返回 JSON 格式的对象?
- python - 以当前日期时间格式保存屏幕截图,
- python - 如何使用 python 循环来迭代具有不同参数的相同代码?