postgresql - 选择按日期排序的唯一值

Question

我正在尝试解决一个有趣的问题。我有一个表，除其他数据外，还有这些列(此示例中的日期以欧洲格式显示 - dd/mm/yyyy):

n_place_id   dt_visit_date
(integer)    (date)
==========   =============
   1           10/02/2012
   3           11/03/2012
   4           11/05/2012
  13           14/06/2012
   3           04/10/2012
   3           03/11/2012
   5           05/09/2012
  13           18/08/2012

基本上，每个地方可能会被访问​​多次 - 日期可能是过去的(已完成的访问)或 future 的(计划的访问)。为简单起见，今天的访问是计划的 future 访问的一部分。

现在，我需要在此表上运行一个选择，这将从该表中提取唯一地点 ID(不带日期)，并按以下顺序排序:

1. future 的访问先于过去的访问
2. 在对同一地点进行排序时， future 的访问优先于过去的访问
3. 对于以后的访问，相同地点的排序必须以最早的日期为准
4. 对于过去的访问，最新日期必须优先于同一地点的排序。

比如上面的示例数据，我需要的结果是:

 5     (earliest future visit)
 3     (next future visit into the future)
13     (latest past visit)
 4     (previous past visit)
 1     (earlier visit in the past)

现在，我可以在 order by 子句中使用 case when 实现所需的排序，如下所示:

select
    n_place_id
from
    place_visit
order by
    (case when dt_visit_date >= now()::date then 1 else 2 end),
    (case when dt_visit_date >= now():: date then 1 else -1 end) * extract(epoch from dt_visit_date)

这类可以满足我的需要，但它确实包含重复的 ID，而我需要唯一的地点 ID。如果我尝试将 distinct 添加到 select 语句中，postgres 会提示我必须在 select 子句中使用 order by - 但是 unique 将不再有意义，因为我在里面有日期。

我觉得应该有一种方法可以在一个 select 语句中获得我需要的结果，但我不知道该怎么做。

如果无法做到这一点，那么，当然，我将不得不在代码中完成所有事情，但我更愿意将其包含在一个 SQL 语句中。

附言我不担心性能，因为我要排序的数据集并不大。应用 where 子句后，它很少会包含超过 10 条记录。

Answer 1

与 DISTINCT ON您可以使用生成的 n_place_id 轻松显示该行的其他列:

SELECT n_place_id, dt_visit_date
FROM  (
   SELECT DISTINCT ON (n_place_id) *
         ,dt_visit_date < now()::date    AS prio  -- future first
         ,@(now()::date - dt_visit_date) AS diff  -- closest first
   FROM   place_visit
   ORDER  BY n_place_id, prio, diff
   ) x
ORDER  BY prio, diff;

实际上，我根据 n_place_id 选择了 future 最早日期(包括“今天”)的行 - 或者过去的最新日期，否则。
然后根据相同的标准对生成的唯一行进行排序。

• FALSE 排序在 TRUE
之前
• "absolute value" @帮助排序“最接近的第一”
• 关于 Postgres 的更多信息 DISTINCT ON在此相关答案中。

结果:

 n_place_id | dt_visit_date
------------+--------------
 5          | 2012-09-05
 3          | 2012-10-04
 13         | 2012-08-18
 4          | 2012-05-11
 1          | 2012-02-10