存储层次数据(MySQL)用于推荐营销

Question

我需要一个5个层次的用户注册到一个网站。每个用户都是由另一个用户邀请的，我需要了解一个用户的所有后代。也是用户的祖先。

我想到了2种解决方案。

1. 用这种方式保持一张有关系的表格。收尾表：

    ancestor_id  descendant_id  distance
    1            1              0
    2            2              0
    3            3              0
    4            4              0
    5            5              0
    6            6              0
    2            3              1

1. 这张桌子是用来维持关系的。在表中保存5级祖先。“祖先”桌：

   user_id ancestor_level1_id ancestor_level2_id ancestor_level3_id ancestor_level4_id ancestor_level5_id
   10      9                  7                  4                  3                  2
   9       7                  4                  3                  2                  1

这些主意好吗？

我知道“邻接列表模型”和“修改的预序树遍历算法”，但是这些是“推荐”系统的好解决方案吗？

我需要在此树上执行的查询是：

• 经常添加新用户
• 当用户买东西时，他们的推荐人会得到一定的佣金。
• 每个用户都应该能够知道他们推荐了多少人(以及他们所指的人推荐了多少人.)在每一级

Answer 1

闭合表

ancestor_id  descendant_id  distance
    1            1              0
    2            2              0
    3            3              0
    4            4              0
    5            5              0
    6            6              0
    2            3              1

要添加用户10，由用户3引用(我不认为您需要在这两个插入之间锁定表)：

insert into ancestor_table
select ancestor_id, 10, distance+1
from ancestor_table
where descendant_id=3;

insert into ancestor_table values (10,10,0);

若要查找用户3引用的所有用户，请执行以下操作。

select descendant_id from ancestor_table where ancestor_id=3;

若要按深度计算这些用户：

select distance, count(*) from ancestor_table where ancestor_id=3 group by distance;

查找用户10的祖先。

select ancestor_id, distance from ancestor_table where descendant_id=10;

此方法的缺点是该表将占用大量存储空间。

Answer 2

使用OQGRAPH存储引擎。

您可能希望跟踪任意数量的级别，而不仅仅是5个级别。获取一个支持MySQL (如MariaDB或OurDelta)的QGRAPH发动机叉，并使用它来存储树。它实现了邻接列表模型，但是通过使用名为latch的特殊列向存储引擎发送命令，告诉它要执行什么样的查询，您可以获得闭包表的所有优点，而无需每次有人为您的站点注册时进行簿记工作。

下面是在OQGRAPH中使用的查询。参见http://openquery.com/graph-computation-engine-documentation的文档

我们将使用起跑者作为推荐人，用destid作为推荐人。

添加用户11，由用户10引用

insert into ancestors_table (origid,destid) values (10,11)

若要查找用户3引用的所有用户，请执行以下操作。

SELECT linkid FROM ancestors_table WHERE latch = 2 AND origid = 3;

查找用户10的祖先。

SELECT linkid FROM ancestors_table WHERE latch = 2 AND destid = 10;

若要查找用户3所指的每个级别的用户数，请执行以下操作：

SELECT count(linkid), weight
FROM ancestors_table
WHERE latch = 2 AND origid = 3
GROUP BY weight;

Answer 3

在MySQL中管理分层数据

一般来说，我喜欢“嵌套集”(尤其是嵌套集)。在MySQL中，它实际上不支持分层数据。它是快速的，但是如果易于维护是件大事的话，您需要确保您的开发人员读过这篇文章。它非常灵活--在你的情况下，这似乎并不重要。

这似乎很适合您的问题--在推荐模型中，您需要找到引用树，这在嵌套的集合模型中是快速的；您还需要知道谁是给定用户的~子级，以及它们之间关系的深度；这也是快速的。