Re: Count of records in a row

Поиск
Список
Период
Сортировка
От Rémi Cura
Тема Re: Count of records in a row
Дата
Msg-id CAJvUf_uj6YU5rBWjt-deqeZGQk931Vrb3FWn=WDZJGLF=S-eGg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Count of records in a row  (David Johnston <polobo@yahoo.com>)
Ответы Re: Count of records in a row  (Robert James <srobertjames@gmail.com>)
Список pgsql-general
Дерево обсуждения
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@yahoo.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

В списке pgsql-general по дате отправления:

Предыдущее
От: Luca Ferrari
Дата:
Сообщение: Re: how to get the connected session pointer ( Archive * AH)
Следующее
От: Albe Laurenz
Дата:
Сообщение: Re: A client and server encoding question