mysql - 查找一对多关系中 "Child Types"组合的频率

Question

用户可以创建“标签”。一个标签可以分配给多个父项。我们想要做的是找出标签组合的使用频率。我们还想知道这些组合与哪些 parent 相关。

如果标签具有相同性，我们认为可以一起使用

Parent a, value $200
Child Tag x
Child Tag y
Child Tag z

Parent b, value $300
Child Tag x
Child Tag z

Parent c, $400
Child Tag y
Child Tag z

在上面的例子中，标签 y,z 一起使用了 2 次——在父 a 和父 c 上。 y、z 组合的总值(value)为 600 美元。

类似地，标签 x,z 一起用于父级 a 和父级 b。 x,z 组合的总值(value)为 500 美元。

我们想要执行的搜索类型是:

"find combinations of any 2 tags that share a parent"
"find the total value for each combination of 2 tags that share a parent"

我对如何在查询中执行此操作感到困惑，并且不断回到使用临时表。

Answer 1

首先，重要的是要认识到这是一个多对多问题，而不是一对多问题。每个父级都有许多标签，每个标签适用于许多父级。

如果您修改模型以反射(reflect)这一点，您将拥有一个如下所示的表格:

parent_id | tag_id
------------------
a         | x
a         | y
a         | z
b         | x
b         | z
c         | y
c         | z

等等。

现在您可以将表本身连接起来以获得每个父项的标签组合列表:

select p1.parent_id, p1.tag_id, p2.tag_id from parent_has_tag as p1 left join parent_has_tag as p2 using(parent_id) where p2.tag_id > p1.tag_id;

> 用于消除将“x,x”作为一对报告，并且还返回“x,y”和“y,x”。

这并不是您真正想要的，但这是您回答上述问题的基石。例如，“每个标签组合出现了多少次？”得到的回答是这样的:

SELECT count(1) AS `frequency`, GROUP_CONCAT(p1.parent_id) AS `parents`, CONCAT_WS(',', p1.tag_id, p2.tag_id) AS `pair` 
    FROM parent_has_tag AS p1 LEFT JOIN parent_has_tag AS p2 USING(parent_id) where p2.tag_id > p1.tag_id
    GROUP BY `pair`;

这会为您提供每个标签组合出现的次数，以及目前拥有这些组合的 parent 的免费列表。但下一个问题的答案只是将 parent 的值(value)观相加:

SELECT count(1) AS `frequency`,
    SUM(`parents`.`value`) AS `total_value`,
    GROUP_CONCAT(p1.parent_id) AS `parents`,
    p1.tag_id AS `tag1`,
    p2.tag_id AS `tag2` 
FROM parent_has_tag AS p1 
JOIN parent_has_tag AS p2 ON p1.parent_id = p2.parent_id AND p2.tag_id > p1.tag_id
JOIN parents on p1.parent_id = parents.id
GROUP BY p1.tag_id, p2.tag_id

我认为还有优化的空间(例如，这个版本在分组方面比上面的版本更智能)，但这是获取所需数据的方法的核心。

使用上述起始数据，该查询的结果如下所示:

+-----------+-------------+---------+------+------+
| frequency | total_value | parents | tag1 | tag2 |
+-----------+-------------+---------+------+------+
|         2 |         600 | c,a     |    x |    y |
|         2 |         500 | b,a     |    x |    z |
|         1 |         200 | a       |    y |    z |
+-----------+-------------+---------+------+------+