[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Hierarchical Query Question (PHP)
From: David Blomstrom <david.blomstrom () gmail ! com>
Date: 2015-10-31 22:49:05
Message-ID: CAA54Z0gZHq0oLyDeRCx4cbgLQC+BDneb5egzfhKENkQSTcfu0A () mail ! gmail ! com
[Download RAW message or body]
Awesome; thanks!
On Sat, Oct 31, 2015 at 7:19 AM, Andy Colson <andy@squeakycode.net> wrote:
> On 10/30/2015 05:10 PM, David Blomstrom wrote:
>
>> Just so I understand what's going on, I can create a lookup table by
>> pasting this code...
>>
>>
> I don't know anything about biology so this data might be laughable, but
> its based on your original question:
>
>
> http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
>
>
> It seemed like the parent_id column was really the rank, and I couldn't
> tell if it should be taxon_rank or parent_rank. Thinking more about the
> problem, I normalized a little, renamed some, and came up with this
> script. It lacks indexes and proper names, etc, etc, because I wanted to
> tackle one problem at a time. Hopefully its a good example.
>
> create table rank (
> id integer,
> descr text
> );
>
> insert into rank values(1, 'Classes');
> insert into rank values(2, 'Orders');
> insert into rank values(3, 'Families');
> insert into rank values(4, 'Genera');
> insert into rank values(5, 'Species');
>
> create table mammals (
> id integer,
> taxon text,
> rank integer,
> parentid integer
> );
>
> insert into mammals values (0, 'Chordata', 1, null);
> insert into mammals values (1, 'Mammalia', 1, 0);
> insert into mammals values (2, 'Carnivora', 2, 1);
> insert into mammals values (3, 'Canidae' , 3, 2);
> insert into mammals values (4, 'Canis' , 4, 3);
> insert into mammals values (5, 'Canis-lupus', 5, 4);
> insert into mammals values (6, 'Canis-latrans', 5, 4);
>
>
> -- This query shows you the basic results. It only
> -- returns the id columns. further queries build on this base one.
> -- (you could this of this query as Order Chordata :-) )
> with recursive heir(id, rank, parentid) as (
> select m.id, 0, m.parentid
> from mammals m
> where taxon = 'Canis'
> union all
> select m.id, m.rank, m.parentid
> from heir
> inner join mammals m on m.parentid = heir.id
> )
> select * from heir;
>
> Results:
> id | rank | parentid
> ----+------+----------
> 4 | 0 | 3
> 5 | 5 | 4
> 6 | 5 | 4
> (3 rows)
>
>
> ----
> -- This looks up the columns for a more meaningful result:
> with recursive heir(id, rank, parentid) as (
> select m.id, 0, m.parentid
> from mammals m
> where taxon = 'Canidae'
> union all
> select m.id, m.rank, m.parentid
> from heir
> inner join mammals m on m.parentid = heir.id
> )
> select m.taxon, r.descr
> from heir
> inner join mammals m on m.id = heir.id
> inner join rank r on heir.rank=r.id
>
> Results:
> taxon | descr
> ---------------+---------
> Canis | Genera
> Canis-lupus | Species
> Canis-latrans | Species
>
>
> ---------
> -- This, finally, groups and counts, like your original question
> with recursive heir(id, rank, parentid) as (
> select m.id, 0, m.parentid
> from mammals m
> where taxon = 'Canidae'
> union all
> select m.id, m.rank, m.parentid
> from heir
> inner join mammals m on m.parentid = heir.id
> )
> select r.id, r.descr, count(*)
> from heir
> inner join mammals m on m.id = heir.id
> inner join rank r on heir.rank=r.id
> group by r.id, r.descr
> order by r.id
>
>
> Results:
> id | descr | count
> ----+---------+-------
> 4 | Genera | 1
> 5 | Species | 2
>
>
>
--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org
[Attachment #3 (text/html)]
<div dir="ltr">Awesome; thanks!</div><div class="gmail_extra"><br><div \
class="gmail_quote">On Sat, Oct 31, 2015 at 7:19 AM, Andy Colson <span \
dir="ltr"><<a href="mailto:andy@squeakycode.net" \
target="_blank">andy@squeakycode.net</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><span class="">On 10/30/2015 05:10 PM, David Blomstrom \
wrote:<br> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"> Just so I understand what's going on, I can create \
a lookup table by pasting this code...<br> <br>
</blockquote>
<br></span>
I don't know anything about biology so this data might be laughable, but its \
based on your original question:<br> <br>
<a href="http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query" \
rel="noreferrer" target="_blank">http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query</a><br>
<br>
<br>
It seemed like the parent_id column was really the rank, and I couldn't tell if \
it should be taxon_rank or parent_rank. Thinking more about the problem, I \
normalized a little, renamed some, and came up with this script. It lacks indexes \
and proper names, etc, etc, because I wanted to tackle one problem at a time. \
Hopefully its a good example.<br> <br>
create table rank (<br>
id integer,<br>
descr text<br>
);<br>
<br>
insert into rank values(1, 'Classes');<br>
insert into rank values(2, 'Orders');<br>
insert into rank values(3, 'Families');<br>
insert into rank values(4, 'Genera');<br>
insert into rank values(5, 'Species');<br>
<br>
create table mammals (<br>
id integer,<br>
taxon text,<br>
rank integer,<br>
parentid integer<br>
);<br>
<br>
insert into mammals values (0, 'Chordata', 1, null);<br>
insert into mammals values (1, 'Mammalia', 1, 0);<br>
insert into mammals values (2, 'Carnivora', 2, 1);<br>
insert into mammals values (3, 'Canidae' , 3, 2);<br>
insert into mammals values (4, 'Canis' , 4, 3);<br>
insert into mammals values (5, 'Canis-lupus', 5, 4);<br>
insert into mammals values (6, 'Canis-latrans', 5, 4);<br>
<br>
<br>
-- This query shows you the basic results. It only<br>
-- returns the id columns. further queries build on this base one.<br>
-- (you could this of this query as Order Chordata :-) )<br>
with recursive heir(id, rank, parentid) as (<br>
select <a href="http://m.id" rel="noreferrer" target="_blank">m.id</a>, \
0, m.parentid<br> from mammals m<br>
where taxon = 'Canis'<br>
union all<br>
select <a href="http://m.id" rel="noreferrer" target="_blank">m.id</a>, \
m.rank, m.parentid<br> from heir<br>
inner join mammals m on m.parentid = <a href="http://heir.id" \
rel="noreferrer" target="_blank">heir.id</a><br> )<br>
select * from heir;<br>
<br>
Results:<br>
id | rank | parentid<br>
----+------+----------<br>
4 | 0 | 3<br>
5 | 5 | 4<br>
6 | 5 | 4<br>
(3 rows)<br>
<br>
<br>
----<br>
-- This looks up the columns for a more meaningful result:<br>
with recursive heir(id, rank, parentid) as (<br>
select <a href="http://m.id" rel="noreferrer" target="_blank">m.id</a>, \
0, m.parentid<br> from mammals m<br>
where taxon = 'Canidae'<br>
union all<br>
select <a href="http://m.id" rel="noreferrer" target="_blank">m.id</a>, \
m.rank, m.parentid<br> from heir<br>
inner join mammals m on m.parentid = <a href="http://heir.id" \
rel="noreferrer" target="_blank">heir.id</a><br> )<br>
select m.taxon, r.descr<br>
from heir<br>
inner join mammals m on <a href="http://m.id" rel="noreferrer" \
target="_blank">m.id</a> = <a href="http://heir.id" rel="noreferrer" \
target="_blank">heir.id</a><br> inner join rank r on heir.rank=<a href="http://r.id" \
rel="noreferrer" target="_blank">r.id</a><br> <br>
Results:<br>
taxon | descr<br>
---------------+---------<br>
Canis | Genera<br>
Canis-lupus | Species<br>
Canis-latrans | Species<br>
<br>
<br>
---------<br>
-- This, finally, groups and counts, like your original question<br>
with recursive heir(id, rank, parentid) as (<br>
select <a href="http://m.id" rel="noreferrer" target="_blank">m.id</a>, \
0, m.parentid<br> from mammals m<br>
where taxon = 'Canidae'<br>
union all<br>
select <a href="http://m.id" rel="noreferrer" target="_blank">m.id</a>, \
m.rank, m.parentid<br> from heir<br>
inner join mammals m on m.parentid = <a href="http://heir.id" \
rel="noreferrer" target="_blank">heir.id</a><br> )<br>
select <a href="http://r.id" rel="noreferrer" target="_blank">r.id</a>, r.descr, \
count(*)<br> from heir<br>
inner join mammals m on <a href="http://m.id" rel="noreferrer" \
target="_blank">m.id</a> = <a href="http://heir.id" rel="noreferrer" \
target="_blank">heir.id</a><br> inner join rank r on heir.rank=<a href="http://r.id" \
rel="noreferrer" target="_blank">r.id</a><br> group by <a href="http://r.id" \
rel="noreferrer" target="_blank">r.id</a>, r.descr<br> order by <a href="http://r.id" \
rel="noreferrer" target="_blank">r.id</a><br> <br>
<br>
Results:<br>
id | descr | count<br>
----+---------+-------<br>
4 | Genera | 1<br>
5 | Species | 2<br>
<br>
<br>
</blockquote></div><br><br clear="all"><div><br></div>-- <br><div \
class="gmail_signature">David Blomstrom<br>Writer & Web Designer (Mac, M$ & \
Linux)<br><a href="http://www.geobop.org" target="_blank">www.geobop.org</a></div> \
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic