In-Reply-To: <4676BCDF.5050700@terra.com.br>
References: <467697ED.8030506@terra.com.br>
	<108B61B7-DB9A-4FE3-832C-323866956DFE@seespotcode.net>
	<7DA5333D-3437-4033-A222-128E541A04C2@seespotcode.net>
	<4676B38A.303@terra.com.br>
	<2D977EAC-654B-44F5-AB1F-336031F6466D@seespotcode.net>
	<4676BCDF.5050700@terra.com.br>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <AE266BF2-A86B-46BB-AB4B-646E598E2EFD@seespotcode.net>
Cc: pgsql-general@postgresql.org,
 pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: [SQL] Setting Variable - (Correct)
Date: Mon, 18 Jun 2007 13:19:17 -0500
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>


On Jun 18, 2007, at 12:11 , Ranieri Mazili wrote:

> Look, I did a UNION, exist other way to do it better?

Considering your aggregates are different, you shouldn't really union  
them. In the upper query of the union, you've got production_period  
(which is actually a date that represents the beginning of a month- 
long period), id_production_area, and an aggregate using sum as  
total_production_hours. In the lower query of the union, you've got  
production_period (a date representing the beginning of a year-long  
period), id_production_area, and a aggregate representing monthly  
average hours as total_production_hours. These are logically two  
separate results, and should not be unioned. It's easier to see if  
the columns are renamed appropriately:

SELECT production_month, id_production_area, monthly_production_hours
...
UNION
SELECT production_year, id_production_area,  
monthly_average_production_hours
...

You can see that they're different. One consequence of this is that  
for the query you have, you'll have more than on column with a date  
'YYYY-01-01': is this a production_month or a production_year?

I guess I'd split it into two queries (and rename the columns). You  
might also be able to join the to queries so you get a result  
something like

SELECT production_year
	, production_month
	, id_production_area
	, monthly_production_hours
	, monthly_average_production_hours

Each month for the entire three-year range would be listed, and the  
production_year and monthly_production_hours would be repeated for  
each month of the year.

Yet another way to do it would be to create a view for  
production_month, id_production_area, and monthly_production_hours  
(with no restriction on date range), and then call the view twice:

once for the monthly figures for a year:

SELECT production_month, id_production_area, monthly_production_hours
FROM monthly_production
WHERE production_month BETWEEN date_trunc('month', ? - interval '1  
year') AND date_trunc('month', ?);

and once more for the yearly figures for the past three:

SELECT date_trunc('year', production_month) as production_year
	, sum(production_month) as number_of_months -- so you can see if you  
have a full twelve-months
	, id_production_area
	, average(monthly_production_hours)
FROM monthly_production
WHERE date_trunc('year', production_month)
GROUP BY -- left as an exercise for the reader :)

Note that if you don't have any lost hours for a given year, you may  
have some surprising results. You might want to look at  
generate_series or some other solution for generating a full list of  
months for you to join against.

By the way, if you're going to do a lot of the same date_trunc work,  
you might want to create some functions that do this for you, e.g.  
(untested),

CREATE FUNCTION trunc_year(date)
RETURNS date
IMMUTABLE
LANGUAGE sql AS $_$
select date_trunc('year', $1)::date
$_$;

CREATE FUNCTION truc_years_ago(date, integer)
RETURNS date
IMMUTABLE
LANGUAGE sql AS $_$
select date_trunc('year', $1 - $2 * INTERVAL '1 year')::date
$_$:

Note that foo::date is PostgreSQL-specific for CAST(foo AS DATE).

Anyway, hope that gives you something to think about.

Michael Glaesemann
grzm seespotcode net