MyTetra Share
Делитесь знаниями!
postgres
Время создания: 21.09.2020 11:35
Раздел: INFO - Development - DATABASE - postgres
Запись: wwwlir/Tetra/master/base/15915428594yb41rcrop/text.html на raw.githubusercontent.com

-- create or replace view periods as

-- SELECT

-- distinct a.period as period

-- FROM

-- public.rtneo_contragent c,

-- public.rtneo_contract ct,

-- public.rtneo_contract_position cp,

-- public.rtneo_accrual a

-- WHERE

-- ct.contragent_id = c.id AND

-- cp.contract_id = ct.id AND

-- a.contract_position_id = cp.id AND

-- c.personal_account = 'ААА3812525543' and a.delete_ts isnull order by a.period;


select

p,

(select ct.number_

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.create_ts =

(select

max(ct.create_ts)

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.accepted = true

and ct.delete_ts is null

and p.period between ct.from_ and ct.before_)

) as ctn

from

periods p;

--

-- drop view periods;







*****************************************************************************************************************************************************


-- create TEMPORARY view periods as

with

periods as(

-- select a as per from generate_series('2019-01-01'::date, '2020-12-01'::date, '1 month') as a --Почему работает медленнее чем запрос? Нужно попробовать на больших объемах

SELECT

distinct a.period as per

FROM

public.rtneo_contragent c,

public.rtneo_contract ct,

public.rtneo_contract_position cp,

public.rtneo_accrual a

WHERE

ct.contragent_id = c.id AND

cp.contract_id = ct.id AND

a.contract_position_id = cp.id AND

c.personal_account = 'ААА3812525543'

and a.delete_ts isnull

order by a.period),

contracts as(

select

p.per as perct,

(select ct.id

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.create_ts =

(select

max(ct.create_ts)

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.accepted = true

and ct.delete_ts is null

and p.per between ct.from_ and ct.before_)

) as contract

from

periods p)


SELECT

a.period, a.document_number

FROM

contracts ct,

rtneo_accrual a left join

public.rtneo_contract_position cp

on a.contract_position_id = cp.id

where

a.period = ct.perct

and ct.contract = cp.contract_id

and a.delete_ts is null

order by a.period

--

-- drop view periods;

Так же в этом разделе:
 
MyTetra Share v.0.55
Яндекс индекс цитирования