From: | Glenn Schultz <glenn(at)bondlab(dot)io> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Recursive CTE |
Date: | 2018-12-29 18:34:31 |
Message-ID: | CAE-4=KHD=VYL06WyCtRL-pRhtgUGyNrOR_98A=vKmXf9i5RuOA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
All,
Following my earlier post on variable instantiation, I rethought how I was
working with dates and realized I can fix the date and use static
interval. I came up with this recursive CTE which is the end goal.
However, the problem is that the convexity query cannot be used as a
subquery. So I think I need to use a join of convexity on the original
query - not sure I am little stuck at this point but I feel I am close.
Any help would be appreciated.
-Glenn
SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
SET enable_partitionwise_aggregate = on;
with recursive convexity (fctrdt, CPR3mo) as
(
select
cast((select max(fctrdt) - interval '1 month' from fnmloan) as date) as
"fctrdt"
,round(
smmtocpr(
cast(
sum(currrpb * case when fnmloan.fctrdt = fnmloan_data.fctrdt then
fnmloan_data.event else 0 end)/ sum(currrpb)
as numeric) * 100
),4) * 100 as "CPR 3mo"
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt between ((select max(fctrdt)-interval '1 month' from
fnmloan) - interval '2 month') and (select max(fctrdt) - interval '1 month'
from fnmloan)
and
fnmloan.poolprefix = 'CL'
union all
select
cast((select max(fctrdt) - interval '1 month' from convexity) as date) as
"fctrdt"
,round(
smmtocpr(
cast(
sum(currrpb * case when fnmloan.fctrdt = fnmloan_data.fctrdt then
fnmloan_data.event else 0 end)/ sum(currrpb)
as numeric) * 100
),4) * 100 as "CPR 3mo"
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt between ((select max(fctrdt)-interval '1 month' from
convexity) - interval '2 month') and (select max(fctrdt) - interval '1
month' from convexity)
and
fnmloan.poolprefix = 'CL'
and
convexity.fctrdt <= (select max(fctrdt) - interval' 12 months' from fnmloan)
)
select * from convexity
From | Date | Subject | |
---|---|---|---|
Next Message | Chuck Martin | 2018-12-29 19:04:37 | getting pg_basebackup to use remote destination |
Previous Message | Glenn Schultz | 2018-12-29 18:27:25 | Re: initialize and use variable in query |