Re: Count of records in a row

On Tue, Oct 22, 2013 at 8:41 AM, Rémi Cura <remi.cura@gmail.com> wrote:
> héhé,
> nice snipping Merlin !
>
> I guess you are almost there, output is still wrong  (should be) (
>> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
>> D,1; A,2; D,2; B,1; C,2
> )
>
> I don't understand enough to make the modifications =)

oh right -- whoops.

CREATE TYPE count_same_t AS
(
  item TEXT,
  item_group INT
);

CREATE OR REPLACE FUNCTION count_same_internal(state count_same_t,
item TEXT) RETURNS count_same_t AS
$$
BEGIN
  state.item_group := CASE WHEN item = state.item THEN
state.item_group  ELSE state.item_group + 1 END;
  state.item := item;
  RETURN state;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION count_same_count(state count_same_t) RETURNS INT AS
$$
BEGIN
  RETURN state.item_group;
END;
$$ LANGUAGE PLPGSQL;

CREATE AGGREGATE count_same(TEXT)
(
  SFUNC=count_same_internal,
  STYPE=count_same_t,
  FINALFUNC=count_same_count,
  INITCOND='(,0)'
);

WITH testdata as (select s, chr((floor(random() * 3))::int + 65) as v
from generate_series(1,50) s)
select v, count(*)  from (SELECT s, v, count_same(v) OVER(order by s)
grp from testdata) q
GROUP BY v, grp order by grp;

 v | count
---+-------
 A |     1
 B |     1
 A |     1
 B |     2
 C |     1
 A |     1
 C |     2
 A |     1
 C |     2
 A |     3
 B |     3
 A |     1
 B |     1

/snip

aside: learn the technique.  custom aggregates may seem awkward and
weird at first, but they can be used to solve all sorts of wonderful
problems.

merlin