1

Good day all!

I'm attempting to write some SQL that can identify the number of rows that fall between two timestamps but iteratively count them up.

An example use case, a telephone call CDR for one user:

|Call Number|Start Time         |EndTime            |
|===========|===================|===================|
|1          |2019-01-01 10:01:23|2019-01-01 10:05:44|
|2          |2019-01-01 10:02:33|2019-01-01 10:05:22|
|3          |2019-01-01 10:03:50|2019-01-01 10:06:11|

I'd like a query that could produce this sort of result, per each minute for the hour (using the example data above, Call ID is an arbitrary number, so can be ignored in this context, it's the count of the calls in progress that's important):

|Time Chunk         |Calls In Progress|
|===================|=================|
|2019-01-01 10:00:00|                0|
|2019-01-01 10:01:00|                1|
|2019-01-01 10:02:00|                2|
|2019-01-01 10:03:00|                3|
|2019-01-01 10:04:00|                3|
|2019-01-01 10:05:00|                3|
|2019-01-01 10:06:00|                1|


A script would iterate through customers to produce reports per customer so the WHERE clause would consist of something like WHERE user = 123 for example.

I've tried a couple of queries, but they will only produce instantaneous results based on the start time of the call and then show me how many were in progress for that particular minute of the day, with a load of gaps in between, and not counting for any calls that are "still in progress", as follows:

SELECT 
    FROM_UNIXTIME(
        CEILING(
            `timestamp`/60
        )*60
    ) AS timeslice,
    COUNT(*) AS calls_in_progress
FROM
    calls
WHERE 
    account = {ACCOUNT_ID}
    AND `timestamp` >= UNIX_TIMESTAMP('2019-01-01 00:00:00')
    AND `timestamp` <= UNIX_TIMESTAMP('2019-01-01 23:59:59')
GROUP BY
    timeslice
WITH ROLLUP;

...which in turn produced the following undesirable result (the rollup is useful but not required)...

|timeslice          |calls_in_progress|
|===================|=================|
|2019-01-01 09:35:00|               18|
|2019-01-01 12:10:00|                2|
|2019-01-01 12:11:00|                2|
|2019-01-01 12:19:00|                1|
|2019-01-01 12:49:00|               51|
|NULL               |               74|

Performance impact of said query is quite important given this is going through a table with millions of rows, so the use of the indexes associated to timestamps would be critical.

Any input would be super useful.

  • Let me suggest to take a look at this article about using calendar tables – McNets Dec 6 '19 at 11:37
  • @McNets It would be difficult to realize the same on MySQL/MariaDB directly... look, for example, at SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)). – Akina Dec 6 '19 at 11:44
  • Should a very brief call be counted if, say, it started at 10:01:22 and ended at 10:01:44? – Rick James Dec 6 '19 at 22:55
1

I'd like a query that could produce this sort of result

Look at

WITH RECURSIVE 
cte1 AS ( SELECT CAST(DATE_FORMAT(MIN(StartTime), '%Y-%m-%d %H:%i:00') AS DATETIME) mintime,
                 CAST(DATE_FORMAT(MAX(EndTime), '%Y-%m-%d %H:%i:00') AS DATETIME) maxtime
                 FROM srctable ),
cte2 AS ( SELECT mintime timechunk, 
                 mintime + INTERVAL 1 MINUTE nextchunk
          FROM cte1
        UNION ALL
          SELECT cte2.nextchunk, 
                 cte2.nextchunk + INTERVAL 1 MINUTE
          FROM cte1, cte2
          WHERE cte2.timechunk < cte1.maxtime )
SELECT cte2.timechunk time_chunk, 
       COUNT(*) calls_in_progress
FROM cte2
LEFT JOIN srctable ON cte2.timechunk < srctable.endtime
                  AND cte2.nextchunk >= srctable.starttime
GROUP BY cte2.timechunk 
/* WITH ROLLUP 
   ORDER BY cte2.timechunk IS NULL, cte2.timechunk */

fiddle

  • This solution works but only under the following conditions that aren't clear from the original question: 1) This query will process all data for all time present. Presumably you'd later filter by customer id and a smaller time range. I doubt the optimiser is clever enough to work out it doesn't need to do all of the work just to throw it away. To fix this you'd replace cte1 with the time window you actually want 2) If you do put your own window in, this solution returns calls that were in session between the two times. It doesn't exclude calls that either started or ended outside the window – Blootac Dec 6 '19 at 14:35
  • 1
    @Blootac This query will process all data for all time present. Nothing prevents replace cte1 and get time range from direct values assign, using UDV or parameters. If you do put your own window in, this solution returns calls that were in session between the two times. It doesn't exclude calls that either started or ended outside the window If you need to count the records weth partial overlapping then your own window must be set as parameters in cte1, not direct assign. Or replace out boundaries in partially overlapped source records. – Akina Dec 6 '19 at 17:01
0

I think the best you can get with SQL is Order(N^2) or maybe Order(N^3).

I suggest you could do much better with application code.

  • Have a bunch of buckets (initialized to 0); one per minute.
  • SELECT the time ranges
  • For each minute in each range increment the appropriate bucket.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.