From: Bryan L Nuse <nuse@uga.edu>
To: Don Parris <parrisdc@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Summing & Grouping in a Hierarchical Structure
Date: Sat, 23 Feb 2013 04:54:05 +0000
Message-ID: <8A586FDE-CC0A-4F26-AD19-1F89C2D9349E@uga.edu> (raw)
In-Reply-To: <CAJ-7yo=6dSoydFCEJo_9LSdUuoA1s68SFSwxzbwGh0oAKk0whw@mail.gmail.com>
References: <CAJ-7yo=cpz0TocY4Qvb5e4=s3FB+VtHT3VV3gbUAz8Uqi7p6pw@mail.gmail.com>
<CAH3i69moWcM9-mxaK5oiiY3o96X0V82QOyjXVDb2Jb-KrDQjog@mail.gmail.com>
<CAJ-7yokc9-KzAd3BRrVPwQywz-f7wkBd2N9Cno-eM9m5gjUAww@mail.gmail.com>
<CAJ-7yo=6dSoydFCEJo_9LSdUuoA1s68SFSwxzbwGh0oAKk0whw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
This works fine:
test_ltree=> SELECT path, trans_amt FROM testcat;
path | trans_amt
-----------------------------------------+-----------
TOP.Transportation.Auto.Fuel | 50.00
TOP.Transportation.Auto.Maintenance | 30.00
TOP.Transportation.Auto.Fuel | 25.00
TOP.Transportation.Bicycle.Gear | 40.00
TOP.Transportation.Bicycle.Gear | 20.00
TOP.Transportation.Fares.Bus | 10.00
TOP.Transportation.Fares.Train | 5.00
TOP.Groceries.Food.Beverages | 30.00
TOP.Groceries.Food.Fruit_Veggies | 40.00
TOP.Groceries.Food.Meat_Fish | 80.00
TOP.Groceries.Food.Grains_Cereals | 30.00
TOP.Groceries.Beverages.Alcohol.Beer | 25.00
TOP.Groceries.Beverages.Alcohol.Spirits | 10.00
TOP.Groceries.Beverages.Alcohol.Wine | 50.00
TOP.Groceries.Beverages.Juice | 45.00
TOP.Groceries.Beverages.Other | 15.00
(16 rows)
So if I want to see:
TOP.Groceries | 240.00
TOP.Transportation | 180.00
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nuse@uga.edu, parrisdc@gmail.com
Subject: Re: Summing & Grouping in a Hierarchical Structure
In-Reply-To: <8A586FDE-CC0A-4F26-AD19-1F89C2D9349E@uga.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox