[mysql] 자주 사용하는 select

1. RANK() / DENSE_RANK() / ROW_NUMBER() -순위매김

 

RANK 함수는 중복 값 들에 대해서 동일순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력하는 함수입니다.

OVER()안에 RANK를 매길 조건을 입력하는데 이 때, 조건이 하나가 아닌 여러개라면 PARTITION BY절을 사용해서 특정 속성 별로 구분을 합니다.

저는 년월, 과목별 점수에 대한 학생별 등수를 보여줘야 했기 때문에, PARTITION BY년월, 과목코드 로, 등수를 매기는 조건은 점수를 내림차순으로 정렬하였습니다.

RANK()를 검색하다보면 DENSE_RANK() 도 볼 수 있는데

RANK() 와 DENSE_RANK()는 둘 다 공동순위가 존재하는데, 차이점은 RANK()는 중복인원 수 만큼 다음 순위가 밀려나는 반면에 DENSE_RANK()는 중복인원수를 검사하지 않고 바로 다음 순위로 자리매김 하게 됩니다.

이와 또 비교할 수 있는게 ROW_NUMBER()인데, ROW_NUMBER()은 공동순위의 개념 없이 순위가 정해진다는 것 입니다.

아래의 예시를 보면 쉽게 이해할 수 있습니다.

number	rank	dense_rank	row_number
100	1	1	1
90	2	2	2
90	2	2	3
88	4	3	4
70	5	4	5

 

2.ROUND() / FLOOR() / CEIL() - 반올림/버림/올림

 

ROUND()함수는 숫자를 각 자리에서 반올림 해주는 함수입니다.

- 1.ROUND(숫자) 일 때는 소수 첫째자리에서 반올림을 해 줍니다.

- 2.ROUND(숫자, 자리수) 일 때는 자리수까지 반올림을 해 줍니다.

아래 이미지를 보면 자세히 알 수 있습니다.

 

 ROUND() 자리수 참고 

FLOOR()함수는 숫자를 소수점 첫째 자리에서 버림하는 함수로, 주어진 숫자와 가장 근접한 작은 정수를 출력합니다.

ROUND() 다른 점으로는, 매개값을 받아 버림할 자리수를 정할 수 없습니다.

 

CEIL() 함수는 소수 첫째자리에서 올림하는 함수로, 주어진 숫자와 가장 근접한 큰 정수를 출력합니다.

FLOOR()함수와 동일하게 올림할 자릿수를 정할 수 없습니다.

 

 

3. SUBSTR(), SUBSTRING() -문자열 slice

SUBSTR()은 하나의 필드 데이터의 일부를 읽는데 사용합니다.

SUBSTR(str, pos) 형식으로 사용하고, str에서 pos번째 위치에서 모든 문자를 읽어들입니다. 

SUBSTR(str, pos, len) 은 str에서 pos번째 위치부터 len개의 문자를 읽어들입니다.

저는 년도와 월이 붙어있는 YYYYMM 을 YYYY와 MM으로 나눠서 각각 사용했습니다.

 

분기를 구하기 위해서는 MM/3 을 한 결과값을 올림 함수인 CELI을 사용해서 나타나게 하였습니다.

만약, 1월이라면 1/3 로 0.33...가 나오게 될 것인데, 이를 올림하게 되면 1이 되어 1분기가 됩니다.

다른 예시로 10월 이라면 10/3 으로 3.33333 이 나오게 될 것인데, 마찬가지로 올림하면 4분기가 됩니다.

 

 

4. CASE WHEN /IF -조건문

 

프로그래밍 언어 중 조건에 따라 작업방식을 달리 할 수 있는 조건문이 있습니다.

대표적으로 IF 문과 CASE 문 입니다.

 

CASE WHEN : 조건에 따라 값을 지정해 주는 역할을 합니다.

CASE WHEN 1

CASE WHEN 조건절 THEN 참일때 값 ELSE 거짓일 때 값 END 컬럼명

 

다중 CASE WHEN

CASE WHEN 2

 

CASE WHEN 조건1 THEN 참일 때 값 WHEN 조건2 THEN 참일 때 값 ... ELSE 마지막 값 END 컬럼명

IF ELSE : CASE WHEN 과 같은 조건문입니다. CASE 문과 마찬가지로 조건에 따라 원하는 작업을 수행할 수 있습니다.

IF ELSE

IF 조건이 참일 때 값 ELSE 거짓일 때 값 END 컬럼명

5. ROWNUM을 사용하여 번호매기기

Oracle처럼 ROWNUM을 사용하기

-- 초기화방법

SET구문으로 초기화 

SET

 

SET @ROWNUM:=0;

WHERE절에서 초기화

WHERE

SELECT @ROWNUM:=@ROWNUM+1, b.*
FROM table1
WHERE (@ROWNUM:=0)=0;

FROM절에서 초기화

FROM

 

SELECT @ROWNUM:=@ROWNUM+1, b.*
FROM table1 a, (SELECT @ROWNUM:=0 FROM dual) b;

201805보다 큰 10개의 년월이 필요했기 때문에 만약, 201805가 아니라면, 1씩 ROWNUM을 추가하여 년도를 10개 구할 수 있었다.

 

6. GROUP_CONCAT()

필요에 의해 서로 다른 결과를 한줄로 합쳐서 보여줘야 할 경우가 있습니다.

GROUP_CONCAT이란 같은 그룹 속에 속한 내용들 중 NULL 이 아닌 값들을 묶어 STRING으로 출력해 주는 함수입니다.

MYSQL에서 PIVOT을 지원하지 않기 때문에 대신 사용하는 방법으로 보통 쓰입니다.

저는 년월별로 값을 한 ROW 에 보여줘야 했기 때문에 위의 ROWNUM과 같이 사용하였습니다.

 

GROUP_CONCAT

 

GROUP_CONCAT(case when t6.rnum=1 then ceil(t6.sum) end) as '201805',

https://mia-dahae.tistory.com/82

[MySQL] 처음 사용해보는 PIVOT

http://www.artfulsoftware.com/infotree/qrytip.php?id=78

Artful Common Queries

pivot 참고