코드 카타

문제 & 제출 답안

• 1683. Invalid Tweets

  SELECT tweet_id
  FROM tweets
  WHERE LENGTH(content) > 15

• 1378. Replace Employee ID With The Unique Identifier

  SELECT u.unique_id, e.name
  FROM employees e
  LEFT JOIN employeeuni u
  ON e.id = u.id

• 1068. Product Sales Analysis I

  SELECT p.product_name, s.year, s.price
  FROM sales s
  LEFT JOIN product p
  ON s.product_id = p.product_id

• 1581. Customer Who Visited but Did Not Make Any Transactions

  SELECT v.customer_id, count(*) as count_no_trans
  FROM visits v
  LEFT JOIN transactions t
  ON v.visit_id = t.visit_id
  WHERE t.transaction_id IS NULL
  GROUP BY 1

• 197. Rising Temperature

  SELECT t.id
  FROM weather t
  LEFT JOIN weather y
  ON t.recorddate = y.recorddate + 1
  WHERE t.temperature > y.temperature

• 1661. Average Time of Process per Machine

  SELECT machine_id, round(AVG(time),4) AS processing_time
  FROM
  (
      SELECT *,
          LEAD(timestamp) OVER(ORDER BY machine_id, process_id, activity_type) as lead_tmp,
          LEAD(timestamp) OVER(ORDER BY machine_id, process_id, activity_type) - timestamp as time
      FROM activity
  ) A
  WHERE activity_type = 'start'
  GROUP BY 1

• 577. Employee Bonus

  SELECT e.name, b.bonus
  FROM employee e
  LEFT JOIN bonus b
  ON e.empID = b.empID
  WHERE b.bonus < 1000
      OR b.bonus IS NULL

오답노트

• 셀프 JOIN

  SELECT T.id
  FROM weather t
  LEFT JOIN weather y
  ON t.recorddate = y.recorddate + 1
  WHERE t.temperature > y.temperature

  • SELF JOIN : On 절 뒤에 연산자를 활용하여 LAG 함수와 같은 기능을 활용할 수 있음.
• Lag, Lead 함수

  SELECT id
  FROM
  (
      SELECT *,
          lag(temperature) over(order by recorddate) as lag_temp
      FROM weather
  ) A
  WHERE temperature > lag_temp

  • Lag(컬럼A) over (order by 컬럼B)
    • 이전 레코드의 컬럼 A를 컬럼 B 순서에 따라 지금 레코드에 붙임
    • 원래 레코드

      id	recordDate	temperature
      1	2015-01-01	10
      2	2015-01-02	25
      3	2015-01-03	20
      4	2015-01-04	30

    • lag(temperature) over(order by recorddate) as lag_temp 추가 시,

      id	recordDate	temperature	lag_temp
      1	2015-01-01	10	NULL
      2	2015-01-02	25	10
      3	2015-01-03	20	25
      4	2015-01-04	30	20

강의 스터디

수강할 강의

스터디를 진행할 강의를 링크해주세요.

[SQL] 예제로 익히는 SQL - 7회차

강의에서 필수 사용되는 문법 요약

강의에서 필수로 사용되는 문법에 대한 개념을 요약해주세요.

1. 순위

   • RANK
     • 정의 : 특정 컬럼 순위 출력
       • 단, 동일 값 ⇒ 동일 순위
       • 다음 값 ⇒ 건너 뛰고 다다음 순위
     • 문법 : RANK() OVER(PARTITION BY 컬럼 1 ORDER BY 컬럼 2)
     • 해석
       • 컬럼 1별로
       • 컬럼 2 순서대로 정렬했을 때 순위
     • 예시

       select *,
       rank() over(partition by JOB order by SALARY DESC) as RANK
       from basic.window1 

       ID	JOB	SALARY	RANK
       A	teacher	10000	1
       B	teacher	10000	1
       C	teacher	7000	3
       D	actor	5000	2
       E	actor	6000	1

   • DENSE_RANK
     • 정의 : 특정 컬럼 순위 출력
       • 단, 동일 값 ⇒ 동일 순위
       • 다음 값 ⇒ 건너 뛰기 X 바로 다다음 순위
     • 문법 : DENSE_RANK() OVER(PARTITION BY 컬럼 1 ORDER BY 컬럼 2)
     • 해석
       • 컬럼 1별로
       • 컬럼 2 순서대로 정렬했을 때 순위
     • 예시

       # 윈도우 함수 - DENSE_RANK 함수 예제
       select *,
       dense_rank() over(partition by JOB order by SALARY DESC)
       from basic.window1 

       ID	JOB	SALARY	DENSE_RANK
       A	teacher	10000	1
       B	teacher	10000	1
       C	teacher	7000	2
       D	actor	5000	2
       E	actor	6000	1

   • ROW_NUMBER
     • 정의 : 특정 컬럼 순위 출력
       • 단, 동일 값 ⇒ 고유 순위 부여
     • 문법 : ROW_NUMBER() OVER(PARTITION BY 컬럼 1 ORDER BY 컬럼 2)
     • 해석
       • 컬럼 1별로
       • 컬럼 2 순서대로 정렬했을 때 순위
     • 예시

       # 윈도우 함수 - ROW_NUMBER 함수 예제
       select *,
       ROW_NUMBER() over(partition by JOB order by SALARY DESC) AS ROW_NUMBER
       from basic.window1 

       ID	JOB	SALARY	ROW_NUMBER
       A	teacher	10000	1
       B	teacher	10000	2
       C	teacher	7000	3
       D	actor	5000	2
       E	actor	6000	1

2. 순서

   • FIRST_VALUE
     • 정의 : 가장 먼저 나온 값 출력
     • 문법 : FIRST_VALUE(컬럼 1) OVER(PARTITION BY 컬럼 2 ORDER BY 컬럼 3)
     • 해석
       • 컬럼 2별로
       • 컬럼 3 순서대로 정렬했을 때
       • 가장 먼저 나오는 컬럼 1
     • 예시

       # 윈도우 함수 - FIRST_VALUE 함수 예제
       select *,
       first_value(NAME) over(partition by JOB order by SALARY)
       from basic.window1 

   • LAST_VALUE
     • 정의 : 가장 마지막에 나온 값 출력
     • 문법 : LAST_VALUE(컬럼 1) OVER(PARTITION BY 컬럼 2 ORDER BY 컬럼 3)
     • 해석
       • 컬럼 2별로
       • 컬럼 3 순서대로 정렬했을 때
       • 가장 나중에 나오는 컬럼 1
     • 예시

       # 윈도우 함수 - LAST_VALUE 함수 예제
       select *,
       last_value(NAME) over(partition by JOB order by SALARY)
       from basic.window1 

   • LAG
     • 정의 : 이전 레코드의 컬럼 값
     • 문법 : LAG(컬럼 1, N) OVER(PARTITION BY 컬럼 2 ORDER BY 컬럼 3)
     • 해석
       • 컬럼 2별로
       • 컬럼 3 순서대로 정렬했을 때
       • N번째 이전 행의 컬럼 1
   • LEAD
     • 정의 : 이후 레코드의 컬럼 값
       • 문법 : LEAD(컬럼 1, N) OVER(PARTITION BY 컬럼 2 ORDER BY 컬럼 3)
       • 해석
         • 컬럼 2별로
         • 컬럼 3 순서대로 정렬했을 때
         • N번째 이후 행의 컬럼 1

3. 비율

   • PERCENT_RANK
     • 정의 : 파티션별 가장 먼저 나오는 값 0, 마지막 값 1 ⇒ 행 순서별 백분율 출력
     • 문법 : PERCENT_RANK(컬럼 1) OVER(PARTITION BY 컬럼 2 ORDER BY 컬럼 3)
     • 해석
       • 컬럼 2별로
       • 컬럼 3 순서대로 정렬했을 때
       • 가장 높은 컬럼 1 의 PERCENT_RANK 값 = 0
       • 컬럼 1 별로 RANK 함수 적용 시 값 부여
       • 컬럼 1 RANK 값 / (총 레코드 수 - 1)
     • 예시

       # 윈도우 함수 - PERCENT_RANK 함수 예제
       select *
       , PERCENT_RANK() OVER (partition by JOB order BY SALARY DESC) AS PERCENT_RANK
       from basic.window1 

       ID	JOB	SALARY	PERCENT_RANK 변환 전	PERCENT_RANK
       A	teacher	10000	0	0
       B	teacher	10000	0	0
       C	teacher	7000	2	0.5
       D	actor	5000	1	1
       E	actor	6000	0	0

       # 윈도우 함수 - PERCENT_RANK 함수 예제
       select *
       , PERCENT_RANK() OVER (order BY SALARY DESC) AS PERCENT_RANK
       from basic.window1 

       ID	JOB	SALARY	PERCENT_RANK 변환 전	PERCENT_RANK
       A	teacher	10000	0	0
       B	teacher	10000	0	0
       C	teacher	7000	2	0.5
       D	actor	5000	4	1
       E	actor	6000	3	0.75

   • CUME_DIST
     - 정의 : 현재 행보다 작거나 같은 행 수에 대한 누적백분율 값 출력
     - 문법 : CUME_DIST() OVER(PARTITION BY 컬럼 2 ORDER BY 컬럼 3)
     - 해석
     • 컬럼 2별로
     • 컬럼 3 기준 줄 세우기
     • 가장 마지막 CUME_DIST 값 = 1
     • RANK 값 / N등분하여 컬럼 3 기준 상위 몇 %인지 출력
       • RANK 값 / 총 레코드 수
       • 동일 값일 땐, 원래 RANK 값 + 1
   • NTITLE
     - 정의 : 전체를 N개의 그룹으로 나누어 순위 부여
     - 문법 : NTILE(N) OVER(PARTITION BY 컬럼 1 ORDER BY 컬럼 2)
     - 해석
     • 컬럼 1별로
     • 컬럼 2 순서대로 정렬했을 때
     • N등분하여 순위 부여
   • RATIO_TO_REPORT (MYSQL 지원 X)
     • 정의 : 파티션 내 전체 SUM값에 대해 행별 백분율 소수점 출력
     • 문법 : RATIO_TO_REPORT(컬럼 1) OVER(PARTITION BY 컬럼 2 ORDER BY 컬럼 3)

4. WITH 구문
   

5. 그 밖의 함수들