最近,我偶然发现了关于堆栈溢出这个非常有趣的问题。 标题是:
在本文中,我们将把用户的命令式方法与极其优雅的(Oracle)SQL方法进行比较。 我们将利用这些出色的SQL功能的任意组合:
- 视窗功能
- FIRST和LAST函数 ,使用特定于Oracle的语法
-
LATERAL JOIN
(或APPLY
- 递归SQL
问题
提出这个问题的用户alhashmiya正在寻找解决方案,以找到数字A子集中的“最接近”元素总和到一组“预期”总和B中。更具体地说,alhasmiya具有以下两个表格:
ID ASSIGN_AMT
--------------
1 25150
2 19800
3 27511
和…
ID WORK_AMT
------------
1 7120
2 8150
3 8255
4 9051
5 1220
6 12515
7 13555
8 5221
9 812
10 6562
ASSIGN_AMT
值是“预期”总和。 alhashmiya在寻找的是WORK_AMT
值A的子集的总和,以使该总和尽可能接近任何“预期”总和。 有两种方法可以了解此问题:
- 可能的“最接近”和被限制为以严格定义的顺序(例如,按
ID
排序)获得的和。 这种理解的一种应用是找出明确定义的有序运行总额(例如银行帐户余额)超过特定阈值的确切时间。 - 可能的“最接近”总和不受限制。 任何无序子集都有资格计算这样的总和。 这种理解的应用是找到离散值的组合以尽可能接近地达到目标值,例如以优化交易。
第二种理解称为“子集和问题” ,如果您正在寻找精确的解决方案,则只有指数算法。 重要的是要注意,无论采用何种解决方案技术,该算法都无法很好地扩展!
但首先让我们看一下更简单的理解:
1.计算值的有序子集的总和
从严格意义上讲,从某种意义上说,我们的意思是说我们要对所有WORK_AMT
值进行排序,例如按ID
排序,并且仅允许以这种特定顺序出现的总和。 即
ID WORK_AMT SUBSET_SUM
------------------------
1 7120 7120 (= WORK_AMT)
2 8150 15270 (= previous SUBSET_SUM + WORK_AMT)
3 8255 23525 (= previous SUBSET_SUM + WORK_AMT)
4 9051 32576 (= ...)
5 1220 33796
6 12515 46311
7 13555 59866
8 5221 65087
9 812 65899
10 6562 72461
上面的SUBSET_SUM
值是ID <= "current" ID
的所有WORK_AMT
值的总和。 我们之前在此博客上已经看到了这一点,这称为运行总计 , 最好使用如下窗口函数进行计算 :
WITH
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
)
SELECT
ID,
WORK_AMT,
SUM (WORK_AMT) OVER (ORDER BY ID) AS SUBSET_SUM
FROM
VALS
ORDER BY
ID
上面的窗口函数计算ID
小于或等于当前ID
的值“窗口”中所有WORK_AMT
值的总和。
用量化比较谓词找到这些和的“最接近”
现在,手头的任务是找到每个值ASSIGN_AMT
在25150
, 19800
和27511
的最接近的值SUBSET_SUM
。 从某种意义上说,我们想要做的是使表达式ABS(SUBSET_SUM - ASSIGN_AMT)
最小化。
但是, MIN()
聚合函数在这里无济于事,因为这只会返回该差异的最小值。 我们首先需要产生差异的SUBSET_SUM
值。
一种解决方案是使用量化比较谓词 ,这是一种在所有SQL数据库中均可使用的很少使用且不知名的比较运算符:
-- The common table expressions are the same as
-- in the previous examples
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
)
-- This is now the interesting part, where we
-- calculate the closest sum
SELECT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
SUBSET_SUM
FROM
ASSIGN
JOIN
SUMS
ON
ABS (ASSIGN_AMT - SUBSET_SUM) <= ALL (
SELECT
ABS (ASSIGN_AMT - SUBSET_SUM)
FROM
SUMS
)
量化的比较谓词可以直观地读取。 我们正在寻找特定SUBSET_SUM
其差异“预期” ASSIGN_AMT
是小于或等于所有其他可能存在的差异。
上面的查询产生:
ID ASSIGN_AMT SUBSET_SUM
--------------------------
1 25150 23525
2 19800 23525
3 27511 23525
在这种情况下,总是一样的。
您可能已经注意到,在允许ASSIGN_AMT
为零的情况下(如果忽略负值的可能性),该解决方案并不完全正确-在这种情况下,我们将在JOIN
生成重复值。 更换时可以实现:
UNION ALL SELECT 4 , 0 FROM DUAL
现在,结果是:
ID ASSIGN_AMT SUBSET_SUM
--------------------------
1 25150 23525
2 19800 23525
2 19800 23525
3 27511 23525
一种解决方案是使用DISTINCT
删除那些重复项( 这是一种反模式。请参阅此列表中的#6 )。 更好的解决方案是通过比较ID
值使JOIN
谓词明确,即:
SELECT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
SUBSET_SUM
FROM
ASSIGN
JOIN
SUMS
ON
(ABS (ASSIGN_AMT - SUBSET_SUM), SUMS.ID) <= ALL (
SELECT
ABS (ASSIGN_AMT - SUBSET_SUM),
ID
FROM
SUMS
)
不幸的是,以上方法在Oracle中尚不可用,但仍会报告错误:
ORA-01796: this operator cannot be used with lists
Oracle仅支持使用相等的比较器而不是小于/大于比较器来比较元组/行值表达式 ,这很可惜。 同一查询在PostgreSQL中无法流畅地运行。
利用Oracle的FIRST功能找到这些总和中的“最接近”
Oracle具有一个非常有趣的功能, 可以按照给定的特定顺序将第一个(或最后一个)值保留在组的一组聚合值中,并仅对组中的那些值计算聚合函数。 以下SQL语句将说明这一点:
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
从本质上讲,我们分组从所有值SUMS
表中的每个ASSIGN_AMT
。 对于每个组,我们将根据标准ABS(ASSIGN_AMT - SUBSET_SUM)
在组中的行排序时出现的"FIRST"
行。 我们仅"KEEP"
保留”组中的那些行,并从这些行中保留最小的SUBSET_SUM
。
该查询将再次产生:
ID ASSIGN_AMT SUBSET_SUM
--------------------------
1 25150 23525
2 19800 23525
3 27511 23525
这是一个非常出色的功能,可以偶尔使用。
请记住, 当我们在window的PARTITION
中寻找FIRST_VALUE
(或LAST_VALUE
)时,我们最近在此博客上看到了类似的功能。 在标准SQL中,可以使用如下窗口函数来实现类似的目的:
SELECT DISTINCT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
FIRST_VALUE (SUBSET_SUM) OVER (
PARTITION BY ASSIGN.ID
ORDER BY ABS (ASSIGN_AMT - SUBSET_SUM)
) AS CLOSEST_SUM
FROM
ASSIGN
CROSS JOIN
SUMS
不幸的是,这些解决方案都产生重复项,我们必须通过GROUP BY
( KEEP
解决方案)或DISTINCT
( FIRST_VALUE
解决方案)将其删除。
用LATERAL JOIN找到这些和的“最接近”
一个不依赖删除重复项的更清洁的解决方案是使用Oracle 12c的新FETCH FIRST
子句以及CROSS JOIN LATERAL
(或CROSS APPLY
,这是相同的)
SELECT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
CLOSEST_SUM
FROM
ASSIGN
CROSS JOIN LATERAL (
SELECT
SUBSET_SUM AS CLOSEST_SUM
FROM
SUMS
ORDER BY
ABS (ASSIGN.ASSIGN_AMT - SUBSET_SUM)
FETCH FIRST 1 ROW ONLY
) SUMS
这是什么意思? 我们基本上是参加在每个值ASSIGN
只有FIRST 1 ROW
中SUMS
,责令由通常的标准。 我们需要LATERAL
(或APPLY
),因为这允许我们从JOIN
表达式的左侧也从右侧引用列,否则是不可能的。
在SQL Server(仅使用CROSS APPLY
)或PostgreSQL(仅使用CROSS JOIN LATERAL
)中支持相同的功能。
每当JOIN
的右侧取决于左侧时,横向都很有用。 与普通联接不同,这意味着JOIN
顺序将从左到右都是一成不变的,并且优化程序减少了一组联接算法选项。 在此类示例(使用ORDER BY
和FETCH FIRST
)中,或者在加入未嵌套的表值函数时,这很有用,我们将在以后的博客文章中介绍。
2.计算任何值子集的总和
到目前为止,我们已经对问题进行了简化。 这可能不是alhashmiya在其堆栈溢出问题上的意思。 他们可能想解决子集总和问题 ,找到WORK_AMT
值的任何子集的“最接近”总和。
我们将使用递归SQL来计算所有可能的总和。 首先,让我们记住递归SQL的工作原理:
在递归SQL中,我们需要在公用表表达式(Oracle中的WITH
子句,或PostgreSQL中的WITH RECURSIVE
子句)中WITH RECURSIVE
UNION ALL
查询。 UNION ALL
的第一个子查询生成递归的“种子行”, UNION ALL
的第二个子查询基于SELECT
从递归声明的表中进行SELECT
,从而生成递归。
因此,可以在这里看到该子集和问题的幼稚解决方案:
-- Repetition of the previous data
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
),
-- A new way of calculating all possible sums by
-- Recursively adding up all the sums
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
)
-- The same technique to match the "closest" sum
-- As before
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
递归很简单。 在递归的第一个子查询(“种子行”)中,我们选择WORK
每一行:
SELECT
WORK_AMT,
ID
FROM
WORK
在递归的第二个子查询(“ recusion rows”)中,我们将上一个递归步骤( SUMS
)的值与所有其余值( WORK
)相连接,即所有具有较高ID
的值:
SELECT
WORK_AMT + SUBSET_SUM,
GREATEST (MAX_ID, WORK.ID)
FROM
SUMS
JOIN
WORK
ON
SUMS.MAX_ID < WORK.ID
在此组合中,我们计算中间和( 顺便说一下,它也是一个运行总数 ),并计算出迄今为止最高的总和ID,以减少组合的数量。 后者是可以做的,因为求和是可交换的 。
与以前的方法相比,此解决方案的主要区别在于以下事实:我们现在在SUMS
表中生成了很多(非常多)不同的值。
在10个不同的WORK_AMT
值仍可接受的0.112s之后,数据库计算出:
ID ASSIGN_AMT CLOSEST_SUM
---------------------------
1 25150 25133
2 19800 19768
3 27511 27488
但是请注意,一旦您开始向VALS
表中添加值,该算法就会在时间和空间复杂度上开始爆炸式增长。 使用以下命令运行相同的查询,只有稍大一点的WORK
表已经需要16.3秒才能产生结果:
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
UNION ALL SELECT 11, 1234 FROM DUAL
UNION ALL SELECT 12, 61 FROM DUAL
UNION ALL SELECT 13, 616 FROM DUAL
UNION ALL SELECT 14, 2456 FROM DUAL
UNION ALL SELECT 15, 5161 FROM DUAL
UNION ALL SELECT 16, 414 FROM DUAL
UNION ALL SELECT 17, 516 FROM DUAL
UNION ALL SELECT 18, 617 FROM DUAL
UNION ALL SELECT 19, 146 FROM DUAL
),
结果将是:
ID ASSIGN_AMT CLOSEST_SUM
---------------------------
1 25150 25150
2 19800 19800
3 27511 27511
是否需要有关实际金额的证明? 使用递归SQL也很容易:
-- Repetition of the previous data
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
),
-- A new way of calculating all possible sums by
-- Recursively adding up all the sums
SUMS (SUBSET_SUM, MAX_ID, CALC) AS (
SELECT
WORK_AMT,
ID,
TO_CHAR(WORK_AMT)
FROM WORK
UNION ALL
SELECT
WORK_AMT + SUBSET_SUM,
GREATEST (MAX_ID, WORK.ID),
CALC || '+' || WORK_AMT
FROM
SUMS
JOIN
WORK
ON
SUMS.MAX_ID < WORK.ID
)
-- The same technique to match the "closest" sum
-- As before
SELECT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
MIN (SUBSET_SUM) KEEP (
DENSE_RANK FIRST
ORDER BY ABS (ASSIGN_AMT - SUBSET_SUM)
) AS CLOSEST_SUM,
MIN (CALC) KEEP (
DENSE_RANK FIRST
ORDER BY ABS (ASSIGN_AMT - SUBSET_SUM)
) AS CALCULATION
FROM
SUMS
CROSS JOIN
ASSIGN
GROUP BY
ASSIGN.ID, ASSIGN.ASSIGN_AMT
现在上面产生:
ID ASSIGN_AMT CLOSEST_SUM CALCULATION
------------------------------------------------------------
1 25150 25150 7120 + 8150 + 9051 + 812
2 19800 19800 1220 + 12515 + 5221 + 812
3 27511 27511 8150 + 8255 + 9051 + 1220 + 812
结论
SQL功能强大。 极其强大。 在本文中,我们使用了各种技术来计算子集和问题,或对其进行简化 。 我们已经展示了如何结合使用这些出色的SQL功能在Oracle或PostgreSQL中解决此问题:
- 视窗功能
-
KEEP FIRST
(仅在Oracle中) -
LATERAL JOIN
(或APPLY
- 递归SQL
翻译自: https://www.javacodegeeks.com/2015/10/how-to-find-the-closest-subset-sum-with-sql.html