Re: Count of records in a row

Hey,
I tried something very similar to compute generalized union of numeric
range (which was innapropriate, anyway).

My conclusion were that it's not possible using windows function as you
need either a memory (windows function are not allowed in update) or
iterations to propagate information (windows functions cannot be nested).
There may be a theoretical possibility of success using windows function
and recursive CTE.
(see end of this mail for a taste to this kind of solution)

But it is immensely easier and sometimes mandatory to use instead
a plpgsql function using cursor (or cursors).

It would be something like that in plpgsql :

cursor on table of letter ordered
accum = 0;
loop on rows of table ordered

if letter = previous letter, new_id = accum
else accum ++ ; new_id = accum

old letter = new_letter
new letter = next letter;

end of loop,

Cheers,
Rémi-C

Piste for solving it with windows function and recursive CTE :

--defining test env :

drop table if exists test_grouping;
create table test_grouping
(id serial
,letter text
--,previous_letter text
,for_computation int
--,previous_for_computation INT
);
INSERT INTO test_grouping (letter) VALUEs
('A'),
('A'),('A'),('A'),('B'),('C'),('A'),('D'),('A'),('A'),('D'),('D'),('B'),('C'),('C'
);
UPDATE test_grouping set for_computation=id;

SELECT *
FROM test_grouping;

--this query gives the result, but it needs to be iterated using a
recursive CTE (not done here):
--you can do it manually by executing it several times

WITH computation AS (
SELECT id
, letter
, for_computation,
lag( letter, 1,NULL) over w,
CASE
WHEN lag( letter, 1,NULL) over w = letter
THEN
lag( for_computation, 1,NULL) over w
--NULL
ELSE
id
END AS new_id,
(SELECT count(*) over ())
FROM test_grouping AS tg
WINDOW w AS (ORDER BY id ASC ROWS 1 preceding)
ORDER BY tg.id ASC
)
UPDATE test_grouping AS tg SET for_computation = new_id FROM computation AS
c WHERE tg.id=c.id
RETURNING tg.*

2013/10/22 David Johnston <polobo(at)yahoo(dot)com>

> Robert James wrote
> > I have a table of event_id, event_time. Many times, several events
> > happen in a row. I'd like a query which replaces all of those events
> > with a single record, showing the count.
> >
> > 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
> >
> > How can I do that?
>
> <Theory Only>
>
> Window functions are going to be your friend.
>
> To solve the grouping problem I would assign the first row's value a group
> value of zero (0). Using the "lag(...)" window function and an
> appropriately defined frame you conditionally add one (1) to the prior
> row's
> group value if the value of lag(1) does not equal the current row's value.
> The result should be a new column where all sequential duplicates share the
> same group number.
>
> Distinct will give you a lookup relation for which letter belongs to which
> group
> Group By + Count on the group will give you counts
>
> Use string_agg(...) to condense the above into single row/column
>
> HTH
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Count-of-records-in-a-row-tp5775363p5775365.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>