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

由于来自 ,50,180, ... 的 ping 超过 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屋！