存储用于推荐营销的分层数据 (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

要添加由用户 3 引用的用户 10。（我认为您不需要在这两个插入之间锁定表）：

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 个级别。获取支持 QGRAPH 引擎 的 MySQL 分支之一（例如例如 MariaDB 或 OurDelta），并使用它来存储您的树。它实现了邻接表模型，但通过使用名为 latch 的特殊列向存储引擎发送命令，告诉它要执行哪种查询，您可以获得闭包表的所有优点无需在每次有人注册您的网站时进行簿记工作。

以下是您在 OQGRAPH 中使用的查询。请参阅以下位置的文档：
http://openquery.com/graph-computation-engine-documentation

我们要去使用 origid 作为引用者，并使用 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 并没有真正提供对分层数据的语言支持。
它速度很快，但如果易于维护很重要，您需要确保您的开发人员阅读该文章。它非常灵活——这对于你的情况来说似乎并不重要。

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

Answer 4

祖先的分隔字符串

如果您强烈考虑使用 5 级关系表，则使用祖先的分隔字符串而不是 5 个单独的列可能会简化事情。

user_id  depth   ancestors
10       7       9,7,4,3,2,1
9        6       7,4,3,2,1
...
2        2       1
1        1       (empty string)

以下是您在此模型中使用的一些 SQL 命令：

添加用户 10 引用的用户 11

insert into ancestors_table (user_id, depth, ancestors)
select 11, depth+1, concat(10,',',ancestors)
from ancestors_table
where user_id=10;

查找用户 3 引用的所有用户。（请注意，此查询不能使用索引。）

select user_id
from ancestors_table
where ancestors like '%,3,%' or ancestors like '3,%' or ancestors like '%,3';

查找用户的祖先10. 您需要在客户端程序中分解字符串。在 Ruby 中，代码为ancestorscolumn.split(",").map{|x| x.to_i}。没有好的方法可以在 SQL 中分解字符串。

select ancestors from ancestors_table where user_id=10;

要查找用户 3 引用的每个级别的用户数量：

select
   depth-(select depth from ancestors_table where user_id=3),
   count(*)
from ancestors_table
where ancestors like '%,3,%' or ancestors like '3,%' or ancestors like '%,3'
group by depth;

您可以使用 like concat( 来避免这些查询的 like '%,3,%' 部分中的 SQL 注入攻击'%,', ?, ',%') 而是将用户号码的整数绑定到占位符。