특정 수업의 일별 출석률을 구하는 쿼리 작성하기

DB table list

  • students
  • teachers
  • classes
  • attendance

문제해결

나의 접근

출석인 값를 구하기 위해 SELECT COUNT(*)를 사용했더니 모든 수업마다 동일하게 전체 "출석" 값들이 찍혀 더 이상 진행을 할 수 없었다.

SELECT
    c.class_name,
    a.attendance_date,
    count(1) AS total_count,
    (SELECT count(1)
         FROM attendance 
         WHERE status = '출석'
      ) AS attended_count
FROM attendance a
INNER JOIN classes c ON a.class_id = c.class_id
GROUP BY c.class_name, a.attendance_date
ORDER BY c.class_name

선생님의 접근

집계 함수를 통해 출석인 값을 합산한다.

SELECT
    c.class_name,
    a.attendance_date,
    count(1) AS total_count,
    sum(CASE
        WHEN a.status = '출석'
        THEN 1
        ELSE 0
    END) as attended_count
FROM attendance a
INNER JOIN classes c ON a.class_id = c.class_id
GROUP BY c.class_name, a.attendance_date
ORDER BY c.class_name

Chat GPT의 제안

막혔던 부분에 대해 GPT에게 물어보니, COUNT()는 조건을 걸 수가 없다며, COUNT() 와 FILTER를 함께 사용하는 방법을 알려줬다.

SELECT
    c.class_name,
    a.attendance_date,
    COUNT(*) AS total_count,
    COUNT(*) FILTER (WHERE a.status = '출석') AS attended_count
FROM attendance a
INNER JOIN classes c ON a.class_id = c.class_id
GROUP BY c.class_name, a.attendance_date
ORDER BY c.class_name

최종 쿼리

최종적으로 출석률을 구하기 위해 위의 쿼리를 감싸는 상위 쿼리를 만들고 x 라는 alias를 부여한다. 상위 쿼리에서는 하위 쿼리들의 컬럼 값을 호출할 수 있다는 것이 신기했다.

SELECT x.class_name, x.attendance_date, x.total_count, x.attended_count, 
       (x.attended_count::float / x.total_count) * 100 AS attended_rate
FROM (
    SELECT
        c.class_name,
        a.attendance_date,
        COUNT(*) AS total_count,
        COUNT(*) FILTER (WHERE a.status = '출석') AS attended_count
    FROM attendance a
    INNER JOIN classes c ON a.class_id = c.class_id
    GROUP BY c.class_name, a.attendance_date
    ORDER BY c.class_name
) x;

댓글 달기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다

위로 스크롤