[9주차] SQL 입문2

Day1

실무에서 SQL 활용하기

 

실무 문제풀이_1

: 2020년 7월 가장 많이 구매한 10위-15위 고객

  - SELECT customer_id, SUM(price) AS rev FROM tbl_purchase

    WHERE purchased_at LIKE '2020-07%' GROUP BY customer_id

    LIMIT 6 OFFSET 9

    → LIMIT n OFFSET x : x개를 넘기고 x+1개째부터 n개의 결과값만 리턴

 

실무 문제풀이_2

: 성별과 연령을 합친 컬럼을 만든 후 유저 세그먼트 및 비율 구하기

  - SELECT CONCAT(
    CASE WHEN LENGTH(gender)<1 OR gender='Others' THEN '기타'
    WHEN gender='F' THEN '여성'
    ELSE '남성' END,
    '(',
    CASE WHEN age<=15 THEN 'under 15'
    WHEN age<=20 THEN '15-20'
    WHEN age<=25 THEN '20-25'
    WHEN age<=30 THEN '25-30'
    WHEN age<=35 THEN '30-35'
    WHEN age<=40 THEN '35-40'
    WHEN age<=45 THEN '40-45'
    WHEN age<=50 THEN '45-50'
    WHEN age<=55 THEN '50-55'
    ELSE 'over 55' END,
    ')'
    ) AS users,
    COUNT(*) AS user_seg,
    COUNT(*)/(SELECT COUNT(*) FROM tbl_customer) AS percentage
    FROM tbl_customer
    GROUP BY users;

 

실무 문제풀이_3

: 2020-07 전일 대비 일별 매출 증감률

  - WITH revenue_day AS (
    SELECT DAY(purchased_at) AS day,
    SUM(price) AS sum
    FROM tbl_purchase
    WHERE purchased_at LIKE '2020-07%'
    GROUP BY day
    ) #2020-07 일별 매출

    SELECT *,
    (sum - LAG(sum) OVER(ORDER BY day)) AS rev_diff,
    ROUND((sum - LAG(sum) OVER(ORDER BY day))/LAG(sum) OVER(ORDER BY day), 2) AS diff_rate
    FROM revenue_day;

    → LAG(컬럼명) OVER(PARTITION BY , ORDER BY 자유롭게 사용)

        - 컬럼보다 N단계 아래의 값을 도출

        - 예) LAG(sum, 1) OVER (ORDER BY day) : 다음 행의 합계 가져오

 

실무 문제풀이_4

: 2020-07 일별, 유저별 매출액 TOP3

  - SELECT days, sum, day_rank FROM (
    SELECT DAY(purchased_at) AS days,
    customer_id, SUM(price) AS sum,
    RANK() OVER (PARTITION BY DAY(purchased_at)
    ORDER BY SUM(price) DESC) AS day_rank
    FROM tbl_purchase
    WHERE purchased_at LIKE '2020-07%'
    GROUP BY days, customer_id ) AS tbl
    WHERE day_rank<=3;

 

실무 문제풀이_5

: 2020-07 일별 리텐션율 구하기

: 일별 리텐션율 - 다음날 방문한 유저/오늘 방문한 유저

  - SELECT DATE_FORMAT(A.visited_at, '%Y-%m-%d') AS date,
    COUNT(DISTINCT A.customer_id) AS active_user,
    COUNT(DISTINCT B.customer_id) AS retained_user,
    ROUND(COUNT(DISTINCT B.customer_id)/COUNT(DISTINCT A.customer_id), 2) AS retention_rate
    FROM tbl_visit A
    LEFT JOIN tbl_visit B
    ON A.customer_id=B.customer_id
    AND DATE_FORMAT(A.visited_at, '%Y-%m-%d') = DATE_FORMAT(B.visited_at - INTERVAL 1 DAY, '%Y-%m-%d')
    WHERE A.visited_at LIKE '2020-07%'
    GROUP BY date;

 

 

 

 

 

Day2

중급 SQL 실습

 

연습문제_9

: 카테고리가 Comedy 인 데이터의 렌탈 횟수 출력하기 (서브쿼리 문법으로 작성)

  - SELECT COUNT(*) AS comedy_rented FROM rental
        WHERE inventory_id IN (
            SELECT inventory_id FROM inventory WHERE film_id IN(
                SELECT film_id FROM film_category WHERE category_id = (
                    SELECT category_id FROM category WHERE name='Comedy' )
                 )
         );

 

 

 

 

 

Day3

중급 SQL 실습

 

연습문제_28

: 대여된 적이 없는 영화를 찾기

  - SELECT title FROM film
    LEFT JOIN inventory USING (film_id)
    LEFT JOIN rental R USING (inventory_id)

    GROUP BY film_id
    HAVING COUNT(R.rental_id)=0;

 

연습문제_36

: 각 고객별로 가장 많이 대여한 영화 카테고리. 해당 카테고리에서의 총 대여 횟수, 해당 고객 이름을 조회하는 SQL 쿼리

: 자주 대여하는 카테고리에 동률이 있을 경우 모두 보여주기

  - SELECT CA.name,

    CONCAT(C.first_name, ' ', C.last_name) AS cus_name
    FROM customer C
    JOIN rental R ON R.customer_id = C.customer_id
    JOIN inventory I ON I.inventory_id = R.inventory_id
    JOIN film_category FC ON FC.film_id = I.film_id
    JOIN category CA ON CA.category_id = FC.category_id
    GROUP BY C.customer_id, CA.name
    HAVING COUNT(*) = (
        SELECT COUNT(*)
        FROM rental R2
        JOIN inventory I2 ON I2.inventory_id = R2.inventory_id
        JOIN film_category FC2 ON FC2.film_id = I2.film_id
        WHERE R2.customer_id = C.customer_id
        GROUP BY FC2.category_id
        ORDER BY COUNT(*) DESC
        LIMIT 1);

 

 

 

 

 

Day4

데이터 생성과 조회

 

JOIN

: INNER JOIN

  - SELECT * FROM 테이블1, 테이블2 WHERE 같은컬럼1=같은컬럼2; 으로도 표기 가능

: OUTER JOIN

 

기타 SQL 수식

: CASE WHEN

: UNION&UNION ALL

  - 교집합: INTERSECT → SELECT * FROM 테이블명 WHERE EXISTS 조건;

  - 차집합: MINUS → SELECT * FROM 테이블명 WHERE NOT EXISTS 조건;

: WITH ROLLUP

  - 집계함수에 소계나 합계 추가

  - SELECT * FROM 테이블명 WHERE  조건 WITH ROLLUP;

: 윈도우 함수

  - 특정 부분을 대상으로 데이터를 계산하는 함수

  - GROUP BY

  - ROW_NUMBER, RANK, DENSE RANK() OVER (PARTITION BY 조건, ORDER BY 조건)

 

서브쿼리

: 스칼라 서브쿼리

  - 하나의 값(단일 행, 단일 열) 등을 출력

  - 주로 SELECT 절에서 사용

: 인라인 뷰

  - FROM 절에서 주로 사용

: 중첩 서브쿼리

  - WHERE, HAVING 등 조건절에서 자주 사용

 

 

 

알아두면 쓸데 있는 SQL 함수

 

STRING FUNCTIONS

: SUBSTRING,SUBSTR(문자열, 시작 위치, 자를 길이)

: LENGTH, CONCAT, UPPER, LOWER

: TRIM(문자열) - 문자열의 양 옆의 공백 제거

: INSTR(문자열, 찾고자 하는 문자열)

  - 찾고자 하는 문자열이 몇 번째 위치에 있는지 찾음

  - 예) 주소에서 가장 앞 주소만 찾기

    → SELECT SUBSTRING(소재지전체주소, 1, INSTR(소재지전체주소, ' ')) FROM camping_info

        → INSTR로 띄어쓰기한 위치를 확인한 후 그 직전까지의 문자열 반환

: REPLACE(문자열, 기존 문자, 변환 문자)

: LPAD(문자열, 숫자, 변환 문자)

  - 숫자만큼의 문자를 삽입하여 하나의 문자열을 만듦

  - 문자열의 문자수가 부족할 경우 부족한 수 만큼 변환 문자를 추가

 

NUMBER FUNCTIONS

: ROUND, FLOOR, CEIL

: ABS - 절댓값

: SIGN - 음수, 양수 판별 후 값(음수일 시 -1, 양수일 시 1, 0일시 0) 반환

: MOD - 두 번째 숫자로 첫 번째 숫자를 나누었을 때의 나머지 반환

 

DATE FUNCTIONS

: NOW, SYSDATE, CURRENT_DATE

: ADDDATE(시간, 더하고 싶은 숫자)

: LAST_DAY() - 해당 날짜가 포함된 월의 마지막 날짜 출력

: YEAR, MONTH, DAY

 

NULL FUNCTIONS

: IFNULL(데이터, 대체값)

: COALESCE(데이터1, 데이터2, 데이터3, ...) - 주어진 데이터에서 NULL이 아닌 최초의 값을 반환

: NULLIF(데이터1, 데이터2) - 주어진 두 데이터의 값이 같으면 NULL 반환, 다르면 데이터1 반환

: ISNULL(데이터) - 데이터가 NULL이면 1, NULL이 아니면 0 반환

 

 

 

 

 

Day5

중급 SQL 실습

 

연습문제_40

: 가장 많은 수의 종류가 다른(동일 영화를 반복하여 대여하지 않고) 영화를 대여한 고객과, 대여한 종류가 다른 (동일 영화를 반복하여 대여하지 않고) 영화의 수

: 또한 해당 고객이 대여한 영화가 가장 많이 속해있는 카테고리

  - SELECT 
    cus.customer_id, 
    CONCAT(cus.first_name, ' ', cus.last_name) AS customer_name,
    COUNT(DISTINCT inv.film_id) AS unique_films_rented,
    (
        SELECT CA2.name 
        FROM customer C2 JOIN rental R2 USING (customer_id)
        JOIN inventory USING (inventory_id)
        JOIN film_category USING (film_id)
        JOIN category CA2 USING (category_id)
        WHERE R2.customer_id = C2.customer_id 
        GROUP BY CA2.name ORDER BY COUNT(*) DESC LIMIT 1
      ) AS most_common_category
FROM customer C JOIN rental R USING (customer_id)
JOIN inventory  USING (inventory_id)
JOIN film_category USING (film_id)
JOIN category USING (category_id)
GROUP BY C.customer_id ORDER BY unique_films_rented DESC LIMIT 1;

 

연습문제_프로그래머스

: https://school.programmers.co.kr/learn/courses/30/lessons/131124?language=mysql

 

 

 

윈도우 함수

 

GROUP CONCAT

: 그룹 내의 여러 행을 하나의 문자열로 결합

: 그룹 내의 동일하지 않은 값도 한 번에 출력 가능하다는 장점이 있음

: GROUP_CONCAT( 출력하고자 하는 컬럼 혹은 데이터값들 SEPERATOR 구분표시(예- 쉼표, 띄어쓰기 등) )

: GROUP_CONCAT 내에 ORDER BY 사용 가능

 

윈도우 함수

: SQL 쿼리 내에서 데이터 집합을 세분화하여 각 부분에 대한 계산을 수행하는 함수

: 기본 집계 함수보다 더 유연하게 데이터 분석 가능

: 특정 '윈도우'(데이터의 부분 집합) 내에서 작동

: 많은 윈도우 함수들은 FUNCTION() OVER (PARTITION BY column1, column2 ... ORDER BY column3)의 형식을 띔

: FUNCTION()에 들어갈 수 있는 함수

  - COUNT(), SUM(expression), AVG(expression), MIN(expression), MAX(expression)

  - ROW_NUMBER(), RANK(), DENSE_RANK()

  - LEAD(expression, offset, default), LAG(expression, offset, default)

  - FIRST_VALUE(expression), LAST_VALUE(expression)

 

ROW/RANGE

: ROWS/RANGE 는 윈도우 함수 내에서 ORDER BY 와 함께 사용하며, 특정 행의 범위 내에서만 데이터를 계산 지정

: ROWS 는 물리적 행의 위치를 기준으로 범위를 설정

: 각 행을 고유하게 취급하며, 정렬된 순서에 따라 정확하게 해당 위치의 행들만을 집계에 포함함

: RANGE 는 정렬 키의 값에 따라 범위를 설정

: 정렬 키 값이 중복되는 경우, RANGE 는 그 값에 해당하는 모든 행을 같은 그룹으로 간주하여 집계

: 같이 쓰기 좋은 옵션들

  - UNBOUNDED PRECEDING : 파티션의 첫 행부터 시작
  - UNBOUNDED FOLLOWING : 파티션의 마지막 행까지
  - CURRENT ROW : 현재 행 포함
  - n PRECEDING/FOLLOWING : 현재 행에서 n행 앞이나 뒤

: 디폴트 값은 BETWEEN UNBOUNDED PRECEDING AND CURRENT NOW

 

RANK, DENSE_RANK, ROW_NUMBER

: RANK() 는 같은 길이의 영화에 대해 같은 순위를 매기고 다음 순위를 건너뜀

: DENSE_RANK() 는 같은 순위를 매기더라도 다음 순위는 건너뛰지 않음
: ROW_NUMBER() 는 각 행에 고유한 번호를 부여

 

SUM()과 SUM(expression) OVER()의 차이

: SUM() OVER()은 일종의 누적치, 가중치를 보여줌

: 쿼리가 반환하는 각 행에 대한 전체 결과 집합의 revenue 합계

: SUM(revenue) OVER ()는 genre_revenue에서 계산된 각 장르별 매출( revenue )의 전체 합을 모든 행에 동일하게 반환

: 각 장르가 전체 매출에서 차지하는 비율( revenue_ratio )을 계산하는 데 사용

 

연습문제_1

: 대여(rental) 테이블에서 각 고객(customer_id)별로 대여 순서에 따른 누적 대여 횟수

  - SELECT rental_id, customer_id, rental_date,
    COUNT(*) OVER(PARTITION BY customer_id ORDER BY rental_date) AS cumulative_rentals
    FROM rental;

 

연습문제_4

: 대여(rental) 테이블에서 각 직원 별 대여 일자에 따른 대여 횟수와 누적 대여 횟수

  - SELECT rental_id, staff_id, rental_date,
    COUNT(*) OVER(PARTITION BY staff_id, DATE(rental_date) ORDER BY rental_date) AS rental_count,
    COUNT(*) OVER (PARTITION BY staff_id ORDER BY rental_date) AS cumulative_rental_count
    FROM rental;

 

LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()

: LEAD(column, n, default)

  - 현재 행을 기준으로 n행 뒤의 값을 가져옴

  - n을 지정하지 않으면 기본값은 1이고, 뒤에 행이 없으면 default 값을 반환

: LAG(column, n, default) 

  - 현재 행을 기준으로 n행 앞의 값을 가져옴

  - n을 지정하지 않으면 기본값은 1이고, 앞에 행이 없으면 default 값을 반환

: FIRST_VALUE(column) - 파티션된 윈도우의 첫 번째 값

: LAST_VALUE(column) - 파티션된 윈도우의 마지막 값

 

PERCENT_RANK(), CUME_DIST(), NTILE()

: PERCENT_RANK() - 행의 백분위 순위를 계산( 0부터 1 사이의 값으로 출력)

: CUME_DIST() - 행의 누적 분포를 계산(0부터 1 사이의 값으로 출력)

: NTILE(n) - 행을 n개의 그룹으로 분할하며, 각 그룹에는 거의 같은 수의 행이 포함

: 세 함수 모두 OVER 절과 함께 사용하며, ORDER BY 절을 통해 윈도우 내의 행 순서를 정의함