How to insert rows distributed evenly between referenced rows?

Поиск
Список
Период
Сортировка
Искать
От
Herouth Maoz
Тема
How to insert rows distributed evenly between referenced rows?
Дата
Msg-id
3CDB43FA-A905-475C-8764-8952BE2C3F81@unicell.co.il
Список
Дерево обсуждения
How to insert rows distributed evenly between referenced rows? Herouth Maoz <herouth@unicell.co.il>
Re: How to insert rows distributed evenly between referenced rows? Erik Darling <edarling80@gmail.com>
Re: How to insert rows distributed evenly between referenced rows? Herouth Maoz <herouth@unicell.co.il>
Re: How to insert rows distributed evenly between referenced rows? Herouth Maoz <herouth@unicell.co.il>
Basically, I want to do something like this.

I have N rows in table rawData.
I have to create batches from these N rows using tables batches (which has a serial id column and some additional data columns) and batchContents (which references id in batches), where there will be M rows in batchContent for each row in table batches.

Example (N=12, M=5, meaning take 12 rows, and create as many batches as needed with a maximum of 5 batchContent rows per batch)

rawData
r01
r02
r03
r04
r05
r06
r07
r08
r09
r10
r11
r12

Expected result:

batches:

id         post
5001   5
5002   5
5003   5

batchContents:

batch_id  datum
5001        r01
5001        r02
5001        r03
5001        r04
5001        r05
5002        r06
5002        r07
5002        r08
5002        r09
5002        r10
5003        r11
5003        r12

The order in which the data are distributed between the batches is not important, but I need to have M data in each batch except the last.

My starting point was a statement for insertion into batches. If I know what N and M are, I know how many batches I'll need (B=ceil(N/M)), so I thought of writing

INSERT INTO batches(post)
SELECT 5 -- All the extra data, like the "post" field, is inserted as literals here
FROM generate_series(1,B)
RETURNING id

This will give me the proper rows in batches, but I don't know how to incorporate this into an insert/select from rawData into batchContent, assuming that I don't want to keep the data programatically and do repeated SELECTs with OFFSET and LIMIT for each id returned. Maybe there is some elegant solution with window functions? Can there be any sort of join over a window or something like that?


TIA,
Herouth



В списке pgsql-sql по дате отправления
От: Andreas Joseph Krogh
Дата:
Сообщение: Re: Update ordered
От: Erik Darling
Дата:
FAQ