[스파르타 내일배움캠프 / 데이터 분석 트랙] TIL(Today I Learned)_2주차_24.12.03

Today's Goals

1. SQL 쿼리 작성 익숙해지기 - 코드카타 레벨3, SQL 라이브 강의

2. SQL로 데이터 기초 분석 하기(2주차_Team Project) - 데이터 분석

 

 

 

1. SQL 쿼리 작성 익숙해지기

 

코드카타 SQL

---

[조건을 필터링 하는 다양한 방법]

• 조건 : 완료된 중고거래, 총 거래 금액 70만원 이상인 회원
• WHERE절에서 먼저 하나의 조건을 필터링해도 되고, GROUP BY 후 HAVING 절에서 모든 조건을 필터링 해도 됨
• 가능하면 WHERE 절에서 필터링 할 수 있는 것을 먼저 해야 로직을 간단하게 생각할 수 있는 듯 함

-- WHERE 사용
SELECT b.USER_ID, b.NICKNAME, SUM(a.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD a JOIN USED_GOODS_USER b ON a.WRITER_ID = b.USER_ID
WHERE a.STATUS = 'DONE'
GROUP BY b.USER_ID
HAVING SUM(a.PRICE) >= 700000
ORDER BY TOTAL_SALES ASC;

-- WHERE 사용 x
SELECT b.USER_ID, b.NICKNAME, SUM(a.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD a JOIN USED_GOODS_USER b ON a.WRITER_ID = b.USER_ID
GROUP BY b.USER_ID, a.STATUS
HAVING a.STATUS = 'DONE' and SUM(a.PRICE) >= 700000
ORDER BY TOTAL_SALES ASC;

 

 

[범주화, CASE WHEN은 효율적이지 않다]

• 가격대별 상품의 개수 구하기, 가격을 만원 단위로 그룹화 해야 함 > 범주화에서 제일 먼저 떠오르는 것 CASE WHEN

SELECT CASE WHEN PRICE>0 AND PRICE<10000 THEN 0
            WHEN PRICE>=10000 AND PRICE<20000 THEN 10000
            WHEN PRICE>=20000 AND PRICE<30000 THEN 20000
            WHEN PRICE>=30000 AND PRICE<40000 THEN 30000
            WHEN PRICE>=40000 AND PRICE<50000 THEN 40000
            WHEN PRICE>=50000 AND PRICE<60000 THEN 50000
            WHEN PRICE>=60000 AND PRICE<70000 THEN 60000
            WHEN PRICE>=70000 AND PRICE<80000 THEN 70000
            WHEN PRICE>=80000 AND PRICE<90000 THEN 80000
            END AS PRICE_GROUP,
        COUNT(*)
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

• 만약 가격의 범위가 N십만원, N백만원 이었다면, 엄청나게 비효율적인 방법
• 만원단위 앞자리만 추출해서 그룹화 하는 방법은 없을까?
• 뒤에서 N번째 자리를 읽어 만원 단위의 범위를 만드는 것은 십만원, 백만원 단위에 적용할 수 없기 때문에 비효율
• 끝의 4자리를 버리는 방법을 이용 해야 함(DIV / FLOOR)

-- DIV 사용(나눗셈에서 몫만 남기는 것)
SELECT 
    (PRICE DIV 10000) * 10000 AS PRICE_GROUP, 
    COUNT(*) AS COUNT
FROM PRODUCT
GROUP BY (PRICE DIV 10000) * 10000
ORDER BY PRICE_GROUP;

-- FLOOR 사용(소수점 이하를 버리는 것)
SELECT 
    FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP, 
    COUNT(*) AS COUNT
FROM PRODUCT
GROUP BY FLOOR(PRICE / 10000) * 10000
ORDER BY PRICE_GROUP;

• 컬럼명 DIV 숫자 : 컬럼값을 숫자로 나누었을 때 몫을 반환
• FLOOR(컬럼명 / 숫자) : 컬럼값을 숫자로 나눈 값에서 소수점 이하를 버림

 

 

 

 

SQL 라이브 강의

---

[윈도우 함수는 도대체 뭘까]

-- 윈도우 함수 기본문법
SELECT 윈도우함수(컬럼1) OVER (
    [PARTITION BY 컬럼2] -- 그룹화
    [ORDER BY 컬럼3 ASC|DESC] -- 정렬
    [ROWS|RANGE BETWEEN A AND B] -- 계산 범위
) AS 결과
FROM 테이블;

* []는 생략 가능하다는 의미

• 특정 컬럼 값의 종류별로 파티션이 생성되고, 파티션별 정렬 후, 조건에 맞는 함수가 적용됨
• 계산 범위를 지정하는 rows는 생략하는 경우 디폴트 값으로 설정되는데, 파티션의 처음 값부터 현재 행까지를 뜻함
• 기존 집계 함수는 그룹별로 하나의 값(A 그룹의 avg는 x야) 반환, 윈도우 함수는 각 행별로(A 파티션에서 n번째 행까지의 avg는 x고 n+1번째 행까지의 avg는 y고.....) 반환
• 윈도우 함수는 원본 데이터를 유지하면서 새로운 컬럼에 집계 내용을 넣는 것으로, 원본 값과 집계 값을 함께 확인 가능

 

 

[윈도우 함수 종류]

• RANK() : 순위, 동일한 값에 같은 순위 부여하고 이후 순위 건너뜀(1 2 3 3 5)
• DENSE_RANK() : 순위, 동일한 값에 같은 순위 부여하고 이후 순서 건너뛰지 않음(1 2 3 3 4)
• ROW_NUMBER() : 행 순서라는 의미처럼 동일한 값에 다른 순위를 부여함(순위에 맞게 INDEX를 붙여주되, 동일 값에 대해서는 임의의 차등을 둔다고 생각)
• SUM() : ROWS조건에 맞는 누적합
• LAG() : 괄호 안 컬럼에 대해, 현재 행 이전 행 값을 반환
• LEAD() : 괄호 안 컬럼에 대해, 현재 행 다음 행 값을 반환
• PERCENT_RANK() : ORDER BY 기준 컬럼 값에 대해 파티션 내 백분위 순위를 계산(사분위 수와 비슷)

 

 

[범위 지정]

• ROWS와 RANGE의 차이는 물리적인 앞뒤 행 순서를 기준으로 한 범위를 계산 하는가, 값 크기를 기준으로 앞뒤 차이 범위를 계산 하는가

source : https://learnsql.com/blog/sql-window-functions-cheat-sheet/#window-frame

 

 

[범위 지정 옵션]

• UNBOUNDED PRECEDING : 윈도우의 시작(맨 처음)부터 현재 행까지 (기본값)
• CURRENT ROW : 현재 행까지
• N PRECEDING : 현재 행에서 N개의 이전 행까지
• N FOLLOWING : 현재 행에서 N개의 이후 행까지
• BETWEEN A AND B : A에서 B까지의 범위를 지정
• UNBOUNDED FOLLOWING : 윈도우의 끝(맨 마지막)까지

 

[윈도우 함수를 포함한 SQL 실행 순서!]

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → 윈도우 함수 → SELECT → DISTINCT → ORDER BY → LIMIT