本文介绍了PostgreSQL:如何相对于找到的行返回行(相对结果)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请原谅我的示例,如果这没有道理。我将尝试一种简化的方法,以鼓励更多的参与。
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:如何相对于找到的行返回行(相对结果)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!