PostgreSQL分组查询各门店最小audit_ending日期及对应certificate_id

数据表定义 #

create table measure_app_scalemeasurement (
  store_code text,
  certificate_id text,
  audit_ending date);
insert into measure_app_scalemeasurement values
('K010','vwv',  '10.12.2023'),
('K010','cert1','12.12.2023'),
('K054','vwv',  '14.12.2023'),
('K054','cert1','20.01.2024');

需求 #

获取每个store_code对应的最小audit_ending日期，同时返回该日期对应的certificate_id。

错误尝试及问题 #

1. 第一次查询：

select store_code, certificate_id, min(audit_ending) from measure_app_scalemeasurement
group by store_code, certificate_id

问题：按store_code和certificate_id联合分组，会保留每个门店下的所有证书记录，无法筛选出每个门店最早日期的那条数据。

2. 第二次查询：

select distinct on (store_code) store_code, certificate_id, min(audit_ending) from measure_app_scalemeasurement
group by store_code, certificate_id

问题：distinct on会取分组后的第一条记录，但分组后的结果顺序未明确指定，导致K054返回了最大日期的记录，不符合需求。

正确解决方案 #

方法1：窗口函数（PostgreSQL推荐） #

利用ROW_NUMBER()窗口函数给每个门店内的记录按日期升序编号，取编号为1的记录：

select store_code, certificate_id, audit_ending
from (
    select 
        store_code, 
        certificate_id, 
        audit_ending,
        row_number() over (partition by store_code order by audit_ending asc) as rn
    from measure_app_scalemeasurement
) t
where rn = 1;

方法2：子查询关联 #

先通过子查询获取每个门店的最小日期，再关联原表匹配对应记录：

select m.store_code, m.certificate_id, m.audit_ending
from measure_app_scalemeasurement m
inner join (
    select store_code, min(audit_ending) as min_audit_date
    from measure_app_scalemeasurement
    group by store_code
) t 
on m.store_code = t.store_code and m.audit_ending = t.min_audit_date;

正确结果 #

注：你给出的期望结果中K054的日期为20.01.2024是错误的，该日期是K054门店的最大日期，最小日期应为14.12.2023。

内容的提问来源于stack exchange，提问作者Jonibek