MySQL로 데이터분석 해보자 - ORDER BY, RANK

오늘은 MySQL 데이터 분석 원하는 데이터 만들기라는 주제에 대해서 살펴보도록 하겠습니다.

---

원하는 데이터 만들기

---

1. 데이터 줄 세우기 : ORDER BY

"10번 이상 구매한 VIP 고객, 구매 금액 순으로 리스트 뽑아주세요" "매출 5천만 원 이상의 상품, 판매량 순으로 리스트 뽑아주세요" 등의 순서를 정해 원하는 데이터를 가져오는 방법. 즉, 데이터를 줄 세우는 방법에 대해서 알아보겠습니다. 가져온 데이터를 정렬해주는 키워드는 ORDER BY입니다.

 

ORDER BY의 특징

• ORDER BY [컬럼 이름] 형식으로 사용합니다.
• 입력한 [컬럼 이름]의 값을 기준으로 모든 row를 정렬합니다.
• 기본 정렬 규칙은 오름차순입니다.
  • ORDER BY [컬럼 이름] = ORDER BY [컬럼 이름] ASC
• 내림차순 정렬을 원할 경우에는 마지막에 DESC 키워드를 추가합니다.
  • ORDER BY [컬럼 이름] DESC
• 여러 컬럼으로 정렬도 가능하며, 키워드 뒤에 [컬럼 이름]을 복수 개 입력하면 됩니다.
  • 위치한 순서 대로 정렬이 됩니다.
  • (예시) ORDER BY [컬럼 1], [컬럼 2]
    • [컬럼 1] 기준으로 정렬, [컬럼 1] 값이 동일한 로우 간에 [컬럼 2] 기준으로 정렬
• 컬럼 번호로도 정렬이 가능합니다.
  • 이 때, 컬럼 번호는 SELECT 절의 컬럼 이름의 순서를 의미합니다.

 

ORDER BY 쿼리 문법

• 오름차순 정렬 : 여기서 ASC는 생략이 가능합니다.
  • SELECT [컬럼 이름]
  • FROM [테이블 이름]
  • WHERE 조건식
  • ORDER BY [컬럼 이름] ASC;
• 내림차순 정렬 : 여기서 DESC는 생략이 불가합니다.
  • SELECT [컬럼 이름]
  • FROM [테이블 이름]
  • WHERE 조건식
  • ORDER BY [컬럼 이름] DESC;

---

2. 데이터 순위 만들기 : RANK

"10번 이상 구매한 VIP 고객, 구매 금액 순으로 BEST 100 리스트 뽑아주세요", "매출 5천만 원 이상의 상품, 판매량 순으로 WORST 30 리스트 뽑아주세요"와 같이 순위를 정해서 원하는 데이터를 가져오려면 어떻게 해야 할까요? 데이터에 순위를 먼저 만들어야 하겠죠? 데이터에 순위를 만드는 방법에 대해서 알아보겠습니다. 데이터를 정렬해서 순위를 만들어 주는 함수는 RANK입니다.

 

RANK의 특징

• RANK() OVER (ORDER BY [컬럼 이름]) 형식으로 사용합니다.
• 항상 ORDER BY와 함께 사용합니다.
• SELECT절에 사용하며, 정렬된 순서에 순위를 붙인 새로운 컬럼을 보여줍니다.
  • 테이블의 실제 데이터에는 영향을 미치지 않습니다.

 

RANK 쿼리 문법

• 오름차순 순위 만들기
  • SELECT [컬럼 이름], ..., RANK() OVER (ORDER BY [컬럼 이름])
  • FROM [테이블 이름]
  • WHERE 조건식;
• 내림차순 순위 만들기
  • SELECT [컬럼 이름], ..., RANK() OVER (ORDER BY [컬럼 이름] DESC)
  • FROM [테이블 이름]
  • WHERE 조건식;

<예시 1>

SELECT name, attack,

     RANK() OVER (ORDER BY attack DESC) AS attack_rank

FROM pokemon.mypokemon;

-> 실행하게 되면 name, attack, attack_rank라는 세 열이 실행결과에 나타나고, attack_rank는 공격력이 높은 순서대로 순위가 매겨집니다.

 

데이터를 정렬해 순위를 만들어주는 함수에는 RANK 외에도 DENSE_RANK와 ROW_NUMBER가 있습니다. 쿼리 문법은 RANK와 동일합니다. 차이점에 대해서 살펴보면 아래와 같습니다.

 

<예시 2>

SELECT name, attack,

     RANK() OVER (ORDER BY attack DESC) AS rank_rank

     DENSE_RANK() OVER (ORDER BY attack DESC) AS rank_dense_rank

     ROW_NUMBER() OVER (ORDER BY attack DESC) AS rank_row_number

FROM pokemon.mypokemon;

-> RANK()는 공통 순위가 있으면 다음 순위로 건너뜁니다. 하지만 DENSE_RANK()는 공통 순위가 있어도 다음 순위를 뛰어넘지 않습니다. RANK_NUMBER()는 공통 순위를 무시합니다.

---

3. 문자형 데이터 정복하기

MySQL 내의 다양한 타입의 데이터는 '함수'를 사용하여 변형할 수 있습니다. 함수는 f(x) = y라고 학교에서 배웠습니다. 함수 f()에 INPUT x가 들어가면 OUTPUT y가 출력이 됩니다. f대신에 여러 가지 함수가 적용됩니다. 예를 들어 보겠습니다. LENGTH("abc") = 3이라는 함수 식에서 "abc"라는 INPUT을 받아 문자열의 글자 수를 반환하는 LENGTH 함수를 처리하여 3이라는 결괏값을 출력하게 됩니다.

 

함수의 특징

• 함수 이름(함수를 적용할 값 또는 컬럼 이름) 형식으로 사용합니다.
• 결괏값을 새로운 컬럼으로 반환합니다.

 

자주 사용하는 문자형 데이터 함수

함수	활용예시	설명	비고
LOCATE	LOCATE("A", "ABC")	"ABC"에서 "A는 몇 번째에 위치해 있는지 검색해 위치 반환	- 문자가 여러 개라면 가장 먼저 찾은 문자의 위치를 가져온다. - 만약 찾는 문자가 없다면 0을 가져온다.
SUBSTRING	SUBSTRING("ABC", 2)	"ABC"에서 2번째 문자부터 반환	- 만약 입력한 숫자가 문자열의 길이보다 크다면 아무것도 가져오지 않는다.
RIGHT	RIGHT("ABC", 1)	"ABC"에서 오른쪽에서 1번째 문자까지 반환
LEFT	LEFT("ABC", 1)	"ABC"에서 왼쪽에서 1번째 문자까지 반환
UPPER	UPPER("abc")	"abc"를 대문자로 바꿔 반환
LOWER	LOWER("ABC")	"ABC"를 소문자로 바꿔 반환
LENGTH	LENGTH("ABC")	"ABC"의 글자 수를 반환
CONCAT	CONCAT("ABC", "DEF")	"ABC" 문자열과 "CDF" 문자열을 합쳐 반환
REPLACE	REPLACE("ABC", "A", "Z")	"ABC"의 "A"를 "Z"로 바꿔 반환

---

4. 숫자형 데이터 정복하기

자주 사용하는 숫자형 데이터 함수

함수	활용	설명
ABS	ABS(숫자)	숫자의 절댓값 반환
CEILING	CEILING(숫자)	숫자를 정수로 올림해서 반환
FLOOR	FLOOR(숫자, 자릿수)	숫자를 정수로 내림해서 반환
ROUND	ROUND(숫자, 자릿수)	- 숫자를 소수점 자릿수까지 반올림해서 반환 - 자릿수에 0을 입력 시 소수점 없이 정수만 반환
TRUNCATE	TRUNCATE(숫자, 자릿수)	- 숫자를 소수점 자릿수까지 버림해서 반환 - 자릿수에 0을 입력 시 소수점 없이 정수만 반환
POWER	POWER(숫자A, 숫자B)	숫자A의 숫자B 제곱 반환
MOD	MOD(숫자A, 숫자B)	숫자A를 숫자B로 나눈 나머지 반환

---

5. 날짜형 데이터 정복하기

자주 사용하는 날짜형 데이터 함수

함수	활용	설명
NOW	NOW()	현재 날짜와 시간 변환
CURRENT_DATE	CURRENT_DATE()	현재 날짜 변환
CURRENT_TIME	CURRENT_TIME()	현재 시간 반환
YEAR	YEAR(날짜)	날짜의 연도 반환
MONTH	MONTH(날짜)	날짜의 월 반환
MONTHNAME	MONTHNAME(날짜)	날짜의 월을 영어로 반환
DAYNAME	DAYNAME(날짜)	날짜의 요일을 영어로 반환
DAYOFMONTH	DAYOFMONTH(날짜)	날짜의 일 변환
DAYOFWEEK	DAYOFWEEK(날짜)	날짜의 요일을 숫자로 반환
WEEK	WEEK(날짜)	날짜가 해당 연도에 몇 번째 주인지 반환

 

HOUR, MINUTE, SECOND

함수	활용	설명
HOUR	HOUR(시간)	시간의 시 반환
MINUTE	MINUTE(시간)	시간의 분 반환
SECOND	SECOND(시간)	시간의 초 반환

괄호 안에 시간 대신에 시, 분, 초가 포함된 날짜형 데이터가 입력이 되어도 시, 분, 초를 반환합니다.

 

DATE_FORMAT 함수

함수	활용	설명
DATE_FORMAT	DATEFORMAT(날짜/시간, 형식)	날짜/시간의 형식을 형식으로 바꿔 변환

예를 들어 SELECT DATA_FORMAT('1996-11-06 17:34:58', '%Y년 %m월 %d일 %H시 %분 %초') AS formatted_date;라는 쿼리를 작성했다면 1996년 11월 06일 17시 34분 58초의 결괏값을 얻습니다.

 

DATEDIFF, TIMEDIFF 함수

함수	활용	설명
DATEDIFF	DATEDIFF(날짜1, 날짜2)	날짜1과 날짜2의 차이 반환 (날짜1 - 날짜2)
TIMEDIFF	TIMEDIFF(시간1, 시간2)	시간1과 시간2의 차이 반환 (시간1 - 시간2)

---

이상으로 ORDER BY, RANK 등을 활용하여 MySQL 데이터 분석 데이터 만들기 주제에 대해 살펴보았습니다.

읽어주셔서 감사합니다. 오늘도 성장하는 하루 보내세요!