[성동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을 나눠서 사용해줘야한다.