mysql - 确定哪些商品被一起购买(杰卡德系数)

Question

我遇到了一个与数据相关的问题，我无法解决。我认为最简单的方法是提供一些示例数据和预期输出来澄清我的问题。

我正在尝试计算 Jaccard coefficient使用购买历史记录。为此，我需要找到一起购买的产品以及购买的次数。

我有以下数据可以这样做:

• category_id
• item_id
• 用户 ID

category_id 和 item_id 一起使产品具有唯一可识别性。我将下面的查询附加到带有一些示例数据的 SQL 导出。具有以下数据:

• 用户 A:购买了商品 1-1、2-2 和 1-3
• 用户 B:购买了商品 2-2、1-3、2-4 和 3-6
• 用户 C:购买了商品 1-1、2-2 和 1-5

期望的结果是:

i1.cat_id : i1.item_id : i2.cat_id : i2:item_id : count
    1     :     1      :      2    :      2     :   2
    1     :     1      :      1    :      3     :   1
    1     :     1      :      1    :      5     :   1
    2     :     2      :      1    :      3     :   2
    2     :     2      :      2    :      4     :   1
    2     :     2      :      3    :      6     :   1

重复出现项目颠倒的情况不是问题，甚至可能是需要的。

示例数据

CREATE TABLE `test_020516_01` (
  `cat_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `visit_id` varchar(2) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test_020516_01` (`cat_id`, `item_id`, `visit_id`) VALUES
(1, 1,  'A'),
(2, 2,  'A'),
(1, 3,  'A'),
(2, 2,  'B'),
(1, 3,  'B'),
(2, 4,  'B'),
(3, 6,  'B'),
(1, 1,  'C'),
(2, 2,  'C'),
(1, 5,  'C');

以下是我的最佳尝试，但尚未得到返回。对于某些组合，计数总计为 4，在这种情况下永远不会超过 3。

SELECT STAT.cat_id, STAT.item_id, STAT1.cat_id, STAT1.item_id, COUNT(*)
FROM test_020516_01 STAT
JOIN (
    SELECT
        cat_id, item_id, visit_id AS visit, count(*) AS count
    FROM
        test_020516_01
    WHERE
        cat_id != 0
    AND
        visit_id IS NOT NULL
    GROUP BY
        cat_id, item_id, visit_id
    ORDER BY
        count DESC
) STAT1
ON STAT.cat_id != STAT1.cat_id
AND STAT.item_id != STAT1.item_id
AND visit_id != STAT1.visit
GROUP BY
    STAT.cat_id, STAT.item_id, STAT1.cat_id, STAT1.item_id;

提前感谢您的帮助:)

Answer 1

我无法理解为什么你将结果总结为 STAT1，或者你到底想要什么，但也许下面的查询会为你指明正确的方向(因为它的结果与你的预期结果相匹配，尽管它包括您遗漏的一些组合，我不能说是为了简洁还是因为某些我不理解的要求):

# non-permutable couples within same visitor
SELECT i1.cat_id, i1.item_id, i2.cat_id, i2.item_id, COUNT(*)
FROM test_020516_01 i1
JOIN test_020516_01 i2 ON (i2.visit_id = i1.visit_id AND 
                           CONCAT(i1.cat_id, ';', i1.item_id) < CONCAT(i2.cat_id, ';', i2.item_id))
GROUP BY i1.cat_id, i1.item_id, i2.cat_id, i2.item_id
ORDER BY 1, 2, 3, 4