[DAY 57] SQL 실습

[천재교육] 프로젝트 기반 빅데이터 서비스 개발자 양성 과정 9기
학습일 : 2024.10.10

📕 학습 목록

• 콘텐츠 이용 실태 조사

 

📗 프로젝트 작업 내역

1) 프로젝트 제목

2022년도 초등학생 대상 콘텐츠 이용 실태 조사

 

2) 프로젝트 목표

• 초등학생(3~6학년)을 대상으로 한 교육 콘텐츠의 이용 현황을 분석
• 학년별 콘텐츠 이용 특성 및 콘텐츠 유형(영상 강의, 문제 풀이 등)별 활용도를 분석

3) 사용한 데이터 셋

• 2022년도 초등학생 미디어 콘텐츠 이용 데이터

4) 워크플로우

① 데이터 수집 및 전처리

WITH quarter_data AS (
    SELECT
        QUARTER(e_media."datestamp[active]") AS quarter, 
        e_study.userid, 
        e_study.mcode, 
        e_study.system_learning_time,  
        e_media."datestamp[active]",      
        e_member.grade,                 
        e_test.item_count,        
        e_test.correct_count,  
        e_test.score,                  
        e_content_meta.u_title
    FROM 
        text_biz_dw.e_study AS e_study
        INNER JOIN text_biz_dw.e_media AS e_media ON e_study.userid = e_media.userid
        INNER JOIN text_biz_dw.e_member AS e_member ON e_study.userid = e_member.userid
        INNER JOIN text_biz_dw.e_test AS e_test ON e_study.userid = e_test.userid
        INNER JOIN text_biz_dw.e_content_meta AS e_content_meta ON e_study.mcode = e_content_meta.mcode
    WHERE 
        e_member.grade BETWEEN 3 AND 6  -- 초등학생 3~6학년 데이터만 필터링
)
SELECT * FROM quarter_data;

 

② 데이터 분석 및 시각화

• 학년별 콘텐츠 이용 패턴 분석: 학년별로 콘텐츠 이용 횟수와 평균 학습 시간을 분석하여, 학년에 따른 콘텐츠 활용 정도를 확인

SELECT 
    grade,
    COUNT(DISTINCT mcode) AS content_use_count,
    AVG(system_learning_time) AS avg_learning_time
FROM 
    quarter_data
GROUP BY 
    grade;

• 콘텐츠 유형별 분석: 콘텐츠 유형에 따른 선호도를 분석하여, 학생들이 자주 이용하는 콘텐츠 유형과 평균 점수를 확인

SELECT 
    u_title AS content_type,
    COUNT(*) AS usage_count,
    AVG(score) AS avg_score
FROM 
    quarter_data
GROUP BY 
    u_title
ORDER BY 
    usage_count DESC;

 

5) 프로젝트 결과

• 구현 기능
  • 분기 값(1, 2, 3, 4)에 따라 학습 콘텐츠의 이용 현황을 확인할 수 있음
  • 각 콘텐츠는 이용한 학생 수에 따라 순위가 매겨져, 가장 많은 학생들이 이용한 콘텐츠를 쉽게 파악할 수 있음
  • 콘텐츠 유형별 분석: 영상 강의와 문제 풀이 콘텐츠에 대한 학년별 이용 빈도를 파악하여, 초등학교 3~6학년의 콘텐츠 선호도에 대한 통계 자료 도출

 6) 트러블 슈팅

• 데이터 불일치: 데이터 전처리 과정에서 e_content_meta와 e_media 테이블 간 불일치 문제가 발생하여, 중복된 데이터와 누락된 데이터를 확인 및 보완
• 성능 최적화: 대량의 데이터 처리 시 쿼리 속도 저하 문제가 발생하여, 작은 테이블부터 우선적으로 JOIN하고 필요한 컬럼만 SELECT하는 방식으로 쿼리를 최적화
• SQL 쿼리 복잡성: 여러 테이블을 조인하는 복잡한 SQL 쿼리를 작성하면서 오류가 발생하여, 쿼리를 단계별로 나누어 실행해 가며 오류를 해결

7) 프로젝트를 통해 얻은 역량

• SQL 쿼리 작성을 통해 다수의 테이블을 조인하고 필요한 데이터를 정확히 추출
• 교육 콘텐츠 사용 실태에 대한 분석을 통해 데이터 시각화

 

📙 내일 일정

• 자연어 처리