MySQL query to Select -> Insert and Delete selecte

Question

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.

Example:

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

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:

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

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 to this query, an statement that will do the following.

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.

• 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.

• 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?

Thanks,

Answer 1

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

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

Apart from that it's not possible to combine insert, delete and select statements, I wouldn't recommend it anyway.

Okay, step by step...

1st. Insert the selected rows into a new table with the excatly same schema the query returns

Here a "trick" comes in handy. Execute your query but write

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

This will automatically create a table (and inserts the data), but this usually has to get adjusted, as there are no primary keys or indexes created and the datatypes sometimes don't fit. Your query would produce something like this (maybe some things are different when you execute it, like the engine or the character set, those settings 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 the above by querying SHOW CREATE TABLE ping;)

I suggest to always have a primary key in a table. It seems that userid and groupnum would be a good primary key. If you don't know you can also stick with an autoincrement column. Anyway, I'd adjust the table like this:

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

Maybe you want to add an index on the other columns...

Now that you have your new create table statement drop the old table and recreate with above statement (or with your adjustments). I did so by adding the DROP TABLE ... statement 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*/

Next step...

2nd. Delete the selected rows, that were selected on the query and inserted into the new table.

I'm a little lost here. Which ones do you want to delete? The ones from the old table, right? Like in this sqlfiddle. But which ones exactly? The query from your questions just displays them as groups. Clear that up and write me comment, then I'll continue to answer...