Oracle SQL - Efficiently calculate number of concurrent phone calls

Question

I know that this question is essentially a duplicate of an older question I asked but quite a few things changed since I asked that question so I thought I'd ask a new question about it.

I have a table that holds phone call records which has the following fields:

 END: Holds the timestamp of when a call ended - Data Type: DATE
 LINE: Holds the phone line that was used for a call - Data Type: NUMBER
 CALLDURATION: Holds the duration of a call in seconds - Data Type: NUMBER

The table has entries like this:

END                    LINE                CALLDURATION
---------------------- ------------------- -----------------------
25/01/2012 14:05:10    6                   65
25/01/2012 14:08:51    7                   1142
25/01/2012 14:20:36    5                   860

I need to create a query that returns the number of concurrent phone calls based on the data from that table. The query should calculate that number in different intervals. What I mean by that is that the results of the query should only contain a new entry whenever a call was started or ended. As long as the number of concurrent phone calls stays the same there should not be any additional entry in the output.

To make this more clear, here is an example of everything the query should return based on the example entries from the previous table:

TIMESTAMP              LINE  CALLDURATION  STATUS  CURRENTLYUSEDLINES          
---------------------- ----- ------------- ------- -------------------
25/01/2012 13:49:49    7     1142          1       1
25/01/2012 14:04:05    6     65            1       2
25/01/2012 14:05:10    6     65            -1      1
25/01/2012 14:06:16    5     860           1       2
25/01/2012 14:08:51    7     1142          -1      1
25/01/2012 14:20:36    5     860           -1      0

I got the following example query from a colleague but unfortunately I do not fully understand it and it also does not work exactly as it should because for calls with a duration of 0 seconds it would sometimes have "-1" in the CURRENTLYUSEDLINES-column:

SELECT COALESCE (SUM (STATUS) OVER (ORDER BY END ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING), 0) CURRENTLYUSEDLINES
    FROM (SELECT END - CALLDURATION / 86400 AS TIMESTAMP,
                 LINE,
                 CALLDURATION,
                 1 AS STATUS
            FROM t_calls
          UNION ALL
          SELECT END,
                 LINE,
                 CALLDURATION,
                 -1 AS STATUS
            FROM t_calls) t
ORDER BY 1;

Now I am supposed to make that query work like in the example but I'm not sure how to do that.

Could someone help me out with this or at least explain this query so I can try fixing it myself?

Gordon Linoff · Answer 1 · 2018-04-09T12:12:20.180

1

I think this will solve your problem:

SELECT TIMESTAMP,
       SUM(SUM(STATUS)) OVER (ORDER BY TIMESTAMP) as CURRENTLYUSEDLINES
FROM ((SELECT END - CALLDURATION / (24*60*60) AS TIMESTAMP,
              COUNT(*) AS STATUS
       FROM t_calls
       GROUP BY END - CALLDURATION / (24*60*60)
      ) UNION ALL
      (SELECT END, - COUNT(*)  AS STATUS
       FROM t_calls
       GROUP BY END
      )
     ) t
GROUP BY TIMESTAMP
ORDER BY 1;

This is a slight simplification of your query. But by doing all the aggregations, you should be getting 0s, but not negative values.

You are getting negative values because the "ends" of the calls are being processed before the begins. This does all the work "at the same time", because there is only one row per timestamp.

edited Apr 09 '18 at 12:12

answered Apr 09 '18 at 11:16

Gordon Linoff

1,242,037
58
646
786

Thanks, this seems to work like a charm! Could you briefly explain what this does and how exactly you changed it? Also, your solution only shows the date and the number of used lines, is it also possible to show the other columns like in my example query? – Chris Apr 09 '18 at 11:35
For some reason the date also seems to only contain the year, month and day in the output, the time is no longer included in the column. Why is that? – Chris Apr 09 '18 at 11:46
@Chris . . . Nothing is truncating the date, so I suspect that is simply the output format you are using. If you cast the values to the timestamp type, then you should see the full time. – Gordon Linoff Apr 09 '18 at 12:10

score 0 · Accepted Answer · answered Apr 09 '18 at 12:25

You can use an UNPIVOT (using a similar technique to my answer here):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( END, LINE, CALLDURATION ) AS
  SELECT CAST( TIMESTAMP '2012-01-25 14:05:10' AS DATE ), 6, 65 FROM DUAL UNION ALL
  SELECT CAST( TIMESTAMP '2012-01-25 14:08:51' AS DATE ), 7, 1142 FROM DUAL UNION ALL
  SELECT CAST( TIMESTAMP '2012-01-25 14:20:36' AS DATE ), 5, 860 FROM DUAL;

Query 1:

SELECT p.*,
       SUM( status ) OVER ( ORDER BY dt, status DESC ) AS currentlyusedlines
FROM   (
  SELECT end - callduration / 86400 As dt,
         t.*
  FROM   table_name t
)
UNPIVOT( dt FOR status IN ( dt As 1, end AS -1 ) ) p

Results:

| LINE | CALLDURATION | STATUS |                   DT | CURRENTLYUSEDLINES |
|------|--------------|--------|----------------------|--------------------|
|    7 |         1142 |      1 | 2012-01-25T13:49:49Z |                  1 |
|    6 |           65 |      1 | 2012-01-25T14:04:05Z |                  2 |
|    6 |           65 |     -1 | 2012-01-25T14:05:10Z |                  1 |
|    5 |          860 |      1 | 2012-01-25T14:06:16Z |                  2 |
|    7 |         1142 |     -1 | 2012-01-25T14:08:51Z |                  1 |
|    5 |          860 |     -1 | 2012-01-25T14:20:36Z |                  0 |

This seems like a neat solution and it also makes the query shorter and easier to read! I will test this query in-depth and let you know if everything works as it should. Also, could you briefly explain how this works? I looked up the UNPIVOT-operator but I have not found any example-uses of it like this and I do not fully understand what happens here. I mainly do OOP and only rarely have to use SQL so the SQL syntax and basically any query that goes beyond the basics of SQL still looks quite strange and confusing to me. — Chris, Apr 09 '18 at 14:33
@Chris In this case `UNPIVOT` is doing the same job as `SELECT line, duration, start_time, 1 As status FROM table_name UNION ALL SELECT line, duration, end_time, -1 As status FROM table_name` but it will do it in a single table scan where using `UNION ALL` will do two table scans (one for each `SELECT`). A breakdown of how the query works is in the link at the top of the answer. — MT0, Apr 10 '18 at 08:19
Thank you for the response, the statement works like a charm! I will check your link soon. — Chris, Apr 10 '18 at 08:46

Oracle SQL - Efficiently calculate number of concurrent phone calls

2 Answers2