Re: Crosstab Confusion

On Mon, Feb 1, 2010 at 3:11 PM, Adam Sherman <adam(at)sherman(dot)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