Awesome; thanks! On Sat, Oct 31, 2015 at 7:19 AM, Andy Colson <a...@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
