MySQL在允许的限制内搜索并发时间戳，然后计算SUM？

问题描述：

Been racking my brain for weeks on how I could correctly achieve this, been playing around with PHP & mySQL but every formula or function I write seems to be missing something. Here's exactly what I'm trying to do..

-mySQL database filled with records of logins.

-Fields are RECORD,START_TIME,DURATION,CUSTOMERID

-RECORD is unique key

-START_TIME is in the "2013-12-12 10:32:59" type format

-DURATION is in seconds

-CUSTOMERID is the unique account number of user

Each user is allowed 3 simultaneous logins, any logins above 3 at a time I need to tally up a total of seconds of DURATION for that user of all sessions during the "overage".

So an example would be..

RECORD  |  START_TIME        |  DURATION  |  CUSTOMERID
   1       2013-1-1 12:00:00      10          BILLYBOB
   2       2013-1-1 12:01:00      600         BILLYBOB
   3       2013-1-1 12:04:00      1200        BILLYBOB
   4       2013-1-1 12:05:20      500         BILLYBOB
   5       2013-1-1 12:06:30      600         BILLYBOB
   6       2013-1-1 16:00:00      100         BILLYBOB
   7       2013-1-1 18:00:00      300         BILLYBOB

In this case, it would return records 2,3,4,5 since there was over 3 simultaneous sessions, then return the total duration of those records which would be 2900.

Is this asking too much of MySQL?

答

Okay, I got it now...

First you must find the time slices per user. Say he/she worked from 10:00:00 to 10:09:59 and from 10:10:00 to 10:19:59 and from 10:00:00 to 10:29:59. That gives you three time slices: 10:00:00 to 10:09:59 (sessions 1 and 3), 10:10:00 to 10:19:59 (session 2 and 3) and 10:20:00 to 10:29:59 (session 3).

Then you count the sessions that were active in each time slice and remove slices were only three or less sessions were active. With those violated time slices found, you select the sessions that were active then.

Here is the complete statement:

select *
from sessions
where exists
(
  select *
  from
  (
    select start_times.customerid, start_times.start_time, min(end_times.end_time) as end_time
    from
    (
      select customerid, start_time from sessions
      union
      select customerid, date_add(start_time, interval duration second) from sessions
    ) start_times
    join
    (
      select customerid, date_add(start_time, interval duration - 1 second) as end_time from sessions
      union
      select customerid, date_add(start_time, interval -1 second) from sessions
    ) end_times
    on (start_times.customerid = end_times.customerid and start_times.start_time < end_times.end_time)
    group by start_times.customerid, start_times.start_time
  ) time_slices
  where 
  (
    select count(*) 
    from sessions
    where time_slices.customerid = sessions.customerid
    and
    (
      time_slices.start_time between sessions.start_time and date_add(sessions.start_time, interval duration - 1 second)
      and
      time_slices.end_time between sessions.start_time and date_add(sessions.start_time, interval duration - 1 second)
    )
  ) > 3
  and time_slices.customerid = sessions.customerid
  and
  (
    time_slices.start_time between sessions.start_time and date_add(sessions.start_time, interval duration - 1 second)
    and
    time_slices.end_time between sessions.start_time and date_add(sessions.start_time, interval duration - 1 second)
  )
)
;

And here is the SQL fiddle: http://sqlfiddle.com/#!2/12c47/1 .

答

here is query which should do the trick:

SELECT
    record, `start_time`, date_add(`start_time`, interval `duration` second) as end_time, `customerid`,
    `duration`+(select sum(`duration`)
        from `testtbl` as b
        where b.record > a.record and b.customerid=a.customerid and b.start_time <= end_time
        having count(*) >= 3
    ) as sumduration
FROM
`testtbl` as a
having sumduration is not null

this query is not very fast, but based on it you can de-normalize you db and obtain faster version

NOTE: there we can have problem if we have such data:

1 2013-1-1 12:00:00 60 BILLYBOB
2 2013-1-1 12:00:10 60 BILLYBOB
3 2013-1-1 12:00:20 60 BILLYBOB
4 2013-1-1 12:00:30 60 BILLYBOB
5 2013-1-1 12:01:09 60 BILLYBOB

record 1 is overlapped only with 2,3,4 NOT with 5
record 2 is overlapped only with 3,4,5

this is not actually an error, as there we 4 logins, then 1 logged out at 1:00 and then again 4th login to the system

both records will be displayed by query, depending on what you will be doing with results you can handle this situation differently

MySQL在允许的限制内搜索并发时间戳，然后计算SUM？

相关推荐