将Dense_Rank Oracle转换为PostgresSQL

Question

我有一个select语句，其中包含一个用Oracle编写的dense_rank函数，但是我在弄清楚如何使它与postgressql (v11.x)兼容时遇到了麻烦。

SELECT facility_id_fk, max(inspection_date) as last_inspection,
               max(inspection_type) keep (dense_rank first order by inspection_date desc) as inspection_type
FROM facility_inspections
GROUP BY facility_id_fk

此查询给出了上次检查以及上次检查的类型：

-------------------------------------------------
facility id | inspection date | inspection type 
-------------------------------------------------
93              04/28/2020        FULL
94              04/28/2020        LIMITED
-------------------------------------------------

我尝试了以下方法，但它省略了我需要的检查类型描述，并且仍然提供了副本，并且查看了rank列，它看起来像是表中所有检查的排名，而不是针对特定设施的排名

SELECT facility_id_fk, max(inspection_date) as last_inspection,
      dense_rank () OVER (
           PARTITION BY inspection_type
           ORDER BY inspection_date DESC
           ) rank_order
FROM facility_inspections
GROUP BY facility_id_fk, inspection_date, inspection_type

Answer 1

在Postgres中，您可以使用方便的扩展distinct on来实现这一点

select distinct on (facility_id_fk) *
from facility_inspections
order by facility_id_fk, inspection_date desc

如果要使用窗口函数来执行此操作，则可以将查询表述为：

select *
from (
    select f.*, row_number() over(partition by facility_id_fk order by inspection_date desc) rn
    from facility_inspections f
) f
where rn = 1