PostgreSQL - 按行组分页

Question

PostgreSQL - 按行组分页

3

我有一张汽车描述表：

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
);

下一个数据填充：

    INSERT INTO car(vendor_name, model_name, body_type, specifications_name, price) VALUES
('Peugeot', '408', 'Sedan', 'Allure 115hp brown', 1144000),
('LADA', 'Vesta', 'Sedan', 'Luxe seawave', 635000),
('Ford', 'Focus', 'Hatchback', 'Sync gray', 1109000),
('Ford', 'Focus', 'Sedan', 'Sync white', 1250800),
('LADA', 'Vesta', 'Sedan', 'Сlassic green', 631800),
('Audi', 'A4', 'Wagon', 'yellow', 2900000),
('Ford', 'Focus', 'Hatchback', 'Special tangerine', 1126000),
('LADA', 'Granta', 'Sedan', 'Comfort gray', 520000),
('LADA', 'Vesta', 'Sedan', 'Сomfort blue', 631100),
('Ford', 'Focus', 'Sedan', 'Trend blue', 1235000),
('LADA', 'Vesta', 'Wagon', 'Comfort orange', 679000),
('Audi', 'A4', 'Sedan', 'yellow', 2000000),
('LADA', 'Granta', 'Sedan', 'Luxe Prestige green', 576000),
('Peugeot', '408', 'Sedan', 'Active red', 1177000),
('Audi', 'A4', 'Sedan', 'yellow', 2000000),
('Ford', 'Focus', 'Sedan', 'Special tangerine', 1203000),
('LADA', 'Granta', 'Sedan', 'Luxe gray', 531000),
('Peugeot', '408', 'Sedan', 'Allure 150hp white', 1122000),
('Audi', 'A4', 'Wagon', 'gray', 2900000),
('LADA', 'Vesta', 'Wagon', 'Luxe white', 680000),
('Ford', 'Focus', 'Sedan', 'Special orange', 1211000),
('Ford', 'Focus', 'Hatchback', 'Special orange', 1125000),
('LADA', 'Vesta', 'Wagon', 'Comfort plum', 630000),
('Peugeot', '408', 'Sedan', 'Allure 150hp purple', 1125000),
('Audi', 'A3', 'HatchBack', 'white', 2000000),
('Ford', 'Focus', 'Hatchback', 'Special lemon', 1088000),
('LADA', 'Vesta', 'Wagon', 'Luxe blue', 699000),
('Ford', 'Focus', 'Sedan', 'Trend green', 1230000),
('LADA', 'Vesta', 'Sedan', 'Luxe dark green', 634000),
('Ford', 'Focus', 'Sedan', 'Sync gray', 1260000),
('LADA', 'Granta', 'Wagon', 'Comfort magenta', 566000),
('LADA', 'Granta', 'Sedan', 'Comfort red', 520000),
('LADA', 'Vesta', 'Sedan', 'Сlassic brown', 631000),
('Ford', 'Focus', 'Sedan', 'Special lemon', 1201000),
('Ford', 'Focus', 'Hatchback', 'Trend blue', 1065000),
('LADA', 'Vesta', 'Wagon', 'Luxe red', 679000),
('LADA', 'Granta', 'Wagon', 'Standart white', 520000),
('Audi', 'A4', 'Wagon', 'black', 3000000),
('LADA', 'Vesta', 'Sedan', 'Сomfort impressive', 641000),
('Ford', 'Focus', 'Sedan', 'Sync black', 1250000),
('LADA', 'Granta', 'Sedan', 'Standart black', 438000),
('Audi', 'A3', 'HatchBack', 'yellow', 2000000),
('LADA', 'Granta', 'Wagon', 'Standart black', 465030),
('LADA', 'Vesta', 'Sedan', 'Сlassic white', 638005),
('LADA', 'Granta', 'Wagon', 'Standart blue', 485000),
('LADA', 'Granta', 'Wagon', 'Comfort asphalt', 566000),
('Audi', 'A4', 'Wagon', 'white', 2900000),
('Ford', 'Focus', 'Hatchback', 'Trend white', 1027000),
('LADA', 'Granta', 'Sedan', 'Standart blue', 438000),
('LADA', 'Granta', 'Wagon', 'Luxe purple', 662000),
('LADA', 'Vesta', 'Wagon', 'Comfort yellow', 679010),
('Ford', 'Focus', 'Sedan', 'Trend white', 1230000),
('Audi', 'A3', 'HatchBack', 'black', 2000000),
('LADA', 'Granta', 'Wagon', 'Comfort cyan', 566000),
('LADA', 'Granta', 'Wagon', 'Luxe brown', 662080),
('LADA', 'Granta', 'Wagon', 'Luxe like a boss', 662100),
('LADA', 'Vesta', 'Sedan', 'Сomfort navy', 631000),
('LADA', 'Vesta', 'Sedan', 'Luxe blue', 636000),
('Ford', 'Focus', 'Hatchback', 'Sync black', 1082000),
('Ford', 'Focus', 'Hatchback', 'Sync white', 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行大小的页面。

请告诉我如何为这种情况形成页码字段。

谢谢！

- Urvin

2个回答

1

主要问题在于保存页面迭代器的状态。可能最好的解决方案是自定义窗口函数，但我找不到编写它的示例。

我发现PostgreSql允许保存“静态变量”。我们可以使用current_setting / set_config函数进行保存。此外，set_config允许仅将值保存在活动事务中，这已经足够好了。

因此，我编写了一个使用这些“静态变量”的函数，可用于具有字符串分组键的排序列表。在我的情况下，该键是vendor-model-body。

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';

所以，这是我的一个查询，它带有一个page_number字段，并根据页面数在变量行上运行：

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

它返回预期的每页15、15、17、13行;

这不是一种优雅的解决方案，但有效。

- Urvin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- S-Man · Accepted Answer

我已经在这里做了非常类似的事情：使用每页限制对分组查询结果进行分页

正如我在那里所述，我没有找到单个查询的解决方案。问题在于您的页面可能会产生非常动态的行数。因此，每个页面内容几乎取决于之前的页面。因此，您无法在一个查询中找到一个简单的解决方案，该查询引用其自身几行之前的结果。

因此，您需要一个小函数来创建您的结果。我编写了一个函数，它接受参数“每页最小行数”和“预期页面ID”（我以上面的SO问题为基础编写了该函数 - 因此两个结果非常相似）：

演示：db<>fiddle

CREATE OR REPLACE FUNCTION get_category_for_page(_min_rows int, _page_id int) RETURNS int[] AS $$
DECLARE
    _remainder int := _min_rows;
    _page_counter int := 1;
    _categories int[] = '{}';
    _temprow record;
BEGIN
    FOR _temprow IN

        SELECT                                                    -- 1 
            min_price_vendor,
            min_price_model,
            min_price_body, 
            COUNT(*)
        FROM (
            -- <your query>
        ) s
        GROUP BY
            min_price_vendor,
            min_price_model,
            min_price_body
        ORDER BY
            min_price_vendor,
            min_price_model,
            min_price_body

    LOOP
        IF (_page_counter = _page_id) THEN                        -- 2
            _categories := _categories || _temprow.min_price_body;
        END IF;

        IF (_remainder - _temprow.count < 0) THEN                 -- 3
            _page_counter := _page_counter + 1;
            _remainder := _max_rows;
        ELSE 
            _remainder := _remainder - _temprow.count;            -- 4
        END IF;

        IF (_page_counter > _page_id) THEN                        -- 5
            EXIT;
        END IF;

    END LOOP;

    RETURN _categories;
END;
$$ LANGUAGE plpgsql;

解释:

此查询计算查询中每个类别的行数。结果将在LOOP内进行迭代。
如果_page_counter等于感兴趣的_page_id，则当前类别将添加到输出中。这可能会发生多次。
_remainder存储已适合当前页面的行数值。如果当前类别的行数超过余数，则生成新页面（增加_page_counter）并重置余数。
否则，余数将减少当前类别的行数。
如果_page_counter大于感兴趣的_page_id，则不需要进行进一步的计算。

现在您可以按以下方式调用函数：

SELECT get_category_for_page(10, 2);

所以最终你的查询将如下所示：

SELECT 
    *
FROM -- <your query>
WHERE 
    min_price_body= ANY(get_category_for_page(10, 2))

免责声明

我相信在某些特殊情况下应该进行测试（并在测试失败时增加函数），但总的来说，这个想法应该是可行的。