Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4E39D63544E for ; Mon, 1 Feb 2010 15:42:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 43360-03-6 for ; Mon, 1 Feb 2010 19:42:41 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f188.google.com (mail-yx0-f188.google.com [209.85.210.188]) by mail.postgresql.org (Postfix) with ESMTP id DA794640544 for ; Mon, 1 Feb 2010 15:22:51 -0400 (AST) Received: by yxe26 with SMTP id 26so454786yxe.28 for ; Mon, 01 Feb 2010 11:22:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=+aZ8S8cTG1b15zYPSmM7+cRSInzNG/Qk807E1Q4MrHg=; b=TKw+E5LOmvuVnjNHdyMoclDOI/lNmn4B121RYoPh+RKhclA5gMAbw5Uae4GXXm63pq zqEpWXmxiJkdZMejpPnRBBoo7njIS46mE3zdO+iN/LL6ZEqUNG+diKxogAVAi04AIRGc f9RUC7hZerUv8ZK4505upbkQjXJ99tGSiwIMM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=elZCdx1XYcbXPQAF430FnvJSrMi9AQDFzrhR72jACmtI3HRXgM7lbnmWU2LVVmlecA JnLsjBcQZdbKI+027gVUuOfB5P/QFLq4UPOsupe5C0UlcZT5+Y4Ru+c+UdM6sUhI6ALX K4pLMsUJMqdH+X9OAF0UJ96TYbo7+QrcEs7do= MIME-Version: 1.0 Received: by 10.91.50.9 with SMTP id c9mr4259983agk.116.1265052167708; Mon, 01 Feb 2010 11:22:47 -0800 (PST) In-Reply-To: <8ACF8F3F-F2DA-4244-886F-6C65F8D1C600@sherman.ca> References: <5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com> <8ACF8F3F-F2DA-4244-886F-6C65F8D1C600@sherman.ca> Date: Mon, 1 Feb 2010 14:22:47 -0500 Message-ID: <5ab13581002011122i46c0687em160c5cdc800c642f@mail.gmail.com> Subject: Re: Crosstab Confusion From: Lee Hachadoorian To: Adam Sherman Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001485f8612e14245c047e8ee9aa X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/19 X-Sequence-Number: 33618 --001485f8612e14245c047e8ee9aa Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable 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 pos= t 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 ORDE= R 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 *. An= d 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 jus= t 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 header= s from your source table like this: 'SELECT DISTINCT date_trunc(''day'', date) AS day WHERE date >=3D ''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 wrote: > On 2010-02-01, at 11:34 , Lee Hachadoorian wrote: > > 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 selec= t > the days from the month instead of listing them manually. > > > > 3) List of output columns, which follows "pivot" in the text above. Not= e > 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. > > Wow that's an incredibly complete response! > > 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 >=3D '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 >=3D ''2010-01-01'' > GROUP BY 1,2 > ORDER BY 1,2', > 'select * from day_of_month' > ) pivot ( > customer integer, > day1 numeric(10,4), > day2 numeric(10,4), > (=85) > 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 > > --=20 Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center --001485f8612e14245c047e8ee9aa Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable The output column data type (day1, day2, etc.) is supposed to match the val= ue data type. I used numeric(10,4) because that's what your original po= st 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 neve= r enter noninteger values?)

What's the output of the category query by itself? I forgot to incl= ude ORDER BY 1 at the end of the category query. (The order should match th= e 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_mo= nth has only one column, but I would suggest naming it explicitly instead o= f using *. And is the day_of_month column defined in the same format as dat= e_trunc('day', date)? They must successfully pass an "equals&q= uot; test to get included in the right crosstab cell. If a category value i= n the source query doesn't match any value produced by the category que= ry, I think the crosstab function just throws out that row, which could lea= d to a table with the correct structure but all NULLs.

I didn't think about this before, but you can also pull the categor= y headers from your source table like this:

'SELECT DISTINCT dat= e_trunc(''day'', date) AS day WHERE date >=3D ''= 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:
> You basically have three parts:
>
> 1) SELECT query in the form (row header, column header, cell value). I= n this case it is an aggregate query so that =A0you can sum the transaction= s over a given day.
>
> 2) List of column headers. If you want, this can SELECT from another t= able, so you can have a table with rows 1, 2, 3, etc and use it to select t= he 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.

Wow that's an incredibly complete response!

I'm not getting any data in my rows though. This query produces the dat= a:

SELECT
cust_id as customer,
date_trunc('day', date) AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE date >=3D '2010-01-01'
GROUP BY 1,2
ORDER BY 1,2;

Which looks like:

=A0customer | =A0 =A0 =A0 =A0 day =A0 =A0 =A0 =A0 | minutes
----------+---------------------+---------
=A0 =A0 =A0 =A01 | 2010-01-01 00:00:00 | =A0 =A01110
=A0 =A0 =A0 =A01 | 2010-01-03 00:00:00 | =A0 =A0 =A060
=A0 =A0 =A0 =A01 | 2010-01-26 00:00:00 | =A0 23010
=A0 =A0 =A0 =A01 | 2010-01-27 00:00:00 | =A0 17910
=A0 =A0 =A0 =A02 | 2010-01-01 00:00:00 | =A0 =A0 =A060
=A0 =A0 =A0 =A02 | 2010-01-02 00:00:00 | =A0 =A0 =A030
=A0 =A0 =A0 =A02 | 2010-01-04 00:00:00 | =A0 26310
etc, etc, etc

But this query:

-- clients by day
SELECT pivot.* FROM crosstab(
=A0'SELECT
=A0 =A0cust_id as customer,
=A0 =A0date_trunc(''day'', date) AS day,
=A0 =A0SUM(billed_duration) AS minutes
=A0FROM master_cdr
=A0WHERE date >=3D ''2010-01-01''
=A0GROUP BY 1,2
=A0ORDER BY 1,2',
=A0'select * from day_of_month'
) pivot (
=A0customer integer,
=A0day1 numeric(10,4),
=A0day2 numeric(10,4),
(=85)
=A0day31 numeric(10,4)
=A0)
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 Hachado= orian
PhD Student, Geography
Program in Earth & Environmental Sci= ences
CUNY Graduate Center
--001485f8612e14245c047e8ee9aa--