CREATE OR REPLACE package call_analysis_pkg AS TYPE call_result_type IS RECORD( period_start_time TIMESTAMP, close_contact NUMBER, agree_result NUMBER, dnd_result NUMBER, refusal_result NUMBER, recall_result NUMBER, per_close_contact NUMBER, per_agree_call NUMBER, per_dnd_call NUMBER, per_refusal_call NUMBER, per_recall_call NUMBER); TYPE call_result_table IS TABLE OF call_result_type; FUNCTION get_call_results_between_dates(d1 TIMESTAMP, d2 TIMESTAMP) RETURN call_result_table PIPELINED; END; / CREATE OR REPLACE package BODY call_analysis_pkg AS FUNCTION get_call_results_between_dates(d1 TIMESTAMP, d2 TIMESTAMP) RETURN call_result_table PIPELINED AS BEGIN FOR rec IN ( WITH sq_last_call AS ( SELECT fid_contact, fid_result, call_start_time FROM (SELECT dense_rank() OVER (PARTITION BY fid_contact ORDER BY id DESC) AS rnk, fid_contact, fid_result, call_start_time, id FROM calls) WHERE rnk = 1), sq_date_interval AS (SELECT d1 + ( level - 1 ) * ( INTERVAL '1' DAY ) AS period_start_time, d1 + ( level * ( INTERVAL '1' DAY ) ) AS period_finish_time, level FROM dual CONNECT BY level <= ( EXTRACT(DAY FROM(d2 - d1 ) ) ) ), -- 4), -- ( TO_DATE(d2, 'DD.MM.YYYY') ) - ( TO_DATE(d1, 'DD.MM.YYYY') )), last_call_date_interval_contacts AS ( SELECT c2.*, d2.*, c.fid_block_reason FROM sq_last_call c2 JOIN contacts c ON c.id = c2.fid_contact RIGHT JOIN sq_date_interval d2 ON c2.call_start_time BETWEEN d2.period_start_time AND d2.period_finish_time), -- SELECT * FROM last_call_date_interval_contacts count_a_day AS( SELECT period_start_time, SUM(NVL2 (call_start_time, 1, 0)) AS count_a_day -- 2 Количество совершенных звонков в день FROM calls c2 RIGHT JOIN sq_date_interval d2 ON c2.call_start_time BETWEEN d2.period_start_time AND d2.period_finish_time GROUP BY ROLLUP (period_start_time)), -- select * from count_a_day count_call_results AS ( SELECT v.period_start_time, COUNT(CASE WHEN v.fid_block_reason>1 THEN 1 ELSE NULL END ) AS close_contact, -- 1 Количество закрытых контактов SUM(CASE WHEN g.id=2 THEN 1 ELSE 0 END ) AS agree_result, -- 4) Кол-во контактов, закрытых как согласие( id group =2) SUM(CASE WHEN g.id=4 THEN 1 ELSE 0 END ) AS dnd_result, -- 6) Кол-во контактов, закрытых как недозвон( id group =4) SUM(CASE WHEN g.id=3 THEN 1 ELSE 0 END ) AS refusal_result, -- 8) Кол-во контактов, закрытых как отказ( id group =3) SUM(CASE WHEN r.id=1 THEN 1 ELSE 0 END ) AS recall_result -- 10) Кол-во контактов, закрытых как перезвон( id result =1) FROM d_results r JOIN d_groups g ON r.fid_group = g.id RIGHT JOIN last_call_date_interval_contacts v ON r.id = v.fid_result GROUP BY ROLLUP (v.period_start_time)) -- select * from count_call_results SELECT r.period_start_time, close_contact, agree_result, dnd_result, refusal_result, recall_result, ROUND(close_contact/(CASE WHEN count_a_day=0 THEN 1 ELSE count_a_day END )*100) AS per_close_contac, --3 Процент закрытий от звонков (1)/(2)*100 ROUND(agree_result/(CASE WHEN count_a_day=0 THEN 1 ELSE count_a_day END )*100) as per_agree_call, -- 5 Процент согласившихся от общего кол-ва звонков (4)/(2)*100 ROUND(dnd_result/(CASE WHEN close_contact=0 THEN 1 ELSE close_contact END )*100) as per_dnd_call, -- 7 Процент недозвонов от закрытых анкет (6)/(1)*100 ROUND(refusal_result/(CASE WHEN count_a_day=0 THEN 1 ELSE count_a_day END )*100) as per_refusal_call, -- 9) Процент отказов от общего кол-ва звонков (8)/(2)*100 ROUND(recall_result/(CASE WHEN count_a_day=0 THEN 1 ELSE count_a_day END )*100) as per_recall_call -- 11) Процент перезвонов от общего кол-ва звонков (10)/(2)*100 FROM count_call_results r JOIN count_a_day d on nvl(r.period_start_time, sysdate) = nvl(d.period_start_time, sysdate) ) LOOP PIPE ROW(rec); END LOOP; END; END; / --- вызов функции SELECT * FROM TABLE(call_analysis_pkg.get_call_results_between_dates( (systimestamp - numtodsinterval(1108, 'day')), (systimestamp - numtodsinterval(1103, 'day')) )); SELECT * FROM TABLE(call_analysis_pkg.get_call_results_between_dates( to_timestamp('01.12.2020', 'DD.MM.YYYY') , to_timestamp('06.12.2020', 'DD.MM.YYYY') ));