Вопрос по postgresql, sql – PostgreSQL: как вернуть строки относительно найденной строки (относительные результаты)?

1

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

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

  •        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 ;
    

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

Почему вы не используетеBETWEEN? Так что ваш запрос будет что-то вродеselect * from table where dt between 2012-08-01 and 2011-10-01, Вы также можете использовать некоторые функции даты, чтобы вычислить начало и конец легче. mistapink
@mistapink: я тестировал сBETWEEN, но это не вернуло правильный набор результатов, возможно, у меня были перевернутые концы или ошибка в другом месте. Я действительно не ожидал, что у меня будет рабочий пример, когда я буду публиковать сообщения, поэтому я думаю, что я уже превзошел мои ожидания :) Суть этого заключалась в том, чтобы иметь запрос, который можно было бы выполнять регулярно. Пример может быть плохим, потому что фактические записи не только в первом месяце, и может быть больше или меньше записей в месяц. vol7ron

Ваш Ответ

3   ответа
1

Вы могли бы использовать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) или любой другой местный диалект.

@ vol7ron: я добавил версию для обложки углового корпуса и подробное объяснение, почему в противном случае она работает правильно.
lead не работает, потому что он не включает будущие даты (ему нужны обе стороны текущей даты). Кроме того, оператор WHERE, вероятно, должен использоватьdt_lead7 - Я не думаю, что псевдоним может быть использован там. vol7ron
Я должен рассмотреть этоtomorrow (Надежды) vol7ron
Вероятность того, что я переключусь на MySQL, минимальна, но я уверен, что в будущем он, вероятно, будет иметь оконные функции - по крайней мере, если бы Oracle его не купил, он мог бы это иметь. Таким образом, я бы с большей вероятностью переключился на Oracle, SQL Server или DB2. SQL Server уже является своего рода кошмаром в своем SQL, но ограничение DB2 - что-то вродеFETCH FIRST n ROWS ONLY, который сильно отличается отTOP n или жеLIMIT n vol7ron
3
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)
Я думал о том, чтобы сделать что-то подобное. Я не знаю почему, но по какой-то причине мне не нравится использоватьlimit, Возможно, потому что он имеет разные синтаксисы между RDBMS, и мне нравится оставаться в основном независимым от платформы vol7ron
Я также переключаю ваши 4 и 7 vol7ron
1

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

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

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

@ vol7ron: Этот ответ просто неправильный. Ноlead() может работать Я отправил ответ.
Извините, я не ответил на ваш комментарий по этому вопросу достаточно рано. Я хотел избежать этого из-за неправильности записей. Я знал, что мой пример будет плохим, но я надеялся найти число и относительность к записи в упорядоченном наборе результатов. В частности, я думал, чтоcume_dist(), lead(), or lag() будет то, что мне нужно. vol7ron

Похожие вопросы