Thread: Numbering rows
Is there an easy way to assign a sequential number, possibly based on an arbitrary minimum (typically 0 or 1) to each row of an ordered result set, or do I have to work with explicit sequences? I need to do quite a lot of maths on successive rows, extracting numeric and timestamp differences hence rates of change. I've typically been doing it manually or in a spreadsheet but there has to be a better way e.g. by a join on offset row numbers. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
May be this function can help : http://www.postgresql.org/docs/8.3/static/functions-srf.html Ries On Oct 15, 2008, at 1:44 PM, Mark Morgan Lloyd wrote: > Is there an easy way to assign a sequential number, possibly based > on an arbitrary minimum (typically 0 or 1) to each row of an ordered > result set, or do I have to work with explicit sequences? > > I need to do quite a lot of maths on successive rows, extracting > numeric and timestamp differences hence rates of change. I've > typically been doing it manually or in a spreadsheet but there has > to be a better way e.g. by a join on offset row numbers. > > -- > Mark Morgan Lloyd > markMLl .AT. telemetry.co .DOT. uk > > [Opinions above are the author's, not those of his employers or > colleagues] > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <pg@rvt.dds.nl> wrote: > May be this function can help : > > http://www.postgresql.org/docs/8.3/static/functions-srf.html Using generate series won't number the rows that way that you would want. You basically will end up with a cross join between the generated series and the requested set. There are three ways that I know of to get a row number: 1) IIRC use a pl-pgsql function that returns an incremented number 2) use SQL by joining using the operator ">=" and Group by aggregate count(*) 3) 8.4 has sum new analytic functions that will do this nicely. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Wed, Oct 15, 2008 at 1:23 PM, Richard Broersma <richard.broersma@gmail.com> wrote: > On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <pg@rvt.dds.nl> wrote: >> May be this function can help : >> >> http://www.postgresql.org/docs/8.3/static/functions-srf.html > > Using generate series won't number the rows that way that you would > want. You basically will end up with a cross join between the > generated series and the requested set. There are three ways that I > know of to get a row number: Can't you put the query into a subselect with an offset 0 and join to that to get the generate_series to work correctly?
On Wed, Oct 15, 2008 at 12:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Can't you put the query into a subselect with an offset 0 and join to
> that to get the generate_series to work correctly?
I've never heard of doing it that way, but I'm very interestes in
seeing how it is done. This is what i've tried so far, but am still
getting the cross join:
postgres=#
select * from generate_series(1,3)
CROSS JOIN ( values('a'),('b'),('c') ) as myvals( letter )
OFFSET 0;
generate_series | letter
-----------------+--------
1 | a
2 | a
3 | a
1 | b
2 | b
3 | b
1 | c
2 | c
3 | c
(9 rows)
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
Thanks everybody- I'm watching with a lot of interest. I was worried that I was asking something stupid with an obvious answer... ries van Twisk wrote: > May be this function can help : > > http://www.postgresql.org/docs/8.3/static/functions-srf.html Thanks, that's already turning out to be useful for something else I was working on today: select percent, to_char(nedcar_tonnes_001(percent), '99990.99') AS nedcar from generate_series(0,110,10) as percent; The one thing I'd say about generate_series() is that the description suggests that one has to put an explicit count() as the second parameter if using it to number rows, i.e. it doesn't have an "as many as necessary" option. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Mark Morgan Lloyd wrote:
> Is there an easy way to assign a sequential number, possibly based on an
> arbitrary minimum (typically 0 or 1) to each row of an ordered result
> set, or do I have to work with explicit sequences?
>
> I need to do quite a lot of maths on successive rows, extracting numeric
> and timestamp differences hence rates of change. I've typically been
> doing it manually or in a spreadsheet but there has to be a better way
> e.g. by a join on offset row numbers.
PERL can remember variables in your session. Here's a function I wrote
that sets a "global" variable in PL/PERL:
----------
CREATE OR REPLACE FUNCTION "public"."global_var_set"
(in_key varchar, in_value bigint)
RETURNS bigint AS
$body$
my ($key, $value) = @_;
$_SHARED{$key} = $value;
return $value;
$body$
LANGUAGE 'plperl' VOLATILE RETURNS NULL ON NULL INPUT;
----------
Then, later you can read that global variable with another function like
this:
----------
CREATE OR REPLACE FUNCTION "public"."global_var_get"
(in_key varchar)
RETURNS bigint AS
$body$
my ($key) = @_;
return $_SHARED{$key} ? $_SHARED{$key} : 0;
$body$
LANGUAGE 'plperl' VOLATILE RETURNS NULL ON NULL INPUT;
----------
Perhaps you can use PL/PERL and a function like these to modify "global"
variables that you can increment as you do your select. Something like:
SELECT global_var_set(0);
SELECT global_var_inc() AS row_counter, *
FROM datatable
ORDER BY whatever;
Just an idea.
-- Dante
----------
D. Dante Lorenso
dante@lorenso.com
D. Dante Lorenso wrote: > PERL can remember variables in your session. Here's a function I wrote > that sets a "global" variable in PL/PERL: Perl can do anything- that's cheating :-) Actually, I use Perl heavily but the advantage of being able to do the sort of analysis being discussed in a single query is that the query can be easily shipped with the results as a description of the method. Having to set up ad-hoc extra functions (in addition to those that are normally in the workflow) can be problematic, particularly if a recipient of the query only has restricted access. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
am Wed, dem 15.10.2008, um 12:23:42 -0700 mailte Richard Broersma folgendes: > On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <pg@rvt.dds.nl> wrote: > > May be this function can help : > > > > http://www.postgresql.org/docs/8.3/static/functions-srf.html > > Using generate series won't number the rows that way that you would > want. You basically will end up with a cross join between the > generated series and the requested set. There are three ways that I > know of to get a row number: > > 1) IIRC use a pl-pgsql function that returns an incremented number > 2) use SQL by joining using the operator ">=" and Group by aggregate count(*) > 3) 8.4 has sum new analytic functions that will do this nicely. Can you show an example for 8.4? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Andreas Kretschmer wrote: > Can you show an example for 8.4? It's not 100% certain that it will be possible for 8.4, probably though. select row_number() over (order by employeeid) as nrow,* from employee order by employeeid It's important to have both the order bys There is more information on windowing functions here http://en.wikipedia.org/wiki/SELECT David.
David Rowley wrote: > It's not 100% certain that it will be possible for 8.4, probably though. > > select row_number() over (order by employeeid) as nrow,* from employee order > by employeeid That makes sense, thanks. So extracting rate-of-change etc. would be a join on two subselects followed by a bit of maths, that's likely to be a big incentive for an upgrade when it comes out. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Wed, Oct 15, 2008 at 10:21 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > > Can you show an example for 8.4? I looked for ROW_NUMBER in the developer docs. I could only find it under the KEY WORDS list. I guess they haven't put in a good example yet. I saw this recently demonstrated at PgWest by Dave Fetter. He illustrated several example of how to use analytic functions such as these. http://fetter.org/Window_Functions_WEST_2008.pdf http://fetter.org/Trees_and_More_WEST_2008.pdf -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
"Richard Broersma" <richard.broersma@gmail.com> writes:
> I looked for ROW_NUMBER in the developer docs. I could only find it
> under the KEY WORDS list. I guess they haven't put in a good example
> yet.
> I saw this recently demonstrated at PgWest by Dave Fetter. He
> illustrated several example of how to use analytic functions such as
> these.
Fetter was demo'ing uncommitted patches.
regards, tom lane