Re: Summing & Grouping in a Hierarchical Structure

Поиск
Список
Период
Сортировка
От Don Parris
Тема Re: Summing & Grouping in a Hierarchical Structure
Дата
Msg-id CAJ-7yo=BOwc5px8oZoxSO-7GGCm+80U2Bav49juaf6w4hPnq_A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Summing & Grouping in a Hierarchical Structure  (Bryan L Nuse <nuse@uga.edu>)
Список pgsql-sql
Дерево обсуждения
Hi Bryan,


The detailed category listing is dead simple to create: SELECT cat_id, cat_name, sum(amount) FROM category, line-item GROUP BY cat_name, cat_id;
But... as you say, the result is a LOT of numbers over the course of 160-ish categories of all levels.  Which is why it is so important to be able to summarize.  I want to summarize by the highest-levels of the category tree, precisely so as not to get a headache looking at the details. But it is still important to be able to drill down when I want more details.


On Sat, Feb 23, 2013 at 11:15 AM, Bryan L Nuse <nuse@uga.edu> wrote:
>  That said, now that I have finally gotten the chance to try ltree, I think I like it a lot.

Hello Don,

Yes, after looking at ltree --which I had not done before-- I have to agree with Misa that it looks like the right solution for your problem.  That is not to say that "brute force" SQL couldn't provide a workable arrangement; but ltree looks very flexible, especially as it allows you to assign cost values to non-terminal nodes.  If it were me, though, I'd still make use of VIEWs to report results of the workhorse queries:  staring at a list of items like "Transportation.Bicycle.Gear.Chain_ring" sounds like headache.  That's a matter of taste, of course.

Bryan



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

В списке pgsql-sql по дате отправления: