现在的用户表是这样 用户名 登录日期时间 新增用户的概念有两个 1) 从没出现过的用户名 select a.用户名,count(*) from tt a left join tt b on a.用户名=b.用户名 where b.用户名 is null and DATE_FORMAT(a.登录日期时间,'%Y%m')=DATE_FORMAT(curdate(),'%Y%m')2) 6个月内没出现过,本月又出现的用户名select a.用户名 from tt a left join tt b on a.用户名=b.用户名 and DATEDIFF(b.登录日期时间,a.登录日期时间)>=180 where DATE_FORMAT(a.登录日期时间,'%Y%m')=DATE_FORMAT(curdate(),'%Y%m') group by a.用户名
1) 从没出现过的用户名 select distinct 用户名 from 用户表 a where 登录日期时间>='2009-11-01 00:00:00' and not exists (select 1 from 用户表 where 登录日期时间<'2009-11-01 00:00:00' and 用户名=a.用户名) 2) 6个月内没出现过,本月又出现的用户名 select distinct 用户名 from 用户表 a where 登录日期时间>='2009-11-01 00:00:00' and not exists (select 1 from 用户表 where 登录日期时间<'2009-11-01 00:00:00' and 登录日期时间>='2009-05-01 00:00:00' and 用户名=a.用户名) 不过,你的记录很多的话,速度是个问题。建议将历史数据汇总到某表,这样可以效率上比较好。
mysql> select * from loginlog; +-----------+------------+ | loginname | logtime | +-----------+------------+ | aaa | 2009-11-20 | | bbb | 2009-11-20 | | ccc | 2008-12-20 | | ddd | 2009-01-20 | | eee | 2009-07-24 | +-----------+------------+ 5 rows in set (0.00 sec)mysql> select * from loginlog l -> where not exists -> (select 1 from loginlog -> where loginname=l.loginname and -> date_format(logtime,'%Y%m')<date_format(now(),'%Y%m')) -> union all -> select l.* from loginlog l, -> (select loginname,max(logtime) mt from loginlog group by loginname) ml -> where l.loginname=ml.loginname and -> date_format(date_add(now(),interval -6 month),'%Y%m') -> >date_format(ml.mt,'%Y%m'); +-----------+------------+ | loginname | logtime | +-----------+------------+ | aaa | 2009-11-20 | | bbb | 2009-11-20 | | ccc | 2008-12-20 | | ddd | 2009-01-20 | +-----------+------------+ 4 rows in set (0.00 sec)
还有些问题 1 我这个mysql好像不支持子查询 2 我记录有几十万条,查询效率能不能提高
select version();看一下你的mysql版本。
如果你非要直接在这个明细表基础上统计的话,那这样吧:以下语句假设你现要统计的月份是'2009-08':select sum(t.user_count) as total_count from ( select count(distinct 用户名) as user_count from tb_name a where not exists (select 1 from tb_name b where b.用户名=a.用户名 and b.登录日期时间<=date_add(concat('2009-08','-01'),interval -1 day)) union all select count(distinct 用户名) from tb_name a where not exists (select 1 from tb_name b where b.用户名=a.用户名 and b.登录日期时间>=date_add(concat('2009-08','-01'),interval -6 month) and b.登录日期时间<=date_add(concat('2009-08','-01'),interval -1 day)) and exists (select 1 from tb_name c where c.用户名=a.用户名 and c.登录日期时间<date_add(concat('2009-08','-01'),interval -6 month)) ) t1
根据注册时间可以得出
2) 6个月内没出现过,本月又出现的用户名
取本次登录时间之前的时间,即SELECT MAX(时间)WHERE 时间<本次登录时间,比较相差月数,
大于6则计算
你这个可以说是“源日志表”我想统计每个月的新增用户
新增用户的概念有两个
1) 从没出现过的用户名
2) 6个月内没出现过,本月又出现的用户名
-------------------------------------
你这个其实就是一个汇总统计结果,而这种统计结果的实现,可以用1楼说的2种方式处理,当然啦,如你说的“源日志表”不能改变,那没关系啊,你只需要增加一个存放统计结果的表,统计后往这个“统计结果表”里面统计数据就可以了,至于怎样的统计,这个主要结合你的数据量及业务应用的需求来选择,如,每天凌晨统计前一天的,然后放到“统计结果表”去(已有记录的updae,没有记录的则update)...这个处理逻辑要结合你实际情况来灵活处理了,上面只是说了一个处理总体思路。
新增用户的概念有两个
1) 从没出现过的用户名
select a.用户名,count(*) from tt a
left join tt b on a.用户名=b.用户名 where b.用户名 is null and DATE_FORMAT(a.登录日期时间,'%Y%m')=DATE_FORMAT(curdate(),'%Y%m')2) 6个月内没出现过,本月又出现的用户名select a.用户名 from tt a
left join tt b on a.用户名=b.用户名 and DATEDIFF(b.登录日期时间,a.登录日期时间)>=180
where DATE_FORMAT(a.登录日期时间,'%Y%m')=DATE_FORMAT(curdate(),'%Y%m')
group by a.用户名
select distinct 用户名 from 用户表 a where 登录日期时间>='2009-11-01 00:00:00'
and not exists (select 1 from 用户表 where 登录日期时间<'2009-11-01 00:00:00' and 用户名=a.用户名)
2) 6个月内没出现过,本月又出现的用户名
select distinct 用户名 from 用户表 a where 登录日期时间>='2009-11-01 00:00:00'
and not exists (select 1 from 用户表 where 登录日期时间<'2009-11-01 00:00:00' and 登录日期时间>='2009-05-01 00:00:00' and 用户名=a.用户名)
不过,你的记录很多的话,速度是个问题。建议将历史数据汇总到某表,这样可以效率上比较好。
mysql> select * from loginlog;
+-----------+------------+
| loginname | logtime |
+-----------+------------+
| aaa | 2009-11-20 |
| bbb | 2009-11-20 |
| ccc | 2008-12-20 |
| ddd | 2009-01-20 |
| eee | 2009-07-24 |
+-----------+------------+
5 rows in set (0.00 sec)mysql> select * from loginlog l
-> where not exists
-> (select 1 from loginlog
-> where loginname=l.loginname and
-> date_format(logtime,'%Y%m')<date_format(now(),'%Y%m'))
-> union all
-> select l.* from loginlog l,
-> (select loginname,max(logtime) mt from loginlog group by loginname) ml
-> where l.loginname=ml.loginname and
-> date_format(date_add(now(),interval -6 month),'%Y%m')
-> >date_format(ml.mt,'%Y%m');
+-----------+------------+
| loginname | logtime |
+-----------+------------+
| aaa | 2009-11-20 |
| bbb | 2009-11-20 |
| ccc | 2008-12-20 |
| ddd | 2009-01-20 |
+-----------+------------+
4 rows in set (0.00 sec)
1 我这个mysql好像不支持子查询
2 我记录有几十万条,查询效率能不能提高
(
select count(distinct 用户名) as user_count from tb_name a
where not exists (select 1 from tb_name b where b.用户名=a.用户名 and b.登录日期时间<=date_add(concat('2009-08','-01'),interval -1 day))
union all
select count(distinct 用户名) from tb_name a
where
not exists (select 1 from tb_name b where b.用户名=a.用户名 and b.登录日期时间>=date_add(concat('2009-08','-01'),interval -6 month) and b.登录日期时间<=date_add(concat('2009-08','-01'),interval -1 day))
and
exists (select 1 from tb_name c where c.用户名=a.用户名 and c.登录日期时间<date_add(concat('2009-08','-01'),interval -6 month))
) t1