判断用户何时处于活动状态的更好方法？

Question

我有一个表格，其中包含了用户的信息谁张贴到我的网站。该表名为logs，并有以下记录：id, epoch, username, msg (epoch是他们发布时的unix时代，msg是发布的消息)

我决定把一天分成4段，每段6小时(0-5，6-11，12-17，18-23)。

我想确定用户在每一段中所发帖子的百分比。

只有一个sql查询就能做到这一点，有什么好的方法吗？如果我必须在每个用户名下面进行4次查询，这将花费很长的时间。

SELECT count( num )
FROM `logs`
WHERE username = 'bob'
AND from_unixtime( epoch )
BETWEEN date_sub( now( ) , INTERVAL 1 week )
AND now( )
AND hour( from_unixtime( epoch ) )
BETWEEN 0
AND 5

上面的查询告诉我bob在过去一周的0到5小时之间发布了多少帖子。这让人觉得效率低下，因为如果查询只加载所有bobs帖子，获取所需的所有数据，然后返回这些数据，则可能会更好；而不必加载他的帖子5次(1次获得总帖子，#2/3/4/5在特定的小时范围内获得帖子)。

我的目标是获得bob在一个查询中所做的所有帖子，按照一天中不同的时间(即0到5小时、6到11小时、12和17小时、18小时和23小时)进行除以。然后，我可以将这些个人信息除以bobs的总发帖数，例如，bob在6和11小时内发布了80%的帖子，等等。

这样，我就能准确地知道鲍勃是什么时候活动的。

Answer 1

create table buckets(int low, int hi);

insert into buckets values(0, 5), (6, 11), (12, 17), (18, 23);

SELECT `low`, `hi`, count( num )
FROM `logs`, `buckets`
WHERE username = 'bob'
AND from_unixtime( epoch )
BETWEEN date_sub( now( ) , INTERVAL 1 week )
AND now( )
AND hour( from_unixtime( epoch ) )
BETWEEN `buckets`.`low`
AND `buckets`.`hi`
GROUP BY `buckets`.`low`;

如果您希望相同的查询也为您提供一整天的统计数据，那么除了其他四个值之外，还可以将(0, 23)插入桶中。

更新:正如halfer在评论中指出的，在您的时间间隔内，您也可以按hour div 6分组。

SELECT hour( from_unixtime( epoch ) ) div 6 * 6, hour( from_unixtime( epoch ) ) div 6 * 6 + 5, count( num )
FROM `logs`
WHERE username = 'bob'
AND from_unixtime( epoch )
BETWEEN date_sub( now( ) , INTERVAL 1 week )
AND now( )
GROUP BY hour( from_unixtime( epoch ) ) div 6;

Answer 2

相反，我会使用这样的方法：

select count(msg) as TotalMsg,
    sum(CASE WHEN (epoch MOD 86400)<21600 THEN 1 ELSE 0 END) as Period_1,
    sum(CASE WHEN (epoch MOD 86400)>=21600 AND (epoch MOD 86400)<43200 THEN 1 ELSE 0 END) as Period_2,
    sum(CASE WHEN (epoch MOD 86400)>=43200 AND (epoch MOD 86400)<64800 THEN 1 ELSE 0 END) as Period_3,
    sum(CASE WHEN (epoch MOD 86400)>=64800 THEN 1 ELSE 0 END) as Period_4
from logs
    where username='bob';

# 86400 = seconds in 24 hours