CODEWITHSUNDEEP
sqloracle-database
Chris
Chris

Reputation: 35

Oracle SQL - Efficiently calculate number of concurrent phone calls

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?

Upvotes: 1

Views: 210

Answers (2)

MT0
MT0

Reputation: 168361

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 |

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270583

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.

Upvotes: 1

Related Questions