PostgreSQL: как вернуть строки относительно найденной строки (относительные результаты)?

Прости мой пример, если он не имеет смысла. Я собираюсь попробовать с упрощенным, чтобы поощрить большее участие.

Рассмотрим таблицу, подобную следующей:

       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 records beforehand а также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 ;

Я чувствую, что должен быть лучший способ вернуть относительные записи с помощью оконной функции, но с тех пор, как я на них посмотрел, прошло некоторое время.

Ответы на вопрос(3)

Вы могли бы использоватьwindow function lead():

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

Несколько короче, ноUNION ALL Версия будет быстрее с подходящим индексом.

Это оставляетcorner case где «дата, ближайшая к сегодняшнему дню»; находится в первых 7 рядах. Вы можете дополнить исходные данные 7 строками-infinity позаботиться об этом:

SELECT d.dt_lead7 AS dt
FROM  (
    SELECT *, lead(dt, 7) OVER (ORDER BY dt) AS dt_lead7
    FROM  (
        SELECT '-infinity'::date AS dt FROM generate_series(1,7)
        UNION ALL
        SELECT dt FROM foo
        ) x
    ) d
WHERE  d.dt <= now()::date -- same as: WHERE  dt <= now()::date1
ORDER  BY d.dt_lead7 DESC  -- same as: ORDER BY dt DESC 1
LIMIT  11;

Я уточнил таблицы во втором запросе, чтобы уточнить, что происходит. Увидеть ниже.
Результат будет включатьNULL значения, если «дата, ближайшая к сегодняшнему дню»; находится в последних 7 строках базовой таблицы. Вы можете отфильтровать их с помощью дополнительного суб-выбора, если вам нужно.

1Чтобы устранить ваши сомнения по поводуoutput names противcolumn names в комментариях - учтите следующие цитаты из руководства.

Где использовать имя выходного столбца:

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.

Жирный акцент мой.WHERE dt <= now()::date ссылается на столбецd.dt, а не выходной столбец с тем же именем - тем самым работает как задумано.

Разрешение конфликтов:

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.

Смелый акцент мой снова.ORDER BY dt DESC в примере ссылается на имя выходного столбца - как и предполагалось. В любом случае, любой столбец будет сортировать одинаково. Единственная разницаcould быть сNULL значения угла корпуса. Но и это не сработало, потому что:

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

КакNULL ценности приходятafter самые большие значения, порядок идентичен в любом случае.

Или же,without LIMIT (согласно запросу в комментарии):

WITH x AS (
    SELECT *
         , row_number() OVER (ORDER BY dt)  AS rn
         , first_value(dt) OVER (ORDER BY (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 - 3 AND rn_nearest + 7
ORDER  BY dt;

Если производительность важна, я бы все равно пошел с @ ClodoaldoUNION ALL вариант. Это будет быстрее всего. SQL, независимый от базы данных, покажет вам только это. В других РСУБД пока нет оконных функций (MySQL) или других имен функций (например,first_val вместоfirst_value). Вы также можете заменитьLIMIT сTOP n (MS SQL) или любой другой местный диалект.

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)

Вы можете использовать что-то вроде этого:

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

это а такжеэтот может помочь вам.

ВАШ ОТВЕТ НА ВОПРОС