Home > mailing lists

Re: Summing & Grouping in a Hierarchical Structure - Mailing list pgsql-sql

From	Don Parris
Subject	Re: Summing & Grouping in a Hierarchical Structure
Date	February 23, 2013 18:50:46
Msg-id	CAJ-7yokLGhpPhYFDPnZ2akQbndBfZ0jEfA+O4LQHDHBcaG-kPg@mail.gmail.com Whole thread Raw
In response to	Re: Summing & Grouping in a Hierarchical Structure (Bryan L Nuse <nuse@uga.edu>)
Responses	Re: Summing & Grouping in a Hierarchical Structure
List	pgsql-sql

Tree view

Hi Bryan,

My current (maybe about to be deprecated now) category table is a recursive table that I join on the line-item table. Your suggestion of using a table for each level of category is probably a bit easier to manage in some respects. However, as you pointed out, it becomes unwieldy when various branches of the category tree have different lengths. One effectively has to have the same number of levels in all branches. If only reality were so refined! :-) That is why I chose the recursive table structure that I gather is fairly common where the depth of a tree is not necessarily "even" or known.

However, I like the view you created - I have not really used views very much so far, so it is good to see this use case for a view. That said, now that I have finally gotten the chance to try ltree, I think I like it a lot. I wonder how portable it is, but it seems to do the trick extremely well. Mind you, I am not necessarily looking to change DBMSes anytime soon, but it is still good to know how to handle recursive/hierarchical structures... ummm... the old-fashioned way(?). ;-)

I think using WITH RECURSIVE would probably do the trick for my recursive category table, but - so far - the WITH construct makes my eyes glaze over. I feel like I will have returned the *one ring* if I get that.

On Fri, Feb 22, 2013 at 11:54 PM, Bryan L Nuse <nuse@uga.edu> wrote:

<SNIP>

Hello Don,

Perhaps I am missing something about what your constraints are, or what you're trying to achieve, but is there any reason you could not use a series of joined tables indicating parent-child relationships? The following example follows that in your previous posts. Note that this approach (as given) will not work if branches stemming from the same node are different lengths. That is, if you have costs associated with "Transportation.Bicycle.Gear", you could not also have a category "Transportation.Bicycle.Gear.Chain_ring". (To add the latter category, you'd have to put costs from the former under something like "Transportation.Bicycle.Gear.General" -- or modify the approach.) However, lengthening the "Alcohol" branches, e.g., by tacking on a level5 table would be easy. Notice that level3 and level4 are not true look-up tables, since they may contain duplicate cat values.

If I'm off base, by all means specify just how.

Regards,
Bryan

--------------------------------------------------

CREATE TABLE level1 (
cat text PRIMARY KEY
);

CREATE TABLE level2 (
cat text PRIMARY KEY,
parent text REFERENCES level1(cat)
);

CREATE TABLE level3 (
cat text,
parent text REFERENCES level2(cat),
cost numeric(6,2)
);

CREATE TABLE level4 (
cat text,
parent text,
cost numeric(6,2)
);

INSERT INTO level1
VALUES ('Transportation'),
('Groceries');

INSERT INTO level2
VALUES ('Auto', 'Transportation'),
('Bicycle', 'Transportation'),
('Fares', 'Transportation'),
('Food', 'Groceries'),
('Beverages', 'Groceries');

INSERT INTO level3
VALUES ('Fuel', 'Auto', 50.00),
('Maintenance', 'Auto', 30.00),
('Fuel', 'Auto', 25.00),
('Gear', 'Bicycle', 40.00),
('Gear', 'Bicycle', 20.00),
('Bus', 'Fares', 10.00),
('Train', 'Fares', 5.00),
('Beverages', 'Food', 30.00),
('Fruit_Veg', 'Food', 40.00),
('Meat_Fish', 'Food', 80.00),
('Grains_Cereals', 'Food', 30.00),
('Alcohol', 'Beverages', NULL),
('Juice', 'Beverages', 45.00),
('Other', 'Beverages', 15.00);

INSERT INTO level4
VALUES ('Beer', 'Alcohol', 25.00),
('Spirits', 'Alcohol', 10.00),
('Wine', 'Alcohol', 50.00);

CREATE VIEW all_cats AS (
SELECT a.cat AS level4,
b.cat AS level3,
c.cat AS level2,
d.cat AS level1,
CASE WHEN a.cost IS NULL THEN 0
WHEN a.cost IS NOT NULL THEN a.cost
END
+ CASE WHEN b.cost IS NULL THEN 0
WHEN b.cost IS NOT NULL THEN b.cost
END AS cost
FROM level4 a
FULL JOIN
level3 b
ON (a.parent = b.cat)
FULL JOIN
level2 c
ON (b.parent = c.cat)
FULL JOIN
level1 d
ON (c.parent = d.cat)
ORDER BY level1, level2, level3, level4
);

SELECT * FROM all_cats;

level4 | level3 | level2 | level1 | cost
  ---------+----------------+-----------+----------------+-------
   Beer | Alcohol | Beverages | Groceries | 25.00
   Spirits | Alcohol | Beverages | Groceries | 10.00
   Wine | Alcohol | Beverages | Groceries | 50.00
    | Juice | Beverages | Groceries | 45.00
    | Other | Beverages | Groceries | 15.00
    | Beverages | Food | Groceries | 30.00
    | Fruit_Veg | Food | Groceries | 40.00
    | Grains_Cereals | Food | Groceries | 30.00
    | Meat_Fish | Food | Groceries | 80.00
        | Fuel | Auto | Transportation | 50.00
       | Fuel | Auto | Transportation | 25.00
        | Maintenance | Auto | Transportation | 30.00
    | Gear | Bicycle | Transportation | 20.00
    | Gear | Bicycle | Transportation | 40.00
    | Bus | Fares | Transportation | 10.00
    | Train | Fares | Transportation | 5.00
  (16 rows)

SELECT level1,
count(cost) AS num_branches,
sum(cost) AS total_cost
FROM all_cats
GROUP BY level1
ORDER BY level1;

    level1 | num_branches | total_cost
  ----------------+--------------+------------
   Groceries | 9 | 325.00
   Transportation | 7 | 180.00
  (2 rows)

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate

http://dcparris.net/

GPG Key ID: F5E179BE

pgsql-sql by date:

From: Misa Simic
Date: 23 February 2013, 15:19:40
Subject: Re: Summing & Grouping in a Hierarchical Structure

From: Bryan L Nuse
Date: 23 February 2013, 19:16:00
Subject: Re: Summing & Grouping in a Hierarchical Structure

Re: Summing & Grouping in a Hierarchical Structure - Mailing list pgsql-sql

Previous

Next