下面的mysql sql如何写

不是simaguo 发布于 2015/09/17 07:55
阅读 265
收藏 1

id uid time

1 1    20150101

2 1    20150102

3 2    20150101

4 1    20150103

5 2    20150102

6 2    20150103

7 1    20150104

8 1    20150110

9 1    20150111

数据如上,如何统计每个人连续天数的次数,得到一下结果

uid count_time

1    4

2    3

1    2

大神求助

加载中
1
canghailan
canghailan
每行计算出3个辅助列,距离最早那天的天数,比本行时间早的行数,缺少的天数(距离最早那天的天数-比本行时间早的行数)。
连续登陆的那几天缺少的天数是相同的,按用户和缺少的天数统计一下就是连续登陆的天数。
按这个思路简单写下SQL:

SELECT
	uid,
	min_date_diff - before_date_count miss_date_count,
	count(*)
FROM
	(
		SELECT
			user_time.*, datediff(
				user_time.time,
				user_min_time.min_time
			) + 1 min_date_diff,
			(
				SELECT
					count(*)
				FROM
					user_time i
				WHERE
					i.uid = user_time.uid
				AND i.time <= user_time.time
			) before_date_count
		FROM
			user_time
		INNER JOIN (
			SELECT
				uid,
				min(time) min_time
			FROM
				user_time
			GROUP BY
				uid
		) user_min_time ON user_min_time.uid = user_time.uid
	) user_time_stat
GROUP BY
	uid,
	miss_date_count



结果:

uid	miss_date_count	count(*)
1	0	4
1	5	2
2	0	3




1
pantrick
pantrick
我觉得这种记录之所以有需求是设计问题,连续登陆应该在每天都计算,如果发现最近一次登陆不连续,马上覆盖重算,比这么计算简单的多
OSC首席键客
OSC首席键客
我之前就是这样搞得,每天第一次登陆+1,如果最近的一个登陆不是昨天,从0开始计算,方便的很。根本不需要像LZ那样!
0
透明如风
我擦,感觉太难了,我想想
0
不是simaguo
不是simaguo

已找到答、案


http://www.oschina.net/question/573517_118821

谢谢上面回答,吐槽一下,发完贴然后居然找不到该贴了,隔了一天,今天才显示出来,不知道怎么回事

0
l
laik
CREATE TABLE countLine AS
SELECT 1 AS id, 1 AS uid, 20150101 AS TIME UNION ALL
SELECT 2 AS id, 1 AS uid, 20150102 AS TIME UNION ALL
SELECT 3 AS id, 2 AS uid, 20150101 AS TIME UNION ALL
SELECT 4 AS id, 1 AS uid, 20150103 AS TIME UNION ALL
SELECT 5 AS id, 2 AS uid, 20150102 AS TIME UNION ALL
SELECT 6 AS id, 2 AS uid, 20150103 AS TIME UNION ALL
SELECT 7 AS id, 1 AS uid, 20150104 AS TIME UNION ALL
SELECT 8 AS id, 1 AS uid, 20150110 AS TIME UNION ALL
SELECT 9 AS id, 1 AS uid, 20150111 AS TIME;


--查询语句

SELECT uid,COUNT(1) AS count_time FROM
(SELECT id,uid,TIME,TIME-IF(@uid=uid,(@rn:=@rn+1),@rn:=1) AS diff ,@uid :=uid FROM countLine e,(SELECT @rn:=0,@uid='',@rnx:=0) c ORDER BY uid,TIME) a
GROUP BY uid,diff;


结果

----

   uid  count_time  
------  ------------
     1             4
     1             2
     2             3


返回顶部
顶部