PostgreSQL:按记录数分组数据的有效方法

Question

示例

CREATE TABLE transactions (
  id    SERIAL PRIMARY KEY,
  value NUMERIC NOT NULL
);

CREATE TABLE batches (
  id          SERIAL PRIMARY KEY,
  total_value NUMERIC NOT NULL
);

CREATE TABLE transaction_batches (
  id             SERIAL PRIMARY KEY,
  batch_id       INT NOT NULL REFERENCES batches (id) ON DELETE CASCADE ON UPDATE CASCADE,
  transaction_id INT NOT NULL REFERENCES transactions (id) ON DELETE CASCADE ON UPDATE CASCADE
);

• 在transaction_batches表中，事务应分组为N个事务
• 用户可以随时删除或创建事务。
• 如果用户已更改事务，则必须重新安排事务批处理(以确保20个事务的批处理)。

目标

根据用户对每个事务的更改，有效地将事务分组为N个事务

问题

你能提出一个解决方案来实现这个目标吗？

P.S.您可以建议另一个表结构

Answer 1

我只想这样做：

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  value NUMERIC NOT NULL,
  batch_num INT NOT NULL
);

将batch_num设置为：

SELECT floor((row_number() over (order by id) - 1) / N)

你可以用扳机做这件事。或者，您可以在查询表时使用视图来计算此值。

Answer 2

CREATE TABLE transactions (
  id    SERIAL PRIMARY KEY,
  value NUMERIC NOT NULL,
  batch_id INT NOT NULL REFERENCES batches (id)
);

CREATE TABLE batches (
  id          SERIAL PRIMARY KEY,
  num_of_transactions INT,
  total_value NUMERIC NOT NULL
);

当用户更改事务时，我们将其从批处理中删除，如下所示：

1. 将其batch_id设置为空
2. 减少批处理中的num_of_transactions

每当我们想要重新排列批次时：

1. 查找num_of_transactions < N的所有批次。
2. 对于每个具有batch_id=NULL的事务，将其添加到下一批有空缺的批处理中，更新批处理的num_of_transactions以及事务中的batch_id。