sql - postgresql:按列/windows 函数/min-max 和复杂查询分组

Question

想象一下，我有两个分行的发票。我需要选择最小和最大发票日期，并在该日期显示分支 ID。如果在最小/最大日期有几个分行有发票，请选择任何一个。

CREATE TEMP TABLE invoice (
   id int not null,
   branch_id int  not null,
   c_date date  not null,
   PRIMARY KEY (id)
);

insert into invoice (id, branch_id, c_date) values
    (1, 1, '2020-01-01') 
    ,(2, 2, '2020-01-01')
    ,(3, 1, '2020-01-02')
    ,(4, 2, '2020-01-02')
    ,(5, 2, '2020-01-03');

直接的解决方案是(跳过最大部分以免使查询过于复杂)。

select i2.branch_id, i2.c_date from (
    select min(i1.id) minid
    from (select min(i.c_date) mind, max(i.c_date) maxd 
        from invoice i
        )a
        join invoice i1
            on a.mind=i1.c_date) b 
    join invoice i2 on b.minid=i2.id

窗口函数解决方案有点简单但也很尴尬。请记住，实际查询比较复杂，我只提供核心部分。

select * from (
    select a.branch_id, a.c_date from(
        select *, rank() over (order by c_date) r from invoice i
    ) a where a.r=1
    limit 1
) mn,
(select a.branch_id, a.c_date from(
        select *, rank() over (order by c_date desc) r from invoice i
    ) a where a.r=1
    limit 1
) mx

关于如何更优雅地编写查询的任何猜测？

Answer 1

一种方法是使用数组的技巧:

select min(date),
       (array_agg(branch_id order by date))[1] as first_branch,
       max(date),
       (array_agg(branch_id order by date desc))[1] as last_branch
from invoice;

这确实将所有值聚合到一个数组中，因此如果每个结果行中有太多值，您就不会想要使用它。