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)?  (David Johnston <polobo@yahoo.com>)
Re: Counting # of consecutive rows with specified value(s)?  (François Beausoleil <francois@teksol.info>)
Список 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 по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Need help in transferring FP to Int64 DateTime
Следующее
От: David Johnston
Дата:
Сообщение: Re: Counting # of consecutive rows with specified value(s)?