PostgreSQL：如何相对于找到的行返回行（相对结果）？ [英] PostgreSQL: How to return rows with respect to a found row (relative results)?

问题描述

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

Forgive my example if it does not make sense. I'm going to try with a simplified one to encourage more participation.

请考虑如下表格：

       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   |  

如果您查找带有<$的记录c $ c> dt 距今天最近的时间（不包括过去），最好的方法是同时还提前返回 3个记录和 7个记录？

If you look for the record with dt closest to today w/o going over, what would be the best way to also return the 3 records beforehand and 7 records after?

我决定尝试窗口功能：

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 ;

我觉得必须有更好的方法返回带有窗口函数的相对记录，但是距离我查看它们已有一段时间了。

I feel like there must be a better way to return relative records with a window function, but it's been some time since I've looked at them.

推荐答案

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)

这篇关于PostgreSQL：如何相对于找到的行返回行（相对结果）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！