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. Consider a table like the following: dt | mn

Solution 1:

createtable foo (dt date);
insertinto 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_dateorderby dt desc
    limit 4
)
unionall
(
    select dt
    from foo
    where dt >current_dateorderby dt
    limit 7
)) s
orderby dt
;
     dt     
------------2012-03-012012-04-012012-05-012012-06-012012-07-012012-08-012012-09-012012-10-012012-11-012012-12-012013-01-01
(11rows)

Solution 2:

You could use the window function lead():

SELECT dt_lead7 AS dt
FROM  (
    SELECT *, lead(dt, 7) OVER (ORDERBY dt) AS dt_lead7
    FROM   foo
    ) d
WHERE  dt <= now()::dateORDERBY dt DESC
LIMIT  11;

Somewhat shorter, but the UNION ALL version will be faster with a suitable index.

That leaves a corner case where "date closest to today" is within the first 7 rows. You can pad the original data with 7 rows of -infinity to take care of this:

SELECT d.dt_lead7 AS dt
FROM  (
    SELECT*, lead(dt, 7) OVER (ORDERBY dt) AS dt_lead7
    FROM  (
        SELECT'-infinity'::dateAS dt FROM generate_series(1,7)
        UNIONALLSELECT dt FROM foo
        ) x
    ) d
WHERE  d.dt &lt= now()::date-- same as: WHERE  dt &lt= now()::date1ORDERBY d.dt_lead7 DESC-- same as: ORDER BY dt DESC 1
LIMIT  11;

I table-qualified the columns in the second query to clarify what happens. See below. The result will include NULL values if the "date closest to today" is within the last 7 rows of the base table. You can filter those with an additional sub-select if you need to.

---

To address your doubts about output names versus column names in the comments - consider the following quotes from the manual.

Where to use an output column's name:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

Bold emphasis mine. WHERE dt <= now()::date references the column d.dt, not the the output column of the same name - thereby working as intended.

Resolving conflicts:

If an ORDER BY expression is a simple name that matches both an output column name and an input column name, ORDER BY will interpret it as the output column name. This is the opposite of the choice that GROUP BY will make in the same situation. This inconsistency is made to be compatible with the SQL standard.

Bold emphasis mine again. ORDER BY dt DESC in the example references the output column's name - as intended. Anyway, either columns would sort the same. The only difference could be with the NULL values of the corner case. But that falls flat, too, because:

the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified

As the NULL values come after the biggest values, the order is identical either way.

---

Or, without LIMIT (as per request in comment):

WITH x AS (
    SELECT *
         , row_number() OVER (ORDERBY dt)  AS rn
         , first_value(dt) OVER (ORDERBY (dt > '2011-11-02')
                                         , dt DESC) AS dt_nearest
    FROM   foo
    )
, y AS (
    SELECT rn AS rn_nearest
    FROM   x
    WHERE  dt = dt_nearest
    )
SELECT dt
FROM   x, y
WHERE  rn BETWEEN rn_nearest - 3AND rn_nearest + 7ORDERBY dt;

If performance is important, I would still go with @Clodoaldo's UNION ALL variant. It will be fastest. Database agnostic SQL will only get you so far. Other RDBMS do not have window functions at all, yet (MySQL), or different function names (like first_val instead of first_value). You might just as well replace LIMIT with TOP n (MS SQL) or whatever the local dialect.

Solution 3:

You could use something like that:

select*from foo 
where dt between now()-interval'7 months'and now()+interval'3 months'

This and this may help you.