Актуальная цена каждого товара на заданную дату

Эту задачу давали на реальном собеседовании: простая на вид история цен, на которой легко ошибиться и взять MAX(price) вместо последней записи.

Задача

Есть таблица товаров items — цена меняется со временем, и каждая строка это одно обновление цены с определённой даты:

item_id | name          | price | update_date
3       | Ластик        | 7     | 2020-04-01
1       | Ручка гелевая | 10    | 2020-02-01
2       | Карандаш 1HH  | 3     | 2020-01-01
1       | Ручка гелевая | 10    | 2020-03-01
3       | Ластик        | 5     | 2020-07-01
2       | Карандаш 1HH  | 2     | 2020-04-01
1       | Ручка гелевая | 5     | 2020-05-01
2       | Карандаш 1HH  | 7     | 2020-06-01

Выведи актуальное состояние товаров на 2020-06-01: по одной строке на товар с той ценой, которая действовала на эту дату.

Схема данных

CREATE TABLE items (
  item_id int,
  name text,
  price numeric,
  update_date date
);

Каждая строка — цена товара, действующая с даты update_date. Актуальная цена на нужный день — это последнее обновление с датой не позже этого дня.

Решение

SELECT DISTINCT ON (item_id) item_id, name, price, to_char(update_date, 'YYYY-MM-DD') AS update_date
FROM items
WHERE update_date <= '2020-06-01'
ORDER BY item_id, update_date DESC;

Разбор

Главная ловушка — соблазн взять MAX(price) по товару. Но нам нужна не максимальная цена, а последняя по времени — а это разные вещи: у «Ластика» самая свежая цена на 2020-06-01 равна 7, хотя позже (2020-07-01) она упадёт до 5, и эту будущую запись брать нельзя.

Решаем в два шага. Сначала WHERE update_date <= '2020-06-01' отсекает будущие обновления — работаем только с тем, что уже действовало на дату. Потом на каждый товар нужно оставить самую свежую строку. В Postgres для этого есть идиома DISTINCT ON (item_id): она берёт первую строку в каждой группе по item_id, а какая строка «первая» — задаёт ORDER BY. Поэтому порядок item_id, update_date DESC критичен: сначала группируем по товару, внутри группы ставим свежие даты вперёд — и DISTINCT ON хватает именно последнюю запись.

Универсальный вариант без Postgres-специфики — ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY update_date DESC) и фильтр rn = 1. Что вообще делает окно и почему PARTITION BY не схлопывает строки — в вопросе что такое оконные функции. А если бы у товара оказались две записи с одинаковой датой — выбор стал бы недетерминированным, и тогда в ORDER BY пришлось бы добавить второй ключ-разрешитель.

В SELECT дату дополнительно оборачиваем в to_char(update_date, 'YYYY-MM-DD') — только для читаемого вывода; WHERE и ORDER BY по-прежнему сравнивают и сортируют по исходной колонке типа date.

Ожидаемый результат

item_id | name | price | update_date
--- | --- | --- | ---
1 | Ручка гелевая | 5 | 2020-05-01
2 | Карандаш 1HH | 7 | 2020-06-01
3 | Ластик | 7 | 2020-04-01