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

Question

[Urvin]

Представим, что есть таблица

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



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

Помогите, пожалуйста, посчитать номер страницы в SQL

Answer 1

[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

Answer 2

[d-stream]

ROWS | RANGE pg умеет?

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

Comments

[Urvin]

RANGE | ROWS, судя по всему, имеет
Но как это использовать?

[d-stream]

Urvin, то есть ими внутри уже "нарезанных" групп можно получить порядковый номер позиции внутри группы. (я же правильно понимаю, что в рамках одной страницы никогда не должны смешиваться разные группы? ).

соответственно получаем порядковый номер, если поделим его на некий max_row_per_page - получим в целой части - порядковый номер страницы в группе.

вот как-то так

[Urvin]

я же правильно понимаю, что в рамках одной страницы никогда не должны смешиваться разные группы?

Да, именно.

Вот только деление позиции в группе даст не то ожидаемое поведение %|

результат

SELECT
  id, vendor_name, model_name, body_type, specifications_name, price,
  row_number() OVER (PARTITION BY vendor_name, model_name, body_type ORDER BY min_price_vendor, min_price_model, min_price_body, price, specifications_name) group_pos,
  row_number() OVER (PARTITION BY vendor_name, model_name, body_type ORDER BY min_price_vendor, min_price_model, min_price_body, price, specifications_name) / 10 page_number
FROM
  (
    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
  ) subquery
ORDER BY min_price_vendor,
  min_price_model,
  min_price_body,
  price,
  specifications_name

[d-stream]

Urvin, осталось синтезировать page_number как совокупность целого от деления и еще какого-нибудь группирующего признака (min_price_vendor, min_price_model, min_price_body)