Counting # of consecutive rows with specified value(s)?
От | Ken Tanzer |
---|---|
Тема | Counting # of consecutive rows with specified value(s)? |
Дата | |
Msg-id | CAD3a31WWFsk6t5MaxQDNBt5x5J-M4_cpFRyiLpQ8x18tA+YWsg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Counting # of consecutive rows with specified value(s)?
Re: Counting # of consecutive rows with specified value(s)? |
Список | pgsql-general |
I'm working with an attendance table, where each person gets a record for each day of class, with an attendance code (ABSENT, ATTENDED, ...). I'm trying to figure out how to get the number of consecutive absences a person has. I'm guessing this can be done without writing a function, but I need some help here.
I can currently test whether someone has at least a specified number of consecutive absences with the query below, but it would be better to get the actual number.
As a second question, what about getting the number of consecutive records for a set of values? (e.g., attendance_code IN ('ATTENDED','EXCUSED')
Any ideas or suggestions? Thanks.
Ken
This query checks for 4 consecutive absences:
SELECT client_id,
array(
SELECT attendance_code
FROM attendance
WHERE client_id=enrollment.client_id
ORDER BY attended_on DESC
LIMIT 4
)=array_fill('ABSENT'::varchar,array[4]) AS absent_last_4
FROM enrollment;

AGENCY Software
A data system that puts you in control
(253) 245-3801
В списке pgsql-general по дате отправления: