데브코스 12일차 - SQL 문법

키워드

JOIN, UNION, WITH, Subquery, 조건절

전환율 - 구매 수/클릭 수

다양한 JOINS

• 두 개 이상의 테이블을 특정 key 기준으로 결합하는 것

• 테이블을 처음부터 합쳐놓으면 되는 것 아닌지?

  • 테이블을 쪼개두면 관리가 편리
  • 변경사항이 생겼을 때 수정 시간을 줄임
  • 중복되는 것을 막음

• INNER JOIN → 교집합

  SELECT products.*, manager.name as manager_name
  FROM products INNER JOIN managers
  	on products.category = managers.managing;

  • 컬럼 값이 양쪽 테이블에서 일치하는 경우만 가져옴

  • 데이터 정확성이 높음

  • 대상 행 수를 줄이기 때문에 일반적으로 다른 JOIN들에 비해 빠름

    SELECT clicks.*, 1 as ordered
    FROM clicks INNER JOIN orders
    	ON clicks.user_name = orders.user_name
    	AND clicks.product_id = orders.product_id
    	AND clicks.date = orders.date;

  • ON에서도 AND를 이용해 조건을 여러개 달아줄 수 있음

    SELECT clicks.*, 1 as ordered
    FROM clicks INNER JOIN orders
    	ON clicks.user_name = orders.user_name
    	AND clicks.product_id = orders.product_id
    	AND clicks.date = orders.date
    INNER JOIN products on clicks.product_id = products.product_id;

• LEFT JOIN

  • 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 가져옴

  • 만약 왼쪽 테이블 데이터와 일치하는 데이터가 오른쪽 테이블에 없을 땐 null로 채움

  • 좌측 테이블의 데이터와 일치하는 데이터가 우측에 여러개일 땐 결과 테이블이 좌측 테이블 행 수보다 늘어날 수 있다.

  • JOIN 결과는 테이블이므로 where절로 필터링할 수 있음
    - 쿼리의 결과물은 임시 테이블로 저장이 되었다가 세션이 끝나면 삭제되는 방식으로 작동
    - JOIN의 결과에 필터링이 적용됨
    - 쿼리를 효율적으로 작성하기 위해선 필터링 후 join을 거는 게 좋다.

    SELECT clicks.*, odr_index, name, category
    FROM clicks LEFT JOIN orders
    	ON clicks.user_name = orders.user_name
    	AND clicks.product_id = order.product_id
    	AND clicks.date = order.date
    INNER JOIN products ON clicks.product_id = products.product_id
    WHERE products.category = '디지털'
    AND odr_index is NULL;

  • 만약 교집합 부분은 제외하고싶다면, where 절에 right.key is null이라는 조건을 주면 된다.

• RIGHT JOIN

  • 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 가져옴

• FULL OUTER JOIN (mysql은 지원하지 않음)

  • 두 테이블의 모든 행을 포함함, 서로의 테이블에 데이터가 없을 땐 null 값으로 채움
  • DB에 과부하(비용 증가)를 줄 수 있기 때문에 사용에 주의해야함
  • set(LEFT + RIGHT)
    • mysql에선 지원하지 않기 때문에 left join 결과와 right join 결과를 union 함

• CROSS JOIN

  • 두 테이블간 가능한 모든 조합

    

    3행 테이블 CROSS JOIN 3행 테이블 → 9행 테이블

  • cross join 결과의 사이즈가 매우 커지기 때문에 사용에 유의해야함

  • 클릭과 구매 날짜간 차이, 연관 클릭 추천(상품간 유사도)

    SELECT products.*,
    products_B.product_id as B_product_id,
    products_B.category as B_category,
    products_B.name as B_name,
    products_B.price as B_price
    FROM products CROSS JOIN products_B

  • 유사도 계산?

    • 카테고리 같으면
    • 상품 이름이 같으면
    • 가격 차이가 1000원 이내

• Alias

  • JOIN 시 alias(별칭)을 사용해 쿼리를 가독성있게 작성할 수 있다.

• SELF JOIN

  • JOIN을 자기 자신과!

  • alias를 무조건 사용해야함

  • 직원 테이블에서 대체자를 가져올 때!

    

UNION

• UNION
  • 두 개 이상의 select문의 결과를 결합해 하나의 결과 집합으로 만듦
    • 즉 두 테이블을 수직으로 붙임
  • 중복되는 행을 제거해줌 (모든 컬럼 값이 동일한 데이터의 경우!)
  • 각 결과의 열의 수와 type이 동일해야함 (편의를 위해 타입캐스팅을 해주는 듯..)
• UNION ALL
  • 중복을 제거하지 않고 결과를 반환

WITH

• CTE(Common Table Expression)
  • 같은 쿼리 블록을 여러번 사용할 수 있도록 함
  • 테이블처럼 사용할 수 있도록 함

WITH odr_cnt as (
SELECT c.customer_id, count(distinct order_id) as odr_cnt, sum(amount) as total_purchase
FROM Customers c INNER JOIN Orders o on c.customer_id = o.customer_id
GROUP BY 1
ORDER BY 2 DESC
),
ship_cnt as (
	SELECT
	FROM Customers c INNER JOIN Shippings s on c.customer_id = s.customer
	WHERE status = 'Pending'
	GROUP BY 1
	ORDER BY 2 DESC
)

SELECT oc.customer_id, odr_cnt, total_purchase, COALESCE(ship_cnt, 0) as shipping_cnt
FROM odr_cnt oc LEFT JOIN ship_cnt sc on oc.customer_id = sc.customer_id

Subquery

• 다른 쿼리 내부에 포함된 쿼리로 주로 더 큰 쿼리의 일부로 사용됨

• 잘못 사용할 경우 많은 리소스를 필요로한다.

• SELECT 구문에서

  

  • 서브쿼리 결과 값을 컬럼으로 붙이겠다는 뜻
  • 유저별 평균 구매 가격과 전체 평균 구매 가격을 비교함
    • 이를 통해 평균 가격보다 높은 경우 할인률이 높은 쿠폰, 낮은 경우 최소 사용 가능 금액이 낮은 쿠폰 발급 등을 통해 구매를 유도함

• FROM 절에

  

  • 서브쿼리에서 필터링을 먼저한 후 join을 함
  • join하기 전에 테이블 크기를 줄여놔 효율을 높임

• WEHRE 절에

  

• EXISTS와 함께

  

  • EXISTS는 서브쿼리 실행 결과의 존재 여부에 따라 true/false를 반환

동일한 결과물이더라고 쿼리는 매우 다양할 수 있다!

상황에 맞게 가장 효율적인 쿼리를 찾는게 좋다.

타임 스탬프 함수

• string은 시간 관련 연산이 어려움

• DATE는 시간을 저장하지 않아 저장 공간을 덜 차지함

• DATETIME는 날짜와 시간을 모두 저장. 시간 간 연산 가능. DATE에 비해 더 많은 정보를 저장하므로 저장공간을 더 차지

• TIMESTAMP은 DATETIME과 유사하며, 뒤쪽에 타임존이 붙을 수 있음. DATETIME보다 제한적(기간이). 시간간 연산이 가능

• NOW()

  • UTC 기준으로 현재 시간을 가져오는 함수, 쿼리가 실행된 시간 반환
  • CURRENT_TIMESTAMP(): 유사
  • CURTIME(): 현재 시간 반환
  • CURRENT_DATE() = CURDATE(): 날짜 반환

• SYSDATE()

  • 함수가 호출된 시간을 반환 → 이 점에서 NOW와 차이가 있는 것임

• YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

  • 날짜(DATETIME)에서 각각을 추출

• WEEKDAY() → 숫자, MONTHNAME(), DAYNAME()→문자

DATEDIFF(NOW(), STR_TO_DATE(date, ‘%Y%m%d))

• STR_TO_DATE - string을 date로 → 이를 통해 날짜 연산이 가능
  • 2번째 인자는 현재 데이터의 구조를 입력해주면됨.
• DATEDIFF - 날짜의 차이를 구해준다.
• TIMEDIFF - 시간 간의 차이를 반환
  • 앞에서 뒤를 뺌
• ADDDATE(날짜, 더할 날짜 수)
• ADDDATE(’2023-11-01’, INTERVAL 3 HOUR)
  • 3시간을 더해줌
• ADDDATE(’2023-11-01’, INTERVAL -1 DAY)
  • 하루를 빼줌
• CONVERT_TZ(NOW(), ‘+00:00’, ‘+09:00’)
  • 타임존을 변경해 시간을 나라 기준에 맞게 변경
  • 인자: 시간, before, after
• TIME_TO_SEC
  • 시간을 초 단위로
• DATE_FORMAT()

  DATE_FORMAT(date, '%Y-%m-%d %T')

클릭 후 다음 클릭까지 몇초가 걸리는지, 구매까지는 몇초가 걸리는지

고객들이 이탈하는 구간, 페이지 전환이 오래 걸리는 경우 등을 분석하기 위해 이를 사용한다.

타입 변환

• 타입 불일치로 인한 연산/빅 오류를 피하기 위함
• CAST
  • CAST(date AS SIGNED INTEGER) + 3 (그냥 예시. 날짜를 쓸 땐 DATE ADD를 사용)
• CONVERT
  • CONVERT(’20231101’, SIGNED INTEGER)
  • CONVERT(20231101, CHAR(4)) → ‘2023’
  • CAST와 동일한 효과를 냄. 사용법에 차이만 있을 뿐

UNION에선 원래 컬럼의 타입이 동일해야 결합되지만 편의를 위해 타입 변환을 해준다.

하지만 데이터의 타입을 제대로 알지 못하고 결합하는 건 좋지 않음.

타입 변환 후 UNION 할 것!

조건절

• IF

  SELECT IF(price >= 10000, '고가','저가') AS '가격 구분'

  • IF(조건, true일 때 반환값, false일 때 반환값)
  • IF문을 중첩해서 사용할 수도 있다.

• IFNULL()

  SELECT IFNULL(date, 20231104)

  • NULL 값의 경우 대체값으로 채움

SELECT user_name, price_class, count(1) as cnt
FROM clicks c
	INNER JOIN (SELECT product_id, IF(price>5000, '고가','저가') as price_class
							FROM products) p
	ON c.product_id = p.product_id
GROUP BY 1,2
ORDER BY 1,2

• 고가의 상품 클릭 수가 많은 고객에게는 할인률이 높은 쿠폰을, 저가 상품에 주로 관심이 있는 고객에게는 최소 사용 가능 금액이 낮은 쿠폰을 지급해 CRM 마케팅을 해볼 수 있음

• CASE WHEN

  SELECT price,
  	CASE WHEN PRICE>10000 THEN '고가'
  	WHEN (price <= 10000 AND price>4000) THEN '중가'
  	ELSE '저가'
  	END AS price_class
  FROM products_B

  CASE WHEN 조건 then 값

  ~

  ELSE 값

  END as 컬럼명

  SELECT date,
  	CASE date
  	WHEN '20231014' THEN '첫째날'
  	WHEN '20231015' THEN '둘째날'
  	ELSE '기타'
  	END AS date_group
  FROM clicks

  • 특정 컬럼 값을 기준으로 case를 사용하는 방법

• where 절에 case를 이용하면 조건을 바꾸기 쉬움

SELECT *
FROM products
ORDER BY (
	CASE
	WHEN category = '디지털' THEN 1
	WHEN category = '주방용품' THEN 2
	WHEN category = '%보호대%' THEN 3
	ELSE 4
	END
) 

• ORDER BY 구문에 CASE를 사용해 category를 원하는 순서대로 정렬할 수 있다.
  
  

그 외 유용한 함수 (윈도우 함수)

• RANK()

  SELECT age,
  RANK() OVER (ORDER BY age) AS asc_rank, --나이 순서로 랭크를 매김
  DENSE_RANK() OVER (ORDER BY age) AS dense_rank --빽빽하게 랭크를 매김
  PERCENT_RANK() OVER (ORDER BY age) AS percent_rank --퍼센트로 랭크를 매김 (나보다 작은 갯수/전체 갯수)
  PERCENT_RANK() OVER (PARTITION BY date ORFER BY price) --날짜가 같은 데이터끼리 가격별로 랭크를 매김

• LEAD()

  SELECT clk_index, user_name, date,
  LEAD(clk_index,1) OVER (partition by user_name, date order by clk_index) next_click
  FROM clicks
  ORDER BY 3,1

  파티션 내에서 N번째 다음으로 오는 값을 찾는 함수 LEAD에 두번째 인자 1은 바로 다음 값을 의미함. 즉 여기선 유저 이름과 날짜로 파티셔닝(?) 하고 데이터의 clk_index의 다음 값을 반환… 어렵다.
  
• LAG() 파티션 내에서 N번째 전에 오는 값을 찾는 함수
  

궁금한 점