关于数据分组统计

[bluezsh]

假如有一张表 table，user_id,login_ip,create_time 记录用户每天登陆数据，
我想分组统计 一周登陆5次以下用户占比，5次~10次用户占比，10~20次占比

如果我按照月统计，那分组显示可能会不一样。按照年也会不一样。

比如周：
一周登陆5次以下用户占比，5次~10次用户占比，10~20次占比
月：
月登陆10次以下用户占比，10次~20次用户占比，20~30次占比
年
年登陆50次以下用户占比，50次~100次用户占比，100~200次占比,200次以上占比

试了试case when then 貌似不行，分组的部分根据统计结果会按照统计方式会发生变化。
有没有其它办法解决？

[ACMAIN_CHM]

引用

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

.

[zjcxc]

周/月/年 写不同的查询不就行了么？干嘛非得弄在一个查询里面折腾来折腾去的？

[zjcxc]

-- 占比
 select a/users, b/users, c/users
 from(
	select	-- 用户总数和 3 个区间登录用户数
		count(*) as users
		, sum(case when data.times < b.a then 1 end) as a
		, sum(case when data.times < b.b and data.times >=b.a then 1 end) as b
		, sum(case when data.times > b.b then 1 end) as c
	from(
		-- 每个用户在指定时间段内（周/月/年）的登录次数
		select user_id, count(*) as times
		from table
		where create_time between 'xx' and 'xx' -- **** 统计区间
		group by user_id
	) data,
	(select 10 as a, 50 as b) b	-- **** 定义 3 个统计区间: <a, <b and >=a, >b
) xx

[bluezsh]

不好意思，图例大概是这样的：


mysql 数据库

表 结构如下：

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`user_ip` char(20) NOT NULL,
`create_time` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- 转存表中的数据 `test`
--

INSERT INTO `test` (`id`, `user_id`, `user_ip`, `create_time`) VALUES
(1, 11, '192.168.1.99', '2016-06-07 10:10:10'),
(2, 22, '192.168.1.100', '2016-06-07 10:20:10');

[zjcxc]

那就3个查询就好了嘛，一个查询对应一个图

[ACMAIN_CHM]

select case when cnt<=5 then '5次以下'
	when cnt<=10 then '5次~10次'
	when cnt<=20 then '10~20次'
	else then '200次以'
	end as c, count(*)
from (
select user_id, count(*) as cnt
from test
where create_time>CURDATE()-interval 7 day
group by user_id
) t
group by case when cnt<=5 then '5次以下'
	when cnt<=10 then '5次~10次'
	when cnt<=20 then '10~20次'
	else then '200次以'
	end

[道玄希言]

你这日月年的登录统计, 如果用户登数不大还好, 如果到了一定的用户数和登录数, 比如每天几十上百万的登录, 就不知道你这么统计, 能不能统计的出来了. 可以再增加个表, 当有用户登录时, 更新下对应的计数字段就好了.

[bluezsh]

引用 6 楼 yangb0803 的回复:

你这日月年的登录统计, 如果用户登数不大还好, 如果到了一定的用户数和登录数, 比如每天几十上百万的登录, 就不知道你这么统计, 能不能统计的出来了. 可以再增加个表, 当有用户登录时, 更新下对应的计数字段就好了.

我只是用这个举一个例子，容易理解一些。

[专注打酱油30年]

版主那个可能不是很简洁，而且有语法错误

SELECT case when cnt <2 then '2次以下' 
							when cnt <3 then '3次以下' 
							when cnt <4 then '4次以下' 
							when cnt <5 then '5次以下' 
							else '5次以上' end as c, count(*) 
from
(select user_ip, count(*) as cnt
from group_table
where create_time > CURDATE() -INTERVAL 7 DAY
GROUP BY user_ip) as t
GROUP BY cnt