问题描述
我正在寻找您的专家的指导，以找出一种将数字与数字子集的总和进行比较的方法像
I am looking a guidance of your gurus to find out a way to Compare a Number with a sum of subset of numberslike
DECLARE
L_NUM_TO_COMPARE NUMBER := 0;
L_NUM_SUBSET NUMBER := 0;
BEGIN
FOR MAIN_REC IN (
SELECT 1 ID, 25150 ASSIGN_AMT FROM DUAL
UNION ALL
SELECT 2 ID, 19800 ASSIGN_AMT FROM DUAL
UNION ALL
SELECT 3 ID, 27511 ASSIGN_AMT FROM DUAL
) LOOP
L_NUM_TO_COMPARE := MAIN_REC.ASSIGN_AMT;
DBMS_OUTPUT.PUT_LINE( L_NUM_TO_COMPARE);
FOR C IN (
SELECT 1 ID, 7120 WORK_AMT FROM DUAL
UNION ALL SELECT 2 ID, 8150 WORK_AMT FROM DUAL
UNION ALL SELECT 3 ID, 8255 WORK_AMT FROM DUAL
UNION ALL SELECT 4 ID, 9051 WORK_AMT FROM DUAL
UNION ALL SELECT 5 ID, 1220 WORK_AMT FROM DUAL
UNION ALL SELECT 6 ID, 12515 WORK_AMT FROM DUAL
UNION ALL SELECT 7 ID, 13555 WORK_AMT FROM DUAL
UNION ALL SELECT 8 ID, 5221 WORK_AMT FROM DUAL
UNION ALL SELECT 9 ID, 812 WORK_AMT FROM DUAL
UNION ALL SELECT 10 ID, 6562 WORK_AMT FROM DUAL
ORDER BY 2 DESC
) LOOP
L_NUM_SUBSET := NVL(L_NUM_SUBSET,0) + C.WORK_AMT;
DBMS_OUTPUT.PUT_LINE( L_NUM_SUBSET);
/*
I NEED TO PUT SOME LOGIC HOW CAN I FIND NEAREST SUM OF SUBSET
*/
IF MAIN_REC.ASSIGN_AMT = L_NUM_SUBSET THEN
DBMS_OUTPUT.PUT_LINE( L_NUM_SUBSET);
END IF;
END LOOP;
END LOOP;
END;
我已经在这个论坛中搜索过，发现了一个问题子集的数字总和
I have been searched this forum and found a questionSum of Sub set of numbers
这对我几乎是相同的要求，我所需要的可以指出我如何在PL/SQL中做到这一点我有(Oracle DB 11g R2)
which is nearly a same requirement of me, what I need can some one point me how can I do this in PL/SQLI have (Oracle DB 11g R2)
推荐答案
您不需要PL/SQL即可解决此问题.仅使用SQL和我已经写了一篇博客文章来更详细地解释我的答案.
You don't need PL/SQL to solve this. This is an extremely interesting problem to be solved with SQL alone, and I've written up a blog post to explain my answer in more detail.
您提出问题的方式，是假设您实际上并未解决子集总和问题，但是这是一个更简单的问题，您想将一个数字与一个非常有限的子集进行比较，这些子集是按WORK_AMT升序排列且没有空格的子集.
The way you've presented your question, I'm assuming you're not really solving the subset sum problem, but a simpler problem where you want to compare a number to a very limited set of subsets, namely the ones that are ordered by WORK_AMT ascendingly, with no gaps.
这可以单独使用Oracle SQL来解决:
This can be solved with Oracle SQL alone:
WITH
ASSIGN(ID, ASSIGN_AMT) AS (
SELECT 1, 25150 FROM DUAL
UNION ALL SELECT 2, 19800 FROM DUAL
UNION ALL SELECT 3, 27511 FROM DUAL
),
VALS (ID, WORK_AMT) AS (
SELECT 1 , 7120 FROM DUAL
UNION ALL SELECT 2 , 8150 FROM DUAL
UNION ALL SELECT 3 , 8255 FROM DUAL
UNION ALL SELECT 4 , 9051 FROM DUAL
UNION ALL SELECT 5 , 1220 FROM DUAL
UNION ALL SELECT 6 , 12515 FROM DUAL
UNION ALL SELECT 7 , 13555 FROM DUAL
UNION ALL SELECT 8 , 5221 FROM DUAL
UNION ALL SELECT 9 , 812 FROM DUAL
UNION ALL SELECT 10, 6562 FROM DUAL
),
SUMS (ID, WORK_AMT, SUBSET_SUM) AS (
SELECT VALS.*, SUM (WORK_AMT) OVER (ORDER BY ID)
FROM VALS
)
SELECT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
MIN (SUBSET_SUM) KEEP (
DENSE_RANK FIRST
ORDER BY ABS (ASSIGN_AMT - SUBSET_SUM)
) AS CLOSEST_SUM
FROM
ASSIGN
CROSS JOIN
SUMS
GROUP BY
ASSIGN.ID, ASSIGN.ASSIGN_AMT
上面的结果:
ID ASSIGN_AMT CLOSEST_SUM
---------------------------
1 25150 29085
2 19800 20935
3 27511 29085
实际子集总和问题
请注意，此问题在时间和空间上具有指数级的复杂性.对于WORK表中的少量值，只能合理地解决它！
The actual subset sum problem
Beware that this problem has exponential complexity in time and space. It can only be solved reasonably for a small amount of values in the WORK table!
WITH
ASSIGN (ID, ASSIGN_AMT) AS (
SELECT 1, 25150 FROM DUAL
UNION ALL SELECT 2, 19800 FROM DUAL
UNION ALL SELECT 3, 27511 FROM DUAL
),
WORK (ID, WORK_AMT) AS (
SELECT 1 , 7120 FROM DUAL
UNION ALL SELECT 2 , 8150 FROM DUAL
UNION ALL SELECT 3 , 8255 FROM DUAL
UNION ALL SELECT 4 , 9051 FROM DUAL
UNION ALL SELECT 5 , 1220 FROM DUAL
UNION ALL SELECT 6 , 12515 FROM DUAL
UNION ALL SELECT 7 , 13555 FROM DUAL
UNION ALL SELECT 8 , 5221 FROM DUAL
UNION ALL SELECT 9 , 812 FROM DUAL
UNION ALL SELECT 10, 6562 FROM DUAL
),
SUMS (SUBSET_SUM, MAX_ID) AS (
SELECT WORK_AMT, ID FROM WORK
UNION ALL
SELECT WORK_AMT + SUBSET_SUM, GREATEST (MAX_ID, WORK.ID)
FROM SUMS JOIN WORK
ON SUMS.MAX_ID < WORK.ID
)
SELECT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
MIN (SUBSET_SUM) KEEP (
DENSE_RANK FIRST
ORDER BY ABS (ASSIGN_AMT - SUBSET_SUM)
) AS CLOSEST_SUM
FROM SUMS
CROSS JOIN ASSIGN
GROUP BY ASSIGN.ID, ASSIGN.ASSIGN_AMT
现在产生:
ID ASSIGN_AMT CLOSEST_SUM
---------------------------
1 25150 25133
2 19800 19768
3 27511 27488
这篇关于将数字与数字子集的总和进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！