Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2A711633708 for ; Mon, 1 Feb 2010 17:14:46 -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 59556-02 for ; Mon, 1 Feb 2010 21:14:35 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f218.google.com (mail-gx0-f218.google.com [209.85.217.218]) by mail.postgresql.org (Postfix) with ESMTP id 9635F632190 for ; Mon, 1 Feb 2010 17:14:35 -0400 (AST) Received: by gxk10 with SMTP id 10so4132654gxk.3 for ; Mon, 01 Feb 2010 13:14:34 -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=rHg3bgfc7MFYbeZ8n1O/fa8is2xrgUR5EEPhmgvABPM=; b=DKdlTjMUHcJjcGlPExKQkacQS1bhi8TS7rMPG/CnDGe7IMnudl4uOqkmRDtqAfUCVX dqMYK1MyYIH4Jm8vhuql8qKCdPNMjZ9hqeYy3pAyxLHrJSHURuX+Ib0CYgz6EcrravO1 9rneVCIe6LkyEuUG5HKKnQ4837l8g9FSIE3DU= 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=cqhM4jZzwpcezn+HhvxWG1gYkrku7RL5qcZc6QvS75gY2gf3Ke9QvsYrK5oPEv+oTo hRIwf70IourG9Zz/V2HDRmpeQQwgR/QJGpNHhTxjWttWfV0GqBE8UVGwxMCayn5NPzOl 0uQsvWmLQDX0a+RylD0QR3dO4iO+Za1SX1Ldg= MIME-Version: 1.0 Received: by 10.91.9.3 with SMTP id m3mr4501379agi.35.1265058874569; Mon, 01 Feb 2010 13:14:34 -0800 (PST) In-Reply-To: <26D00B1B-5C3D-4E4B-8350-9C8BE8456F97@sherman.ca> References: <5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com> <8ACF8F3F-F2DA-4244-886F-6C65F8D1C600@sherman.ca> <5ab13581002011122i46c0687em160c5cdc800c642f@mail.gmail.com> <26D00B1B-5C3D-4E4B-8350-9C8BE8456F97@sherman.ca> Date: Mon, 1 Feb 2010 16:14:34 -0500 Message-ID: <5ab13581002011314y6e51a9e8v3521cf060396945d@mail.gmail.com> Subject: Re: Crosstab Confusion From: Lee Hachadoorian To: Adam Sherman Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016364ee3bad6b63a047e907841 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/21 X-Sequence-Number: 33620 --0016364ee3bad6b63a047e907841 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable On Mon, Feb 1, 2010 at 3:11 PM, Adam Sherman 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 th= e > 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 serie= s 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) =3D 1 GROUP BY 1,2 ORDER BY 1,2', 'select day from generate_series(1,31) day' ) pivot ( customer integer, day1 bigint, day2 bigint, (=85) day31 bigint ) ORDER BY customer; For half-over months or months with fewer than 31 days, the final columns o= f the crosstab should just be blank. PS: The way I have constructed it, I would avoid using WHERE date >=3D ''2010-01-01''. If data from February gets into the table, it will aggregat= e data from, e.g. Jan 2 and Feb 2 as both being part of "Day 2". --Lee --=20 Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center --0016364ee3bad6b63a047e907841 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
On Mon, Feb 1, 2010 at 3:11 PM, Adam Sherman <adam@sherman.ca&g= t; wrote:
Actually, the query I was running is:

SELECT
=A0 =A0cust_id as customer,
=A0 =A0date_trunc(''day'', date) AS day,
=A0 =A0SUM(billed_duration)/60.0::numeric(10,4) AS minutes

billed_duration is an integer. Make sense?

If billed_duration is an inte= ger, sum(billed_duration) will be int or bigint. I would just define the ou= tput columns as bigint (day1 bigint, day2 bigint, etc.). Although, formatti= ng it in the source SQL (I see you are dividing by 60 and casting to numeri= c) saves you from having to format 31 output columns.

Right, = my list of columns weren't equal to the truncated date. Using your sugg= ested 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 outp= ut columns with no data in them. In order to have your list of column heade= rs match the source SQL, I would recommend going back to the extract() func= tion I first recommended to extract the day of month as an integer, and the= n generate a 31 number series for your category headers. It would look like= this:

SELECT pivot.* FROM crosstab(
=A0'SELECT
=A0 =A0cust_id as customer,
=A0=A0 extract(day from date)::integer AS day,
=A0 =A0SUM(billed_duration) AS minutes
=A0FROM master_cdr
=A0WHERE extract(month FROM date) =3D 1=20
=A0GROUP BY 1,2
=A0ORDER BY 1,2',
=A0'select day from generate_series(1,31) day'
) pivot (
=A0customer integer,
=A0day1 bigint,
=A0day2 bigint,
(=85)
=A0day31 bigint
=A0)
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: T= he way I have constructed it, I would avoid using WHERE date >=3D '&= #39;2010-01-01''. If data from February gets into the table, it wil= l aggregate data from, e.g. Jan 2 and Feb 2 as both being part of "Day= 2".

--Lee

--
Lee Hachadoorian
PhD Student, Geography
Progr= am in Earth & Environmental Sciences
CUNY Graduate Center
--0016364ee3bad6b63a047e907841--