MySQL查询按日期范围对结果分组? [英] MySQL query to group results by date range?

问题描述

我得到了一个系统，当他连接应用程序时，它每2到5秒对数据库执行一次ping操作.根据他的连接，ping时间可能会更长，例如10秒左右.

I got a system that pings to the database every 2 to 5 seconds when he is connected the application. Depending on his connection, the ping timeframe can be bigger, like 10 seconds or so.

示例:

Pings: 1,4,6,8,9,12,16,20,50,180,187,189,200,203,206,210 ...

我想运行一个查询以捕获两次ping之间不超过1分钟的范围，将它们分组，这样我就可以知道用户已连接多长时间，并保存到新表中，如:

I want run a query to grab ranges that does not exceed 1 minute between the pings, group them, so I can tell for how long the user has been connected, and save into a new table like:

Connections table:
user: X | start_date: 1   | end_date: 50  | duration: 49
user: X | start_date: 180 | end_date: 210 | duration: 30

由于ping从，50、180开始，...超过了1分钟，因此查询组放弃了此操作.

Since the pings from ,50,180, ... that exceeded 1 minute, where disconsidered by the query groups.

ping存储为时间戳，因此可以轻松转换为日期，从最后一次ping到第一次ping的范围将为我提供会话持续时间.

The pings are stored as timestamp, so can be easily converted to date, and the range from the last ping to the first one will give me the session duration.

在MySQL查询中是否有这样做的方法?有帮助吗?

Is there anyway to do it in a MySQL query? Any help?

添加ping历史记录模式

Adding ping history schema

id  int(11)
user_id int(11)
ping_timestamp  int(11)

谢谢

推荐答案

我将您给定的示例数据加倍，以显示它如何与多个userId一起使用.

I doubled your given sample data to show how it works with multiple userIds.

在sqlfiddle中实时查看它的运行情况此处.

See it working live in an sqlfiddle here.

select
userid, groupnum,
min(ping) as start_date,
max(ping) as end_date,
max(ping) - min(ping) as duration
from (
select
*,
@groupnum := if(@prevUser != userId, @groupnum + 1, @groupnum),
@groupnum := if(ping - @prevTS > 60, @groupnum + 1, @groupnum) as groupnum,
@prevUser := userid,
@prevTS := ping
from
Table1 t
, (select @groupnum:=1, @prevTS:=NULL, @prevUser:=NULL) vars
order by userid, ping
) sq
group by userid, groupnum

这篇关于MySQL查询按日期范围对结果分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！