Re: [SQL] Table Design for Hierarchical Data

2010-04-12 Thread Achilleas Mantzios 
Στις Thursday 08 April 2010 17:59:01 ο/η Rob Sargent έγραψε:
> The "parent" node in a genealogy is the mother-father tuple, so given
> that as a singularity it still fits a tree.
No, because the child and parent node would be of different schema.
> 
> On 04/08/2010 12:56 AM, Achilleas Mantzios wrote:
> > Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε:
> >> Achilleas Mantzios wrote:
> >>> Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε:
> >>>   
>  Achilleas Mantzios wrote:
>  
> > You could also consider the genealogical approach, e.g.
> >
> >
> > The parents of any node to the root, i.e. the path of any node to the 
> > root are depicted as
> > parents[0] : immediate parent
> > parents[1] : immediate parent of the above parent
> >   
> >   
>  What I have more than one parent?
>  
> >>>
> >>> Then it is no longer neither a tree, nor a hierarchical structure, but 
> >>> rather a graph.
> >>> This a totally different problem.
> >>>   
> >> My question was actually an attempt to point at the inability of what 
> >> you call the 'genealogical approach' database design to store 
> >> information of more than one parent.
> > 
> > 
> > Are you suggesting that we should change our definition of trees ADT, just 
> > because it does not
> > fit the mere detail that humans have two parents?
> > Or are you just suggesting that the "genealogical" term is inaccurate?
> > 
> > Take a look here: www.tetilab.com/roberto/pgsql/postgres-trees.pdf
> > 
> >>
> >> regards,
> >> Yeb Havinga
> >>
> >>
> > 
> > 
> > 
> 



-- 
Achilleas Mantzios

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

Re: [SQL] Howto get a group_number like row_number for groups

2010-04-12 Thread msi77 
Hi,

dense_rank()  over  (order by  object)  %  2


> Hi 
> I'd like to have an alternating colorindex in the output of a query that 
> consecutive rows that are the same within a colum the same number. 
> The query generates a readable output from a log-table and a few others 
> that hold referenced texts. 
> log (log_id int, log_event_fk int, object_fk int , ts timestamp) 
> events (event_id int, event text) 
> objects (object_id int, object text, is_active bool) 
> the easy part :) 
> mind the the ordering is not primarily based on the timestamp 
> select log_id, event, object, ts 
> from log 
> join events on event_id = log_event_fk 
> join objects on object_id = object_fk 
> where object.is_active 
> order by object, ts 
> Now I'd need a dynamically generated column that alternates between 0 
> and 1 so that I can later color the rows where object is the same. 
> row_number() over (order by object, ts) % 2 
> or rank() over (order by object, ts) % 2 
> produces the 0/1 alternation for rows 
> When I create a subselect for objects that adds the colorindex and join 
> this to the log instead of objects, I get the group-color only if I 
> omit the sorting on the timestamp. 
> When I order the outer select by object, ts the colorindex gets 0 in 
> every row. :( 
> I'd like to get something as this 
> 3, up, dev3, 2010-4-2 10:00, 0 
> 8, down, dev3, 2010-4-2 14:00, 0 
> 9, down, dev3, 2010-4-2 15:00, 0 
> 1, up, dev7, 2010-4-2 09:00, 1 
> 5, down, dev7, 2010-4-2 17:00, 1 
> 2, up, dev11, 2010-4-2 12:00, 0 
> 7, down, dev11, 2010-4-2 13:00, 0 
> . 
> . 
> regards :) 
> -- 
> Sent via pgsql-sql mailing list ([email protected]) 
> To make changes to your subscription: 
> http://www.postgresql.org/mailpref/pgsql-sql 
> 

Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign

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

Re: [SQL] Table Design for Hierarchical Data

2010-04-12 Thread Rob Sargent 
Believe me: "ego-ma-pa" will correctly define genealogical relationships
(at least among humans).

On 04/12/2010 02:14 AM, Achilleas Mantzios wrote:
> Στις Thursday 08 April 2010 17:59:01 ο/η Rob Sargent έγραψε:
>> The "parent" node in a genealogy is the mother-father tuple, so given
>> that as a singularity it still fits a tree.
> No, because the child and parent node would be of different schema.
>>
>> On 04/08/2010 12:56 AM, Achilleas Mantzios wrote:
>>> Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε:
 Achilleas Mantzios wrote:
> Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε:
>   
>> Achilleas Mantzios wrote:
>> 
>>> You could also consider the genealogical approach, e.g.
>>>
>>>
>>> The parents of any node to the root, i.e. the path of any node to the 
>>> root are depicted as
>>> parents[0] : immediate parent
>>> parents[1] : immediate parent of the above parent
>>>   
>>>   
>> What I have more than one parent?
>> 
>
> Then it is no longer neither a tree, nor a hierarchical structure, but 
> rather a graph.
> This a totally different problem.
>   
 My question was actually an attempt to point at the inability of what 
 you call the 'genealogical approach' database design to store 
 information of more than one parent.
>>>
>>>
>>> Are you suggesting that we should change our definition of trees ADT, just 
>>> because it does not
>>> fit the mere detail that humans have two parents?
>>> Or are you just suggesting that the "genealogical" term is inaccurate?
>>>
>>> Take a look here: www.tetilab.com/roberto/pgsql/postgres-trees.pdf
>>>

 regards,
 Yeb Havinga


>>>
>>>
>>>
>>
> 
> 
> 

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

Re: [SQL] Table Design for Hierarchical Data

2010-04-12 Thread Leif Biberg Kristensen 
On Monday 12. April 2010 16.57.38 Rob Sargent wrote:
> Believe me: "ego-ma-pa" will correctly define genealogical relationships
> (at least among humans).

Yes, but a family tree is not a hierarchical tree as defined in database 
theory. Believe me: I'm a genealogist.

Hint: Where is the root node of a family tree? Old Adam & Eve?

On the other hand, a pedigree may be considered a true binary tree with a root 
node, the proband.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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

Re: [SQL] Table Design for Hierarchical Data

2010-04-12 Thread Yeb Havinga 

Leif Biberg Kristensen wrote:

On Monday 12. April 2010 16.57.38 Rob Sargent wrote:
  

Believe me: "ego-ma-pa" will correctly define genealogical relationships
(at least among humans).



Yes, but a family tree is not a hierarchical tree as defined in database 
theory. Believe me: I'm a genealogist.
  
The last sentence is almost like the 'proof by authority' from 36 
methods of mathematical proof, see e.g. 
http://jwilson.coe.uga.edu/EMT668/EMAT6680.F99/Challen/proof/proof.html.






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

Re: [SQL] Table Design for Hierarchical Data

2010-04-12 Thread Leif Biberg Kristensen 
On Monday 12. April 2010 17.37.58 Yeb Havinga wrote:
> Leif Biberg Kristensen wrote:
> > On Monday 12. April 2010 16.57.38 Rob Sargent wrote:
> >   
> >> Believe me: "ego-ma-pa" will correctly define genealogical relationships
> >> (at least among humans).
> >> 
> >
> > Yes, but a family tree is not a hierarchical tree as defined in database 
> > theory. Believe me: I'm a genealogist.
> >   
> The last sentence is almost like the 'proof by authority' from 36 
> methods of mathematical proof, see e.g. 
> http://jwilson.coe.uga.edu/EMT668/EMAT6680.F99/Challen/proof/proof.html.

Sure, I'm also an autocephalic bishop of no fixed abode 
. Sorry that I forgot to mention 
that.

:D

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/

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