date:20130514

[SQL] array_agg() with join question

2013-05-14 Thread George Woodring

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.00027520751953}
 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: [SQL] array_agg() with join question

2013-05-14 Thread Igor Neyman

On Tue, May 14, 2013 at 10:08 AM, George Woodring <
george.woodr...@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.00027520751953}
>  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

Re: [SQL] Correct implementation of 1:n relationship with n>0?

2013-05-14 Thread Wolfgang Keller

> > The point is that I would have expected that problem to be solved
> > within the past four decades since relational databases have been
> > invented. Or at least in the past two decades since PostgreSQL has
> > been developed.
> 
> Then what about n>1, n>2, n>k where k an arbitrarily large positive
> integer? isn't it the same problem class actually?

Not quite.

In conceptual modeling, relationships between entities aren't directed.
And there's no reason to not have a "not null" constraint on any of the
two sides of a one-to-many (or many-to-many) relationship from the
conceptual point of view.

The way relationships are implemented in physical database schemas, by
foreign key constraints, just makes it non-trivial to implement such a
"not null" constraint on the "wrong" end of a one-to-many relationship.
 
But this a technical issue with the implementation of databases at the
physical level, not a conceptual issue.

> Is there any serious database vendor who provides out of the box
> support for 1:n, n>0 ? Or is it an "unusual" user requirement in the
> first place.

It definitely is not, since conceptual diagrams I have seen are full of
such relationships. They're just never correctly implemented at the
physical level.

> Ever thought why not so many people have asked for this?

Having quite some professional experience in companies, I don't doubt
for a second that the very only reason is simply total absence of care
for scientific correctness of database at the level of the physical
implementation.
 
Sincerely,

Wolfgang


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] array_agg() with join question

2013-05-14 Thread George Woodring

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 wrote:

>
>
>
> On Tue, May 14, 2013 at 10:08 AM, George Woodring <
> george.woodr...@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.00027520751953}
>>  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
>

[SQL] array_agg() with join question

Re: [SQL] array_agg() with join question

Re: [SQL] Correct implementation of 1:n relationship with n>0?

Re: [SQL] array_agg() with join question

4 matches

Site Navigation

Mail list logo

Footer information