生成行组合的最快方法

Question

我们有一个计划表，每个计划都有许多服务。我们希望有一种快速的方法来查找不包含重复服务但包含某些服务的计划组合。

例如表格计划

id | service_1 | service_2 | ...
---------------------------------
1  |   true    |  true     | ...
2  |   true    |  false    | ...
3  |   false   |  true     | ...

例如包含 service_1 和 service_2 的有效组合

更新

如果有 2 项服务并且我需要这两项服务，我们将组合最多 2 行（或计划），因为每行中至少可以包含 1 项服务。

id | service_1 | service_2 | id | service_1 | service_2 |
---------------------------------------------------------
1  |   true    |  true     |NULL|    NULL   |    NULL   |
2  |   true    |  false    | 3  |    false  |    true   |

更新

它目前通过积极的修剪或行自行连接来工作。查询是根据服务数量动态生成的。它创建了有效连接条件的排列，使得发布不切实际。

目前，成本按计划数量 ^ 服务数量的顺序排列。

我最感兴趣的是解决这个问题的其他方法，不一定是对当前方法的改进。

Answer 1

这似乎工作正常

设置数据

DROP TABLE IF EXISTS plan;
CREATE TABLE plan (id int, service1 bool, service2 bool, service3 bool);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (1, 1, 0, 0);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (2, 0, 1, 0);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (3, 1, 1, 1);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (4, 1, 0, 1);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (5, 0, 0, 1);

查询

select *
from plan A
left join (
    select id, service1, service2, service3 from plan
    union all
    select null, null, null, null) B on B.id > A.id or B.id is null
left join (
    select id, service1, service2, service3 from plan
    union all
    select null, null, null, null) C on C.id > B.id or C.id is null
WHERE (A.service1 + A.service2 + A.service3)
  AND (A.service1 + ifnull(B.service1,0) + ifnull(C.service1,0)) = 1
  AND (A.service2 + ifnull(B.service2,0) + ifnull(C.service2,0)) = 1
  AND (A.service3 + ifnull(B.service3,0) + ifnull(C.service3,0)) = 1

结果

id | service1 | service2 | service3 | id | service1 | service2 | service3 | id | service1 | service2 | service3
1 | 1 | 0 | 0 | 2 | 0 | 1 | 0 | 5 | 0 | 0 | 1
1 | 1 | 0 | 0 | 5 | 0 | 0 | 1 | 2 | 0 | 1 | 0
2 | 0 | 1 | 0 | 4 | 1 | 0 | 1 | NULL | NULL | NULL | NULL
2 | 0 | 1 | 0 | NULL | NULL | NULL | NULL | 4 | 1 | 0 | 1
3 | 1 | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL

Answer 2

正如我在评论中提到的，确定“重复”的规则尚不清楚。然而，从它的声音来看，你只是在做按位与。

With RawData As
    (
    Select 1 As id, 1 As service_1, 1 As service_2
    Union All Select 2, 1, 0
    Union All Select 3, 0, 1
    )
    , BinData As
    (
    Select A.id, A.service_1, A.service_2
        , A.service_1 * 2 + A.service_2 As Bin
    From RawData As A
    )
Select *
From BinData As F1
    Left Join BinData As F2
        On F2.id <> F1.id
            And F1.Bin & F2.Bin = 0
Order By F1.id

但是，您会注意到在此解决方案中我得到了 id=3 的行。出于同样的原因，id=3 是 id=2 的“重复”，反之亦然。

如果这是不正确的，我们需要更清晰的数据和一些更好的样本数据来说明什么是“重复”和什么不是“重复”的边缘情况。

更新

鉴于cyberwiki在评论中所述，如果每个计划所寻求的是另一个计划，当组合起来时仅提供一次所有服务，那么所寻求的是一个二进制的恭维，它将产生所有1 的。我们可以通过查找所有计划来做到这一点，这些计划在与当前计划进行异或时会产生所有计划：

With RawData As
    (
    Select 1 As id, 1 As service_1, 1 As service_2
    Union All Select 2, 1, 0
    Union All Select 3, 0, 1
    )
    , BinData As
    (
    Select A.id, A.service_1, A.service_2
        , A.service_1 * 2 + A.service_2 As Bin
    From RawData As A
    )
Select *, F1.Bin ^ F2.Bin
From BinData As F1
    Left Join BinData As F2
        On F2.id <> F1.id
            And F1.Bin ^ F2.Bin = 3
Order By F1.id

再次注意，id=3 将显示在结果中，因为正如 id=3 与 id=2 完美匹配一样，反之亦然也是如此。