Re: Crosstab Confusion - Mailing list pgsql-sql
From | Lee Hachadoorian |
---|---|
Subject | Re: Crosstab Confusion |
Date | |
Msg-id | 5ab13581002011122i46c0687em160c5cdc800c642f@mail.gmail.com Whole thread Raw |
In response to | Re: Crosstab Confusion (Adam Sherman <adam@sherman.ca>) |
Responses |
Re: Crosstab Confusion
|
List | pgsql-sql |
The output column data type (day1, day2, etc.) is supposed to match the value data type. I used numeric(10,4) because that's what your original post specified, but the billed_duration column in your most recent post looks like it might be integer? (Or is it defined as numeric(10,4), but you never enter noninteger values?)
What's the output of the category query by itself? I forgot to include ORDER BY 1 at the end of the category query. (The order should match the order of output columns, but I think without it you wouldn't get NULL values, just the values would be in the wrong columns.) I assume day_of_month has only one column, but I would suggest naming it explicitly instead of using *. And is the day_of_month column defined in the same format as date_trunc('day', date)? They must successfully pass an "equals" test to get included in the right crosstab cell. If a category value in the source query doesn't match any value produced by the category query, I think the crosstab function just throws out that row, which could lead to a table with the correct structure but all NULLs.
I didn't think about this before, but you can also pull the category headers from your source table like this:
'SELECT DISTINCT date_trunc(''day'', date) AS day WHERE date >= ''2010-01-01'' ORDER BY 1'
Let me know if this gets you anywhere.
--Lee
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
What's the output of the category query by itself? I forgot to include ORDER BY 1 at the end of the category query. (The order should match the order of output columns, but I think without it you wouldn't get NULL values, just the values would be in the wrong columns.) I assume day_of_month has only one column, but I would suggest naming it explicitly instead of using *. And is the day_of_month column defined in the same format as date_trunc('day', date)? They must successfully pass an "equals" test to get included in the right crosstab cell. If a category value in the source query doesn't match any value produced by the category query, I think the crosstab function just throws out that row, which could lead to a table with the correct structure but all NULLs.
I didn't think about this before, but you can also pull the category headers from your source table like this:
'SELECT DISTINCT date_trunc(''day'', date) AS day WHERE date >= ''2010-01-01'' ORDER BY 1'
Let me know if this gets you anywhere.
--Lee
On Mon, Feb 1, 2010 at 11:52 AM, Adam Sherman <adam@sherman.ca> wrote:
On 2010-02-01, at 11:34 , Lee Hachadoorian wrote:Wow that's an incredibly complete response!
> You basically have three parts:
>
> 1) SELECT query in the form (row header, column header, cell value). In this case it is an aggregate query so that you can sum the transactions over a given day.
>
> 2) List of column headers. If you want, this can SELECT from another table, so you can have a table with rows 1, 2, 3, etc and use it to select the days from the month instead of listing them manually.
>
> 3) List of output columns, which follows "pivot" in the text above. Note that "pivot" is an arbitrary name. You can use foo, bar, or whatever, but that will be the name of the table which must be used to reference the columns in the top SELECT list.
I'm not getting any data in my rows though. This query produces the data:
SELECT
cust_id as customer,
date_trunc('day', date) AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE date >= '2010-01-01'
GROUP BY 1,2
ORDER BY 1,2;
Which looks like:
customer | day | minutes
----------+---------------------+---------
1 | 2010-01-01 00:00:00 | 1110
1 | 2010-01-03 00:00:00 | 60
1 | 2010-01-26 00:00:00 | 23010
1 | 2010-01-27 00:00:00 | 17910
2 | 2010-01-01 00:00:00 | 60
2 | 2010-01-02 00:00:00 | 30
2 | 2010-01-04 00:00:00 | 26310
etc, etc, etc
But this query:
-- clients by day
SELECT pivot.* FROM crosstab(
'SELECT
cust_id as customer,
date_trunc(''day'', date) AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE date >= ''2010-01-01''GROUP BY 1,2'select * from day_of_month'
ORDER BY 1,2',
) pivot (
customer integer,day1 numeric(10,4),(…)
day2 numeric(10,4),
day31 numeric(10,4)
)
ORDER BY customer;
Gives me a table that looks right but all values are null for the days.
Something simple maybe?
Thanks,
A.
--
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400
"When the burning husks of your startups warm the last of your bones, remember I told you so." - Zed
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center