PARTITION BY with DISTINCT

Question

我有以下数据。我想在ID上分区，并且只返回基于Date描述的唯一OrgIDs。

对于ID 14，根据Date在输出中选择Encompass和Premier。对于ID 57，选择Encompass、Example和Premier是因为Example属于独特的OrgID类别。

ID  Num  Dx   Code   Describe   OrgID   Nm1     Name        Type         Date
14  242  438  26994  Fractures  4534    2332    ENCOMPASS   AGENCY       2020-01-01
14  242  438  26994  Fractures  4533    2332    Premier     Hospital     2020-02-01
14  242  438  26994  Fractures  4533    2332    Premier     Hospital     2019-08-08
14  242  438  26994  Fractures  4534    2332    ENCOMPASS   AGENCY       2019-07-09
14  242  438  26994  Fractures  4534    2332    ENCOMPASS   AGENCY       2019-08-07
57  242  438  22699  Nervous    4533    2332    Premier     Hospital     2018-01-12
57  242  438  22699  Nervous    4534    2332    ENCOMPASS   AGENCY       2020-01-09
57  242  438  22699  Nervous    4533    2332    Premier     Hospital     2020-01-01
57  242  438  22699  Nervous    4535    2332    Example     Nurse        2019-11-11
57  242  438  22699  Nervous    4534    2332    ENCOMPASS   AGENCY       2019-06-30

预期输出-

ID  Num  Dx   Code   Describe   OrgID   Nm1     Name        Type         Date
14  242  438  26994  Fractures  4534    2332    ENCOMPASS   AGENCY       2020-01-01
14  242  438  26994  Fractures  4533    2332    Premier     Hospital     2020-02-01
57  242  438  22699  Nervous    4533    2332    Premier     Hospital     2020-01-01
57  242  438  22699  Nervous    4534    2332    ENCOMPASS   AGENCY       2020-01-09
57  242  438  22699  Nervous    4535    2332    Example     Nurse        2019-11-11

Answer 1

似乎您可以使用row_number()来获取每个id和orgid对的最新版本。

SELECT x.id,
       x.num,
       ...
       x.date
       FROM (SELECT t.id,
                    t.num,
                    ...
                    t.date,
                    row_number() OVER (PARTITION BY t.id,
                                                    t.orgid
                                       ORDER BY t.date DESC) rn
                    FROM elbat t) x
       WHERE x.rn = 1;

Answer 2

在Postgres中，我推荐DISTINCT ON

select distinct on (id, orgid) t.*
from t
order by id, orgid, date desc;

这通常比使用窗口函数或子查询的相应查询快。并在(id, orgid, date desc)上建立索引，速度相当快。