sql - PostgreSQL：基于 sum(field) < x 条件的页面大小分页

问题描述

在我的项目中，我有一个包含 ~8M 记录的数据库表。每条记录都包含一个整数width：

create table blocks(id bigserial, width int);
insert into blocks(width) values (1),(2),(6),(3),(3),(3),(2),(5),(4),(2);

前端部分在一个非常长的网页中显示所有这些块，该网页在滚动时动态加载。上面的宽度将适合其自身宽度等于 10 的视图，如下所示：

0: 1, 2, 6
1: 3, 3, 3
2: 2, 5
3: 4, 2

单行上元素的宽度之和必须小于或等于行宽。所以，现在我有一个应用程序端查询的行偏移表列表，例如line width == 10：

0: 0
1: 3
2: 6
3: 8

我想在光标打开时创建一个临时表，但不知道如何计算和构建它。它必须使用线宽参数并且可能应该使用滚动总和，但我不喜欢编写 sql 语句。

UPD ：我在dba.stackexchange.com上发现了完全相同的问题，但那里的答案不正确，我在下面留下了评论。

标签： sqlpostgresql

最基本的方法是使用递归查询：

with recursive row_selector(id, width_collect, row_number, counter) AS (
  select id, width, 0, 0
    from blocks
    where id = 1
  UNION
    select b.id,
    case when row_selector.width_collect + b.width > 10 
      then b.width 
      else 
        row_selector.width_collect + b.width 
      end, 
    case when row_selector.width_collect + b.width > 10 
      then row_selector.row_number + 1 
      else 
        row_selector.row_number
      end,
    row_selector.counter + 1
    from blocks b
    JOIN row_selector on row_selector.id + 1 = b.id
)
select row_number, min(counter)
FROM row_selector
group by row_number
order by 1
;
 row | offset
-----+--------
   0 |      0
   1 |      3
   2 |      6
   3 |      8

基本上，我们遍历块并在每次超过 10 时递增 row_number。我们还使用计数器来计算偏移量。然后我们可以获取最小计数器并按行分组以获得每行的偏移量。

编辑：上面概述的方法有效，但正如评论正确指出的那样，对于任何可观数量的行来说，它都非常慢。一种更快的方法是创建自定义聚合函数。

create type row_offsets_type AS (counter int, width_cum int, offsets int[]);

create function row_offset_final_func(offsets row_offsets_type)
  RETURNS int[]
  IMMUTABLE
  AS $$
SELECT $1.offsets;
$$
LANGUAGE SQL;

create function row_offsets_func(offsets row_offsets_type, width int, row_width int)
    RETURNS row_offsets_type
    IMMUTABLE
    AS $$
      select ROW(
        offsets.counter + 1,
        CASE WHEN offsets.width_cum + width > row_width THEN width ELSE  offsets.width_cum + width END,
        CASE WHEN offsets.width_cum + width > row_width THEN array_append(offsets.offsets, offsets.counter) ELSE offsets.offsets END
      )::row_offsets_type;
    $$
    LANGUAGE SQL;

create aggregate row_offsets(width int, row_width int)
(
  SFUNC = row_offsets_func(row_offsets_type, int, int),
  STYPE = row_offsets_type,
  FINALFUNC = row_offset_final_func,
  INITCOND = '(0, 0, {0})'

);

WITH offsets AS 
  (select row_offsets(width, 10 ORDER BY id) FROM blocks)
SELECT nr - 1 as row_num,
offset_num
FROM offsets, unnest(row_offsets) with ordinality as a(offset_num, nr);
 row_num | offset_num
---------+------------
       0 |          0
       1 |          3
       2 |          6
       3 |          8

它仍然不快。在未调整的 docker 容器中，自定义聚合方法需要 20 秒才能处理 100k 行。在运行了几分钟后，我放弃了递归查询方法。

sql - PostgreSQL：基于 sum(field) < x 条件的页面大小分页

问题描述

解决方案

推荐阅读