daily pastebin goal
76%
SHARE
TWEET

Untitled

tyomasun Mar 22nd, 2019 55 in 2 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH RECURSIVE t AS (
  2.     SELECT
  3.         *
  4.     ,   (
  5.             SELECT
  6.                 call_begin dt
  7.             FROM
  8.                 CALL c
  9.             WHERE
  10.                 reverse(sub_phone) = dv AND
  11.                 call_begin <= '2019-03-21 18:02:37.361695'::timestamptz AND
  12.                 call_begin >= '2016-03-21 18:02:37.374827'::timestamptz AND
  13.                 call_result = ANY(ARRAY[3, 4, 5]::INTEGER[]) AND
  14.                 NOT is_inner AND
  15.                 NOT EXISTS(
  16.                     SELECT
  17.                         NULL
  18.                     FROM
  19.                         call_link
  20.                     WHERE
  21.                         call_id = id AND
  22.                         link_type = ANY(ARRAY[0, 1, 10, 11, 12])
  23.                     LIMIT 1
  24.                 ) AND
  25.                 NOT c.is_inner AND
  26.                 c.call_type = ANY('{0}'::INTEGER[]) AND
  27.                 c.client = 3::BIGINT AND
  28.                 c.call_result = ANY('{3,4,5}'::INTEGER[])
  29.             ORDER BY
  30.                 call_begin DESC
  31.             LIMIT 1
  32.         ) dtb
  33.     FROM
  34.         unnest(
  35.             ARRAY['111870559480', '99131015948', '99931015948', '02041015948', '42041015948', '84041015948', '21033015948', '19775015948', '89786015948', '83117015948', '90417015948', '80477015948', '83269015948', '76170215948', '04313215948', '90344215948', '33454215948', '15374215948', '04894215948', '77994215948', '50145215948', '84145215948', '17665215948', '22978215948', '09339215948', '77171315948', '98146315948', '59617315948', '45037315948', '64757315948', '56167315948', '15287315948', '87421415948', '10541415948', '69612415948', '60393415948', '54706415948', '31816415948', '65296415948', '38368415948', '08478415948', '06310515948', '06350515948', '60260515948', '12631515948', '00791515948', '28342515948', '59782515948', '39594515948', '59347515948', '95624615948', '00984615948', '32326615948', '62536615948', '08936615948', '77349615948', '92103715948', '44343715948', '27084715948', '46608715948', '55559715948', '92410815948', '03250815948', '98360815948', '45070815948', '81411815948', '07511815948', '20161815948', '88802025948', '00532025948', '05862025948', '21413025948', '30433025948', '29873025948', '09383025948', '00711225948', '37421225948', '22281225948', '36281225948', '00743225948', '63663225948', '79293225948', '15335225948', '56445225948', '58096225948', '30196225948', '80196225948', '11196225948', '81318225948', '40418225948', '01458225948', '07950325948', '91002325948', '64102325948', '03302325948', '23802325948', '24532325948', '34532325948', '01304325948', '81524325948', '46144325948', '05305325948', '56265325948', '76785325948', '45117325948', '96637325948', '98277325948', '38828325948', '21878325948', '35188325948', '10988325948', '76921425948', '91551425948', '52671425948', '57704425948', '70505425948', '21735425948', '24206425948', '38346425948', '38276425948', '03886425948', '26217425948', '77258425948', '14578425948', '63740525948', '27270525948', '14790525948', '00311525948', '50951525948', '23371525948', '72922525948', '41462525948', '81962525948', '78633525948', '50953525948', '80283525948', '80034525948', '50305525948', '78026525948', '70136525948', '22536525948', '51007525948', '88537525948', '53647525948', '25457525948', '94208525948', '41528525948', '54938525948', '77938525948', '80358525948', '52358525948', '63068525948', '49068525948', '33288525948', '25998525948', '85998525948', '50629525948', '93649525948', '01699525948', '43911625948', '28191625948', '69642625948', '04962625948', '86072625948', '02203625948', '24573625948', '03105625948', '09505625948', '44455625948', '97655625948', '63166625948', '29607625948', '33127625948', '22128625948', '14688625948', '16360925948', '14960925948', '07180925948', '56191925948', '34491925948', '39501035948', '02431035948', '37141035948', '21172035948', '95292035948', '60243035948', '75804035948', '17934035948', '98476035948', '47196035948', '61037035948', '97201135948', '54672135948', '31534135948', '09908135948', '93342235948', '17753235948', '34364235948', '02794235948', '00505235948', '01025235948', '11055235948', '47855235948', '34685235948', '08907235948', '62787235948', '88822335948', '55922335948', '67953335948', '64163335948', '59074335948', '50616335948', '90916335948', '00418335948', '06862435948', '46862435948', '00285435948', '94295435948', '00516435948', '00196435948', '11196435948', '11597435948', '26208435948', '12059435948', '84430535948', '19047535948', '91188535948', '36051635948', '70012635948', '21962635948', '42913635948', '11223635948', '04293635948', '72825635948', '69135635948', '62145635948', '94855635948', '31137635948', '90957635948', '88067635948', '18769635948', '62989635948', '67320735948', '55011735948', '06994735948', '55525735948', '70188735948', '46500835948', '74441835948', '73672835948', '49204835948', '69464835948', '54268835948', '36419835948', '72549835948', '72951935948', '11755935948', '75176935948', '22411045948', '92165045948', '30885045948', '74006045948', '10029045948', '14351145948', '24351145948', '00061145948', '11171145948', '18191145948', '49822145948', '51154145948', '31864145948', '45815145948', '40906145948', '31507145948', '86210245948', '86932245948', '79542245948', '06823245948', '03474245948', '88475245948', '25306245948', '88556245948', '03398245948', '77669245948', '47400345948', '10920345948', '49060345948', '42502345948', '44433345948', '67204345948', '94394345948', '26786345948', '93947345948', '59300445948', '36600445948', '61910445948', '15160445948', '47960445948', '93291445948', '47913445948', '81123445948', '42673445948', '06817445948', '55538445948', '00778445948', '31000545948', '30200545948', '10920545948', '71102545948', '80292545948', '91026545948', '94586545948', '46289545948', '83351645948', '25142645948', '73488645948', '89219645948', '64460745948', '84070745948', '72321745948', '33354745948', '42135745948', '65826745948', '43866745948', '37951845948', '11242845948', '88993845948', '10634845948', '38635845948', '31006845948', '27886845948', '06499845948', '28130945948', '83630945948', '53272945948', '52182945948', '00204945948', '02284945948', '50960055948', '40570055948', '20322055948', '84362055948', '03292055948', '96404055948', '10884055948', '49355055948', '58755055948', '07855055948', '24426055948', '56066055948', '20476055948', '00796055948', '25917055948', '19818055948', '38868055948', '61710155948', '40040155948', '32350155948', '33680155948', '21090155948', '87004155948', '21214155948', '41064155948', '75687155948', '94218155948', '16090355948', '01351355948', '15692355948', '24504355948', '93664355948', '96494355948', '23475355948', '49485355948', '78795355948', '30656355948', '77656355948', '58080455948', '27244455948', '19474455948', '08805455948', '19936455948', '60681555948', '20048555948', '75348555948', '66398555948', '19332655948', '19070855948', '42380855948', '81894855948', '83894855948', '09401955948', '22501955948', '15221065948', '15051065948', '90291065948', '89344065948', '36325065948', '16945065948', '43585065948', '81216065948', '79548065948', '27268065948', '64588065948', '89698065948', '93369065948', '38600165948', '93520165948', '30070165948', '55980165948', '42331165948', '50631165948', '77562165948', '22572165948', '91403165948', '67553165948', '72263165948', '03414165948', '32134165948', '50264165948', '07664165948', '72194165948', '54694165948', '88525165948', '04775165948', '31926165948', '32446165948', '43446165948', '49946165948', '14656165948', '22666165948', '63028165948', '54328165948', '38358165948', '29849165948', '08279165948', '22479165948', '00340265948', '61611265948', '51231265948', '81331265948', '85951265948', '67771265948', '86822265948', '28003265948', '40843265948', '05353265948', '87963265948', '84873265948', '39293265948', '13004265948', '01604265948', '42054265948', '41264265948', '02674265948', '69494265948', '33965265948', '02795265948', '26895265948', '47828265948', '37448265948', '04059265948', '86689265948', '23299265948', '28711365948', '61807365948', '34777365948', '86058365948', '01372465948', '17436465948', '72707465948', '27838465948', '19578465948', '89100565948', '74740565948', '98850565948', '42701565948', '77911565948', '28821565948', '14787565948', '52458565948', '43758565948', '02510665948', '23810665948', '66530665948', '07900765948', '40070765948', '11270765948', '42921765948', '15122765948', '34105765948', '87405765948', '58905765948', '44215765948', '57355765948', '51506765948', '33226765948', '10846765948', '83047765948', '13409765948', '70519765948', '81519765948', '01549765948', '04949765948', '95949765948', '47340865948', '36090865948', '02921865948', '89731865948', '02061865948', '02222865948', '67513865948', '61304865948', '33234865948', '80054865948', '44374865948', '45266865948', '72576865948', '05576865948', '01447865948', '60219865948', '36039865948', '23430965948', '03150965948', '59360965948', '66141965948', '78502965948', '24432965948', '69742965948', '82942965948', '08014965948', '25264965948', '23364965948', '84029965948', '46049965948', '22379965948', '86089965948', '91689965948', '14199965948', '50299965948', '34000075948', '50201075948', '08662075948', '24804075948', '74214075948', '00154075948', '00254075948', '00374075948', '64975075948', '68636075948', '28656075948', '64817075948', '77787075948', '17088075948', '11829075948', '05800175948', '45102175948', '54822175948', '42272175948', '81557175948', '33088175948', '89498175948', '30260275948', '84660275948', '08011275948', '06631275948', '55674275948', '85505275948', '17805275948', '92905275948', '16905275948', '26905275948', '51445275948', '57606275948', '70707275948', '45517275948', '36617275948', '56247275948', '66247275948', '09358275948', '67539275948', '60000375948', '10110375948', '77240375948', '71250375948', '81250375948', '68580375948', '09603375948', '89393375948', '61615375948', '22707375948', '68467375948', '42077375948', '39377375948', '47477375948', '83087375948', '07687375948', '17687375948', '09987375948', '48609375948', '80809375948', '02920475948', '28940475948', '48940475948', '58940475948', '25480475948', '60901475948', '30331475948', '41362475948', '43604475948', '32114475948', '39344475948', '63274475948', '56555475948', '29065475948', '89595475948', '35066475948', '82896475948', '01028475948', '12178475948', '22178475948', '45410575948', '93330575948', '92301575948', '69271575948', '11222575948', '08065575948', '04485575948', '58985575948', '68985575948', '78095575948', '03195575948', '46916575948', '67676575948', '63638575948', '25119575948', '66669575948', '38007675948', '24401775948', '35211775948', '07651775948', '00161775948', '09471775948', '30012775948', '48462775948', '18672775948', '45033775948', '58005775948', '72105775948', '06305775948', '34705775948', '61615775948', '61525775948', '88135775948', '91345775948', '55685775948', '81885775948', '98917775948', '22337775948', '36457775948', '26267775948', '10867775948', '48887775948', '71997775948', '70218775948', '36468775948', '94588775948', '05769775948', '17700875948', '13530875948', '67730875948', '75150875948', '18150875948', '31550875948', '51550875948', '60490875948', '83501875948', '18011875948', '59011875948', '50161875948', '58682875948', '54103875948', '47103875948', '48743875948', '03553875948', '24493875948', '67264875948', '23674875948', '80855875948', '84485875948', '51436875948', '25536875948', '75786875948', '63107875948', '65707875948', '42227875948', '11257875948', '95067875948', '34967875948', '15967875948', '76677875948', '97777875948', '66608875948', '90908875948', '01018875948', '89618875948', '03148875948', '29148875948', '39148875948', '59148875948', '56658875948', '53958875948', '77778875948', '04098875948', '02998875948', '04799875948', '35999875948', '96942975948', '08034975948', '08334975948', '00605975948', '77127975948', '76627975948', '00037975948', '51237975948', '77247975948', '51347975948', '28347975948', '79347975948', '07087975948', '23799975948', '11280095948', '23280095948', '35361095948', '60833095948', '72183095948', '71383095948', '27683095948', '00684095948', '27035095948', '83787095948', '11231195948', '45631195948', '60831195948', '56512195948', '95542195948', '32952195948', '61962195948', '54105195948', '24555195948', '13955195948', '85165195948', '00975195948', '12595195948', '93906195948', '72316195948', '70516195948', '85226195948', '43726195948', '29636195948', '26446195948', '14066195948', '76066195948', '87117195948', '96447195948', '39257195948', '60577195948', '67777195948', '11069195948', '08080295948', '14055295948', '87640395948', '74280395948', '47222395948', '79323395948', '19453395948', '02783395948', '28783395948', '00884395948', '00325395948', '37675395948', '66585395948', '21637395948', '65857395948', '99667395948', '41077395948', '42077395948', '92809395948', '01810495948', '57550495948', '78780495948', '20964495948', '10815495948', '03255495948', '10648495948', '09209495948', '49930595948', '98960595948', '51351595948', '10751595948', '38161595948', '07961595948', '97961595948', '99012595948', '08282595948', '63133595948', '67533595948', '59453595948', '00015595948', '45115595948', '99215595948', '93815595948', '10925595948', '20925595948', '79925595948', '02916595948', '76026595948', '92246595948', '42386595948', '52096595948', '63396595948', '16017595948', '45918595948', '96748595948', '30909595948', '73039595948', '24039595948', '10339595948', '46279595948', '01810695948', '66060695948', '35401695948', '69221695948', '00502695948', '75712695948', '08282695948', '23482695948', '40003695948', '01003695948', '18923695948', '26073695948', '68173695948', '62473695948', '11114695948', '23414695948', '73694695948', '96994695948', '73285695948', '89410795948', '41750795948', '01311795948', '52792795948', '18714795948', '60264795948', '95774795948', '07284795948', '06384795948', '09606795948', '21207795948', '33917795948', '55587795948', '22560895948', '88170895948', '29880895948', '04011895948', '02931895948', '46941895948', '63051895948', '65233995948', '52063995948', '36993995948', '31725995948', '96085995948', '32695995948', '13806995948', '88576995948', '49647026948', '29382226948', '94998526948', '50127626948', '64407636948', '77233736948', '64643736948', '53012836948', '29742836948', '05074836948', '64474836948', '59925836948', '57195836948', '01257836948', '23417446948', '89674646948', '95966456948', '89542166948', '81384166948', '44447166948', '22105366948', '37315366948', '21214466948', '43319466948', '43113766948', '65813766948', '94423766948', '90614276948', '84224276948', '63154276948', '68154276948', '46170576948', '69870576948', '05280576948', '00857576948', '47001676948', '41601676948', '42211676948', '87615676948', '94955676948', '81385676948', '60685676948', '17447676948', '95705776948', '29594509948', '00856219948', '36838219948', '19948219948', '02088219948', '31712319948', '92072319948', '92315319948', '34010419948', '30110419948', '74325419948', '33062519948', '90717519948', '66657519948', '55677519948', '86518519948', '39368519948', '53768519948', '06598519948', '69625619948', '28307619948', '22128619948', '57828619948', '77068619948', '61298619948', '23212819948', '30152819948', '67356819948', '56856819948', '77396819948', '16858819948', '56198819948', '63401919948', '52552919948', '46062919948', '09162919948', '11492919948', '91503919948', '02553919948', '87304919948', '67894919948', '80995919948', '05406919948', '51717919948', '19537919948', '28737919948', '88747919948', '61198919948', '13298919948', '69009919948', '03764029948', '59323229948', '43871329948', '08871329948', '51555329948', '08275329948', '84367329948', '85610429948', '88330429948', '06940429948', '45752429948', '58382429948', '56982429948', '66982429948', '18274429948', '07806429948', '18678429948', '17109429948', '63119429948', '21489429948', '12500529948', '73161529948', '54523529948', '09275529948', '15107529948', '12971629948', '60004629948', '22544629948', '49754629948', '29064629948', '55774629948', '44465629948', '23937629948', '81157629948', '08577629948', '27927139948', '63567139948', '54577139948', '36087139948', '76945339948', '13036539948', '78567539948', '29443739948', '14363049948', '10226049948', '24592749948', '21856749948', '11059749948', '15676949948', '14750169948', '11970169948', '28582169948', '86423169948', '86393169948', '77826169948', '92537169948', '11168169948', '41709169948', '88306769948', '41056969948', '10468279948', '24059279948', '82191379948', '44443379948', '77058379948', '70798379948', '30830479948', '49068479948', '59068479948', '11014679948', '63457679948', '84585779948', '39332879948', '70432879948', '77317979948', '04173099948', '43153499948', '13386499948', '79786499948', '37367699948', '78232799948', '52503799948', '04113799948', '81913799948', '02623799948', '85333799948', '55733799948', '12773799948', '92773799948', '18193799948', '85493799948', '67108799948', '10028799948', '46328799948', '68928799948', '91668799948', '98968799948', '21378799948', '23088799948', '80864513098', '67672806098', '79490916198', '92403426198', '94158866198', '84411475298', '07609106298', '74558026298', '45558026298', '75558026298', '92854166298']::text[]
  36.         ) dv
  37. )
  38. , rng AS (
  39.     SELECT DISTINCT ON(dtb)
  40.         dtb
  41.     ,   COALESCE(lead(dtb) OVER(w [???][WindowDef : frameOptions=1058]), '-infinity') dte
  42.     ,   array_agg(dv) OVER(w [???][WindowDef : frameOptions=1058]) dvs
  43.     FROM
  44.         t
  45.     WHERE
  46.         dtb IS NOT NULL
  47.     WINDOW
  48.         w AS(ORDER BY dtb DESC, dv [???][WindowDef : frameOptions=1058])
  49.     ORDER BY
  50.         dtb DESC
  51.     ,   dv DESC
  52. )
  53. , rngid AS (
  54.     SELECT
  55.         ROW_NUMBER() OVER(ORDER BY dtb DESC [???][WindowDef : frameOptions=1058]) rn
  56.     ,   *
  57.     FROM
  58.         rng
  59. )
  60. , cy AS (
  61.     SELECT
  62.         0::BIGINT i
  63.     ,   NULL::timestamptz dtb
  64.     ,   NULL::timestamptz dte
  65.     ,   NULL::text[]
  66.     ,   '{}'::CALL[] facts
  67. UNION ALL
  68.     SELECT
  69.         i + 1
  70.     ,   r.dtb
  71.     ,   r.dte
  72.     ,   r.dvs
  73.     ,   cy.facts || ARRAY(
  74.             SELECT
  75.                 unnest(ARRAY(
  76.                     SELECT
  77.                         c
  78.                     FROM
  79.                         CALL c
  80.                     WHERE
  81.                         reverse(sub_phone) = dvl AND
  82.                         call_begin <= r.dtb AND
  83.                         call_begin > r.dte AND
  84.                         call_result = ANY(ARRAY[3, 4, 5]::INTEGER[]) AND
  85.                         NOT is_inner AND
  86.                         NOT EXISTS(
  87.                             SELECT
  88.                                 NULL
  89.                             FROM
  90.                                 call_link
  91.                             WHERE
  92.                                 call_id = id AND
  93.                                 link_type = ANY(ARRAY[0, 1, 10, 11, 12])
  94.                             LIMIT 1
  95.                         ) AND
  96.                         NOT c.is_inner AND
  97.                         c.call_type = ANY('{0}'::INTEGER[]) AND
  98.                         c.client = 3::BIGINT AND
  99.                         c.call_result = ANY('{3,4,5}'::INTEGER[])
  100.                     ORDER BY
  101.                         call_begin DESC
  102.                     LIMIT 22 - COALESCE(array_length(cy.facts, 1), 0)
  103.                 ))
  104.             FROM
  105.                 unnest(r.dvs) dvl
  106.         )
  107.     FROM
  108.         cy
  109.     JOIN
  110.         rngid r
  111.             ON r.rn = cy.i + 1
  112.     WHERE
  113.         22 > COALESCE(array_length(cy.facts, 1), 0)
  114. )
  115. SELECT
  116.     id
  117. ,   pbx_uniq_id
  118. ,   call_begin
  119. FROM
  120.     unnest((
  121.         SELECT
  122.             facts
  123.         FROM
  124.             cy
  125.         WHERE
  126.             facts <> '{}'
  127.         ORDER BY
  128.             i DESC
  129.         LIMIT 1
  130.     ))
  131. ORDER BY
  132.     call_begin DESC
  133. LIMIT 22 OFFSET 0;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top