Re: [SQL] Need help with a special JOIN

[Johnny Winn]

On Sat, Sep 29, 2012 at 2:28 PM, Victor Sterpu  wrote:

> This is a way to do it, but things will change if you have many
> attributes/object
>
> SELECT o.*, COALESCE(a1.value, a2.value)
> FROM objects AS o
> LEFT JOIN attributes AS a1 ON (a1.object_id = o.id)
> LEFT JOIN attributes AS a2 ON (a2.object_id = 0);
>
> On 29.09.2012 19:02, Andreas wrote:
>
>> Hi,
>>
>> asume I've got 2 tables
>>
>> objects ( id int, name text )
>> attributes ( object_id int, value int )
>>
>> attributes   has a default entry with object_id = 0 and some other where
>> another value should be used.
>>
>> e.g.
>> objects
>> (   1,   'A'   ),
>> (   2,   'B'   ),
>> (   3,   'C'   )
>>
>> attributes
>> (   0,   42   ),
>> (   2,   99   )
>>
>> The result of the join should look like this:
>>
>> object_id, name, value
>> 1,   'A',   42
>> 2,   'B',   99
>> 3,   'C',   42
>>
>>
>> I could figure something out with 2 JOINs, UNION and some DISTINCT ON but
>> this would make my real query rather chunky.   :(
>>
>> Is there an elegant way to get this?
>>
>>
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql
>


I like this join option because it's a little more simplified. Depending on
the "default option" requirement you could change the nested select or
otherwise replace all together.

 SELECT "Objects"."ID", "Objects"."Name",
   COALESCE("Attributes".value, (SELECT "Attributes".value FROM
"Attributes" WHERE object_id = 0))
 FROM "Objects" LEFT JOIN
   "Attributes" ON "Objects"."ID" = "Attributes".object_id;

Thanks,
Johnny

Re: [SQL] Need help with a special JOIN

[Victor Sterpu]

This is a way to do it, but things will change if you have many
attributes/object

SELECT o.*, COALESCE(a1.value, a2.value)
FROM objects AS o
LEFT JOIN attributes AS a1 ON (a1.object_id = o.id)
LEFT JOIN attributes AS a2 ON (a2.object_id = 0);

On 29.09.2012 19:02, Andreas wrote:

Hi,

asume I've got 2 tables

objects ( id int, name text )
attributes ( object_id int, value int )

attributes   has a default entry with object_id = 0 and some other 
where another value should be used.


e.g.
objects
(   1,   'A'   ),
(   2,   'B'   ),
(   3,   'C'   )

attributes
(   0,   42   ),
(   2,   99   )

The result of the join should look like this:

object_id, name, value
1,   'A',   42
2,   'B',   99
3,   'C',   42


I could figure something out with 2 JOINs, UNION and some DISTINCT ON 
but this would make my real query rather chunky.   :(


Is there an elegant way to get this?






--
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] Need help with a special JOIN

[David Johnston]

On Sep 29, 2012, at 12:02, Andreas  wrote:

> Hi,
> 
> asume I've got 2 tables
> 
> objects ( id int, name text )
> attributes ( object_id int, value int )
> 
> attributes   has a default entry with object_id = 0 and some other where 
> another value should be used.
> 
> e.g.
> objects
> (   1,   'A'   ),
> (   2,   'B'   ),
> (   3,   'C'   )
> 
> attributes
> (   0,   42   ),
> (   2,   99   )
> 
> The result of the join should look like this:
> 
> object_id, name, value
> 1,   'A',   42
> 2,   'B',   99
> 3,   'C',   42
> 
> 
> I could figure something out with 2 JOINs, UNION and some DISTINCT ON but 
> this would make my real query rather chunky.   :(
> 
> Is there an elegant way to get this?
> 

General form (idea only, syntax not tested)

Select objectid, name, coalesce(actuals.value, defaults.value)
From objects cross join (select ... From  attributes ...) as defaults
Left join attributes as actuals on ...

Build up a master relation with all defaults then left join that against the 
attributes taking the matches where present otherwise taking the default.

David J.



-- 
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] Need help with a special JOIN

[Samuel Gendler]

On Sat, Sep 29, 2012 at 9:02 AM, Andreas  wrote:

> Hi,
>
> asume I've got 2 tables
>
> objects ( id int, name text )
> attributes ( object_id int, value int )
>
> attributes   has a default entry with object_id = 0 and some other where
> another value should be used.
>
> e.g.
> objects
> (   1,   'A'   ),
> (   2,   'B'   ),
> (   3,   'C'   )
>
> attributes
> (   0,   42   ),
> (   2,   99   )
>
> The result of the join should look like this:
>
> object_id, name, value
> 1,   'A',   42
> 2,   'B',   99
> 3,   'C',   42
>
>
> I could figure something out with 2 JOINs, UNION and some DISTINCT ON but
> this would make my real query rather chunky.   :(
>
> Is there an elegant way to get this?
>
>
I'm not sure it is any more elegant than the kind of solution you suggest,
but this works:

# select id, name, value from
(select *, count(o.id) over (partition by o.id) as total from objects o
join attributes a on a.object_id = o.id or a.object_id = 0) q
where total = 1 or object_id != 0;
 id | name | value
+--+---
  1 | A|42
  2 | B|99
  3 | C|42

[SQL] Need help with a special JOIN

[Andreas]

Hi,

asume I've got 2 tables

objects ( id int, name text )
attributes ( object_id int, value int )

attributes   has a default entry with object_id = 0 and some other where 
another value should be used.


e.g.
objects
(   1,   'A'   ),
(   2,   'B'   ),
(   3,   'C'   )

attributes
(   0,   42   ),
(   2,   99   )

The result of the join should look like this:

object_id, name, value
1,   'A',   42
2,   'B',   99
3,   'C',   42


I could figure something out with 2 JOINs, UNION and some DISTINCT ON 
but this would make my real query rather chunky.   :(


Is there an elegant way to get this?


--
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] Reuse temporary calculation results in an SQL update query [SOLVDED]

[Matthias Nagel]

Hello,

thank you. The "WITH" clause did the trick. I did not even know that such a 
thing exists. But as it turns out it makes the statement more readable and 
elegant but not faster.

The reason for the latter is that both the CTE and the UPDATE statement have 
the same "FROM ... WHERE ..." part, because the tempory calculation needs some 
input values from the same table. Hence the table is looked up twice instead 
once.

Matthias


Am Samstag 29 September 2012, 17:13:18 schrieb Andreas Kretschmer:
> 
> Thomas Kellerer  hat am 29. September 2012 um 16:13
> geschrieben:
> > Matthias Nagel wrote on 29.09.2012 12:49:
> > > Hello,
> > >
> > > is there any way how one can store the result of a time-consuming
> > > calculation if this result is needed more
> > >than once in an SQL update query? This solution might be PostgreSQL 
> > >specific
> > >and not standard SQL compliant.
> > >  Here is an example of what I want:
> > >
> > > UPDATE table1 SET
> > > StartTime = 'time consuming calculation 1',
> > > StopTime = 'time consuming calculation 2',
> > > Duration = 'time consuming calculation 2' - 'time consuming 
> > > calculation
> > > 1'
> > > WHERE foo;
> > >
> > > It would be nice, if I could use the "new" start and stop time to 
> > > calculate
> > > the duration time.
> > >First of all it would make the SQL statement faster and secondly much more
> > >cleaner and easily to understand.
> >
> >
> > Something like:
> >
> > with my_calc as (
> >  select pk,
> > time_consuming_calculation_1 as calc1,
> > time_consuming_calculation_2 as calc2
> >  from foo
> > )
> > update foo
> >set startTime = my_calc.calc1,
> >stopTime = my_calc.calc2,
> >duration = my_calc.calc2 - calc1
> > where foo.pk = my_calc.pk;
> >
> > http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING
> 
> 
> 
> Yeah, with a WITH - CTE, cool ;-)
> 
> Andreas
> 
> 
> 
--
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.na...@gmail.com
ICQ: 499797758
Skype: nagmat84



-- 
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] Reuse temporary calculation results in an SQL update query

[Andreas Kretschmer]

Thomas Kellerer  hat am 29. September 2012 um 16:13
geschrieben:
> Matthias Nagel wrote on 29.09.2012 12:49:
> > Hello,
> >
> > is there any way how one can store the result of a time-consuming
> > calculation if this result is needed more
> >than once in an SQL update query? This solution might be PostgreSQL specific
> >and not standard SQL compliant.
> >  Here is an example of what I want:
> >
> > UPDATE table1 SET
> > StartTime = 'time consuming calculation 1',
> > StopTime = 'time consuming calculation 2',
> > Duration = 'time consuming calculation 2' - 'time consuming calculation
> > 1'
> > WHERE foo;
> >
> > It would be nice, if I could use the "new" start and stop time to calculate
> > the duration time.
> >First of all it would make the SQL statement faster and secondly much more
> >cleaner and easily to understand.
>
>
> Something like:
>
> with my_calc as (
>  select pk,
> time_consuming_calculation_1 as calc1,
> time_consuming_calculation_2 as calc2
>  from foo
> )
> update foo
>set startTime = my_calc.calc1,
>stopTime = my_calc.calc2,
>duration = my_calc.calc2 - calc1
> where foo.pk = my_calc.pk;
>
> http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING



Yeah, with a WITH - CTE, cool ;-)

Andreas


-- 
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] Reuse temporary calculation results in an SQL update query

[Thomas Kellerer]

Matthias Nagel wrote on 29.09.2012 12:49:

Hello,

is there any way how one can store the result of a time-consuming calculation 
if this result is needed more
than once in an SQL update query? This solution might be PostgreSQL specific 
and not standard SQL compliant.
 Here is an example of what I want:

UPDATE table1 SET
StartTime = 'time consuming calculation 1',
StopTime = 'time consuming calculation 2',
Duration = 'time consuming calculation 2' - 'time consuming calculation 1'
WHERE foo;

It would be nice, if I could use the "new" start and stop time to calculate the 
duration time.
First of all it would make the SQL statement faster and secondly much more 
cleaner and easily to understand.



Something like:

with my_calc as (
select pk,
   time_consuming_calculation_1 as calc1,
   time_consuming_calculation_2 as calc2
from foo
)
update foo
  set startTime = my_calc.calc1,
  stopTime = my_calc.calc2,
  duration = my_calc.calc2 - calc1
where foo.pk = my_calc.pk;

http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING




--
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] Reuse temporary calculation results in an SQL update query

[David Johnston]

On Sep 29, 2012, at 6:49, Matthias Nagel  wrote:

> Hello,
> 
> is there any way how one can store the result of a time-consuming calculation 
> if this result is needed more than once in an SQL update query? This solution 
> might be PostgreSQL specific and not standard SQL compliant. Here is an 
> example of what I want:
> 
> UPDATE table1 SET
>   StartTime = 'time consuming calculation 1',
>   StopTime = 'time consuming calculation 2',
>   Duration = 'time consuming calculation 2' - 'time consuming calculation 1'
> WHERE foo;
> 
> It would be nice, if I could use the "new" start and stop time to calculate 
> the duration time. First of all it would make the SQL statement faster and 
> secondly much more cleaner and easily to understand.
> 
> Best regards, Matthias
> 
> 

You are allowed to use a FROM clause with UPDATE so if you can figure out how 
to write a SELECT query, including a CTE if needed, you can use that as your 
cache.

An immutable function should also be optimized in theory though I've never 
tried it.

David J.



-- 
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] Reuse temporary calculation results in an SQL update query

[Matthias Nagel]

Hello,

> Matthias Nagel  hat am 29. September 2012 um 12:49
> geschrieben:
> > Hello,
> >
> > is there any way how one can store the result of a time-consuming 
> > calculation
> > if this result is needed more than once in an SQL update query? This 
> > solution
> > might be PostgreSQL specific and not standard SQL compliant. Here is an
> > example of what I want:
> >
> > UPDATE table1 SET
> >StartTime = 'time consuming calculation 1',
> >StopTime = 'time consuming calculation 2',
> >Duration = 'time consuming calculation 2' - 'time consuming calculation 
> > 1'
> > WHERE foo;
> 
> The Duration - field is superfluous ...
> 

I expected the answer ;-), but no, it is not superfluous. In this small example 
it might appear as if it is, but there are cases in that the start time and the 
duration time have values and the stop time equals null to indicate a running 
session. And for reasons that are beyond the orginal question, there are also 
cases where duration does not equal the difference between start and stop time.

> As far as i know there is no way to re-use the result.

Too bad.

> Regards, Andreas

Thanks, Matthias

--
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.na...@gmail.com
ICQ: 499797758
Skype: nagmat84



-- 
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] Reuse temporary calculation results in an SQL update query

[Andreas Kretschmer]

Matthias Nagel  hat am 29. September 2012 um 12:49
geschrieben:
> Hello,
>
> is there any way how one can store the result of a time-consuming calculation
> if this result is needed more than once in an SQL update query? This solution
> might be PostgreSQL specific and not standard SQL compliant. Here is an
> example of what I want:
>
> UPDATE table1 SET
>StartTime = 'time consuming calculation 1',
>StopTime = 'time consuming calculation 2',
>Duration = 'time consuming calculation 2' - 'time consuming calculation 1'
> WHERE foo;

The Duration - field is superfluous ...

As far as i know there is no way to re-use the result.


Regards, Andreas


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

[SQL] Reuse temporary calculation results in an SQL update query

[Matthias Nagel]

Hello,

is there any way how one can store the result of a time-consuming calculation 
if this result is needed more than once in an SQL update query? This solution 
might be PostgreSQL specific and not standard SQL compliant. Here is an example 
of what I want:

UPDATE table1 SET
   StartTime = 'time consuming calculation 1',
   StopTime = 'time consuming calculation 2',
   Duration = 'time consuming calculation 2' - 'time consuming calculation 1'
WHERE foo;

It would be nice, if I could use the "new" start and stop time to calculate the 
duration time. First of all it would make the SQL statement faster and secondly 
much more cleaner and easily to understand.

Best regards, Matthias

--
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.na...@gmail.com
ICQ: 499797758
Skype: nagmat84



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