[성동2기 전Z전능 데이터 분석가] DAY 37

정렬과 연산

함수명	기능	예시	기능
ROUND	반올림 (기본: 소수점 첫째 자리)	SELECT ROUND(열,-1) FROM  테이블명;	1단위에서 반올림
CONCAT	문자열 결합	SELECT CONCAT(열1,"+", 열2) FROM  테이블명;	"열1+열2"를 출력
SUBSTRING	일부 문자열 반환 (주로 날짜 연산에서 사용)	SELECT SUBSTRING(열,3,1) FROM  테이블명;	3번째 자리수에서 1개의 글자를 출력한다
TRIM	문자열 앞뒤 공백 제거	TRIM('ABC   ')	'ABC' 출력
CURRENT _TIMESTAMP	시스템날짜를 출력한다 FROM 생략가능	SELECT CURRENT_TIMESTAMP;	시스템 날짜를 출력
DATE_ADD(기준시, INTERVAL n 단위)	날짜 덧셈	SELECT DATE_ADD (NOW(),INTERVAL 1 DAY);	내일을 출력
DATEDIFF('날짜1','날짜2')	날짜 뺄셈	SELECT DATEDIFF('날짜1','날짜2');	날짜1부터 날짜2까지 기간 출력
CASE	데이터 변환	SELECT a, CASE    WHEN 조건식1 THEN 1    WHEN 조건식2 THEN 식2    ELSE 식3 END as "칼럼명" FROM 테이블명	조건에 따라 출력하고  해당 출력의 칼럼명 지정

 

 

TRIM

CHAR형의 문자열형에는 문자열의 길이가 고정되어 남는 공간은 스페이스로 채워진다

=> 이때 사용하는 함수!

인수를 지정가능

=>스페이스 이외의 문자를 제거할 수도 있음

 

---

 

 

CASE문으로 데이터 변환하기

파이썬에서의 if문이라고 할 수 있다

 

CASE WHEN 조건식1 THEN 식1

          WHEN 조건식2 THEN 식2 ...

          ELSE 식3

 

when 만족할 시, THEN뒤에 있는  출력

 

 

 

case문에서 비교할 항목을 지정하고, when에는 1이나 2처럼

비교할 값만 기술하는 것도 가능

 

CASE 열 이름

WHEN 비교값 THEN 식1

END

 

---

 

coalesce

null값을 다룰 때 사용. null값을 변경하고 싶을 때 사용

열을 병합하지 않고 하나의 열 안에 있는 결측치를

다른 값으로 바꾸고 싶을 때 사용할 수 있음

 

---

집계와 서브쿼리

 

함수명	기능	예시	기능
COUNT	행 개수 구하기	SELECT COUNNT(*) FROM 테이블명;	전체 행 개수 구하기
DISTINCT	중복 제거하기	SELECT DISTINCT 열이름, 열이름2... FROM 테이블명;	중복되는 이름 지우기
		SELECT COUNT ( DISTINCT 열이름)  FROM 테이블명;	중복없이 카운팅
SUM	행의 합 구하기	SELECT SUM( 열이름 ) FROM 테이블명;	열의 원소들 합 구하기
AVG	행의 평균 구하기	SELECT AVG( 열이름 ) FROM 테이블명;	열의 평균 구하기
MIN	행의 최솟값 구하기	SELECT MIN( 열이름 ) FROM 테이블명;	열의 최솟값 구하기
MAX	행의 최댓값 구하기	SELECT MAX( 열이름 ) FROM 테이블명;	열의 최댓값 구하기
Group by	행들을 그룹화	SELECT name, count(name), sum(quantity) FROM 테이블명 Group by name;	name별로 그룹화하여 그룹별 행의 개수와 합계를 계산
HAVING	집계함수 조건식 지정	SELECT 열 FROM 테이블명 Group by 열 HAVING 조건식;	그룹화 이후 조건식 지정

 

 

COUNT

 

count의 인수로 * 대신 열이름을 지정해주면, 그 열에 한해서 행의 개수를 구할 수 있다.

Q. NULL 값이 있으면 어떻게 되나?
A. NULL 값은 제외하고 카운트한다.

 

예시)

name열의 중복을 제거할 경우, 제거하지 않을 경우

select count( all name), count(distinct name) from sample51;

(all은 넣어도 되고 생략해도 됨)

 

---

 

SUM

COUNT와 마찬가지로 NULL값을 무시한다.

 

---

Group by

혼자서 쓰면 특별하지 않지만 집계함수랑 같이 쓸 때 진정한 가치를 드러낸다.

열을 그룹화한 후, 다양한 집계함수들을 통해 집계 가능

 

주의할 점!

Group by에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT에 기술해서는 안됨.

 

 

<내부처리 순서>

WHERE→ GROUP BY → HAVING → SELECT → ORDER BY

 

• 집계함수는WHERE구의 조건식을 사용할 수 없다. 따라서 HAVING을 사용해서 지정 가능
• HAVING에서는 별명을 사용하는 것이 불가능하다.
• GROUP BY로 그룹화한 경우에도, ORDER BY를 통해서 정렬할 수 있다

 

예제

 

Q. track 테이블을 사용하여 다음 정보를 조회하는 쿼리를 작성하세요
각 아티스트의 ID (Composer 컬럼 사용)
각 아티스트가 작곡한 트랙의 수
각 아티스트의 트랙들의 총 재생 시간 (분 단위로 변환)
각 아티스트의 트랙들의 총 가격트랙 수가 5개 이상이고, 
총 재생 시간이 30분을 초과하는 아티스트만 조회
결과를 트랙 수가 많은 순서대로 정렬

 

조건을 어떻게 처리할지가 고민이었음

WHERE 절에 다 떄려넣을까?

where count(TrackId) >=55 and sum(Milliseconds)/60000 >30 and ID is not null;

오류 발생...

 

Why? 집계함수는 Where구의 조건식을 사용할 수 없기 때문

select composer as ID,

count(*) as trackcount,

sum(Milliseconds)/60000 as playtime,

sum(UnitPrice)

from track

where composer is not null

group by composer

having count(TrackId) >=5 and sum(Milliseconds)/60000 >30

order by trackcount desc;

 

그래서 이런 식으로 where과 having을 나눠서 사용해줘야한다.