From: Michael Glaesemann <grzm@seespotcode.net>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
Date: Mon, 18 Jun 2007 13:19:17 -0500
Message-ID: <AE266BF2-A86B-46BB-AB4B-646E598E2EFD@seespotcode.net> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, ranieri.oliveira@terra.com.br, pgsql-general@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
In-Reply-To: <AE266BF2-A86B-46BB-AB4B-646E598E2EFD@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox