From: Igor Neyman <ineyman.at.work@gmail.com>
To: George Woodring <george.woodring@iglass.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: array_agg() with join question
Date: Tue, 14 May 2013 11:37:19 -0400
Message-ID: <CAF6bNOzJSOKwP5vmui_W+nXkaB3eTxbVcbvLFN2+grsi1Q6DCA@mail.gmail.com> (raw)
In-Reply-To: <CACi+J=S3c_bYwv0LiRbm-qAPojjut6DpjZZ5rB0AQyjekcQoHw@mail.gmail.com>
References: <CACi+J=S3c_bYwv0LiRbm-qAPojjut6DpjZZ5rB0AQyjekcQoHw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On Tue, May 14, 2013 at 10:08 AM, George Woodring <
george.woodring@iglass.net> wrote:
> To summarize my question at the top, why is it that when I did the JOIN,
> the array_agg results reversed order?
>
> I had a function that ran the following query:
>
> SELECT timeslot, pollgrpid, array_agg(outval)
> FROM
> (SELECT * FROM rrd.a_current WHERE pollgrpid=8 ORDER BY timeslot,
> dsnum) AS foo
> WHERE timeslot >= now() - '1 hour'::interval AND timeslot <= now() + '1
> hour'::interval
> GROUP BY timeslot, pollgrpid
> ORDER BY timeslot;
>
> timeslot | pollgrpid | array_agg
> ------------------------+-----------+----------------------------
> 2013-05-14 08:58:00-04 | 8 | {1,1,0.00125217437744141}
> 2013-05-14 09:00:00-04 | 8 | {1,1,0.000999927520751953}
> 2013-05-14 09:02:00-04 | 8 | {1,1,0.00318312644958496}
> 2013-05-14 09:04:00-04 | 8 | {1,1,0.000761985778808594}
> 2013-05-14 09:06:00-04 | 8 | {1,1,0.000777959823608398}
> 2013-05-14 09:08:00-04 | 8 | {1,1,0.101096868515015}
> 2013-05-14 09:10:00-04 | 8 | {1,1,0.86168384552002}
> 2013-05-14 09:12:00-04 | 8 | {1,1,0.00656795501708984}
> 2013-05-14 09:14:00-04 | 8 | {1,1,0.102259159088135}
> 2013-05-14 09:16:00-04 | 8 | {1,1,0.000636100769042969}
>
> I wanted to include missing timestamps in my results, so I joined it with
> generate_series.
>
> SELECT timeslot, pollgrpid, array_agg(outval)
> FROM
> ( SELECT generate_series(rrd_timeslot('avail', now() - '58
> minutes'::interval), now() + '1 hour'::interval, '2 minutes') AS timeslot )
> AS bar
> LEFT JOIN
> (SELECT * FROM rrd.a_current WHERE pollgrpid=8 AND timeslot >= now()
> - '1 hour'::interval AND timeslot <= now() + '1 hour'::interval ORDER BY
> timeslot, dsnum) AS foo
> USING(timeslot)
> GROUP BY timeslot, pollgrpid
> ORDER BY timeslot;
>
> timeslot | pollgrpid | array_agg
> ------------------------+-----------+----------------------------
> 2013-05-14 09:02:00-04 | 8 | {0.00318312644958496,1,1}
> 2013-05-14 09:04:00-04 | 8 | {0.000761985778808594,1,1}
> 2013-05-14 09:06:00-04 | 8 | {0.000777959823608398,1,1}
> 2013-05-14 09:08:00-04 | 8 | {0.101096868515015,1,1}
> 2013-05-14 09:10:00-04 | 8 | {0.86168384552002,1,1}
> 2013-05-14 09:12:00-04 | 8 | {0.00656795501708984,1,1}
> 2013-05-14 09:14:00-04 | 8 | {0.102259159088135,1,1}
> 2013-05-14 09:16:00-04 | 8 | {0.000636100769042969,1,1}
> 2013-05-14 09:18:00-04 | 8 | {0.000638008117675781,1,1}
> 2013-05-14 09:20:00-04 | 8 | {0.174574136734009,1,1}
> 2013-05-14 09:22:00-04 | 8 | {0.1006920337677,1,1}
> 2013-05-14 09:24:00-04 | 8 | {0.00069117546081543,1,1}
> 2013-05-14 09:26:00-04 | 8 | {0.114289045333862,1,1}
> 2013-05-14 09:28:00-04 | 8 | {0.116230010986328,1,1}
> 2013-05-14 09:30:00-04 | 8 | {0.0349528789520264,1,1}
>
> The array_agg results are reversed. I had to ODER BY timeslot, dsnum desc
> on the right of the join to make it match. I am curious as to why this
> happened. I am running 9.2.4.
>
> Thanks,
> Woody
>
> iGLASS Networks
> www.iglass.net
>
As always (with databases) order is not guaranteed unless you specify
"ORDER BY ...".
So, specify whatever order you want inside aggregate function:
array_agg(outval order by column1)
Check the docs:
http://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-AGGREGATES
Igor Neyman
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: ineyman.at.work@gmail.com, george.woodring@iglass.net
Subject: Re: array_agg() with join question
In-Reply-To: <CAF6bNOzJSOKwP5vmui_W+nXkaB3eTxbVcbvLFN2+grsi1Q6DCA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox