mysql - 检索包含不止一次一起出现的 object_id 列表的列和包含它出现次数的计数的第二列

Question

请注意，object_id 的 18,10 和 21 与 cart_id 3 相关联一次。我想知道组合是否在另一个 cart_id 中再次出现，以及在所有存在的行中出现了多少次。我希望有两列作为结果集“combination”和“combination_occurrence_count”

Answer 1

检查所有可能的组合是一项相当复杂的任务 too many of them .

但是，如果您稍微简化您的要求，您可以获得一些有用的东西。

让我们从查找两个项目的所有组合开始。一开始你可以试试下面的查询:

SELECT
      c1.cart_id   AS cart1_id
    , c1.object_id AS object1_id
    , c2.object_id AS object2_id
    , cx1.cart_id   AS cartX_id
    , cx1.object_id AS objectX1_id
    , cx2.object_id AS objectX2_id
FROM
    cart_item AS c1
    INNER JOIN cart_item AS c2 ON (
            c2.cart_id   = c1.cart_id
        AND c2.object_id > c1.object_id
    )
    INNER JOIN cart_item AS cx1 ON (
            cx1.cart_id   > c1.cart_id
        AND cx1.object_id = c1.object_id
    )
    INNER JOIN cart_item AS cx2 ON (
            cx2.cart_id   = cx1.cart_id
        AND cx2.object_id = c2.object_id
    )
ORDER BY
      c1.cart_id
    , c1.object_id
    , c2.object_id
    , cx1.cart_id
    , cx1.object_id
    , cx2.object_id

查询背后有两个想法:

• 获取存在于推车。只有一件商品的购物车将被排除在外。现存的唯一将分析组合(而不是所有可能的组合)。 [c1 & c2]
• 查找具有相同对象 ID 组合的其他购物车 [cx1 & cx2]

结果会是这样的:

cart1_id    object1_id  object2_id  cartX_id    objectX1_id objectX2_id
3   10  18  30  10  18
3   10  18  31  10  18
3   10  21  30  10  21
3   18  21  30  18  21
30  10  18  31  10  18

然后您可以将这些结果分组以获得“最受欢迎”的配对:

SELECT
      cx1.object_id AS object1_id
    , cx2.object_id AS object2_id
    , 1 + COUNT(DISTINCT cx1.cart_id) AS cnt
FROM
    cart_item AS c1
    INNER JOIN cart_item AS c2 ON (
            c2.cart_id   = c1.cart_id
        AND c2.object_id > c1.object_id
    )
    INNER JOIN cart_item AS cx1 ON (
            cx1.cart_id   > c1.cart_id
        AND cx1.object_id = c1.object_id
    )
    INNER JOIN cart_item AS cx2 ON (
            cx2.cart_id   = cx1.cart_id
        AND cx2.object_id = c2.object_id
    )
GROUP BY
      cx1.object_id
    , cx2.object_id
ORDER BY
    cnt DESC
LIMIT
    20

结果:

object1_id  object2_id  cnt
10  18  3
10  21  2
18  21  2

所以 10 + 18 对最受欢迎，并且存在于 3 个购物车中。第 10 + 21 对和第 18 + 21 对在 2 个不同的推车中。

您可以继续对 3 对象组合执行类似的操作。

附言我使用了以下数据集(向您的数据添加了几行以获得更有趣的结果):

id  cart_id object_id
10  2   24
9   3   10
3   3   18
19  3   21
12  4   24
1   7   30
5   9   24
2   11  10
20  14  12
14  14  18
8   14  27
13  15  11
7   16  9
18  16  13
15  20  11
6   21  6
4   23  5
17  23  6
16  25  16
11  29  11
23  30  1
21  30  10
22  30  18
24  30  21
25  31  10
26  31  18

附言我没有花太多时间在这上面，所以我可能在查询中遗漏了一些东西。不过，我希望您能理解总体思路。