From: | Henry Drexler <alonup8tb(at)gmail(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Analytic type functionality, matching patters in a column then increment an integer |
Date: | 2011-10-05 20:02:53 |
Message-ID: | CAAtgU9S5M2uSjAWmk=gKymTjJAFuUvs+35ruUaFa-4vuErFzsA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
that was spot on Richard. Thank you for your time and the solution.
On Wed, Oct 5, 2011 at 3:22 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:
> On 05/10/11 19:29, Henry Drexler wrote:
>
>>
>> and would like to have a column indicate like this:
>>
>> 'evaluation' 'indicator'
>> tf 1
>> tt 1
>> ft 1
>> ff
>> ff
>> tf 2
>> ft 2
>> tf 3
>> tt 3
>> ft 3
>> ff
>>
>
> SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end) over
> (order by id) FROM tfcount ORDER BY id;
>
> id | evaluation | sum
> ----+------------+-----
> 1 | tf | 1
> 2 | tt | 1
> 3 | ft | 1
> 4 | ff | 1
> 5 | ff | 1
> 6 | tf | 2
> 7 | ft | 2
> 8 | tf | 3
> 9 | tt | 3
> 10 | ft | 3
> 11 | ff | 3
> (11 rows)
>
> OK, so that's almost it, but you'd like "ff" to be null. You probably can
> do it with a suitably nested CASE, but it's probably clearer as a sub-query.
>
> SELECT
> id,
> evaluation,
> CASE WHEN evaluation='ff' THEN null::int
> ELSE sum::int END AS section_num
> FROM (
> SELECT
> id,
> evaluation,
> sum(case when evaluation='tf' then 1 else 0 end) over (order by id)
> FROM tfcount
> ) AS rows
> ORDER BY id;
>
> HTH
>
> P.S. - I always find the windowing function syntax confusing, but it's as
> the standards define I believe.
>
> --
> Richard Huxton
> Archonet Ltd
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-10-05 20:41:49 | Re: I/O error on data file, can't run backup |
Previous Message | Richard Huxton | 2011-10-05 19:22:23 | Re: Analytic type functionality, matching patters in a column then increment an integer |