soapee01

Find max concurrent inbound calls in CDR's

Dec 3rd, 2021
1,473
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --on newer cdr's use tstzrange instead of tsrange
  2. --this query can take a long time on systems with a lot of records.
  3. --needs optimization (esp filtering the date)
  4. WITH timeslots AS (
  5.         SELECT * FROM (
  6.         SELECT
  7.                 direction, tsrange(timepoint, lead(timepoint) OVER (ORDER BY timepoint)),
  8.                 lead(timepoint) OVER (ORDER BY timepoint)
  9.                 FROM (
  10.                         SELECT
  11.                                 direction, unnest(ARRAY["start_stamp", "end_stamp"]) as timepoint
  12.                                 FROM
  13.                                 v_xml_cdr
  14.                                 WHERE "direction" = 'inbound' AND (CAST("start_stamp" AS text) LIKE '%2021-11%' OR CAST("end_stamp" AS text) LIKE '%2021-11%')
  15.                                         AND "domain_uuid" = 'PASTE_UUID_HERE' AND ("hangup_cause" != 'NO_ROUTE_DESTINATION' OR "sip_hangup_disposition" != 'send_refuse')
  16.                                 ORDER BY timepoint
  17.                         ) s
  18.             )s  WHERE lead IS NOT NULL
  19. )
  20. SELECT
  21.    GREATEST(MAX(start_stamp), lower(tsrange)),
  22.    LEAST(MIN("end_stamp"), upper(tsrange)),
  23.    COUNT(xml_cdr_uuid),
  24.         array_agg(xml_cdr_uuid)
  25.         FROM
  26.         timeslots t
  27.         JOIN v_xml_cdr a
  28.         ON (t.tsrange && tsrange(a.start_stamp, a.end_stamp)) AND a.direction='inbound'
  29.         GROUP BY tsrange
  30.         HAVING cardinality(array_agg(xml_cdr_uuid)) > 1
  31.         ORDER BY COUNT
RAW Paste Data