Thread: array_agg() with join question

array_agg() with join question

From
George Woodring
Date:
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

Re: array_agg() with join question

From
Igor Neyman
Date:



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)
Igor Neyman

Re: array_agg() with join question

From
George Woodring
Date:
Thank you.  I was trying to do the order by at the sub-query level.

Thanks again.

Woody

iGLASS Networks
www.iglass.net


On Tue, May 14, 2013 at 11:37 AM, Igor Neyman <ineyman.at.work@gmail.com> wrote:



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)
Igor Neyman