学生出勤场景条件聚合查询SQL解法可行性咨询

涉及两张表结构如下：

• attendance_events：date（考勤日期）| student_id（学生ID）| attendance（出勤状态）
• all_students：student_id（学生ID）| school_id（学校ID）| grade_level（年级）| date_of_birth（出生日期）| hometown（家乡）

问题1：生日当天出勤的学生占比 #

你的原有SQL存在以下问题：

• JOIN条件字段拼写错误：all_students表的学生ID字段是student_id，你写的als.studentid少了下划线，会触发字段不存在报错
• CASE WHEN语句缺少END闭合，语法不合法
• 百分比计算逻辑颠倒：正确计算逻辑是「生日当天出勤的学生数 / 总学生数」，你写反了会得到大于1的异常结果
• 没有处理除数为0的异常，可加NULLIF避免报错
• 补充说明：如果要匹配每年生日的出勤情况，需要匹配日期的月和日而非完整日期，否则只能统计出生日期当天的考勤记录

修正后SQL参考 #

WITH agg_join AS (
    SELECT 
        att.date AS dates, 
        att.attendance AS attendance, 
        als.date_of_birth AS DOB, 
        att.student_id AS student_id
    FROM attendance_events att
    JOIN all_students als ON att.student_id = als.student_id
)
SELECT 
    COUNT(DISTINCT student_id) AS total_students, 
    COUNT(DISTINCT CASE WHEN DOB = dates AND attendance = TRUE THEN student_id END) AS count_of_dobs,
    ROUND(COUNT(DISTINCT CASE WHEN DOB = dates AND attendance = TRUE THEN student_id END) * 100.0 / NULLIF(COUNT(DISTINCT student_id), 0), 2) AS percent_of_student
FROM agg_join

如果需要匹配每年生日的出勤，把DOB = dates替换为EXTRACT(MONTH FROM DOB) = EXTRACT(MONTH FROM dates) AND EXTRACT(DAY FROM DOB) = EXTRACT(DAY FROM dates)即可。

问题2：昨日和今日出勤率下降幅度最大的年级 #

你的原有SQL存在以下问题：

• 同样存在JOIN条件的studentid拼写错误
• 日期判断逻辑语法错误：你写的dates ( 'd', -1, currentdate)不符合标准SQL语法，不能直接这么判断日期等于昨日/今日
• 没有聚合计算出勤率：你直接把CASE WHEN写在SELECT层级后直接GROUP BY年级，没有计算出勤人数占总人数的比例，得到的不是出勤率
• 下降幅度计算逻辑错误：下降幅度应为「昨日出勤率 - 今日出勤率」，你的现有逻辑完全不满足需求

修正后SQL参考 #

WITH grade_attendance AS (
    SELECT 
        als.grade_level AS grade,
        att.date,
        -- 计算当日该年级出勤人数
        SUM(CASE WHEN att.attendance = TRUE THEN 1 ELSE 0 END) AS att_count,
        -- 计算该年级总人数
        COUNT(DISTINCT att.student_id) AS total_stu
    FROM attendance_events att
    JOIN all_students als ON att.student_id = als.student_id
    WHERE att.date IN (CURRENT_DATE, CURRENT_DATE - INTERVAL '1 day') -- 只取昨日和今日的数据
    GROUP BY als.grade_level, att.date
),
attendance_diff AS (
    SELECT 
        grade,
        -- 昨日出勤率
        MAX(CASE WHEN date = CURRENT_DATE - INTERVAL '1 day' THEN att_count * 100.0 / total_stu END) AS yesterday_att_rate,
        -- 今日出勤率
        MAX(CASE WHEN date = CURRENT_DATE THEN att_count * 100.0 / total_stu END) AS today_att_rate
    FROM grade_attendance
    GROUP BY grade
)
SELECT 
    grade,
    yesterday_att_rate,
    today_att_rate,
    (yesterday_att_rate - today_att_rate) AS drop_range
FROM attendance_diff
ORDER BY drop_range DESC
LIMIT 1

内容的提问来源于stack exchange，提问作者Gwillis