Re: [GENERAL] Setting Variable - (Correct) - Mailing list pgsql-sql

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