Re: Counting # of consecutive rows with specified value(s)? - Mailing list pgsql-general
Thank you both for the suggestions. I started playing with the window functions, but found and copied an "islands and gaps" example that didn't need them, and was simpler than I thought. This query seems to do the trick:
SELECT
client_id,
count(*)
FROM
(SELECT
client_id,
attendance_code
FROM recovery_circle_attendance rca
WHERE attended_on >
(SELECT max(attended_on)
FROM recovery_circle_attendance
WHERE client_id=rca.client_id AND attendance_code != 'ABSENT')
) foo
GROUP BY client_id;
It's a fairly small dataset, so at least right now I'm not too worried about performance, but am curious if this is a reasonably well-optimized way to get this info, or if there are any glaring issues or room for improvement in this regard?
Cheers,
Ken
--
On Thu, Jun 7, 2012 at 12:35 PM, François Beausoleil <francois@teksol.info> wrote:
Le 2012-06-06 à 22:20, Ken Tanzer a écrit :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.This is similar to the islands and gaps problem. Search for that on StackOverflow and you'll get it.Bye!François
AGENCY Software
A data system that puts you in control
(253) 245-3801
pgsql-general by date: