MySQL查询以选择->插入和删除选定的行 [英] MySQL query to Select -> Insert and Delete selected rows
问题描述
当用户连接到应用程序后，我得到了一个系统，该系统每2到5秒ping一次数据库.根据他的连接，ping时间可能会更长，例如10秒左右.
I got a system that pings to the database every 2 to 5 seconds, when an user 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'm running 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:
这是我正在运行的查询，用于选择结果，如@fancyPants所建议的那样，在此问题上: MySQL查询可按日期范围对结果进行分组?
Here is the query I'm running to select the results, as advised by @fancyPants on this question: MySQL query to group results by date range?
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
产生以下结果:
user: X | start_date: 1 | end_date: 50 | duration: 49
user: X | start_date: 180 | end_date: 210 | duration: 30
我需要帮助，在此查询中添加一条语句，该语句将执行以下操作.
I need help, adding to this query, an statement that will do the following.
1st.使用查询返回的完全相同的模式将选定的行插入到新表中:
1st. Insert the selected rows into a new table with the excatly same schema the query returns:
id: auto_increment| user: X | start_date: 1 | end_date: 50 | duration: 49
id: auto_increment| user: X | start_date: 180 | end_date: 210 | duration: 30
第二.删除在查询中选择并插入到新表中的所选行.
2nd. Delete the selected rows, that were selected on the query and inserted into the new table.
-
此查询将由服务器上的cronjob每10分钟运行一次.因此，我可以清理会受到严重打击的ping表，并将将要显示给冲浪者的值存储到新表中.
This query will be run by a cronjob on the server, every 10 minutes. So I can clean the ping table, that will be heavily hit, and store into a new one the values that we are going to display to our surfers.
在新查询中，我需要一个子句来过滤未过期的ping.未过期的ping是指在cron运行当前时间之前不超过60秒执行的ping.例如，如果now = 100，则要抓取的最后一个ping不能小于41.这样，当cron运行时，我不会从仍要ping通数据库的用户中选择行.
On the new query, I need a clause to filter non expired pings. Non expired pings, are the ones done no longer than 60 seconds before the current time when the cron runs. For example, if now = 100, the last ping to grab can not be less than 41. This way, when the cron runs, I don't select the rows from the users that are still pinging to the database.
可以在一个查询中完成，还是需要两个?
Can it be done in one query, or will I need two?
谢谢
推荐答案
(按照我以前的回答)
ping_timestamp列中到底存储了什么? Unix时间戳还是其他?我将假定它是unix时间戳.
(following up on my previous answer)
What exactly is stored in the ping_timestamp column? Unix timestamp or something else? I will assume it is unix timestamp.
创建用于保存用户活动数据的表:
Create the table that will hold the user activity data:
create table user_activity (
user_id int(11) not null
, start_date int(11) not null
, end_date int(11) not null
, duration int(11) not null
);
汇总数据并跳过尚未关闭的时间间隔:
Aggregate the data skipping the intervals that are not closed yet:
set @rnum = 1;
set @cut_off = unix_timestamp() - 60;
insert
into user_activity
select user_id
, min(ping_timestamp) start_date
, max(ping_timestamp) end_date
, max(ping_timestamp)-min(ping_timestamp) duration
from ( select user_id
, ping_timestamp
, @rnum := if(ping_timestamp - @prev_ping_ts > 60, @rnum+1, @rnum) rnum
, @prev_ping_ts := ping_timestamp
from ping_data
order by user_id, ping_timestamp
) t
group by user_id, rnum
having end_date <= @cut_off
;
此后，我们可以根据user_activity表中的数据删除已处理的行:
After that we can delete the processed rows based on the data in the user_activity table:
delete t
from ping_data t
join ( select user_id
, max(end_date) max_timestamp
from user_activity
group by user_id
) ua
on t.user_id = ua.user_id
where t.ping_timestamp <= ua.max_timestamp
;
这篇关于MySQL查询以选择->插入和删除选定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
