Выберите только строки с самой последней датой в postgres

Question

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

Date of sale | house number | street | price |uniqueref
-------------|--------------|--------|-------|----------
15-04-1990   |1             |castle  |100000-| 1xzytt   
15-04-1995   |1             |castle  |200000-| 2jhgkj
15-04-2005   |1             |castle  |800000-| 3sdfsdf
15-04-1995   |2             |castle  |200000-| 2jhgkj
15-04-2005   |2             |castle  |800000-| 3sdfsdf

То, что я работаю, заключается в следующем

Создание VIEW как (v_orderedhouses) ORDER BY номер дома, улица с указанием даты на DESCтак что последняя дата сначала возвращается.

Я тогда кормить это в другой VIEW (v_latesthouses) используя DISTINCT ON (номер дома, улица). Что дает мне;

Date of sale | house number | street | price |uniqueref
-------------|--------------|--------|-------|----------
15-04-2005   |1             |castle  |800000-| 3sdfsdf
15-04-2005   |2             |castle  |800000-| 3sdfsdf

Это работает, но кажется, что должно быть более элегантное решение. Могу ли я добраться до отфильтрованного представления за один шаг?

Answer 1

Вы можете использовать оконную функцию row_number за это

select  * from (
   select your_table.*, row_number() over(partition by house_number order by Date_of_sale desc) as rn from your_table
) tt
where rn = 1

Answer 2

Вам не нужно создавать кучу просмотров, просто:

select distinct on(street, house_number)
  *
from your_table
order by
  street, house_number, -- those fields should be in the "order by" clause because it is in the "distinct on" expression
  date_of_sale desc;

Чтобы сделать этот запрос быстрее, вы можете создать индекс в соответствии с order by:

create index index_name on your_table(street, house_number, date_of_sale desc);

Не забудь analyse Ваши таблицы регулярно (в зависимости от скорости роста):

analyse your_table;

Answer 3

Это то, что я использую, и это работает быстро (это общее решение, насколько я мог протестировать, каждое программное обеспечение базы данных может сделать это):

SELECT t1.date_of_sale, t1.house_number
FROM table t1
LEFT JOIN table t2 ON (t2.house_number = t1.house_number AND t2.date_of_sale>t1.date_of_sale)
WHERE t2.pk IS NULL
GROUP BY t1.date_of_sale, t1.house_number