SQL 실습04-순위&CASE

ROW_NUMBER()	# 1, 2, 3, 4
RANK()			# 1, 2, 2, 4
DENSERANK()		# 1, 2, 2, 3

SELECT
ROW_NUMBER() OVER (ORDER BY socre DESC) AS colname1,
RANK() OVER (ORDER BY socre DESC, name DESC) AS colname2,
DENSERANK () OVER (ORDER BY socre DESC) AS colname3
FROM tb;

PARTITION BY 		# 소그룹 순위 (ex.전교석차 / 학급석차)

SELECT
  ROW_NUMBER() OVER (PARTITION BY class ORDER BY socre DESC) AS col1,
  RANK() OVER (PARTITION BY class ORDER BY socre DESC) AS col2,
  DENSERANK () OVER (PARTITION BY class ORDER BY socre DESC) AS col3
FROM tb;

SELECT
  ord_month,
  CASE
      WHEN SUM(price) >= 300 THEN 'A'
      WHEN SUM(price) >= 150 AND SUM(price) < 300 THEN 'B'
      ELSE 'C'
  END AS level
FROM tb
GROUP BY ord_month

이 글은 제로베이스 데이터 분석 취업 스쿨의 강의자료 일부를 발췌하여 작성되었습니다.