mysql - 查找昨天和今天之间的出勤率降幅最大的SQL

我试图编写一个SQL查询来查找昨天到今天出勤率下降幅度最大的成绩。
我们有以下表格：
出勤：日期|学生证|出勤
如果出席是“出席”或“缺席”
和
学生：学生身份证年级
我试过了

SELECT grade, COUNT(a.student_id) as 'present',
CASE
  WHEN date = (SELECT MAX(date) FROM attendance_events) THEN 'today'
  ELSE 'yesterday'
END AS 'day'
FROM attendance a
JOIN students s
ON a.student_id = s.student_id
WHERE attendance = 'present'
GROUP BY 1,3;

但很显然，只有在今天和昨天这两天的情况下，这个方法才有效，而且实际上并没有说明哪个年级的出勤率下降幅度最大，我必须亲自看看哪个年级的出勤率下降幅度最大才能回答这个问题。

最佳答案

使用window函数（MySQL 8.0中提供）可以很直接地找到出勤率下降幅度最大的类：

SELECT date, grade, today, yesterday, today - yesterday diff
FROM
    (SELECT a.date, s.grade, COUNT(a.attendance) today,
            LAG(COUNT(a.attendance), 1) OVER (PARTITION BY grade ORDER BY date) yesterday
     FROM attendance a JOIN students s ON a.student_id = s.student_id
     WHERE a.attendance = 'present'
       AND a.date >= DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)
     GROUP BY a.date, s.grade) last_days
WHERE date = date(now())
ORDER BY diff LIMIT 1;

说明：子查询将计算过去两天每个年级的出勤率。此外，它还使用“滞后窗口”功能包括前一天同一年级的出勤率（按年级和按日期排序）。
外部查询将选择当前日期的行，并计算今天和昨天的出勤率之间的差异。为了找到降幅最大的等级，我们按计算出的差值排序，并使用LIMIT 1返回降幅最大的行（如果没有任何类的降幅，则返回降幅最小的行）。

关于mysql - 查找昨天和今天之间的出勤率降幅最大的SQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58811536/