Urvin
@Urvin

Как хитро отпагинировать данные в PostgreSql?

Представим, что есть таблица
create table car
(
	id serial constraint car_pk primary key,
	vendor_name varchar not null,
	model_name varchar not null,
	body_type varchar not null,
	specifications_name varchar not null,
	price int4 not null
);

И ее заполнение пусть будет таким:
SQL
INSERT INTO car(vendor_name, model_name, body_type, specifications_name, price) VALUES
('Peugeot', '408', 'Седан', 'Allure 115лс бежевый', 1144000),
('LADA', 'Vesta', 'Седан', 'Luxe морская волна', 635000),
('Ford', 'Focus', 'Хэтчбэк', 'Sync сепый', 1109000),
('Ford', 'Focus', 'Седан', 'Sync белый', 1250800),
('LADA', 'Vesta', 'Седан', 'Сlassic зеленый', 631800),
('Audi', 'A4', 'Универсал', 'Желтый', 2900000),
('Ford', 'Focus', 'Хэтчбэк', 'Special мандариноый', 1126000),
('LADA', 'Granta', 'Седан', 'Comfort серый', 520000),
('LADA', 'Vesta', 'Седан', 'Сomfort синий', 631100),
('Ford', 'Focus', 'Седан', 'Trend синий', 1235000),
('LADA', 'Vesta', 'Универсал', 'Comfort оранжевый', 679000),
('Audi', 'A4', 'Седан', 'Желтый', 2000000),
('LADA', 'Granta', 'Седан', 'Luxe Prestige зеленый', 576000),
('Peugeot', '408', 'Седан', 'Active красный', 1177000),
('Audi', 'A4', 'Седан', 'Желтый', 2000000),
('Ford', 'Focus', 'Седан', 'Special мандариноый', 1203000),
('LADA', 'Granta', 'Седан', 'Luxe серый', 531000),
('Peugeot', '408', 'Седан', 'Allure 150лс белый', 1122000),
('Audi', 'A4', 'Универсал', 'Серый', 2900000),
('LADA', 'Vesta', 'Универсал', 'Luxe белый', 680000),
('Ford', 'Focus', 'Седан', 'Special апельсиновый', 1211000),
('Ford', 'Focus', 'Хэтчбэк', 'Special апельсиновый', 1125000),
('LADA', 'Vesta', 'Универсал', 'Comfort виноградный', 630000),
('Peugeot', '408', 'Седан', 'Allure 150лс пурпурный', 1125000),
('Audi', 'A3', 'Хетчбек', 'Белый', 2000000),
('Ford', 'Focus', 'Хэтчбэк', 'Special лимонный', 1088000),
('LADA', 'Vesta', 'Универсал', 'Luxe синиый', 699000),
('Ford', 'Focus', 'Седан', 'Trend зеленый', 1230000),
('LADA', 'Vesta', 'Седан', 'Luxe вердепомовый', 634000),
('Ford', 'Focus', 'Седан', 'Sync сепый', 1260000),
('LADA', 'Granta', 'Универсал', 'Comfort малиновый', 566000),
('LADA', 'Granta', 'Седан', 'Comfort красный', 520000),
('LADA', 'Vesta', 'Седан', 'Сlassic бежевый', 631000),
('Ford', 'Focus', 'Седан', 'Special лимонный', 1201000),
('Ford', 'Focus', 'Хэтчбэк', 'Trend синий', 1065000),
('LADA', 'Vesta', 'Универсал', 'Luxe красный', 679000),
('LADA', 'Granta', 'Универсал', 'Standart белый', 520000),
('Audi', 'A4', 'Универсал', 'Черный', 3000000),
('LADA', 'Vesta', 'Седан', 'Сomfort восхитительный', 641000),
('Ford', 'Focus', 'Седан', 'Sync черный', 1250000),
('LADA', 'Granta', 'Седан', 'Standart черный', 438000),
('Audi', 'A3', 'Хетчбек', 'Желтый', 2000000),
('LADA', 'Granta', 'Универсал', 'Standart черный', 465030),
('LADA', 'Vesta', 'Седан', 'Сlassic белый', 638005),
('LADA', 'Granta', 'Универсал', 'Standart синий', 485000),
('LADA', 'Granta', 'Универсал', 'Comfort мокрый асфальт', 566000),
('Audi', 'A4', 'Универсал', 'Белый', 2900000),
('Ford', 'Focus', 'Хэтчбэк', 'Trend белый', 1027000),
('LADA', 'Granta', 'Седан', 'Standart синий', 438000),
('LADA', 'Granta', 'Универсал', 'Luxe пурпурный', 662000),
('LADA', 'Vesta', 'Универсал', 'Comfort желтый', 679010),
('Ford', 'Focus', 'Седан', 'Trend белый', 1230000),
('Audi', 'A3', 'Хетчбек', 'Черный', 2000000),
('LADA', 'Granta', 'Универсал', 'Comfort голубой', 566000),
('LADA', 'Granta', 'Универсал', 'Luxe коралловый', 662080),
('LADA', 'Granta', 'Универсал', 'Luxe аквамарин', 662100),
('LADA', 'Vesta', 'Седан', 'Сomfort абрикосовый', 631000),
('LADA', 'Vesta', 'Седан', 'Luxe синий', 636000),
('Ford', 'Focus', 'Хэтчбэк', 'Sync черный', 1082000),
('Ford', 'Focus', 'Хэтчбэк', 'Sync белый', 1092000)
;


Я должен отсортировать таблицу так, чтобы:
- Первыми шли автомобили вендора, у которого машины стоят меньше
- Внутри вендора - сортировка по минимальной цене модели
- Внутри модели - по минимальной цене типа кузова
- далее по цене и наименованию спецификации:

SELECT
  *,
  MIN(price) OVER win_vendor min_price_vendor,
  MIN(price) OVER win_model min_price_model,
  MIN(price) OVER win_body min_price_body
FROM
  car
WINDOW
  win_vendor AS (PARTITION BY vendor_name),
  win_model AS (PARTITION BY vendor_name, model_name),
  win_body AS (PARTITION BY vendor_name, model_name, body_type)
ORDER BY
  min_price_vendor,
  min_price_model,
  min_price_body,
  price,
  specifications_name


А теперь пагинация.
Отсекать страницы мне нужно не простым Limit/Offset, а так, чтобы начало или конец страницы непременно находились на начале или окончании списка автомобилей вендора-модели-типа_кузова. При этом на странице должно находиться, если возможно, не менее N записей (или не более, если проще).

Пусть, N=10

5c483c9cf2de4129268059.png

Получаются страницы по 15, 15, 17, 13 записей. Я ожидаю формирование нового столбца page_number, чтобы я в запросах писал WHERE page_number = K для вытаскивания порции данных с К-той страницы.

Помогите, пожалуйста, посчитать номер страницы в SQL
  • Вопрос задан
  • 134 просмотра
Решения вопроса 1
Urvin
@Urvin Автор вопроса
Сам спросил - сам ответил. Я нашел решение, но мне оно совсем не нравится, может, у %username% найдется чего поинтереснее.

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

Однако, обнаружилось, что postgres все же может позволить хранить какие-то данные, причем даже не вылезая за пределы транзакции. "Статичные переменные" можно реализовать с помощью функций current_setting / set_config. Невероятный костыль, конечно, но что есть.

Итак, с помощью "статики" создаем функцию, которая по понятной логике щелкает страницы сортированного списка по изменению строкового ключа:
CREATE OR REPLACE FUNCTION grouped_pagination_page(current_key VARCHAR, per_page INT4) RETURNS INT4 AS $$
  DECLARE
    last_key VARCHAR;
    last_row_count INT4;
    last_page INT4;
  BEGIN
    SELECT COALESCE(current_setting('GPP.last_key', TRUE), '') INTO last_key;
    SELECT CAST(COALESCE(NULLIF(current_setting('GPP.last_row_count', TRUE),''),'0') AS INT) INTO last_row_count;
    SELECT CAST(COALESCE(NULLIF(current_setting('GPP.last_page', TRUE),''),'1') AS INT) INTO last_page;

    IF current_key <> last_key THEN
      PERFORM set_config('GPP.last_key', current_key, TRUE);
      IF last_row_count >= per_page THEN
        last_page = last_page + 1;
        last_row_count = 0;

        PERFORM set_config('GPP.last_page', last_page::VARCHAR, TRUE);
      END IF;
    END IF;

    last_row_count = last_row_count + 1;
    PERFORM set_config('GPP.last_row_count', last_row_count::VARCHAR, TRUE);

    RETURN last_page;
  END;
$$ LANGUAGE 'plpgsql';


И вот, собственно, использование ее в запросе (у меня ключ группы - вендор-модель-кузов):
SELECT *,
  MIN(price) OVER win_vendor min_price_vendor,
  MIN(price) OVER win_model min_price_model,
  MIN(price) OVER win_body min_price_body,
  grouped_pagination_page((vendor_name || model_name || body_type)::VARCHAR, 10) page_number
FROM
  car
WINDOW
  win_vendor AS (PARTITION BY vendor_name),
  win_model AS (PARTITION BY vendor_name, model_name),
  win_body AS (PARTITION BY vendor_name, model_name, body_type)
ORDER BY min_price_vendor,
  min_price_model,
  min_price_body,
  price,
  specifications_name
Ответ написан
Комментировать
Пригласить эксперта
Ответы на вопрос 1
@d-stream
Готовые решения - не подаю, но...
ROWS | RANGE pg умеет?

Ну или накрайняк row_number в селекте, который ручками превращать в sub_page_number в рамках окна (для ситуации когда вест на 20 страниц ) по своему критерию n-m строк на страницу
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы