Re: Count of records in a row

On Tue, Oct 22, 2013 at 8:16 AM, Rémi Cura <remi(dot)cura(at)gmail(dot)com> wrote:
> Hey,
> when using a for you implicitly use a cursor (I think),
> so this is the same, use FOR if you like it more.
> It should be *very* fast to write !
>
> As I wrote, relational algebra can handle it, but it is not practically
> feasible :
>
> If you just execute 3 times the query I wrote, you will have your answer.
> It is 3 times because the biggest sequence is A A A A.
> That's the problem, your number of execution depends on the max size of
> sequence.
>
> The problems boils down to this : the answer for one row depends on the
> answer of the previous row, the row before , etc.
>
> You could succeed with ordering by id in a windows function, and in this
> window function order by new_id and putting null to the end, but such nested
> windows functions calls are not allowed.
>
> Nevertheless if you find something purely relational please keep me posted !

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

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

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

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

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

s | v | count_same
----+---+------------
1 | A | 1
2 | B | 1
3 | A | 1
4 | A | 2
5 | C | 1
6 | A | 1
7 | C | 1
8 | C | 2
9 | C | 3
10 | C | 4

/snip

merlin :-D