2023-12-05 데브코스 3주차 - 2 TIL

JOIN

• Inner join : 교집합, 정보 손실 있을 수 있음

  SELECT clicks.*, 1 as ordered, name
  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

  table.* 이런 식으로도 사용 가능함.

• Left join(Left outer join) : 왼쪽 테이블의 모든 행을 출력.

• Right join(Right outer join) : 오른쪽 테이블의 모든 행을 출력

• Full outer join : left join union right join. db에 과부하를 줄 수 있음

• Cross join(Cartesian product) : 모든 조합. 두 테이블 유사도 분석할 때 사용할 수 있음

• Self join : Inner join을 사용.

  SELECT m1.*, m2.id as sub_id, m2.name as sub_name
  FROM managers_v2 m1 INNER JOIN managers_v2 m2
  ON m1.substitute = m2.id

  같은 테이블을 참조하는 컬럼인 대체자 id를 대체자 이름으로 표현할 때 사용

  
  

필터링 먼저 하고 join하는 것이 효율적이다.

full outer join 한 후 어느 한쪽의 key값이 null인것만 고르면 교집합 뺀 결과 얻을 수 있음

위에 있는 쿼리 구조 잘 봐두기

### UNION

union: select 결과 합칠 때 사용, 중복 제거
union all: 중복 제거 안함

### WITH

CTE(Common Table Expression)라고도 함
MySQL 8.0 버전 이상에서 지원
임시 결과 집합을 생성하여 복잡한 쿼리 쉽게 작성할 수 있음
SELECT 결과를 테이블처럼
가독성 좋아짐
실습 링크 : https://www.programiz.com/sql/online-compiler/

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 c.customer_id, count(distinct shipping_id) as ship_cnt
  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

시간

STRING: 'yyyy-mm-dd', 'yyyy-mm-dd HH:MM:SS' -> 시간 계산 함수 사용 불가능
DATE: yyyy-mm-dd
DATETIME: YYYY-MM-DD HH:MM:SS
TIMESTAMP: YYYY-MM-DD HH:MM:SS UTC

NOW(): 쿼리 실행 시간
SYSDATE(): 자신이 호출된 시간

DATE_ADD + INTERVAL: 시간 더하고 빼기

타입 변환

CAST: CAST('12131' AS SIGNED INTEGER)
CONVERT: 사용법 비슷

조건절

IF(조건, 참일 경우, 거짓일 경우)
IFNULL(컬럼, 널일 경우 채울 값)

CASE: ORDER BY, WHERE 절 안에서도 쓸 수 있음
CASE WHEN 조건 THEN 값
WHEN 조건 THEN 값
ELSE 값
END as 별칭

유용한 함수

rank(): rank() over (order by age desc)
dense_rank()
percent_rank()

partition by column: 컬럼 기준 파티션
lead(컬럼, 다음 n번째) over (partition)
lag(컬럼, 이전 n번째) over (partition)
ㄴ윈도우 함수라고 한다.