sql - Postgresql:如何在 JOIN 中使用 WITH 子查询

Question

我有 2 个表:orders 和 contragents。每个承包商可能有很多订单。每个订单都有一个 order_date。我想获得每个契约(Contract)的第一个订单日期，但有一个警告:如果订单之间的间隔超过 180 天，我需要“忘记”间隔之前的日期(因此考虑间隔之后的第一个订单“第一个”。

为此，我实现了以下声明:

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 

这会导致为代码为code 的contragent 返回单个值，这正是我所需要的。

但我无法弄清楚如何运行一个选择来为表中的每个contragent返回这个日期!

我能够做到这一点的唯一方法是使用 CREATE FUNCTION，但我无法在生产环境中做到这一点，所以..非常感谢任何建议!

Answer 1

你想添加partition by，这有点像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

现在 lag 查找具有相同 contragent_code 的行的前一个 order_date。

更新:最后，这似乎还不够。这是最后的声明:

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