Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UcHI6-0003EJ-2Y for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2013 15:37:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UcHI5-0007ab-Hv for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2013 15:37:25 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UcHI4-0007ZJ-6Z for pgsql-sql@postgresql.org; Tue, 14 May 2013 15:37:24 +0000 Received: from mail-ve0-x234.google.com ([2607:f8b0:400c:c01::234]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UcHI0-0008NY-K7 for pgsql-sql@postgresql.org; Tue, 14 May 2013 15:37:23 +0000 Received: by mail-ve0-f180.google.com with SMTP id pa12so794817veb.11 for ; Tue, 14 May 2013 08:37:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:in-reply-to:references:date:message-id :subject:from:to:cc:content-type; bh=mwJCsN/XZ3opPZqANQd0XFbn+QimdVpZwnwSjwjIx+k=; b=ydPJyXM7vm8i/aMgdx5aTszUiF2e03mEUDlSDlWu/ER8/CCWWytxOzq3G/oNPasjZq FfXTVQ2Y0CBZE9hsK/X/L8/Yn9ahj/09lcIFrjpkb+nttqrBDqwQEp2UwNsM1XI/Qt0U nEtfBQyx/eHmRkqEuCiXODf3Kg3x/REAry9aqeXbAfeiYbqENkLoEGNxM3Wq/I3YRAY2 gPtpEa0xWKIyld3ITd1jYBLFfa7T9sdjxu93LyQFij/5fckyyao7yg8hbjUCXiDafuhG yW2P0o7XVrmcJB2NAtNJavU/WzRtqmTxCpTVfA36DQ5wx4Fbkekg29kSzP9klua7rzob VDZg== MIME-Version: 1.0 X-Received: by 10.59.9.39 with SMTP id dp7mr22158549ved.36.1368545839951; Tue, 14 May 2013 08:37:19 -0700 (PDT) Received: by 10.52.0.41 with HTTP; Tue, 14 May 2013 08:37:19 -0700 (PDT) In-Reply-To: References: Date: Tue, 14 May 2013 11:37:19 -0400 Message-ID: Subject: Re: array_agg() with join question From: Igor Neyman To: George Woodring Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=047d7bf0d5eea6373304dcaf682c X-Pg-Spam-Score: -0.1 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --047d7bf0d5eea6373304dcaf682c Content-Type: text/plain; charset=ISO-8859-1 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 --047d7bf0d5eea6373304dcaf682c Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable



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 a= rray_agg results reversed order?=A0

I had a function that ran the following query:

SELECT timeslot, pollgrpid, array_agg(outval)=A0
=A0 =A0FROM=A0
=A0 =A0 =A0 (SELECT * FROM rrd.a_current WHER= E pollgrpid=3D8 ORDER BY timeslot, dsnum) AS foo=A0
=A0 =A0WHERE = timeslot >=3D now() - '1 hour'::interval AND timeslot <=3D no= w() + '1 hour'::interval=A0
=A0 =A0GROUP BY timeslot, pollgrpid=A0
=A0 =A0ORDER BY times= lot;

=A0 =A0 =A0 =A0 timeslot =A0 =A0 =A0 =A0= | pollgrpid | =A0 =A0 =A0 =A0 array_agg =A0 =A0 =A0 =A0 =A0
-----= -------------------+-----------+----------------------------
=A02013-05-14 08:58:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.001252174377441= 41}
=A02013-05-14 09:00:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.000999= 927520751953}
=A02013-05-14 09:02:00-04 | =A0 =A0 =A0 =A0 8 | {1,= 1,0.00318312644958496}
=A02013-05-14 09:04:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.000761985778808= 594}
=A02013-05-14 09:06:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.00077= 7959823608398}
=A02013-05-14 09:08:00-04 | =A0 =A0 =A0 =A0 8 | {1= ,1,0.101096868515015}
=A02013-05-14 09:10:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.86168384552002}=
=A02013-05-14 09:12:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.006567955= 01708984}
=A02013-05-14 09:14:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.= 102259159088135}
=A02013-05-14 09:16:00-04 | =A0 =A0 =A0 =A0 8 | {1,1,0.000636100769042969}<= /div>

I wanted to include missing timestamps in my resul= ts, so I joined it with generate_series.

SELECT ti= meslot, pollgrpid, array_agg(outval)=A0
=A0 =A0FROM=A0
=A0 =A0 =A0 ( SELECT generate_series(rrd_time= slot('avail', now() - '58 minutes'::interval), now() + '= ;1 hour'::interval, '2 minutes') AS timeslot ) AS bar=A0
<= div>=A0 =A0LEFT JOIN=A0
=A0 =A0 =A0 (SELECT * FROM rrd.a_current WHERE pollgrpid=3D8 AND times= lot >=3D now() - '1 hour'::interval AND timeslot <=3D now() += '1 hour'::interval ORDER BY timeslot, dsnum) AS foo=A0
= =A0 =A0USING(timeslot)=A0
=A0 =A0GROUP BY timeslot, pollgrpid=A0
=A0 =A0ORDER BY times= lot;

=A0 =A0 =A0 =A0 timeslot =A0 =A0 =A0= =A0| pollgrpid | =A0 =A0 =A0 =A0 array_agg =A0 =A0 =A0 =A0 =A0
-= -----------------------+-----------+----------------------------
=A02013-05-14 09:02:00-04 | =A0 =A0 =A0 =A0 8 | {0.00318312644958496,1= ,1}
=A02013-05-14 09:04:00-04 | =A0 =A0 =A0 =A0 8 | {0.0007619857= 78808594,1,1}
=A02013-05-14 09:06:00-04 | =A0 =A0 =A0 =A0 8 | {0.= 000777959823608398,1,1}
=A02013-05-14 09:08:00-04 | =A0 =A0 =A0 =A0 8 | {0.101096868515015,1,1= }
=A02013-05-14 09:10:00-04 | =A0 =A0 =A0 =A0 8 | {0.861683845520= 02,1,1}
=A02013-05-14 09:12:00-04 | =A0 =A0 =A0 =A0 8 | {0.006567= 95501708984,1,1}
=A02013-05-14 09:14:00-04 | =A0 =A0 =A0 =A0 8 | {0.102259159088135,1,1}
=A02013-05-14 09:16:00-04 | =A0 =A0 =A0 =A0 8 | {0.00063610076904296= 9,1,1}
=A02013-05-14 09:18:00-04 | =A0 =A0 =A0 =A0 8 | {0.0006380= 08117675781,1,1}
=A02013-05-14 09:20:00-04 | =A0 =A0 =A0 =A0 8 | {0.174574136734009,1,1}
=A02013-05-14 09:22:00-04 | =A0 =A0 =A0 =A0 8 | {0.1006920337677,1,1= }
=A02013-05-14 09:24:00-04 | =A0 =A0 =A0 =A0 8 | {0.000691175460= 81543,1,1}
=A02013-05-14 09:26:00-04 | =A0 =A0 =A0 =A0 8 | {0.114= 289045333862,1,1}
=A02013-05-14 09:28:00-04 | =A0 =A0 =A0 =A0 8 | {0.116230010986328,1,1= }
=A02013-05-14 09:30:00-04 | =A0 =A0 =A0 =A0 8 | {0.034952878952= 0264,1,1}

The array_agg results are reversed= . =A0I 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. =A0I am running 9.2.4.

Thanks,
Woody=A0

iGL= ASS Networks
www.igl= ass.net

As always (with dat= abases) order is not guaranteed unless you specify "ORDER BY ..."= .
So, specify whatever order you want i= nside aggregate function:

array_agg(outval order by column1)
=

Check the docs:
h= ttp://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-AGGREG= ATES

Igor Neyman
--047d7bf0d5eea6373304dcaf682c--