sql 查询子集_如何使用SQL查找最近的子集总和

最新推荐文章于 2024-04-24 10:08:55 发布

danpu0978

最新推荐文章于 2024-04-24 10:08:55 发布

阅读量938

点赞数

文章标签：算法数据库 python java 人工智能

原文链接：https://www.javacodegeeks.com/2015/10/how-to-find-the-closest-subset-sum-with-sql.html

版权

sql 查询子集

最近，我偶然发现了关于Stack Overflow这个非常有趣的问题。标题是：

[如何]比较数字和数字子集的总和

在本文中，我们将把用户的命令式方法与极其优雅的（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的工作原理：

幻灯片摘自jOOQ高级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

sql 查询子集

danpu0978

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql 查询子集_如何使用SQL查找最近的子集总和

sql 查询子集最近，我偶然发现了关于Stack Overflow这个非常有趣的问题。标题是： [如何]比较数字和数字子集的总和在本文中，我们将把用户的命令式方法与极其优雅的（Oracle）SQL方法进行比较。我们将利用这些出色SQL功能的任意组合：视窗功能 FIRST和LAST函数，使用特定于Oracle的语法 LATERAL JOIN （或APPLY ...
复制链接

扫一扫