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

From Misa Simic
Subject Re: Summing & Grouping in a Hierarchical Structure
Date
Msg-id CAH3i69mPNRJLi2n=KGp8owuGdOyfpv6U08LB7=j-sNjEUhDztA@mail.gmail.com
Whole thread Raw
In response to Re: Summing & Grouping in a Hierarchical Structure  (Don Parris <parrisdc@gmail.com>)
List pgsql-sql
Hi Don,

To be honest with you - i dont know - but think it is not...

We use it to solve the problem with hierarchy relations - but it is nowhere "visible" to users in the app...

Our internal rule is to use ids in ltree structure to solve many others problems, actually to easy get, actual category info... From any point...

So if needed, it is easy from 1.2.3.4, get: TOP.Groceries.Food.Herbs & Spices if needed... Each of them are actually category names in the table...

Kind regards,

Misa


On Saturday, February 23, 2013, Don Parris wrote:
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.simic@gmail.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)
);

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE

pgsql-sql by date:

Previous
From: Ashwin Jayaprakash
Date:
Subject: Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
Next
From: Ben Morrow
Date:
Subject: Re: Summing & Grouping in a Hierarchical Structure