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