CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
62mkv
62mkv

Reputation: 1522

Postgresql: How to use a WITH subquery with JOIN

I have 2 tables: orders and contragents. Each contragent might have many orders. Each order has an order_date. I want to get a first order date for each contragent, but with a caveat: if there was a gap between orders more than 180 days, I need to "forget" those before the gap (and thus the first order after the gap is considered "the first".

For this, I've implement a following statement: 

with o1 as (
    select order_date, lag(order_date) over(order by order_date ASC) as prev_order_date 
    from orders o 
    where o.contragent_code = :code
    order by order_date desc) 
select o1.date_debts from o1
where extract(day from o1.order_date-o1.prev_order_date)>=180 or o1.prev_order_date is null 
order by order_date desc
limit 1

this results in a single value being returned for a contragent with code code, which is what I need.

But I cannot figure out how to run a select that would return this date for every contragent in a table!

The only way I was able to do it was using a CREATE FUNCTION, but I will be unable to do it on production, so.. any advice is highly appreciated!

Upvotes: 2

Views: 44

Answers (1)

Atomosk
Atomosk

Reputation: 2017

You want to add partition by, which is kinda like group by for over. 

with o1 as (
    select order_date, lag(order_date) over(partition by contragent_code order by order_date ASC) as prev_order_date 
    from orders o 
    order by order_date desc) 
select o1.date_debts from o1
where extract(day from o1.order_date-o1.prev_order_date)>=180 or o1.prev_order_date is null 
order by order_date desc

Now lag looks for the previous order_date of rows with same contragent_code.

UPDATE: at the end, it appears that that was not exactly enough. This is the final statement: 

with s as (
    select o.contragent_code, o.order_date, 
        case
        when
            extract(day from order_date-lag(order_date) over(partition by contragent_code order by order_date asc))>=180 
        then o.order_date else null
        end as date_with_gap
    from orders o 
) select contragent_code, coalesce(max(date_with_gap), min(order_date)) from s 
group by contragent_code

Upvotes: 1

Related Questions