会员推荐系统中数据统计和表结构设计

系统会员采用推荐方式,比如A推荐B,B推荐C、D、E,E推荐F,...,要求能统计出某个会员发展的所有会员数以及第N级会员数(例如C发展的第3级会员数)。



第一种方案

①.表结构

create table MZ_User
(
    Id int identity(1,1) primary key,
    Name nvarchar(20),
    Fid int --上级编号
)

示例数据:
引用内容 引用内容
insert into MZ_User select 'a',0
insert into MZ_User select 'b',1
insert into MZ_User select 'c',2
insert into MZ_User select 'd',2
insert into MZ_User select 'e',4
insert into MZ_User select 'f',4
insert into MZ_User select 'g',4
insert into MZ_User select 'h',0

②.数据统计

--第3级会员数
select count(*) from (select Id from MZ_User where Fid in(select Id from MZ_User where Fid in(select Id from MZ_User where Fid=1))) as tab

③.方案缺点

不易统计出某个会员发展的所有会员数,统计第N级会员数时子查询可能过长,语句也不易编写。

第二种方案

①.表结构

create table MZ_User
(
    Id int identity(1,1) primary key,
    Name nvarchar(20),    
    TreeId int, --层级
    UPath nvarchar(100) --层级路径
)

示例数据:
引用内容 引用内容
insert into MZ_User select 'a',0,''
insert into MZ_User select 'b',1,'1,'
insert into MZ_User select 'c',2,'1,2,'
insert into MZ_User select 'd',2,'1,2,'
insert into MZ_User select 'e',3,'1,2,4,'
insert into MZ_User select 'f',3,'1,2,4,'
insert into MZ_User select 'g',3,'1,2,4,'
insert into MZ_User select 'h',0,''

②.数据统计

declare @id int, @treeid int, @upath nvarchar(200)
select @id=Id,@treeid=TreeId,@upath=UPath from MZ_User where Id=1
set @upath=@upath+cast(@id as nvarchar)+','
--所有会员数
select COUNT(*) from MZ_User where UPath like @upath+'%'
--第3级会员数
select count(*) from MZ_User where TreeId=@treeid+3 and UPath like @upath+'%'

③.方案缺点

UPath字段可能过长。

第三种方案

①.表结构

create table MZ_User
(
    Id int identity(1,1) primary key,
    Name nvarchar(20),    
    TreeId int, --层级
    Lid int, --左值
    Rid int --右值
)

示例数据:
引用内容 引用内容
insert into MZ_User select 'root',0,1,18
insert into MZ_User select 'a',1,2,15
insert into MZ_User select 'b',2,3,14
insert into MZ_User select 'c',3,4,5
insert into MZ_User select 'd',3,6,13
insert into MZ_User select 'e',4,7,8
insert into MZ_User select 'f',4,9,10
insert into MZ_User select 'g',4,11,12
insert into MZ_User select 'h',1,16,17



②.数据统计

declare @treeid int, @lid int, @rid int
select @treeid=TreeId,@lid=Lid,@rid=Rid from MZ_User where Id=2
--所有会员数
select COUNT(*) from MZ_User where Lid>@lid and Rid<@rid
--第3级会员数
select COUNT(*) from MZ_User where TreeId=@treeid+3 and Lid>@lid and Rid<@rid

③.方案说明

·方案要求有且只有一个根节点;
·增加记录时,大于上级会员左值的左右值+2,新记录左值为上级会员左值+1,右值为上级会员左值+2;
·删除记录时,删除当前节点及子节点,大于当前节点右值的左右值-(当前节点右值-当前节点左值+1);

④.方案缺点

增删记录要全表查找并更新左右值,若表数据量较大,会非常耗时。用一张22w条记录的表做测试,下边两条语句平均耗时4800ms(Lid和Rid有建索引):

update MZ_User set Lid=Lid+2
update MZ_User set Rid=Rid+2


资料参考

[1].无限级分类实现思路:http://www.cnblogs.com/yangmanyan/archive/2011/06/16/2082963.html

评论: 0 | 引用: 0 | 查看次数: 4638
发表评论
登录后再发表评论!