简单实用SQL脚本Part9：纵向回填信息

       需求：我们的一个系统有这样一个功能：记录用户访问网站页面的轨迹，比如A->B->C->D（A、B、C、D表示不同的页面URL），在系统的业务上用户可以使用帐号访问网站，也可以使用匿名访问网站，而用户使用的机器已经被标识为一个GUID，所以当用户使用了匿名访问A、B页面后在C页面使用帐号登陆了系统，那么为了对用户进行分析，我们需要把A、B页面的登录名设置登陆的账号，这样用户的访问过程就有了一个完整的一条线。（如图1所示）

       注意1：的是一台机器（GUID）有可能使用了多个帐号进行登陆，那么同一个GUID就有可能包含了匿名、帐号a、帐号b的交替记录。（如图1所示）

       注意2：这个表中的记录是按照自增ID值来进行排序，你也可以通过访问的时间来排序。下面的操作都是基于自增ID排序的。

       注意3：纵向回填信息需要包括向上和向下回填信息。因为用户有可能使用帐号登陆了系统之后退出了系统再匿名访问，所以这个回填的过程除了要向上回填用户名之外还得向下回填用户名才是合理的。

       注意4：必须先执行完向上回填再执行向下回填。

 

（图1） 

--生成测试数据
if exists (select * from sysobjects where id = OBJECT_ID('[t_RLoginname]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [t_RLoginname]

CREATE TABLE [t_RLoginname] (
[ID] [int]  NOT NULL,
[Loginname] [varchar]  (50) NULL,
[Guid] [varchar]  (50) NULL)

INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10701389,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10701581,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10701621,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10701658,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10701828,'17851307','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10702068,'17851307','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10702226,'17851307','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10702361,'17851307','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10704102,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10704166,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10704559,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10704615,'21958792','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10704715,'21958792','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10704766,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10705208,'21958792','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10705317,'21958792','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10705637,'21958792','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10705774,'21958792','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10780602,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10781073,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')
INSERT [t_RLoginname] ([ID],[Loginname],[Guid]) VALUES ( 10781162,'未知','7E5863ED-53B2-5245-DE1F-0D08CC743356')

 

逻辑：找出未知用户需要回填的账号：找到比当前ID大的；并且loginname不为“未知”的那条记录；并且是相当的guid的记录。

 

（图2原理图） 

 

向上回填：使用下面的查询语句得到的结果如图3所示

--查询出向上未知用户需要回填的loginname
select ID,guid,loginname,new_loginname
    from (
        select ID,guid,loginname,new_loginname = 
        (
        SELECT TOP 1 loginname from [t_RLoginname] where
            id=(select min(id) from [t_RLoginname] 
                where id>a.id and loginname <>'未知' AND guid = a.guid)
        )
        from [t_RLoginname] as a
        where a.loginname = '未知'
    ) as b
    where b.new_loginname is not null

 

 （图3：向上对应未知用户）

--向上回填
--插入到临时表
select ID,guid,loginname,new_loginname
    into [t_RLoginname_up]
    from (
        select ID,guid,loginname,new_loginname = 
        (
        SELECT TOP 1 loginname from [t_RLoginname] where
            id=(select min(id) from [t_RLoginname] 
                where id>a.id and loginname <>'未知' AND guid = a.guid)
        )
        from [t_RLoginname] as a
        where a.loginname = '未知'
    ) as b
    where b.new_loginname is not null

--更新记录
update a set a.loginname = b.new_loginname
    from [t_RLoginname] as a,
    [t_RLoginname_up] as b
    where a.ID = b.ID 
    and a.guid = b.guid

 

向下回填：这条语句和向上回填的不同主要是min与max，id>a.id与id<a.id的区别。

--向下回填
--插入到临时表
select ID,guid,loginname,new_loginname
    into [t_RLoginname_down]
    from (
        select ID,guid,loginname,new_loginname = 
        (
        SELECT TOP 1 loginname from [t_RLoginname] where
            id=(select max(id) from [t_RLoginname] 
                where id<a.id and loginname <>'未知' AND guid = a.guid)
        )
        from [t_RLoginname] as a
        where a.loginname = '未知'
    ) as b
    where b.new_loginname is not null


--更新记录
update a set a.loginname = b.new_loginname
    from [t_RLoginname] as a,
    [t_RLoginname_down] as b
    where a.ID = b.ID 
    and a.guid = b.guid