Re: [SQL] Summing Grouping in a Hierarchical Structure
I would use the recursive CTE to gather the hierarchical portion of the data you need and then join that CTE to another table or CTE with the other data you need. I had a situation like this at my job were organization info was in a hierarchal table and I needed to join it to two other tables. I created a CTE with the combined data from the non-hierarchical tables and left joined it to the recursive CTE. If you're having trouble with this, I suggest looking into CTEs and the different types of joins. From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Don Parris Sent: Thursday, February 21, 2013 4:38 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Summing Grouping in a Hierarchical Structure Hi Alexander, I appreciate you taking time to reply to my post. I like the idea of the WITH RECURSIVE query, but... The two examples in the link you offered are not so helpful to me. For example, the initial WITH query shown uses a single table, and I wander how that might apply in my case, where the relevant information is actually found in two tables, one of them a recursive table. The second example, which applies the WITH RECURSIVE clause, is even less so. I wonder if there is a good tutorial somewhere on this that shows some other examples? That might help me catch on a little better. I'll search for that today. On Thu, Feb 14, 2013 at 11:30 PM, Alexander Gataric gata...@usa.net wrote: I would try a recursive http://www.postgresql.org/docs/8.4/static/queries-with.html query to determine the category structure and aggregate as you go. I had a similar problem with a hierarchical structure for an organization structure. Another thing you might try is to create a separate CTE for each category and then aggregate the individual CTEs. From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Don Parris Sent: Thursday, February 14, 2013 7:58 PM To: pgsql-sql@postgresql.org Subject: [SQL] Summing Grouping in a Hierarchical Structure Hi all, I posted to this list some time ago about working with a hierarchical category structure. I had great difficulty with my problem and gave up for a time. I recently returned to it and resolved a big part of it. I have one step left to go, but at least I have solved this part. Here is the original thread (or one of them): http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TL LSW=rmo1v...@mail.gmail.com Here is my recent blog post about how I managed to show my expenses summed and grouped by a mid-level category: http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/ Specifically, I wanted to sum and group expenses according to categories, not just at the bottom tier, but at higher tiers, so as to show more summarized information. A CEO primarily wants to know the sum total for all the business units, yet have the ability to drill down to more detailed levels if something is unusually high or low. In my case, I could see the details, but not the summary. Well now I can summarize by what I refer to as the 2nd-level categories. Anyway, I hope this helps someone, as I have come to appreciate - and I mean really appreciate - the challenge of working with hierarchical structures in a 2-dimensional RDBMS. If anyone sees something I should explain better or in more depth, please let me know. Regards, Don -- D.C. Parris, FMP, Linux+, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate http://dcparris.net/ https://www.xing.com/profile/Don_Parris GPG Key ID: F5E179BE -- D.C. Parris, FMP, Linux+, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate http://dcparris.net/ https://www.xing.com/profile/Don_Parris GPG Key ID: F5E179BE
Re: [SQL] Summing Grouping in a Hierarchical Structure
I would try a recursive http://www.postgresql.org/docs/8.4/static/queries-with.html query to determine the category structure and aggregate as you go. I had a similar problem with a hierarchical structure for an organization structure. Another thing you might try is to create a separate CTE for each category and then aggregate the individual CTEs. From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Don Parris Sent: Thursday, February 14, 2013 7:58 PM To: pgsql-sql@postgresql.org Subject: [SQL] Summing Grouping in a Hierarchical Structure Hi all, I posted to this list some time ago about working with a hierarchical category structure. I had great difficulty with my problem and gave up for a time. I recently returned to it and resolved a big part of it. I have one step left to go, but at least I have solved this part. Here is the original thread (or one of them): http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TL LSW=rmo1v...@mail.gmail.com Here is my recent blog post about how I managed to show my expenses summed and grouped by a mid-level category: http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/ Specifically, I wanted to sum and group expenses according to categories, not just at the bottom tier, but at higher tiers, so as to show more summarized information. A CEO primarily wants to know the sum total for all the business units, yet have the ability to drill down to more detailed levels if something is unusually high or low. In my case, I could see the details, but not the summary. Well now I can summarize by what I refer to as the 2nd-level categories. Anyway, I hope this helps someone, as I have come to appreciate - and I mean really appreciate - the challenge of working with hierarchical structures in a 2-dimensional RDBMS. If anyone sees something I should explain better or in more depth, please let me know. Regards, Don -- D.C. Parris, FMP, Linux+, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate http://dcparris.net/ https://www.xing.com/profile/Don_Parris GPG Key ID: F5E179BE
[SQL] Re: [SQL] need some magic with generate_series()
I would create a common table expression with the series from Filip and left join to the table you need to report on. Sent from my smartphone - Reply message - From: Andreas maps...@gmx.net To: Filip Rembiałkowski plk.zu...@gmail.com Cc: jan zimmek jan.zim...@web.de, pgsql-sql@postgresql.org Subject: [SQL] need some magic with generate_series() Date: Tue, Jan 22, 2013 4:49 pm Thanks Filip, with your help I came a step further. :) Could I do the folowing without using a function? CREATE OR REPLACE FUNCTION month_series ( date ) RETURNS table ( monthnr integer ) AS $BODY$ select to_char ( m, 'MM' )::integer fromgenerate_series ( $1, current_date, '1 month'::interval ) as m $BODY$ LANGUAGE sql STABLE; select project_id, month_series ( createdate ) fromprojects order by 1, 2; Am 22.01.2013 22:52, schrieb Filip Rembiałkowski: or even select m from generate_series( '20121101'::date, '20130101'::date, '1 month'::interval) m; On Tue, Jan 22, 2013 at 3:49 PM, jan zimmek jan.zim...@web.de wrote: hi andreas, this might give you an idea how to generate series of dates (or other datatypes): select g, (current_date + (g||' month')::interval)::date from generate_series(1,12) g; regards jan Am 22.01.2013 um 22:41 schrieb Andreas maps...@gmx.net: Hi I need a series of month numbers like 201212, 201301 MM to join other sources against it. I've got a table that describes projects: projects ( id INT, project TEXT, startdate DATE ) and some others that log events events( project_id INT, createdate DATE, ...) to show some statistics I have to count events and present it as a view with the project name and the month as MM starting with startdate of the projects. My problem is that there probaply arent any events in a month but I still need this line in the output. So somehow I need to have a select that generates: project 7,201211 project 7,201212 project 7,201301 It'd be utterly cool to get this for every project in the projects table with one select. Is there hope? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [SQL] need some magic with generate_series()
Create a CTE with the project code and starting month of the project. Left join to month series CTE. Sent from my smartphone - Reply message - From: Andreas maps...@gmx.net To: Alexander Gataric gata...@usa.net Cc: Filip Rembiałkowski plk.zu...@gmail.com, jan zimmek jan.zim...@web.de, pgsql-sql@postgresql.org Subject: [SQL] need some magic with generate_series() Date: Tue, Jan 22, 2013 7:00 pm The query should work for all projects in the projects table where everyone has a seperate startdate for the series. For the join I need ( project_id, month_nr ). When I tried I couldn't figure out how to feed the startdate into Filip's expression without using the function to encapsulate the generate_series(). The folowing doesn't work: select project_id, (select to_char ( m, 'MM' )::integer fromgenerate_series ( projects.createdate, current_date, '1 month'::interval ) as m ) fromprojects order by 1, 2; Am 23.01.2013 01:08, schrieb Alexander Gataric: I would create a common table expression with the series from Filip and left join to the table you need to report on. - Reply message - From: Andreas maps...@gmx.net To: Filip Rembiałkowski plk.zu...@gmail.com Cc: jan zimmek jan.zim...@web.de, pgsql-sql@postgresql.org Subject: [SQL] need some magic with generate_series() Date: Tue, Jan 22, 2013 4:49 pm Thanks Filip, with your help I came a step further. :) Could I do the folowing without using a function? CREATE OR REPLACE FUNCTION month_series ( date ) RETURNS table ( monthnr integer ) AS $BODY$ select to_char ( m, 'MM' )::integer fromgenerate_series ( $1, current_date, '1 month'::interval )as m $BODY$ LANGUAGE sql STABLE; select project_id, month_series ( createdate ) fromprojects order by 1, 2; Am 22.01.2013 22:52, schrieb Filip Rembiałkowski: or even select m from generate_series( '20121101'::date, '20130101'::date, '1 month'::interval) m; On Tue, Jan 22, 2013 at 3:49 PM, jan zimmek jan.zim...@web.de wrote: hi andreas, this might give you an idea how to generate series of dates (or other datatypes): select g, (current_date + (g||' month')::interval)::date from generate_series(1,12) g; regards jan Am 22.01.2013 um 22:41 schrieb Andreas maps...@gmx.net: Hi I need a series of month numbers like 201212, 201301 MM to join other sources against it. I've got a table that describes projects: projects ( id INT, project TEXT, startdate DATE ) and some others that log events events( project_id INT, createdate DATE, ...) to show some statistics I have to count events and present it as a view with the project name and the month as MM starting with startdate of the projects. My problem is that there probaply arent any events in a month but I still need this line in the output. So somehow I need to have a select that generates: project 7,201211 project 7,201212 project 7,201301 It'd be utterly cool to get this for every project in the projects table with one select. Is there hope? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
