From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: Adam Sherman <adam@sherman.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Crosstab Confusion
Date: Mon, 1 Feb 2010 16:14:34 -0500
Message-ID: <5ab13581002011314y6e51a9e8v3521cf060396945d@mail.gmail.com> (raw)
In-Reply-To: <26D00B1B-5C3D-4E4B-8350-9C8BE8456F97@sherman.ca>
References: <C5C50165-6162-460C-8E13-A0A76D0B726B@sherman.ca>
<5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com>
<8ACF8F3F-F2DA-4244-886F-6C65F8D1C600@sherman.ca>
<5ab13581002011122i46c0687em160c5cdc800c642f@mail.gmail.com>
<26D00B1B-5C3D-4E4B-8350-9C8BE8456F97@sherman.ca>
On Mon, Feb 1, 2010 at 3:11 PM, Adam Sherman <adam@sherman.ca> wrote:
> Actually, the query I was running is:
>
> SELECT
> cust_id as customer,
> date_trunc(''day'', date) AS day,
> SUM(billed_duration)/60.0::numeric(10,4) AS minutes
>
> billed_duration is an integer. Make sense?
>
> If billed_duration is an integer, sum(billed_duration) will be int or
bigint. I would just define the output columns as bigint (day1 bigint, day2
bigint, etc.). Although, formatting it in the source SQL (I see you are
dividing by 60 and casting to numeric) saves you from having to format 31
output columns.
Right, my list of columns weren't equal to the truncated date. Using your
> suggested query to generate the columns fixed the problem!
>
> Now, is there a way to generate the labels? Otherwise I have to adjust the
> query for th number of days returned.
>
>
Not that I know of, but I have confirmed that as long as the category SQL
matches the output column list, you can have output columns with no data in
them. In order to have your list of column headers match the source SQL, I
would recommend going back to the extract() function I first recommended to
extract the day of month as an integer, and then generate a 31 number series
for your category headers. It would look like this:
SELECT pivot.* FROM crosstab(
'SELECT
cust_id as customer,
extract(day from date)::integer AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE extract(month FROM date) = 1
GROUP BY 1,2
ORDER BY 1,2',
'select day from generate_series(1,31) day'
) pivot (
customer integer,
day1 bigint,
day2 bigint,
(…)
day31 bigint
)
ORDER BY customer;
For half-over months or months with fewer than 31 days, the final columns of
the crosstab should just be blank.
PS: The way I have constructed it, I would avoid using WHERE date >=
''2010-01-01''. If data from February gets into the table, it will aggregate
data from, e.g. Jan 2 and Feb 2 as both being part of "Day 2".
--Lee
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
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: lee.hachadoorian@gmail.com, adam@sherman.ca
Subject: Re: Crosstab Confusion
In-Reply-To: <5ab13581002011314y6e51a9e8v3521cf060396945d@mail.gmail.com>
* 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