时间戳

首页 > 解决方案 > Psql : 获取每个合作伙伴发票和最后一次付款的 Min、Max 和 Count 记录

sql - Psql : 获取每个合作伙伴发票和最后一次付款的 Min、Max 和 Count 记录

问题描述

我有一张这样的表格发票：

id, partner_id, number, invoice_date

和这样的付款表：

id, payment_date, partner_id

我想获得 number 和 invoice_date 的最小值和最大值，并计算发票，以及每个合作伙伴的最后一次付款，如下所示：

partner_id, min number, min date, max number, max date, count, last_pay
1, INV-2017-003, 02-01-2017, INV-2020-010, 01-01-2020, 142, 02-12-2019
5, INV-2019-124, 05-03-2019, INV-2020-005, 01-01-2020, 150, 01-01-2020
....

标签： sqlpostgresql

解决方案

您可以加入这三个表，包括合作伙伴和按合作伙伴的 id 列分组以及相关的聚合：

 select pr.id, min(invoice_date), max(invoice_date), count(*), max(payment_date) as last_pay
   from partners pr
   left join invoices i on i.partner_id = pr.id
   left join payments p on p.partner_id = pr.id
  group by pr.id

更新：您可以根据最大和最小日期使用和min() over ()分析函数来获取所需的代码：max() over ()row_number()

select *
  from
 (
 select pr.id, 
        min(invoice_date) over (partition by pr.id order by invoice_date) as min_invoice_date, 
        max(invoice_date) over (partition by pr.id order by invoice_date desc) as max_invoice_date, 
        max(code) over (partition by pr.id order by invoice_date desc) as max_code,
        min(code) over (partition by pr.id order by invoice_date) as min_code,
        count(*)  over (partition by pr.id) as cnt, 
        max(payment_date) over (partition by pr.id) as last_pay,
        row_number() over (partition by pr.id order by invoice_date desc) as rn
   from partners pr
   left join invoices i on i.partner_id = pr.id
   left join payments p on p.partner_id = pr.id      
   ) q
 where rn = 1

推荐阅读