SQL 2일차

윈도우 함수

• 순번과 순위를 구할 때, GROUP BY 가 아니라 어떠한 기준을 가지고 잘라내어서 활용을 해야할 때

• WHERE 조건으로 자르는게 아니라 잘라낸 구간 내에서 순위를 부여 데이터 프레임을 그대로 유지하지만 안에서 특정 기준이 되는 구간을 나누어서 조작을 가할 때. 구간안에 행 값을 순서대로 더함 데이터 쉐입을 바꾸지 않은 한.

• row의 모든 값이 똑같아 pk속성이 사라질 때, 테이블 내에서 유니크한 것이 깨질 때

• pk속성을 덜어내고 안에있는 다른 데이터만 가지고 distinct가 불가능할 때 순번을 매길 때 사용

-partion by 를 이용해서 구간안에서의 순번 재정렬

하루중 학습을 한 순서

• 행과 행 간을 비교, 연산, 정의하기 위한 함수
  learning seq을 순서가 바뀐 값을 부여하겠다.

row number (순번 부여)

learning seq와 row number가 다를 수 있음
유저 아이디가 동일한 애들끼리 하나로 묶어 구분 짓겠다.
(유저 아이디 중복 많으니) partion by 해서 유저
window= row단위로 구간을 나누는 것을 하나의 윈도우로 만들고 덩어리 안에서
시작시간이 빠른 순서로, 시작시간이 동일한 경우 종료시간이 빠른 순서로.
row number을 활용해서 번호를 순서매김. 데이터 자체가 분산적재된 경우 살짝 다를 수 있음
데이터를 잘라내었는데 중복 발생. 일부로 row 넘버해서 row 넘버가 1인것만 가져오기.

SELECT userid, mcode, start_timestamp, end_timestamp, learning_time, learning_seq 
    , ROW_NUMBER() OVER (PARTITION BY userid ORDER BY start_timestamp ASC) AS _ROW_NUMBER
FROM "text_biz_dw"."e_learning_time_proc" 
WHERE 1=1
    AND CONCAT(yyyy,mm,dd) = '20221201'
    AND userid = '0002c2cb-6c1f-4fe7-bcee-272fef13544a'
ORDER BY userid ASC, learning_seq ASC
LIMIT 1000;

RANK, DENSE_RANK

ROW_NUMBER의 경우 내부적인 인덱스 사용 되서 중복 x
RANK의 경우 공통의 값이 있으면 12번이 중복되게 나옴 같은 값으로 사용 그리고 뛰어넘음 (동석차 배재)
DENSE_RANK는 중복값은 중복으로 처리하고 그 다음 값이 계속 나옴(동석차 고려)

SELECT userid, mcode, start_timestamp, end_timestamp, learning_time, learning_seq 
    , ROW_NUMBER() OVER (PARTITION BY userid ORDER BY start_timestamp ASC) AS _ROW_NUMBER
    , RANK() OVER (PARTITION BY userid ORDER BY start_timestamp ASC) AS _RANK
    , DENSE_RANK() OVER (PARTITION BY userid ORDER BY start_timestamp ASC) AS _DENSE_RANK
FROM "text_biz_dw"."e_learning_time_proc" 
WHERE 1=1
    AND CONCAT(yyyy,mm,dd) = '20221201'
    AND userid = '0002c2cb-6c1f-4fe7-bcee-272fef13544a'
ORDER BY userid ASC, learning_seq ASC
LIMIT 1000;

PERCENT_RANK()

• 백분위 현재 순번의 누적치의 값 MAX값을 1
• 4분위 수 계산 MEDIAN MEAN 비교 할때 지점 끊어낼 때 사용

SUM

유저 아이디가 똑같고 START TIME이 빠른 순서
누적 시간이 나옴
PERCENT_RANK와 같이 쓰면 중간쯤 왔을 때(0.5) 누적 학습 시간을 알 수 있음

SELECT userid, mcode, start_timestamp, end_timestamp, learning_time, learning_seq 
    , PERCENT_RANK() OVER (PARTITION BY userid ORDER BY start_timestamp ASC) AS _PERCENT_RANK
    , SUM(learning_time) OVER (PARTITION BY userid ORDER BY start_timestamp ASC) AS _SUM
FROM "text_biz_dw"."e_learning_time_proc" 
WHERE 1=1
    AND CONCAT(yyyy,mm,dd) = '20221201'
    AND userid = '0002c2cb-6c1f-4fe7-bcee-272fef13544a'
ORDER BY userid ASC, learning_seq ASC
LIMIT 1000;

깨알 TYPE OF

SELECT TYPEOF('123'), TYPEOF(123), TYPEOF(123.0);

두 개 이상의 데이터를 조작하는 방법!

• (PANDAS 데이터 붙이는 법 merge concat join)
  두가지 방법

• JOIN (pandas의 merge라고 생각하면 됨)
• UNION (pandas의 행 방향으로 붙이는 concat과 유사 )

FROM table_name AS a
    JOIN table_name AS b
    ON a.column_name = b.column_name

join의 방법들

cross join

• 두 개 테이블의 곱 연산을 할 때 집합
  3월 1일부터 31일까지 로우 날짜의 10만명의 유저가 각각 유저의 아이디 목록과 날짜 목록
  각각 곱연산 이용해서 정렬

기준의 테이블에 여러개를 붙이기 위해서 right join 사용

UNION

• 데이터 프레임 A와 B를 아래 위로 붙임
  데이터 프레임 1,a와 2,B를 붙임 (pandas concat과 유사)
  

UNION (DISTINCT)

중복제거가 디폴트로 들어가 있음

UNION ALL

중복 제거하지 않고 쿼리의 결과를 합한다.

UNION 처리 형식

WITH 활용하기

• 이 코드가 동작하는 동안 활성화 되어있다.
• 임시로 이러한 테이블로 선언해서 놓고 보겠다

WITH 
    table1 AS (
        SELECT userid FROM "text_biz_dw"."e_member" WHERE CONCAT(yyyy, mm) = '202201' AND memberstatus_codename = '학습생(정)'
    ),
    table2 AS (
        SELECT userid FROM "text_biz_dw"."e_member" WHERE CONCAT(yyyy, mm) = '202212' AND memberstatus_codename = '학습생(정)'
    ),
    table3 AS (
        SELECT * FROM "text_biz_dm"."learning_analytics_user" WHERE CONCAT(yyyy, mm) = (SELECT * FROM ym)
    )
SELECT *
FROM table1;

# 1월과 12월에 정회원인 사람들 모음

서브쿼리와 with

관계형 데이터 베이스

• join을 통해 row를 붙여나갈 수 있음.

SELECT *
FROM ( SELECT userid, mcode, "datestamp[active]", latest_completed_yn, score, item_count, correct_count, startdate, credate
    FROM "text_biz_dw"."e_test" 
    WHERE CONCAT(yyyy,mm) ='202212'
    ) AS et
    LEFT JOIN(SELECT userid, gender, membertype_codename, grade_codename,                       memberstatus_codename,memberstatus_change, city, district
                FROM "text_biz_dw"."e_member"
                WHERE CONCAT(yyyy,mm)= '202212'
                )AS em
        ON et.userid = em.userid -- etest 오른쪽에 멤버가 행 방향으로 붙음
    LEFT JOIN(SELECT mcode, leccode ,l_title, u_title, l_type, menu_name,                       subject_name, grade AS content_grade, term
                FROM "text_biz_dw"."e_content_meta" 
                WHERE CONCAT(yyyy,mm)='202212'
                )AS ecm
        ON et.mcode = ecm.mcode
LIMIT 10;

UNION

SELECT '202210' AS "YYYYMM"
    , memberstatus_codename, gender, COUNT(*) AS cnt
FROM "text_biz_dw"."e_member"
WHERE 1=1
    AND CONCAT(yyyy, mm)='202210'
    AND memberstatus_codename ='학습생(정)'
GROUP BY memberstatus_codename, gender


UNION -- 마지막 아웃풋으로 던지는 데이터가 깔끔하게 나왔고, 베이스가 되는 테이블이 반드시 포함되는 경우 사용. 2022년 10월은 반드시 포함됨 과거에 밀어 넣으면서 붙일 때 사용, 동일한 형태의 데이터 프레임을 CONCAT 한다 위아래 방향으로

SELECT '202212' AS "YYYYMM"
    , memberstatus_codename, gender, COUNT(*) AS cnt
FROM "text_biz_dw"."e_member"
WHERE 1=1
    AND CONCAT(yyyy, mm)='202212'
    AND memberstatus_codename ='학습생(정)'
GROUP BY memberstatus_codename, gender


ORDER BY YYYYMM DESC, gender ASC --먼저 SELECT해서 만들어 놨기에 이렇게 써도 ORDER BY가능 

WITH

--별칭이 먼저 나오고 AS 뒤에 뭔지 알려줌 
WITH 
    ym AS ( 
        VALUES (CHAR '202212')
    ),
    ymd AS (
        VALUES (CHAR '20221201')
    ),
    table1 AS (
        SELECT userid FROM "text_biz_dw"."e_member" WHERE CONCAT(yyyy, mm) = '202201' AND memberstatus_codename = '학습생(정)'
    ),
    table2 AS (
        SELECT userid FROM "text_biz_dw"."e_member" WHERE CONCAT(yyyy, mm) = '202212' AND memberstatus_codename = '학습생(정)'
    ),
    table3 AS (
        SELECT * FROM "text_biz_dm"."learning_analytics_user" WHERE CONCAT(yyyy, mm) = (SELECT * FROM ym)
    ) -- 202212라는 스트링 값을 가지고 있는 ym 선언된 변수
SELECT *
FROM table1;

--with : where 조건 들어가는 파라미터를 변수형태로 선언하여 가변적으로 만들어서 넣을수 있고 table을 만들 수 있음

BI툴

POWER BI, 테블로 aws quicksight

문제 1번

WITH 
    table1 AS (
        SELECT memberstatus_codename, COUNT(*) AS cnt, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202210'
    AND memberstatus_change LIKE '-,%'
GROUP BY memberstatus_codename
    ),
    table2 AS (
        SELECT COUNT(*) AS s_cnt, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202210'
    AND memberstatus_change LIKE '-,%'
    )

SELECT '202210' AS "YYYYMM", table1.memberstatus_codename, (cast(table1.cnt as double) / cast(table2.s_cnt as double))*100 AS ratio
FROM table1
JOIN table2
ON table1.YYYYMM=table2.YYYYMM;

문제 1-2,3번

SELECT memberstatus_codename, sum(caliper_learning_time)
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
where a.yyyy = '2022'
and a.mm='10'
AND memberstatus_change LIKE '-,%'
group by memberstatus_codename
LIMIT 10;

SELECT memberstatus_codename, sum(video_pause_count) as sum_pause, avg(video_pause_count) as mean__pause, sum(video_jump_count) as sum_jump, avg(video_jump_count) as mean_jump
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
where a.yyyy = '2022'
and a.mm='10'
AND memberstatus_change LIKE '-,%'
group by memberstatus_codename
LIMIT 10;

문제 1- 4번

WITH 
    table1 AS (
        SELECT memberstatus_codename,sum(b.item_count) as item
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
where a.yyyy = '2022'
and a.mm='10'
AND memberstatus_change LIKE '-,%'
group by memberstatus_codename
    ),
    table2 AS (
        SELECT memberstatus_codename,sum(b.correct_count) as correct, avg(b.score) as mean_score
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
where a.yyyy = '2022'
and a.mm='10'
AND memberstatus_change LIKE '-,%'
group by memberstatus_codename
    )

SELECT table1.memberstatus_codename, mean_score, (cast(table2.correct as double) / cast(table1.item as double))*100 AS ratio
FROM table1
JOIN table2
ON table1.memberstatus_codename=table2.memberstatus_codename;

문제 1-5번

SELECT memberstatus_codename,sum(correct_count) as sum_wrong_correct
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
where a.yyyy = '2022'
and a.mm='10'
AND memberstatus_change LIKE '-,%'
group by memberstatus_codename