MySQL query to select & # 8594; Insert and delete selected rows

Question

I have a system that connects to the database every 2-5 seconds when the user connects the application. Depending on its connection, the ping timeframe may be longer, for example, 10 seconds or so.

Example:

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

I run a query to capture ranges that do not exceed 1 minute between pings, group them, so I can tell how long the user has been connected:

Here is the query that I execute to select the results, as recommended by @fancyPants on this issue: MySQL query to group the 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 

Producing the following results:

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

I need help adding instructions to this request that will do the following.

first. Insert the selected rows into the new table using the same schema that 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 

second. Delete the selected rows that were selected in the query and inserted into the new table.

• This request will be executed by cronjob on the server every 10 minutes. Therefore, I can clear the ping table, which will hit hard, and save in the new values ​​that we are going to display to our surfers.

• In a new query, I will need a sentence to filter emails without expiration. Without expired pings, they are executed no more than 60 seconds before the current time when cron is running. For example, if now = 100, the last ping to capture cannot be less than 41. Thus, when cron is executed, I do not select rows from users who are still pinging to the database.

Could this be done in one request, or do I need two?

Thanks,

Answer 1

(after my previous answer )

What exactly is stored in the ping_timestamp column? Unix timestamp or something else? I assume this is a unix timestamp.

Create a table in which user activity data will be stored:

 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 ); 

A collection of data skipping intervals that are not yet closed:

 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 ; 

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 ; 

Answer 2

In addition, it is impossible to combine insertion, deletion and selection of operators, I would not recommend it anyway.

Ok, step by step ...

first. Insert the selected rows into the new table using the same schema that the query returns

This is where the "trick" comes in handy. Fulfill your request, but write

 CREATE TABLE new_ping /*or whatever tablename*/ AS SELECT ... 

This will automatically create the table (and insert the data), but usually it needs to be set up, since primary keys or indexes are not created, and data types are sometimes not suitable. Your request will have something like this (maybe some things will be different if you run it, for example, an engine or a character set, these parameters depend on the default settings):

 CREATE TABLE `new_ping` ( `userid` int(11) DEFAULT NULL, `groupnum` mediumtext, `start_date` int(11) DEFAULT NULL, `end_date` int(11) DEFAULT NULL, `duration` bigint(12) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

(you can get higher by requesting SHOW CREATE TABLE ping; )

I suggest always having a primary key in the table. It looks like userid and groupnum will be a good primary key. If you do not know, you can also use the auto-increment column. Anyway, I would adjust the table as follows:

 DROP TABLE IF EXISTS new_ping; CREATE TABLE `new_ping` ( `userid` int(11) DEFAULT NULL, `groupnum` mediumtext, `start_date` int(11) DEFAULT NULL, `end_date` int(11) DEFAULT NULL, `duration` int(12) DEFAULT NULL, /*bigint is certainly too big*/ primary key (userid, groupnum) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

Perhaps you want to add an index to other columns ...

Now that you have a new create table statement, you delete the old table and recreate it using the instruction (or with your settings). I did this by adding a DROP TABLE ... statement DROP TABLE ... above the CREATE statement.

Now you want to insert the data.

 INSERT INTO new_ping (userid, groupnum, start_date, end_date, duration) SELECT ... /*the query in your question*/ 

The next step...

second. Delete the selected rows that were selected in the query and inserted into the new table.

I'm a little lost here. Which ones do you want to remove? Those from the old table, right? Like in this sqlfiddle . But which ones? A query from your questions simply displays them as groups. Clear it and write me a comment, then I will continue to answer ...