StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Unfortunately, mysql does not support windows functions. This would be much easier with row_number() or better yet cumulative sums (as supported in Oracle).</p> <p>I will describe the solution. Imagine that you have two additional columns in your table:</p> <ul> <li>ClassSeqNum -- a sequence starting at 1 and incrementing by 1 for each class date.</li> <li>AbsentSeqNum -- a sequence starting a 1 each time a student misses a class and then increments by 1 on each subsequent absence.</li> </ul> <p>The key observation is that the difference between these two values is constant for consecutive absences. Because you are using mysql, you might consider adding these columns to the table. They are big challenging to add in the query, which is why this answer is so long.</p> <p>Given the key observation, the answer to your question is provided by the following query:</p> <pre><code>select studentid, subjectid, absenceid, count(*) as cnt from (select a.*, (ClassSeqNum - AbsentSeqNum) as absenceid from Attendance a ) a group by studentid, subjectid, absenceid having count(*) > 2 </code></pre> <p>(Okay, this gives every sequence of absences for a student for each subject, but I think you can figure out how to whittle this down just to a list of students.)</p> <p>How do you assign the sequence numbers? In mysql, you need to do a self join. So, the following adds the ClassSeqNum:</p> <pre><code>select a.StudentId, a.SubjectId, count(*) as ClassSeqNum from Attendance a join Attendance a1 on a.studentid = a1.studentid and a.SubjectId = a1.Subjectid and a.ClassDate >= s1.classDate group by a.StudentId, a.SubjectId </code></pre> <p>And the following adds the absence sequence number:</p> <pre><code>select a.StudentId, a.SubjectId, count(*) as AbsenceSeqNum from Attendance a join Attendance a1 on a.studentid = a1.studentid and a.SubjectId = a1.Subjectid and a.ClassDate >= a1.classDate where AttendanceStatus = 0 group by a.StudentId, a.SubjectId </code></pre> <p>So the final query looks like:</p> <pre><code>with cs as ( select a.StudentId, a.SubjectId, count(*) as ClassSeqNum from Attendance a join Attendance a1 on a.studentid = a1.studentid and a.SubjectId = a1.Subjectid and a.ClassDate >= s1.classDate group by a.StudentId, a.SubjectId ), a as ( select a.StudentId, a.SubjectId, count(*) as AbsenceSeqNum from Attendance a join Attendance a1 on a.studentid = a1.studentid and a.SubjectId = a1.Subjectid and a.ClassDate >= s1.classDate where AttendanceStatus = 0 group by a.StudentId, a.SubjectId ) select studentid, subjectid, absenceid, count(*) as cnt from (select cs.studentid, cs.subjectid, (cs.ClassSeqNum - a.AbsentSeqNum) as absenceid from cs join a on cs.studentid = a.studentid and cs.subjectid = as.subjectid ) a group by studentid, subjectid, absenceid having count(*) > 2 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload