sql 3일차

sql 주의 점
-- where 절 조건 걸고 count(*)해서 단계마다 확인하는 게 좋음

강사님 풀이

/*
SQL 기본문법 활용 과제2

2022년 분기 별 (총 4분기) 콘텐츠 이용 실태 조사를 진행하고자 합니다.

초등 3학년, 4학년, 5학년, 6학년을 대상으로 하는 콘텐츠 중
영상강의+문제풀이 가 함께 서비스되는 콘텐츠 중

    1) 콘텐츠 별 학습을 진행한 학생 수
    2) 콘텐츠 별 학습을 진행한 학생의 학년 평균
    3) 콘텐츠 별 학습시간
    4) 콘텐츠 별 평가문항 평균 개수, 정답문항 평균 개수, 평가점수 평균

를 확인할 수 있는 SQL 쿼리를 전달해 주시면 감사드리겠습니다.
(분기 별 데이터를 조회 할 수 있는 방법 함께 안내해 주세요~)

* 참조 테이블 : e_content_meta, e_member, e_test, e_media, e_study, e_learning_time_proc
*/

WITH
    user_insert (_year, _quarter) AS (
        VALUES 
            (VARCHAR '2022', INT '1') -- 사용자 입력
    ),
    quarter_start AS (
        SELECT DATE_ADD('quarter', _quarter - 1, CAST(DATE_PARSE(_year, '%Y') AS DATE)) FROM user_insert
    ),
    quarter_end AS (
        SELECT DATE_ADD('day', -1, DATE_ADD('quarter', _quarter, CAST(DATE_PARSE(_year, '%Y') AS DATE))) FROM user_insert
    ),
    content_list AS (-- 2022년 1분기 >> row count : 8,689
        SELECT et.mcode -- distinct mcode count : 8,689
            , em.mcode AS mcode_check -- distinct mcode count : 8,689
        FROM (  SELECT DISTINCT(t.mcode) 
                FROM "text_biz_dw"."e_test" AS t
                    LEFT JOIN "text_biz_dw"."e_content_meta" AS ecm
                    ON t.mcode = ecm.mcode AND CONCAT(t.yyyy, t.mm) = CONCAT(ecm.yyyy, ecm.mm) 
                WHERE 1=1
                    AND t."datestamp[active]" IS NOT NULL
                    AND t."datestamp[active]" BETWEEN (SELECT * FROM quarter_start) AND (SELECT * FROM quarter_end)
                    AND ecm.grade IN (3, 4, 5, 6)) AS et
            INNER JOIN (SELECT DISTINCT(m.mcode)
                        FROM "text_biz_dw"."e_media" AS m
                            LEFT JOIN "text_biz_dw"."e_content_meta" AS ecm
                            ON m.mcode = ecm.mcode AND CONCAT(m.yyyy, m.mm) = CONCAT(ecm.yyyy, ecm.mm) 
                        WHERE 1=1
                            AND m."datestamp[active]" IS NOT NULL
                            AND m."datestamp[active]" BETWEEN (SELECT * FROM quarter_start) AND (SELECT * FROM quarter_end)
                            AND ecm.grade IN (3, 4, 5, 6)) AS em
            ON et.mcode = em.mcode
    ),
    study AS (-- 2022년 1분기 >> distinct mcode count : 8,689, distinct userid count : 115,095
        SELECT es.mcode, es.userid, es.grade, es.caliper_learning_time
        FROM (SELECT mcode FROM content_list) AS cl
            LEFT JOIN ( SELECT s.mcode, s.userid, embr.grade, s.caliper_learning_time
                        FROM "text_biz_dw"."e_study" AS s
                            LEFT JOIN "text_biz_dw"."e_member" AS embr
                            ON s.userid = embr.userid AND CONCAT(s.yyyy, s.mm) = CONCAT(embr.yyyy, embr.mm)
                        WHERE 1=1
                            AND s."datestamp[active]" IS NOT NULL
                            AND s."datestamp[active]" BETWEEN (SELECT * FROM quarter_start) AND (SELECT * FROM quarter_end)) AS es
            ON cl.mcode = es.mcode
    ),
    test AS (-- 2022년 1분기 >> distinct mcode count : 8,689, distinct userid count : 109,947
        SELECT et.mcode, et.userid, et.grade, et.item_count, et.correct_count
        FROM (SELECT mcode FROM content_list) AS cl
            LEFT JOIN ( SELECT t.mcode, t.userid, embr.grade, t.item_count, t.correct_count
                        FROM "text_biz_dw"."e_test" AS t
                            LEFT JOIN "text_biz_dw"."e_member" AS embr
                            ON t.userid = embr.userid AND CONCAT(t.yyyy, t.mm) = CONCAT(embr.yyyy, embr.mm)
                        WHERE 1=1
                            AND t."datestamp[active]" IS NOT NULL
                            AND t."datestamp[active]" BETWEEN (SELECT * FROM quarter_start) AND (SELECT * FROM quarter_end)) AS et
        ON cl.mcode = et.mcode
    ),
    media AS (-- 2022년 1분기 >> distinct mcode count : 8,689, distinct userid count : 106,626
        SELECT em.mcode, em.userid, em.grade
        FROM (SELECT mcode FROM content_list) AS cl
            LEFT JOIN ( SELECT m.mcode, m.userid, embr.grade
                        FROM "text_biz_dw"."e_media" AS m
                            LEFT JOIN "text_biz_dw"."e_member" AS embr
                            ON m.userid = embr.userid AND CONCAT(m.yyyy, m.mm) = CONCAT(embr.yyyy, embr.mm)
                        WHERE 1=1
                            AND m."datestamp[active]" IS NOT NULL
                            AND m."datestamp[active]" BETWEEN (SELECT * FROM quarter_start) AND (SELECT * FROM quarter_end)) AS em
        ON cl.mcode = em.mcode
    ),
    mcode_by_user AS (-- 2022년 1분기 >> distinct mcode count : 8,689, distinct userid count : 115,115
        SELECT mcode, userid, grade FROM study
        UNION
        SELECT mcode, userid, grade FROM test
        UNION 
        SELECT mcode, userid, grade FROM media
    ),
    history AS (
        SELECT mbu.mcode, mbu.userid, mbu.grade AS user_grade
            , s.caliper_learning_time
            , t.item_count, t.correct_count
        FROM mcode_by_user AS mbu
            LEFT JOIN study AS s
            ON mbu.mcode = s.mcode AND mbu.userid = s.userid
            LEFT JOIN test AS t
            ON mbu.mcode = t.mcode AND mbu.userid = t.userid
    )
SELECT *
FROM history
LIMIT 10

파이프라인

• 데이터 처리 단계의 출력이 다음 단계의 입력으로 이어지는 형태로 연결된 구조.
  버티컬 바 - | 앞에 나온 명령을 실행하고 다음 명령을 실행 해.

class에서 enter, exit, init 메소드 뭔지 알아오기
파이썬 모듈 실행 파일을 실행시키는 방법에 대해 준비 해오기
https://hyoje420.tistory.com/45
파이썬 with 문 안에서 커넥터가 될 때만 실행되도록 할 것
파이썬 with 문 공부해오기
with_open 만들어서 등등
with 문법이 시작되면, 끝나면 어떠한 결과가 나오는지

transform.py하는 법?