Блог

Главная - Вопросы по программированию - Как оптимизировать запрос на основе другого запроса агрегации

Как оптимизировать запрос на основе другого запроса агрегации

#sql #postgresql

Вопрос:

Там есть столик:

 create table transfers(
ts timestamp without time zone,
departure varchar(3),
destination varchar(3),
price numeric
);

содержащий:

 ts                      departure  destination  price
______________________________________________________
2020-11-01T15:00:00Z    Chicago     Berlin      500
2020-11-01T12:00:05Z    Chicago     Berlin      400
2020-11-01T20:01:00Z    Chicago     Berlin      920
2020-11-01T20:01:00Z    London      Berlin      800
2020-11-01T22:00:00Z    London      Berlin      750

Мне нужно получить цены для каждого пункта отправления с самой высокой отметкой времени, поэтому результат должен быть:

 ts                      departure  destination  price
______________________________________________________
2020-11-01T20:01:00Z    Chicago     Berlin      920
2020-11-01T22:00:00Z    London      Berlin      750

Я сделал это с помощью запроса:

 with max_ts_per_departure AS (
        SELECT MAX(ts) AS ts, departure
        FROM transfers
        GROUP BY departure
)
        SELECT t.ts, mtpd.departure, t.price
        FROM max_ts_per_departure AS mtpd
        INNER JOIN transfers AS t
        ON mtpd.departure=t.departure AND mtpd.ts=t.ts;

Есть ли способ сократить запрос и избежать использования оператора WITH, не слишком замедляя время выполнения? Реальные данные намного больше.

Ответ №1:

Попробуйте использовать DISTINCT ON :

 SELECT DISTINCT ON (departure) *
FROM transfers
ORDER BY departure, ts DESC;

Комментарии:

1. Это возвращает: «цена вылета max_ts Чикаго 400 2020-11-01T12:00:05Z Чикаго 500 2020-11-01T15:00:00Z Чикаго 920 2020-11-01T20:01:00Z Лондон 750 2020-11-01T22:00:00Z Лондон 800 2020-11-01T20:01:00Z» Не ожидаемый результат.

2. Попробуйте обновленный ответ.

3. Это работает. Для полной бд время выполнения становится медленным, но я решил эту проблему с помощью дополнительного индекса. CREATE INDEX idx_max_ts ON transfers (ts DESC NULLS LAST, departure); Спасибо.

Метки: