집계함수
집계 함수는 여러 행의 값을 하나의 결과 값으로 요약하는 함수입니다.
count, sum, avg, max, min 이 있습니다.
-- 전체 카운트
SELECT count(*) FROM member;
-- member 에서 teamId 가 null 이 아닌 열 카운트
SELECT count(teamId) FROM member;주의! count(*) 과 count(column) 은 값이 다릅니다.
count(column) 은 해당 컬럼의 null 을 제거한 값을 응답합니다.
그룹함수
집계 함수와 함께 사용되는 함수들 입니다.
-- 첫 번쨰 컬럼을 기준으로 계층적으로 집계합니다.
-- department + job 조합
-- department 별로 소계
-- 전체 합계
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY ROLLUP(region, product);
-----------------------------
region | product | sum(sales)
Asis | A | 100 -- 상세
Asis | B | 200
Asis | null | 300 -- 소계
EU | A | 300
EU | B | 400
EU | null | 700
null | null | 1000 -- 총계
-----------------------------
-- ROLLUP 중첩괄호를 사용하는 경우 department + job 조합의 복합키에 대한 상세정보와 총계 응답
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY ROLLUP((region, product));
-----------------------------
region | product | sum(sales)
Asis | A | 100
Asis | B | 200
EU | A | 300
EU | B | 400
null | null | 1000
------------------------------- department + job 조합
-- department 별로 소계
-- job 별로 소계
-- 전체 합계
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY CUBE (region, product);
-----------------------------
region | product | sum(sales)
Asis | A | 100
Asis | B | 200
Asis | null | 300
EU | A | 300
EU | B | 400
EU | null | 700
null | A | 400
null | B | 600
null | null | 1000
-----------------------------
-- ROLLUP, CUBE 와 함께 사용됩니다 (1/0 으로 응답됩니다)
-- null 로 응답되는 값에 1을 리턴함으로서 어떤 그룹인지 명시합니다.
SELECT region, product, SUM(sales),
GROUPING(region) AS region_grp,
GROUPING(product) AS product_grp
FROM sales_data
GROUP BY CUBE (region, product);
--------------------------------------------------------
region | product | sum(sales) | region_grp | product_grp
Asis | A | 100 | 0 | 0
Asis | B | 200 | 0 | 0
Asis | null | 300 | 0 | 1
EU | A | 300 | 0 | 0
EU | B | 400 | 0 | 0
EU | null | 700 | 0 | 1
null | A | 400 | 1 | 0
null | B | 600 | 1 | 0
null | null | 1000 | 1 | 1
--------------------------------------------------------SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS (
(region, product), -- 각 조합
(region), -- 소계
() -- 전체 합계
);
--- 다음의 결과와 같습니다 ---
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY region, product
UNION ALL
SELECT region, NULL AS product, SUM(sales)
FROM 테이블
GROUP BY product
UNION ALL
SELECT NULL AS region, NULL AS product, SUM(COL3)
FROM 테이블
-----------------------------
region | product | sum(sales)
Asis | A | 300
Asis | B | 700
Africa | A | 400
Asis | null | 1000
Africa | null | 400
null | null | 1400
-----------------------------
윈도우 함수
행과 행 간의 관계를 정의하기 위한 함수 입니다.
기본적인 형태는 <윈도우 함수> OVER (<윈도우 정의>) 입니다.
| 윈도우 함수 | 설명 |
|---|---|
| ROW_NUMBER() | 행에 순서를 매깁니다 |
| RANK() | 동점일 경우 같은 순위를 매깁니다 |
| DENSE_RANK() | 동점일 경우 같은 순위를 매기며 다음 순위자의 순번이 연결됩니다 |
| SUM(Col) | 윈도우 정의에 따른 Col 의 합계를 구합니다 |
| NTILE(n) | 데이터를 n개의 구간으로 나누어 각 행에 해당 구간 번호를 부여 합니다 |
| LAG(Col) | 해당 컬럼의 이전값을 가져 옵니다 (기본값 == 1) |
| NTILE(Num) | Num 만큼 그룹으로 나눠서 숫자 부여 |
| 윈도우 정의 | 설명 |
|---|---|
| PARTITION BY | 데이터를 그룹화 합니다 |
| ORDER BY | 각 파티션 내에서 정렬 기준을 설정합니다 |
| ROWS BETWEEN num1 PRECENDING AND num2 FOLLOWING | 현재 값을 기준으로 -num1 ~ +num2 의 값을 범위로 지정 |
| RANGE UNBOUNDED PRECEDING | 파티션 전체를 범위로 지정 |
| RANGE num1 PRECEDING | 현재 값을 기준으로 -num1 을 범위로 지정 |
-- 학급별 등수를 출력
select name, class, score,
rank() over (partition by class order by score desc) as class_rank
from t
-----------------------------------
name | class | score | class_rank |
A | 1 | 80 | 1
B | 1 | 70 | 2
C | 1 | 60 | 3
D | 2 | 70 | 1
E | 2 | 60 | 2
-----------------------------------
-- 학급별 총 점을 출력
select name, class, score,
sum(score) over (partition by class) as class_total_score
from t
------------------------------------------
name | class | score | class_total_score |
A | 1 | 80 | 210
B | 1 | 70 | 210
C | 1 | 60 | 210
D | 2 | 70 | 130
E | 2 | 60 | 130
------------------------------------------
문자열 함수
| 함수명 | 설명 | 예제 |
|---|---|---|
| LOWER | 소문자로 변환 | SELECT LOWER(컬럼명) FROM 테이블명; |
| UPPER | 대문자로 변환 | SELECT UPPER(컬럼명) FROM 테이블명; |
| LENGTH | 길이 반환 | SELECT LENGTH(컬럼명) FROM 테이블명; |
| SUBSTR | 잘라서 응답 | SELECT SUBSTR(컬럼명, START, END) FROM 테이블명; |
| INSTR | 위치 반환 | SELECT INSTR(컬럼명, '문자열') FROM 테이블명; |
| LPAD | 특정 길이가 되도록 채움 | SELECT LPAD(컬럼명, 길이, 채울 문자열) FROM 테이블명; |
| RPAD | 특정 길이가 되도록 채움 | SELECT RPAD(컬럼명, 길이, 채울 문자열) FROM 테이블명; |
| TRIM | 공백 제거 | SELECT TRIM(컬럼명) FROM 테이블명; |
| LTRIM | 공백 제거 | SELECT LTRIM(컬럼명) FROM 테이블명; |
| RTRIM | 공백 제거 | SELECT RTRIM(컬럼명) FROM 테이블명; |
| REPLACE | 문자열 치환 | SELECT REPLACE(컬럼명, OLD, NEW) FROM 테이블명; |
숫자 함수
| 함수명 | 설명 | 예제 |
|---|---|---|
| ROUND | N번째 소수점 자리에서 반올림 | SELECT ROUND(컬럼명, N) FROM 테이블명; |
| TRUNCATE | N번째 소수점 자리이하 숫자 버림 | SELECT TRUNCATE(컬럼명, N) FROM 테이블명; |
| CEIL/FLOOR | N번째 소수점 자리에서 올림/내림 반환 | SELECT CEIL(컬럼명, N) FROM 테이블명; |
| POWER | N번 곱한 숫자 반환 | SELECT POWER(컬럼명, N) FROM 테이블명; |
날짜 함수 (Oracle)
오라클은 날짜를 숫자처럼 취급하기 때문에 연산을 통해 날짜 추가가 가능합니다.
1을 더한 경우 1일이 추가되며, 1/24/60 을 더한 경우 1분이 추가 됩니다.
| 함수명 | 설명 | 예제 |
|---|---|---|
| SYSDATE | 날짜 반환 | SELECT SYSDATE FROM 테이블명; |
| SYSTIMESTAMP | 날짜와 시간 반환 | SELECT SYSTIMESTAMP FROM 테이블명; |
| ADD_MONTHS | 특정 월만큼 더한 날짜 반환 | SELECT ADD_MONTHS(SYSDATE, 3) FROM 테이블명; |
| NEXT_DAY | 특정 날만큼 더한 날짜 반환 | SELECT ADD_MONTHS(SYSDATE, 3) FROM 테이블명; |
| LAST_DAY | 전 날 반환 | SELECT LAST_DAY(SYSDATE) FROM 테이블명; |
| TO_DATE | 문자열을 날짜 타입으로 변환 (미입력 타입은 0으로 설정) | SELECT TO_DATE('문자열', '형식') FROM DUAL |
분기문 (Oracle)
| 함수명 | 설명 | 예제 |
|---|---|---|
| NVL | null인 경우 val 응답 | SELECT NVL(컬럼명, val) FROM 테이블명; |
| NVL2 | 컬럼 != null ? val1 : val2 | SELECT NVL(컬럼명, val1, val2) FROM 테이블명; |
| CASE | 조건에 따른 응답 | SELECT CASE WHEN COL1 = 'A' THEN 1 WHEN COL1 = 'B' THEN 2 ELSE 0 END FROM 테이블명; SELECT CASE COL1 WHEN 'A' THEN 1 WHEN 'B' THEN 2 ELSE 0 END FROM 테이블명; |
| DECODE | 조건에 따른 응답 | SELECT DECODE (COL1, 'A', 1, 'B', 1, 0) FROM 테이블명; |
| NULLIF(A, B) | A == B ? null : A | SELECT NULLIF(A, B) FROM DUAL; |
| COALESCE(Col1, Co2..) | null 이 아닌 첫 번째 값 응답 | SELECT COALESCE(A, null) FROM DUAL; |
차분하게 단단히 쌓아가는 개발자