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

小编典典

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

sql

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

考虑如下表：

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 ;

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

阅读 219

2021-04-14

共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)

2021-04-14