为引荐营销存储分层数据（MySQL）

2018-06-05 06:37:39

我需要为注册到网站的用户设置一个5级层次结构。每个用户都被另一个用户邀请，我需要知道用户的所有后代。还有一个用户的祖先。

我有两个解决方案。

用这种方式保持关系。封闭台：


    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

有这张关系表。保持在一个表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

这些好主意吗？

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

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

经常添加新用户

当用户购买东西时，他们的推荐人可以获得百分比佣金

每个用户应该能够找出他们提到的人数（以及他们提到的人引用了多少人......）

关闭表

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;

这种方法的缺点是这个表需要占用大量的存储空间。

使用OQGRAPH存储引擎。

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

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

我们将使用origid作为推荐人，并将其作为推荐人。

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

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;

划定的祖先串

如果您强烈考虑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} 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 concat('%,', ?, ',%')这些查询的like '%,3,%'部分中的SQL注入攻击，并将用户编号的整数绑定到占位符。

链接地址: http://www.djcxy.com/p/16791.html

上一篇: Storing Hierarchical Data (MySQL) for Referral Marketing

下一篇: optimizing a view for hierarchical data