Re: Summing & Grouping in a Hierarchical Structure

Misa,

Is it possible to use spaces in the ltree path, like so:
TOP.Groceries.Food.Herbs & Spices

Or do the elements of the path have to use underscores and dashes?

On Sat, Feb 23, 2013 at 7:19 AM, Misa Simic <misa(dot)simic(at)gmail(dot)com> wrote:

> Hi Don,
>
> Yes, its better to use it in category table...
>
> Bryan, how many levels there will be - we dont know...
>
> With one table - and ltree is solved all cases...
>
> To add new subcategory user just picks the parent category... So it is
> easy to add chain ring to gear... As category...
>
> In another transaction table is category_id, amount...
>
>
> Don already posted query for sum... In these case just category and
> transaction table should be joined sum amount, group by functions on
> lpath....(depending what is the goal...)
>
> Kind Regards,
>
> Misa
>
>
>
> On Saturday, February 23, 2013, Bryan L Nuse wrote:
>
>>
>>>
>>> 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)
>>
>>

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE