sql - 查询以查找一个表中的行总和等于另一个表中的一个或多个行

Question

我需要查询方面的帮助。任务是获取一个表的行，其金额加起来将成为另一个表中的值，反之亦然。

示例说明:

Table 1:                                Table2:

  r_id   |    r_date   |   r_amt          p_id   |    p_date   |   p_amt    
---------+-------------+--------        ---------+-------------+--------
    1    |  2/23/2012  |   200              1    |  3/22/2012  |   450
---------+-------------+--------        ---------+-------------+--------
    2    |  3/21/2012  |   100              2    |  5/25/2012  |   530
---------+-------------+--------        ---------+-------------+--------
    3    |  4/12/2012  |   300              3    |  5/26/2012  |   700
---------+-------------+--------        ---------+-------------+--------
    4    |  4/18/2012  |   250              4    |  5/26/2012  |   40
---------+-------------+--------        ---------+-------------+--------
    5    |  5/20/2012  |   130
---------+-------------+--------
    6    |  5/21/2012  |   740
---------+-------------+--------

现在这些测试数据是这样的，表 1 的几行加起来成为表 2 的一行，反之亦然。

我想要一个查询来分析上述数据，使一个表中的记录总和等于另一个表中的一行。

分析完成后，它应该像这样将数据提供给新表。

让我们调用此表匹配

  m_id   |    tbl1     |   tbl2   | match_type
---------+-------------+----------+-----------
    1    |    1,4      |   1      |   n-1
---------+-------------+----------+-----------
    1    |    2,3,5    |   2      |   n-1
---------+-------------+----------+-----------
    1    |     6       |   3,4    |   1-n
---------+-------------+----------+-----------

现在，我正在计算每个表的总和并输入一个临时表，然后与该表进行比较以获得上述结果。但是对于超过 10 行的查询变得非常慢并且挂起我的开发服务器。

Link to my test Queries

执行此任务的有效方法是什么？

Answer 1

好的，这是一个粗略的答案。我没有测试过。递归 CTE 有一些奇怪的陷阱，我可能遇到了一个，但这应该能让你继续。可能还可能进行一些性能调整，但这可能会让您继续前进。

算法大致如下:

1. 生成所有行的所有排列

2. 将一侧的每个排列与另一侧的每一行进行比较

第一个将通过递归 CTE 完成。第二个是简单的连接。

WITH RECURSIVE table1_combos as (
     SELECT r_id as last_id, r_id::text as path, r_amt as amount
       FROM table1
  UNION ALL 
     SELECT r.r_id as last_id, p.path || ',' || r_id::text, p.amount + r_amt
       FROM table1_combos p
 CROSS JOIN table1 r
      WHERE r.r_id < p.last_id
),
RECURSIVE table2_combos AS (
     SELECT p_id as last_id, p_id::text as path, p_amt as amount
       FROM table2
  UNION ALL 
     SELECT p_id AS last_id, p.path || ',' || p_id::text, p.amount + p_amt
       FROM table2_combos p
 CROSS JOIN table2 
      WHERE p_id < p.last_id
)
SELECT c.path, p_id::text, c.amount, 'n-1' as type
  FROM table1_combos c
  JOIN table2 t ON c.amount = p_amt
UNION ALL
SELECT r_id::text, c.path, c.amount, '1-n' as type
  FROM table2_combos c
  JOIN table1 t ON r_amt = c.amount; 

至于性能，根本问题是您将有大量空间进行搜索。不幸的是，没有一种相当简单的方法可以做到这一点。组合空间非常大，并且随着您添加的每行的增加而变得更大。

嗯，重新审视我的估计。一个 10 行的表应该生成 630 万个组合，而一个 11 行的表应该生成 6860 万个组合。在 PostgreSQL 中，您可以使用以下 SQL 语句检查预期组合的数量:

select sum(factorial(11)/factorial(f)) from generate_series(1, 11) f;

对于一个 11 行的表。注意事项如下:

select sum(factorial(100)/factorial(f)) from generate_series(1, 100) f;

         sum                                                                    

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------
 1603607401161831447335715093560135199544316103019165207641822220922316539151565
30909999021448995531507013709811500779735358328288932830176709764490323163992001
.00000000000000000000
(1 row)

如果您有一个 100 行的表，您将需要等待一段时间......

现在您可以通过限制 CTE 本身来进一步解决这个问题，例如说“当您达到另一个表的最大值时停止”。