agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Samed YILDIRIM <samed@reddoc.net>
To: Torsten Grust <torsten.grust@gmail.com>
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Group by a range of values
Date: Sat, 08 Aug 2020 14:29:35 +0300
Message-ID: <615631596886135@mail.yandex.com.tr> (raw)
In-Reply-To: <CAGqkgpgDH7O_AqtPcY91y2TrPaEF4oe-jFwuqXeuFCedgSAUXw@mail.gmail.com>
References: <CAOwYNKYcQR_Fzx0=L+5LBh6zqDXQuA3_a+ggAm5RZ3G6zHNbOQ@mail.gmail.com>
<CAGqkgpgDH7O_AqtPcY91y2TrPaEF4oe-jFwuqXeuFCedgSAUXw@mail.gmail.com>
<div><div>Hi,</div><div> </div><div>Another (and less optimised :)) alternative;</div><div> </div><div><div>pgsql-sql=# select m,dense_rank() over (order by (m-1)/3) from generate_series(1,10) m;</div><div>m | dense_rank</div><div>----+------------</div><div>1 | 1</div><div>2 | 1</div><div>3 | 1</div><div>4 | 2</div><div>5 | 2</div><div>6 | 2</div><div>7 | 3</div><div>8 | 3</div><div>9 | 3</div><div>10 | 4</div><div>(10 rows)</div><div> </div></div></div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>01.08.2020, 15:35, "Torsten Grust" <torsten.grust@gmail.com>:</div><blockquote><div><div style="font-family:'arial' , sans-serif"><div style="font-family:'arial' , sans-serif">Hi,</div><div style="font-family:'arial' , sans-serif"> </div><div style="font-family:'arial' , sans-serif">maybe this does the job already (/ is integer division):</div><div style="font-family:'arial' , sans-serif"> </div><div style="font-family:'arial' , sans-serif">SELECT i, 1 + (i-1) / 3 </div><div style="font-family:'arial' , sans-serif">FROM generate_series(1,10) AS i;</div><div style="font-family:'arial' , sans-serif"> </div><div style="font-family:'arial' , sans-serif">An expression like (i-1) / 3 could, of course, also be used as partitioning criterion in GROUP BY and/or window functions.</div><div style="font-family:'arial' , sans-serif"> </div><div style="font-family:'arial' , sans-serif">Cheers,</div><div style="font-family:'arial' , sans-serif"> —T</div></div></div> <div><div>On Sat, Aug 1, 2020 at 2:15 PM Mike Martin <<a href="mailto:redtux1@gmail.com">redtux1@gmail.com</a>> wrote:</div><blockquote style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><div><div>Say I have a field of ints, as for example created by with ordinality or generate_series, is it possible to group by a range? eq</div><div> </div><div>1,2,3,4,5,6,7,8,9,10</div><div>step 3</div><div>so output is</div><div> </div><div>1 1</div><div>2 1</div><div>3 1</div><div>4 2</div><div>5 2</div><div>6 2</div><div>7 3</div><div>8 3</div><div>9 3</div><div>10 4</div><div> </div><div>thanks</div></div></blockquote></div> <div> </div>--<div><div><div><div><div><font face="monospace, monospace">| Torsten Grust<br />| <a href="mailto:Torsten.Grust@gmail.com">Torsten.Grust@gmail.com</a></font><br /> </div></div></div></div></div></blockquote>
view thread (5+ messages)
Message-ID: <615631596886135@mail.yandex.com.tr>
Permalink: ../615631596886135@mail.yandex.com.tr/
Also on: postgresql.org/message-id/615631596886135@mail.yandex.com.tr
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: samed@reddoc.net, torsten.grust@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Group by a range of values
In-Reply-To: <615631596886135@mail.yandex.com.tr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox