PostgreSQL：如何相对于找到的行返回行（相对结果）？

Question

如果这没有道理，请原谅我的示例。我将尝试使用一种简化的方法来鼓励更多的参与。

考虑如下表：

• dt | mnth | foo
  --------------+------------+--------
  2012-12-01 | December |
  …
  2012-08-01 | August |
  2012-07-01 | July |
  2012-06-01 | June |
  2012-05-01 | May |
  2012-04-01 | April |
  2012-03-01 | March |
  …
  1997-01-01 | January |
  
  

如果您要查找dt最接近今天且没有结束记录的记录，那么最好是还同时返回 3条记录 和 7条之后的 记录 的最佳方法是什么？

我决定尝试开窗功能：

• WITH dates AS (
  select row_number() over (order by dt desc)
  , dt
  , dt - now()::date as dt_diff
  from foo
  )
  , closest_date AS (
  select * from dates
  where dt_diff = ( select max(dt_diff) from dates where dt_diff <= 0 )
  )

  SELECT *
  FROM dates
  WHERE row_number - (select row_number from closest_date) >= -3
  AND row_number - (select row_number from closest_date) <= 7 ;

我觉得必须有一个更好的方法来通过窗口函数返回相对记录，但是自从我查看它们以来已经有一段时间了。

Answer 1

create table foo (dt date);
insert into foo values
('2012-12-01'),
('2012-08-01'),
('2012-07-01'),
('2012-06-01'),
('2012-05-01'),
('2012-04-01'),
('2012-03-01'),
('2012-02-01'),
('2012-01-01'),
('1997-01-01'),
('2012-09-01'),
('2012-10-01'),
('2012-11-01'),
('2013-01-01')
;

select dt
from (
(
    select dt
    from foo
    where dt <= current_date
    order by dt desc
    limit 4
)
union all
(
    select dt
    from foo
    where dt > current_date
    order by dt
    limit 7
)) s
order by dt
;
     dt     
------------
 2012-03-01
 2012-04-01
 2012-05-01
 2012-06-01
 2012-07-01
 2012-08-01
 2012-09-01
 2012-10-01
 2012-11-01
 2012-12-01
 2013-01-01
(11 rows)