114 lines
5.8 KiB
SQL
114 lines
5.8 KiB
SQL
--explain analyze
|
|
with
|
|
status(status, status_id, probability_pc) as (VALUES
|
|
('Закрыта неуспешно',7,0),('Закрыта успешно',6,100),('Договор подписан',5,100),('Договор на подписании',15,90),('Договор на согласовании',12,70),
|
|
('Тестирование',8,50),('Отправлено ТКП',10,20),('Внутреннее согласование',37,10),('Проработка решения',4,10),('Сбор потребностей',1,0)
|
|
),
|
|
begin_of_scale (dt) as (values ('2025-01-01'::timestamp)), -- этот изыск не дает видимого ускорения
|
|
end_of_scale (dt) as (values ('2025-12-01'::timestamp)), -- этот изыск не дает видимого ускорения
|
|
dayscale as (
|
|
SELECT dd::date as dt
|
|
, DATE_PART('days', DATE_TRUNC('month', dd) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) monthdays
|
|
FROM begin_of_scale, end_of_scale, generate_series(
|
|
begin_of_scale.dt
|
|
,end_of_scale.dt
|
|
,'1 day'::interval) dd
|
|
),
|
|
line as (
|
|
select siv.__id as siv_uid, siv.discount, siv.type as line_pricing_model_id
|
|
,siv.hash as line_key, siv.user_description, siv.is_actual as siv_is_actual
|
|
,siv.version::numeric as line_version
|
|
, GREATEST(siv.date_nop,d."_plannedDueDate")::date as dt_from
|
|
, COALESCE(siv.date_end::date, end_of_scale.dt) as dt_to -- не дало видимого ускорения
|
|
,siv.is_easy as is_simple, siv.cnt as quantity -- maybe should mutliply
|
|
, a.__id as agreement_uid, a.contract_uid, a.is_actual as agreement_is_actual, a.deal_uid, a.index as agreement_version
|
|
,d."_plannedDueDate", d.__name as deal, d.__status_status as deal_status_id
|
|
,c.__name as contract
|
|
,s.probability_pc
|
|
,(select sum(p.price*p.cnt*(100-p.discount)/100) from elma.service_parametrs p where(p.__id = ANY (siv.params))) as cost
|
|
from elma.deals_services siv
|
|
join elma.additional_agreements a on (a.__id = ANY(siv.additional_agreement))
|
|
join elma.deals d on (d.__id = ANY(siv.deal))
|
|
join elma.aggrements c on (a.contract_uid = c.__id)
|
|
join status s on (d.__status_status=s.status_id)
|
|
cross join end_of_scale
|
|
)
|
|
|
|
select dayscale.dt
|
|
,sum (o.cost/dayscale.monthdays*o.probability_pc/100) as sum_unbounded
|
|
,sum(
|
|
case when (o.dt_to > dayscale.dt /*OR o.dt_to IS NULL*/) then
|
|
o.cost*o.quantity*(100 - coalesce(o.discount,0)/100)*COALESCE(
|
|
case when o.agreement_is_actual then 100 else o.probability_pc end
|
|
,0)/100/dayscale.monthdays
|
|
else 0
|
|
end
|
|
) as sum_working
|
|
,sum(
|
|
case when (o.dt_to_base > dayscale.dt /*OR o.dt_to_base IS NULL*/) then 0 else
|
|
o.cost_base*o.quantity_base*(100 - coalesce(o.discount_base,0)/100)*(100 - COALESCE(
|
|
case when o.agreement_is_actual then 100 else o.probability_pc end
|
|
,0))/100/dayscale.monthdays
|
|
end
|
|
) as sum_base
|
|
,sum(
|
|
COALESCE(case when (o.dt_to > dayscale.dt /*OR o.dt_to IS NULL*/) then
|
|
o.cost*o.quantity*(100 - coalesce(o.discount,0)/100)*(
|
|
case when o.agreement_is_actual then 100 else o.probability_pc end
|
|
)/100/dayscale.monthdays
|
|
else 0 /*по окончании рабочей сделки она дает нулевой вклад*/
|
|
end,0)
|
|
+
|
|
COALESCE(case when (o.dt_to_base > dayscale.dt /*OR o.dt_to_base IS NULL*/) then
|
|
o.cost_base*o.quantity_base*(100 - coalesce(o.discount_base,0)/100)*(100 - COALESCE(
|
|
case when o.agreement_is_actual then 100 else o.probability_pc end
|
|
,0))/100/dayscale.monthdays
|
|
else 0 /*по окончании базовой сделки она дает нулевой вклад*/
|
|
end,0)
|
|
) as sum_bounded
|
|
,max(o.dt_from) as max_dt_from, max(o.dt_from_base) as max_dt_from_base, max(o.probability_pc) as max_probability_pc
|
|
,count(*)
|
|
from dayscale
|
|
LEFT OUTER JOIN (
|
|
SELECT t.*,
|
|
coalesce((LEAD(t.dt_from) OVER (PARTITION BY t.line_key ORDER BY t.dt_from, t.agreement_version,t.line_version)), (select max(dt) from elma.mv_dt)) dt_next,
|
|
b.discount as discount_base, b.quantity as quantity_base, b.dt_from as dt_from_base, b.dt_to as dt_to_base, b.cost as cost_base
|
|
|
|
FROM (
|
|
SELECT *,
|
|
MIN(dt_from) OVER (PARTITION BY line_key ORDER BY agreement_version desc,line_version desc) running_min_dt
|
|
FROM line where siv_is_actual AND probability_pc > 0
|
|
) t
|
|
|
|
LEFT OUTER JOIN (
|
|
select * from -- базовая лесенка
|
|
(
|
|
SELECT *,
|
|
MIN(dt_from) OVER (PARTITION BY line_key ORDER BY agreement_version desc,line_version desc) running_min_dt
|
|
FROM line where siv_is_actual AND probability_pc=100 AND agreement_is_actual AND deal_status_id in (5,6)
|
|
) i where i.dt_from=i.running_min_dt
|
|
) b on (t.line_key=b.line_key AND b.siv_uid=(
|
|
select siv_uid from
|
|
( -- базовая лесенка
|
|
SELECT siv_uid, dt_from, agreement_version, line_version, line_key,
|
|
MIN(dt_from) OVER (PARTITION BY line_key ORDER BY agreement_version desc, line_version desc) running_min_dt
|
|
FROM line ii
|
|
where ii.siv_is_actual AND ii.probability_pc=100 AND ii.agreement_is_actual AND ii.deal_status_id in (5,6)
|
|
AND ((t.agreement_version > ii.agreement_version) OR (t.agreement_version = ii.agreement_version AND t.line_version > ii.line_version))
|
|
) i
|
|
where i.dt_from=i.running_min_dt
|
|
AND t.line_key=i.line_key
|
|
AND i.dt_from < t.dt_from
|
|
ORDER BY /*dt_from desc,*/ agreement_version desc, line_version desc
|
|
LIMIT 1
|
|
)
|
|
)
|
|
where t.dt_from=t.running_min_dt -- это условие формирования лесенки, далко оторвано от ее селекта
|
|
AND t.line_pricing_model_id in (2,3)
|
|
AND t.probability_pc > 0 -- несколько искусственное условие, убирающее несущественные и неуспешные сделки
|
|
|
|
--AND t.line_key in ('0a61f9e4-520b-4f0a-aac0-c1d42c037e1b','0bde160d-52d0-4d55-b4e0-05f45cfd739e','08485a0c-a913-4332-bb36-e40dec27d12e','1809bb31-4d37-424b-ba2b-26b9a873af7f')
|
|
) o
|
|
on (o.dt_from <= dayscale.dt AND dayscale.dt < o.dt_next)
|
|
group by dayscale.dt
|
|
order by dayscale.dt |