Re: How to insert rows distributed evenly between referenced rows? - Mailing list pgsql-sql

<p dir="ltr">I think NTILE()  will be your friend here. <p dir="ltr"><a
href="http://www.postgresql.org/docs/9.3/static/functions-window.html">http://www.postgresql.org/docs/9.3/static/functions-window.html</a><br
/><divclass="gmail_quote">On Jan 27, 2014 10:11 AM, "Herouth Maoz" <<a
href="mailto:herouth@unicell.co.il">herouth@unicell.co.il</a>>wrote:<br type="attribution" /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> Basically, I want to do
somethinglike this.<br /><br /> I have N rows in table rawData.<br /> I have to create batches from these N rows using
tablesbatches (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.<br /><br /> Example (N=12, M=5,
meaningtake 12 rows, and create as many batches as needed with a maximum of 5 batchContent rows per batch)<br /><br />
rawData<br/> r01<br /> r02<br /> r03<br /> r04<br /> r05<br /> r06<br /> r07<br /> r08<br /> r09<br /> r10<br /> r11<br
/>r12<br /><br /> Expected result:<br /><br /> batches:<br /><br /> id         post<br /> 5001   5<br /> 5002   5<br />
5003  5<br /><br /> batchContents:<br /><br /> batch_id  datum<br /> 5001        r01<br /> 5001        r02<br /> 5001  
    r03<br /> 5001        r04<br /> 5001        r05<br /> 5002        r06<br /> 5002        r07<br /> 5002      
 r08<br/> 5002        r09<br /> 5002        r10<br /> 5003        r11<br /> 5003        r12<br /><br /> The order in
whichthe data are distributed between the batches is not important, but I need to have M data in each batch except the
last.<br/><br /> My starting point was a statement for insertion into batches. If I know what N and M are, I know how
manybatches I'll need (B=ceil(N/M)), so I thought of writing<br /><br /> INSERT INTO batches(post)<br /> SELECT 5 --
Allthe extra data, like the "post" field, is inserted as literals here<br /> FROM generate_series(1,B)<br /> RETURNING
id<br/><br /> This will give me the proper rows in batches, but I don't know how to incorporate this into an
insert/selectfrom rawData into batchContent, assuming that I don't want to keep the data programatically and do
repeatedSELECTs with OFFSET and LIMIT for each id returned. Maybe there is some elegant solution with window functions?
Canthere be any sort of join over a window or something like that?<br /><br /><br /> TIA,<br /> Herouth<br /><br /><br
/><br/> --<br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></blockquote></div>