请教个SQL写法，请赐教，谢谢！！！！！！

[aspfeng9527]

现需要统计各渠道（baidu,google,hao123等等渠道）带来的用户PV数，需要使用就近原则。。
有类似如下数据，具体数据请以表中数据为准- -
user_id qudao_name url ref_url flag time
==============================================================================================
0001 百度 www.A.com www.baidu.com 1 2011-05-02 00:00:08
0001 www.A.com?a=1 www.A.com 0 2011-05-02 00:02:01
0001 www.A.com?a=2 www.A.com 0 2011-05-02 00:05:01
0001 谷歌 www.A.com www.google.com 1 2011-05-02 01:00:13
0001 www.A.com?a=1 www.A.com 0 2011-05-02 01:22:01
0001 www.A.com?a=2 www.A.com 0 2011-05-02 02:05:51
0001 www.A.com?a=3 www.A.com 0 2011-05-02 02:33:51
0001 本网站 www.A.com 1 2011-05-02 03:00:00
0001 www.A.com?a=4 www.A.com 0 2011-05-02 03:33:51

create table Test_qudao
(
   user_id nvarchar(20),qudao_name nvarchar(20),url nvarchar(100),ref_url nvarchar(100),flag int,time datetime
)
insert into test_qudao values('0001','百度','www.A.com','www.baidu.com',1,'2011-05-02 00:00:08')
insert into test_qudao values('0001','','www.A.com?a=1','www.A.com',0,'2011-05-02 00:01:08')
insert into test_qudao values('0001','','www.A.com?a=2','www.A.com',0,'2011-05-02 00:02:08')
insert into test_qudao values('0001','谷歌','www.A.com','www.google.com',1,'2011-05-02 00:03:08')
insert into test_qudao values('0001','','www.A.com?a=1','www.A.com',0,'2011-05-02 00:04:08')
insert into test_qudao values('0001','本网站','www.A.com','',1,'2011-05-02 00:08:08')
insert into test_qudao values('0001','','www.A.com?a=2','www.A.com',0,'2011-05-02 00:09:08')
insert into test_qudao values('0002','百度','www.A.com','www.baidu.com',1,'2011-05-02 00:15:00')

其中qudao_name表示渠道名称,URL表示当前访问的URL，ref_url表示当前页的前一页URL
现在想得到如下数据：
渠道 渠道带来PV数 渠道带来人数
============================================================
百度 4 2
谷歌 2 1
本站 1 1

解释下就近原则，就是这条记录归属于他前面的第1个渠道（比较时间字段），比如2011-05-02 00:04:08这条记录前面有谷歌，有百度2个渠道(flag=1表示这条的ref_url就是渠道的URL)，但是他的时间离谷歌更近，所以他归属于谷歌。

请贴出代码供我和其他菜鸟学习，谢谢了^-^

[sekai2011]

[sekai2011]

;WITH  cte AS (
 SELECT ROW_NUMBER()OVER(ORDER BY  TIME ) AS rowid ,qudao_name,url,ref_url,flag,[TIME] FROM  Test_qudao
)

SELECT *  INTO  Test_qudao_1 FROM cte 
GO


CREATE    FUNCTION fn_getname ( @time DATETIME )
RETURNS NVARCHAR(20)
AS BEGIN  
    RETURN ( SELECT qudao_name
             FROM   Test_qudao_1
             WHERE  rowid = ( SELECT    MAX(rowid) AS rowid
                              FROM      ( SELECT    MAX([rowid]) AS [rowid],
                                                    qudao_name
                                          FROM      Test_qudao_1
                                          WHERE     time <= @time
                                                    AND flag = 1
                                          GROUP BY  qudao_name
                                        ) a
                            )
           )
   END 
 
GO

 
SELECT  NAME,
        SUM(pvcount) AS pvcount,
        SUM(toalsum) AS toalsum
FROM    ( SELECT    NAME,
                    COUNT(flag) AS pvcount,
                    ( SELECT    COUNT(rowid)
                      FROM      Test_qudao_1 b
                      WHERE     a.rowid = b.rowid
                                AND b.flag = 1
                    ) AS toalsum
          FROM      ( SELECT    *,
                                dbo.fn_getname(time) AS NAME
                      FROM      Test_qudao_1 a
                    ) a
          GROUP BY  a.NAME,
                    a.rowid
        ) a
GROUP BY NAME 


/*
NAME	pvcount	toalsum
百度	4	2
本网站	2	1
谷歌	2	1
*/
 

求高手解答 。。

[aspfeng9527]

因为我的日志量比较大，所以性能上要求比较高，楼上的方法很好，坐等其他方法，菜鸟想多学点知识

[cd731107]

declare @qudao_name varchar(100),@ls varchar(100)
select @ls=qudao_name from (select top 1 qudao_name from  test_qudao where qudao_name<>'') as tb 
update Test_qudao set @qudao_name=@ls,
                 @ls=case when qudao_name='' then @ls else qudao_name end,
                 qudao_name=case when qudao_name='' then @qudao_name else qudao_name end

select qudao_name,count(*) pvcount,
       sum(case flag when 0 then 1 else 0 end) toalsum 
from Test_qudao group by qudao_name

/*
qudao_name           pvcount     toalsum     
-------------------- ----------- ----------- 
百度                   4           2
本网站                  2           1
谷歌                   2           1

（所影响的行数为 3 行）
*/

[xuexiaodong2009]

太复杂了，不会写成函数，一步一步计算吗

[hexuyang]

学习中....

[aspfeng9527]

[Quote=引用 4 楼 cd731107 的回复:]
SQL code


declare @qudao_name varchar(100),@ls varchar(100)
select @ls=qudao_name from (select top 1 qudao_name from test_qudao where qudao_name<>'') as tb
update Test_qudao set @qudao_name=@l……
[/Quote]

让人眼前一亮，还能这样写，NB

[春风十里耶耶耶]

[Quote=引用 8 楼 aspfeng9527 的回复:]
引用 4 楼 cd731107 的回复:
SQL code


declare @qudao_name varchar(100),@ls varchar(100)
select @ls=qudao_name from (select top 1 qudao_name from test_qudao where qudao_name<>'') as tb
update Test_quda……
[/Quote]
NB。