On Jun 6, 2012, at 22:20, Ken Tanzer <ken.tan...@gmail.com> wrote:
> 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;
>
Quick thought if you have window functions. If you perform a rank over the
appropriate subset of columns (with order by) you might be able to get
consecutive records with matching codes to have the same rank. The in an outer
query you can simply sum with the rank as the group. I am unsure which
function gives you equal values for ties, or whether this approach will even
work, but it seems worth considering at least. Using a min date your result
might be readable as "starting on 9/1, 4 absences; starting on 9/5, 21
attendeds; etc...".
Consider using a frame of 1 prior row to see whether a given date/code is
different than the immediately preceding day. If it is that day qualifies as
the possible beginning of a chain otherwise it has to be added to an existing
chain. You probably need to do this first then use the dates of the applicable
chain starts (and ends maybe...) as fields in the partition mentioned above.
HTH
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
