sql - Oracle SQL/PLSQL：具有重复数据的分层递归查询
问题描述
我在下面有一个递归函数，效果很好，但我现在发现有些数据不是唯一的，我需要一种方法来处理它。
FUNCTION calc_cost (model_no_ NUMBER,
revision_ NUMBER,
sequence_no_ IN NUMBER,
currency_ IN VARCHAR2)
RETURN NUMBER
IS
qty_ NUMBER := 0;
cost_ NUMBER := 0;
BEGIN
SELECT NVL (new_qty, qty), purch_cost
INTO qty_, cost_
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND sequence_no = sequence_no_
AND (purch_curr = currency_
OR purch_curr IS NULL);
IF cost_ IS NULL
THEN
SELECT SUM (calc_cost (model_no,
revision,
sequence_no,
purch_curr))
INTO cost_
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND (purch_curr = currency_
OR purch_curr IS NULL)
AND part_no IN (SELECT component_part
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND sequence_no = sequence_no_);
END IF;
RETURN qty_ * cost_;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 0;
END calc_cost;
以下标准是此功能失败的地方...part_no in (select component_part...。
样本数据：
rownum., model_no, revision, sequence_no, part_no, component_part, level, cost, purch_curr, qty
1. 62, 1, 00, XXX, ABC, 1, null, null, 1
2. 62, 1, 10, ABC, 123, 2, null, null, 1
3. 62, 1, 20, 123, DEF, 3, null, null, 1
4. 62, 1, 30, DEF, 456, 4, 100, GBP, 1
5. 62, 1, 40, DEF, 789, 4, 50, GBP, 1
6. 62, 1, 50, DEF, 024, 4, 20, GBP, 1
7. 62, 1, 60, ABC, 356, 2, null, null, 2
8. 62, 1, 70, 356, DEF, 3, null, null, 3
9. 62, 1, 80, DEF, 456, 4, 100, GBP, 1
10. 62, 1, 90, DEF, 789, 4, 50, EUR, 1
11. 62, 1, 100, DEF, 024, 4, 20, GBP, 1
如果我要将以下值传递给函数参数：model_no、revision、sequence_no（忽略货币，因为它与问题无关）：
62, 1, 20
我希望它仅汇总第 4-6 行 = 170，但是它汇总了第 4-6 行和第 9-11 行 = 340。
最终，这个函数将在下面的 SQL 查询中使用：
SELECT LEVEL,
SYS_CONNECT_BY_PATH (sequence_no, '->') PATH,
calc_cost (model_no,
revision,
sequence_no,
'GBP')
total_gbp
FROM prod_conf_cost_struct_clv
WHERE model_no = 62
AND revision = 1
CONNECT BY PRIOR component_part = part_no
AND PRIOR model_no = 62
AND PRIOR revision = 1
START WITH sequence_no = 20
ORDER BY sequence_no
如您所见，这也会引入component_part = part_no.
更新
除了提供的答案之外，我想我会扩展原始问题，以便同时处理货币和数量元素。我已更新示例数据以包括货币和数量。
如果我要将以下值传递给函数参数：model_no、revision、sequence_no、currency：
Input: 62, 1, 70, EUR
Expected Cost Output: 150
Input: 62, 1, 60, EUR
Expected Cost Output: 300
Input: 62, 1, 60, GBP
Expected Cost Output: 720
任何帮助将不胜感激。
提前致谢。
解决方案
注意：如果您在运行这些东西时遇到问题MATCH_RECOGNIZE，可能是因为您正在运行（不是太）旧版本的 SQL*Developer。尝试最新版本或改用 SQL*Navigator、TOAD 或 SQL*Plus。问题是“？” 字符，这会使 SQL*Developer 感到困惑，因为这是 JDBC 用于绑定变量的字符。
您遇到了数据模型问题。即，prod_conf_cost_struct_cvl表中的子记录没有显式链接到它们的父行。这就是“DEF”子组件引起问题的原因。如果没有显式链接，就无法干净地计算数据。
您应该更正此数据模型并parent_sequence_no为每条记录添加一个，以便（例如）您可以分辨出sequence_no80 是sequence_no70 的子代，而不是sequence_no20 的子代。
但是，由于我不能假设您有时间或权限来更改您的数据模型，所以我将按原样使用数据模型回答问题。
首先，让我们将QTY和添加PURCH_CURR到您的示例数据中。
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
select * from prod_conf_cost_struct_clv;
+----------+----------+-------------+---------+----------------+-----+------+-----+------------+ | MODEL_NO | REVISION | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR | +----------+----------+-------------+---------+----------------+-----+------+-----+------------+ | 62 | 1 | 0 | XXX | ABC | 1 | | 1 | GBP | | 62 | 1 | 10 | ABC | 123 | 2 | | 1 | GBP | | 62 | 1 | 20 | 123 | DEF | 3 | | 1 | GBP | | 62 | 1 | 30 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 40 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 50 | DEF | 024 | 4 | 20 | 1 | GBP | | 62 | 1 | 60 | ABC | 356 | 2 | | 1 | GBP | | 62 | 1 | 70 | 356 | DEF | 3 | | 1 | GBP | | 62 | 1 | 80 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 90 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 100 | DEF | 024 | 4 | 20 | 1 | GBP | +----------+----------+-------------+---------+----------------+-----+------+-----+------------+
注意：您没有显示测试数据中如何表示多种货币，因此我在此答案中对该问题的处理可能不正确。
好的，所以我们需要做的第一件真正的事情是计算出parent_sequence_no（它真的应该在你的表中 - 见上文）的值。由于它不在您的表中，因此我们需要计算它。我们将把它计算为比当前行小一sequence_no的最高sequence_no行和比当前行小一的 a level（我调用它lvl是为了避免使用 Oracle 关键字）。
为了有效地找到这个值，我们可以使用MATCH_RECOGNIZE功能来描述每个孩子的父行应该是什么样子。
我们将使用这个新parent_sequence_no列调用结果集corrected_hierarchy。
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
PARTITION BY model_no, revision
ORDER BY sequence_no desc
MEASURES (P.sequence_no) AS parent_sequence_no,
c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
-- C => child row
-- S* => zero or more siblings or children of siblings that might be
-- between child and its parent
-- P? => parent row, which may not exist (e.g., for the root operation)
PATTERN (C S* P?)
DEFINE
C AS 1=1,
S AS S.lvl >= C.lvl,
P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no )
SELECT * FROM corrected_hierarchy;
+----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+ | MODEL_NO | REVISION | PARENT_SEQUENCE_NO | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR | +----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+ | 62 | 1 | | 0 | XXX | ABC | 1 | | 1 | GBP | | 62 | 1 | 0 | 10 | ABC | 123 | 2 | | 1 | GBP | | 62 | 1 | 10 | 20 | 123 | DEF | 3 | | 1 | GBP | | 62 | 1 | 20 | 30 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 20 | 40 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 20 | 50 | DEF | 024 | 4 | 20 | 1 | GBP | | 62 | 1 | 0 | 60 | ABC | 356 | 2 | | 1 | GBP | | 62 | 1 | 60 | 70 | 356 | DEF | 3 | | 1 | GBP | | 62 | 1 | 70 | 80 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 70 | 90 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 70 | 100 | DEF | 024 | 4 | 20 | 1 | GBP | +----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+
现在，如果你愿意，你可以停在那里。您需要做的就是使用函数corrected_hierarchy中的逻辑calc_cost，替换
and part_no in (
select component_part
...
和
and parent_sequence_no = sequence_no_
但是，正如@Def 指出的那样，您确实不需要PL/SQL 函数来执行您正在尝试做的事情。
您似乎试图做的是打印分层材料清单，每个项目的级别成本（级别成本是项目的直接和间接子组件的成本）。
这是一个执行此操作的查询，将所有内容放在一起：
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
PARTITION BY model_no, revision
ORDER BY sequence_no desc
MEASURES (P.sequence_no) AS parent_sequence_no,
c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (C S* P?)
DEFINE
C AS 1=1,
S AS S.lvl >= C.lvl,
P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no ),
sequence_hierarchy_costs as (
SELECT model_no,
revision,
min(sequence_no) sequence_no,
purch_curr,
sum(h.qty * h.cost) hierarchy_cost
FROM corrected_hierarchy h
WHERE 1=1
connect by model_no = prior model_no
and revision = prior revision
and parent_sequence_no = prior sequence_no
group by model_no, revision, connect_by_root sequence_no, purch_curr )
SELECT level,
sys_connect_by_path(h.sequence_no, '->') path,
shc.hierarchy_cost
FROM corrected_hierarchy h
INNER JOIN sequence_hierarchy_costs shc ON shc.model_no = h.model_no and shc.revision = h.revision and shc.sequence_no = h.sequence_no and shc.purch_curr = h.purch_curr
WHERE h.model_no = 62
and h.revision = 1
START WITH h.sequence_no = 20
connect by h.model_no = prior h.model_no
and h.revision = prior h.revision
and h.parent_sequence_no = prior h.sequence_no;
+-------+----------+----------------+ | LEVEL | PATH | HIERARCHY_COST | +-------+----------+----------------+ | 1 | ->20 | 170 | | 2 | ->20->30 | 100 | | 2 | ->20->40 | 50 | | 2 | ->20->50 | 20 | +-------+----------+----------------+
parent_sequence_no如果一开始就在您的数据模型中，您会发现这会容易得多。
推荐阅读
- python - 有没有办法使用列表推导来创建参数化 lambda 函数的列表？
- angular - 捆绑到 UMD 错误无法从 dist\fesm5\my-api-name.js 解析“my-service”
- r - ggplot2 - 为控制变量的不同条件绘制拟合值
- sql - 返回所有用户，即使是那些不符合我标准的用户
- c# - 排序列表
关于对象的属性 - r - ggplot2中的指数衰减
- python - Numpy数组 - IndexError：数组索引过多
- javascript - 如何将此 json 对象返回为字符串
- r - 如何在换行符处终止我的模式？
- javascript - 如何在 React 中提交表单后消除对输入的关注