Re: Count of records in a row

[Rémi Cura <remi.cura@xxxxxxxxx>]

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@xxxxxxxxx>

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general