7天内活跃用户统计怎么写

IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test')
DROP TABLE test
GO
CREATE TABLE test(id INT IDENTITY , username VARCHAR(02) , time1 DATE )
GO
INSERT INTO test 
SELECT 'aa' , '2014.1.1' UNION ALL
SELECT 'bb' , '2014.1.1' UNION ALL
SELECT 'aa' , '2014.1.2' UNION ALL
SELECT 'bb' , '2014.1.2' UNION ALL
SELECT 'cc' , '2014.1.2' UNION ALL
SELECT 'aa' , '2014.1.3' UNION ALL
SELECT 'cc' , '2014.1.3' UNION ALL
SELECT 'aa' , '2014.1.4' UNION ALL
SELECT 'aa' , '2014.1.7' UNION ALL
SELECT 'cc' , '2014.1.7' UNION ALL
SELECT 'dd' , '2014.1.7' 
--print dateadd(dayofyear,-7, getdate() )
SELECT a.TIME1,
       (
           SELECT COUNT(1)
           FROM   (
                      SELECT username
                      FROM   test AS b
                      WHERE  b.time1 <= a.time1
                             AND b.time1 >= DATEADD(dayofyear, -7, a.time1)
                             AND username IN (SELECT d.username
                                              FROM   test AS d
                                              WHERE  d.time1 = a.time1)
                      GROUP BY
                             b.username
                      HAVING COUNT(1) >= 2
                  ) AS d
       ) AS 活跃用户
FROM   test AS a
GROUP BY
       a.time1
       
------------结果
TIME1      活跃用户
---------- -----------
2014-01-01 0
2014-01-02 2
2014-01-03 2
2014-01-04 1
2014-01-07 2(5 row(s) affected)
写的较复杂，有没有更好的写法

解决方案 »

  1.   

    select username,次数
    from
    (
    select username,COUNT(1) as 次数
    from test
    where DATEDIFF(day,time1,GETDATE())<=7
    group by username
    ) as temp
    where temp.次数>=2
      

  2.   


    感觉是蛮复杂的，还有能不能结果改成只显示当天的，一行数据
    就是 2014.1.7  2再加上where条件SELECT a.TIME1,
           (
               SELECT COUNT(1)
               FROM   (
                          SELECT username
                          FROM   test AS b
                          WHERE  b.time1 <= a.time1
                                 AND b.time1 >= DATEADD(dayofyear, -7, a.time1)
                                 AND username IN (SELECT d.username
                                                  FROM   test AS d
                                                  WHERE  d.time1 = a.time1)
                          GROUP BY
                                 b.username
                          HAVING COUNT(1) >= 2
                      ) AS d
           ) AS 活跃用户
    FROM   test AS a
    where a.time1 = '2014.1.7'
    GROUP BY
           a.time1
      

  3.   

    select 用户,sum(活跃用户)活跃用户 from A where TIME>='2014.1.1' and TIME<'2014.1.7' and 活跃用户>='2' group by 用户估计这个是你所想要的结果！