Re: Analytic type functionality, matching patters in a column then increment an integer

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