如何在MySQL中实现食材共现邻接矩阵并存储关联关系

前提说明 #

假设你的原始数据表名为recipe_ingredients，包含id（食材归属的关联ID）、ingredient（食材名称）两列，若你的表名不同，替换后续代码中对应的表名即可。

第一步：生成食材共现关联关系表 #

1.1 新建存储结果的表 #

在phpMyAdmin的SQL运行框中执行以下建表语句：

CREATE TABLE ingredient_cooccurrence (
    ingredient1 VARCHAR(255) NOT NULL,
    ingredient2 VARCHAR(255) NOT NULL,
    frequency INT DEFAULT 0,
    PRIMARY KEY (ingredient1, ingredient2)
);

1.2 插入共现统计数据 #

执行以下SQL自动计算所有食材组合的共现频次并写入新表：

INSERT INTO ingredient_cooccurrence (ingredient1, ingredient2, frequency)
SELECT 
    i1.ingredient AS ingredient1,
    i2.ingredient AS ingredient2,
    CASE 
        WHEN i1.ingredient = i2.ingredient THEN 0
        ELSE COALESCE(COUNT(DISTINCT ri1.id), 0) 
    END AS frequency
FROM 
    (SELECT DISTINCT ingredient FROM recipe_ingredients) i1
CROSS JOIN 
    (SELECT DISTINCT ingredient FROM recipe_ingredients) i2
LEFT JOIN recipe_ingredients ri1 ON ri1.ingredient = i1.ingredient
LEFT JOIN recipe_ingredients ri2 ON ri2.ingredient = i2.ingredient AND ri2.id = ri1.id
GROUP BY i1.ingredient, i2.ingredient;

执行完成后ingredient_cooccurrence表就是你需要的关联关系表。

第二步：查询生成邻接矩阵 #

如果需要直接查看矩阵格式的结果，在phpMyAdmin中执行以下动态行转列SQL即可：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN ingredient2 = ''',
        ingredient,
        ''' THEN frequency END) AS ',
        '`', ingredient, '`'
    )
) INTO @sql
FROM recipe_ingredients;

SET @sql = CONCAT('SELECT ingredient1 AS ``, ', @sql, ' FROM ingredient_cooccurrence GROUP BY ingredient1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

运行后输出的结果就是行列均为食材名称的邻接矩阵。

注意事项 #

• 食材数量超过100种时，建议先过滤掉出现频次极低的食材再运行，避免矩阵列数过多加载缓慢
• 若食材名称包含特殊字符，建议统一将ingredient相关字段的字符集调整为utf8mb4避免编码错误

内容的提问来源于stack exchange，提问作者Imsal Khan