개발일지
이번 글에서는 SQL 함수와 GROUP BY, HAVING, ORDER BY 에 대하여 정리해보려 합니다.
이번 학습을 통해 SQL 주요 함수들을 다양한 상황에서 활용하며 데이터를 효과적으로 다루는 방법을 익혀보았습니다. SQL을 통해 데이터를 다양한 기준에 따라 그룹화하고 정렬하며 필요한 정보를 추출할 수 있습니다. 이러한 기능들을 조합하여 데이터베이스에서 유용한 정보를 효율적으로 분석할 수 있었습니다.
함수
숫자형 함수
- ABS 함수:
- 설명: 숫자의 절댓값을 반환합니다.
- 사용법: ABS(숫자)
- CEIL 또는 CEILING 함수:
- 설명: 주어진 숫자 이상의 가장 작은 정수를 반환합니다.
- 사용법: CEIL(숫자) 또는 CEILING(숫자)
- FLOOR 함수:
- 설명: 주어진 숫자 이하의 가장 큰 정수를 반환합니다.
- 사용법: FLOOR(숫자)
- ROUND 함수:
- 설명: 숫자를 반올림하여 반환합니다.
- 사용법: ROUND(숫자, [소수자릿수])
- TRUNC 함수:
- 설명: 숫자를 잘라내어 정수 부분만 남깁니다.
- 사용법: TRUNC(숫자, [소수자릿수])
- MOD 함수:
- 설명: 나눗셈의 나머지를 반환합니다.
- 사용법: MOD(나눠질숫자, 나눌숫자)
- POWER 함수:
- 설명: 숫자의 거듭제곱을 계산합니다.
- 사용법: POWER(숫자, 지수)
- SQRT 함수:
- 설명: 숫자의 제곱근을 계산하여 반환합니다.
- 사용법: SQRT(숫자)
- EXP 함수:
- 설명: 오일러 상수 e의 숫자 제곱을 반환합니다.
- 사용법: EXP(숫자)
- LN 함수:
- 설명: 숫자의 자연 로그를 계산하여 반환합니다.
- 사용법: LN(숫자)
- LOG 함수:
- 설명: 주어진 밑(base)에 대한 로그를 계산하여 반환합니다.
- 사용법: LOG(숫자, 밑)
- SIGN 함수:
- 설명: 숫자의 부호를 반환합니다.
- 사용법:
SIGN(숫자)
- TRUNCATE 함수:
- 설명: 숫자를 지정된 자릿수로 잘라내어 반환합니다.
- 사용법: TRUNCATE(숫자, 자릿수)
- BIN_TO_NUM 함수:
- 설명: 이진수를 십진수로 변환합니다.
- 사용법: BIN_TO_NUM(이진수)
- NUMTODSINTERVAL 함수:
- 설명: 숫자를 일정한 간격의 시간으로 변환합니다.
- 사용법: NUMTODSINTERVAL(숫자, ‘간격’)
집계 함수
- AVG() 함수:
- 설명: 숫자 열의 평균 값을 계산합니다.
- 사용법: AVG(열이름)
- SUM() 함수:
- 설명: 숫자 열의 합을 계산합니다.
- 사용법: SUM(열이름)
- COUNT() 함수:
- 설명: 행의 수를 세어 갯수를 반환합니다.
- 사용법: COUNT(열이름)
- MIN() 함수:
- 설명: 숫자나 날짜 열에서 최소 값을 찾습니다.
- 사용법: MIN(열이름)
- MAX() 함수:
- 설명: 숫자나 날짜 열에서 최대 값을 찾습니다.
- 사용법: MAX(열이름)
문자열 함수
- CONCAT() 함수:
- 설명: 문자열을 연결합니다.
- 사용법: CONCAT(문자열1, 문자열2) 또는 문자열1 || 문자열2
- LENGTH() 함수:
- 설명: 문자열의 길이를 반환합니다.
- 사용법: LENGTH(문자열)
- SUBSTR() 함수:
- 설명: 문자열을 잘라내어 일부분을 반환합니다.
- 사용법: SUBSTR(문자열, 시작위치, 길이)
- INSTR() 함수:
- 설명: 문자열에서 특정 문자나 부분 문자열의 위치를 찾습니다.
- 사용법: INSTR(원본문자열, 찾을문자열)
- UPPER() 함수:
- 설명: 문자열을 대문자로 변환합니다.
- 사용법: UPPER(문자열)
- LOWER() 함수:
- 설명: 문자열을 소문자로 변환합니다.
- 사용법: LOWER(문자열)
- TRIM() 함수:
- 설명: 문자열의 앞뒤에서 특정 문자나 공백을 제거합니다.
- 사용법: TRIM([leading | trailing | both] 제거할문자 FROM 문자열)
- INITCAP() 함수:
- 설명: 첫 글자를 대문자로 반환합니다.
- 사용법: INITCAP(문자열)
- REPLACE() 함수:
- 설명: 문자열에서 특정 문자나 문자열을 다른 문자나 문자열로 교체합니다.
- 사용법: REPLACE(원본문자열, 찾을문자열, 바꿀문자열)
- LPAD() 함수:
- 설명: 문자열을 지정된 길이만큼 왼쪽으로 채우는 데 사용됩니다.
- 사용법: LPAD(원본문자열, 지정된길이, [패딩문자열])
- RPAD() 함수:
- 설명: 문자열을 지정된 길이만큼 오른쪽으로 채우는 데 사용됩니다.
- 사용법: RPAD(원본문자열, 지정된길이, [패딩문자열])
날짜 및 시간 함수
- SYSDATE 함수:
- 설명: 현재 날짜와 시간을 반환합니다.
- 사용법: SYSDATE
- TO_DATE() 함수:
- 설명: 문자열을 날짜 형식으로 변환합니다.
- 사용법: TO_DATE(문자열, 형식)
- MONTHS_BETWEEN() 함수:
- 설명: 두 날짜 간의 월 차이를 계산합니다.
- 사용법: MONTHS_BETWEEN(날짜1, 날짜2)
- ADD_MONTHS() 함수:
- 설명: 날짜에 월을 더합니다.
- 사용법: ADD_MONTHS(날짜, 월수)
- TRUNC() 함수:
- 설명: 날짜를 더 낮은 단위로 자릅니다.
- 사용법: TRUNC(날짜, [형식])
- LAST_DAY() 함수:
- 설명: 월의 마지막 날짜를 반환합니다.
- 사용법: LAST_DAY(날짜)
- NEXT_DAY(date, char) 함수:
- 설명: 특정 날짜(date) 이후로 다음으로 나타나는 특정 요일(char)의 날짜를 반환합니다.
- 사용법: NEXT_DAY(날짜, 요일)
조건 함수
- CASE 함수:
- 설명: 조건에 따라 다른 값을 반환합니다.
- 사용법: CASE WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2 ELSE 기본결과 END
- DECODE 함수:
- 설명: 조건에 따라 다른 값을 반환하는 간단한 CASE 표현입니다.
- 사용법: DECODE(열, 값1, 결과1, 값2, 결과2, …, 기본결과)
변환 함수
- TO_NUMBER() 함수:
- 설명: 문자열을 숫자로 변환합니다.
- 사용법: TO_NUMBER(문자열)
- TO_CHAR() 함수:
- 설명: 값을 문자열로 변환합니다.
- 사용법: TO_CHAR(값, [형식])
- TO_DATE() 함수:
- 설명: 문자열을 날짜로 변환합니다.
- 사용법: TO_DATE(문자열, [형식])
분석 함수
- RANK() 함수:
- 설명: 순위를 할당합니다.
- 사용법: RANK() OVER (PARTITION BY 파티션열 ORDER BY 정렬열)
- DENSE_RANK() 함수:
- 설명: 밀집 순위를 할당합니다.
- 사용법: DENSE_RANK() OVER (PARTITION BY 파티션열 ORDER BY 정렬열)
- ROW_NUMBER() 함수:
- 설명: 각 행에 고유한 번호를 할당합니다.
- 사용법: ROW_NUMBER() OVER (PARTITION BY 파티션열 ORDER BY 정렬열)
- LAG() 함수:
- 설명: 이전 행의 값을 현재 행과 함께 반환합니다.
- 사용법: LAG(열, 오프셋, 기본값) OVER (ORDER BY 정렬열)
- LEAD() 함수:
- 설명: 다음 행의 값을 현재 행과 함께 반환합니다.
- 사용법: LEAD(열, 오프셋, 기본값) OVER (ORDER BY 정렬열)
GROUP BY
GROUP BY 절은 SQL에서 특정 열(또는 열들)을 기준으로 행들을 그룹화하여 집계 함수를 적용하는 데 사용됩니다. GROUP BY를 사용하는 데 있어서 주의해야 할 몇 가지 규칙과 사용법이 있습니다.
그룹화할 열 명시
- GROUP BY를 사용하려면 쿼리의 SELECT 문에서 그룹화할 열(들)을 명시해야 합니다. 이 열들은 집계 함수를 적용하기 위한 기준 역할을 합니다.
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id;집계 함수 사용
- GROUP BY와 함께 사용되는 주된 함수로는 COUNT, SUM, AVG, MIN, MAX 등이 있습니다. 그룹화된 결과에 대한 계산을 수행하는 데 사용됩니다.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;HAVING 절 사용
- HAVING 절은 그룹화된 결과에 대한 조건을 지정하는 데 사용됩니다. WHERE 절은 그룹화되기 전에 적용되며, HAVING 절은 그룹화된 결과에 적용됩니다.
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5;그룹화된 열 외의 열 사용
- GROUP BY 절에서 그룹화된 열 외의 열을 SELECT 문에 사용하려면 해당 열에 대한 집계 함수를 적용해야 합니다.
SELECT department_id, AVG(salary) AS avg_salary, MAX(job_id) AS max_job_id
FROM employees
GROUP BY department_id;정렬과 GROUP BY
- GROUP BY와 함께 사용되는 경우, 그룹화된 결과는 주로 그룹화된 열 기준으로 정렬됩니다. 그러나 정확한 정렬 순서를 원한다면 ORDER BY 절을 추가해야 합니다.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;NULL 값 처리
- GROUP BY는 NULL 값을 하나의 그룹으로 간주합니다. NULL 값을 가진 행들은 하나의 그룹으로 묶입니다.
SELECT job_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY job_id;✔️GROUP BY를 사용할 때 주의할 점은 명시한 열 외의 열은 대부분 집계 함수를 사용해야 하며, HAVING 절을 통해 그룹 단위로 조건을 필터링할 수 있다는 것입니다. 그룹화된 결과를 분석하고 원하는 집계를 얻기 위해선 적절한 집계 함수와 조건을 사용하는 것이 중요합니다.
HAVING 절
HAVING 절은 SQL에서 그룹화된 결과에 대한 조건을 지정하는 데 사용되며, 주로 GROUP BY 절과 함께 사용됩니다. HAVING 절은 특정 집계 함수를 적용한 그룹에 대한 조건을 설정하여 그룹화된 결과를 필터링하는 데에 활용됩니다.
집계 함수와 함께 사용
- HAVING 절은 주로 집계 함수와 함께 사용됩니다. 집계 함수를 적용한 그룹에 대한 조건을 지정하여 그룹화된 결과를 필터링합니다.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;위의 예제에서 HAVING AVG(salary) > 5000은 평균 급여가 5000보다 큰 그룹만을 선택합니다.
집계 함수 이외의 조건 지정 (HAVING)
- HAVING은 집계 함수 외에도 일반적인 비교 연산자, 논리 연산자를 사용하여 그룹화된 결과에 대한 조건을 지정할 수 있습니다.
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5 AND MAX(salary) > 80000;위의 예제에서는 직원 수가 5명 이상이고 최대 급여가 80000보다 큰 그룹만을 선택합니다.
NULL 값 처리
- HAVING은 NULL 값을 처리할 수 있으며, NULL 값을 가진 그룹은 조건을 충족하거나 충족하지 않는 그룹에 포함됩니다.
SELECT job_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY job_id
HAVING COUNT(employee_id) > 5 OR COUNT(employee_id) IS NULL;위의 예제에서는 직무별로 직원 수가 5명 이상이거나 NULL 값을 가진 그룹을 선택합니다.
HAVING vs WHERE
- HAVING은 그룹화된 결과에 대한 조건을 지정하는 데 사용되고, WHERE은 행 단위의 조건을 지정하는 데 사용됩니다. 따라서 HAVING은 GROUP BY 절과 함께 사용되어야 하며, WHERE은 HAVING 이전에 사용됩니다.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > TO_DATE('2022-01-01', 'YYYY-MM-DD')
GROUP BY department_id
HAVING AVG(salary) > 5000;위의 예제에서는 WHERE 절로 특정 기간 이후 입사한 직원만을 필터링하고, 그 후 GROUP BY와 HAVING으로 그룹화된 결과를 조건에 맞게 선택합니다.
💡HAVING 절은 그룹화된 결과에 대한 조건을 지정하여 필요한 그룹만을 선택하는 도구입니다. 사용 시 GROUP BY와 함께 활용하여 데이터를 적절하게 필터링하고 원하는 그룹을 추출할 수 있습니다.
ORDER BY
ORDER BY 구문은 SQL에서 사용되며 결과 집합을 특정 기준에 따라 정렬하는데에 쓰입니다. 이 구문을 사용하면 데이터를 원하는 순서로 표시할 수 있습니다. ORDER BY는 SELECT 문의 끝에 나타나며, 정렬에 사용되는 열(또는 열 목록)과 정렬 순서(오름차순 또는 내림차순)를 지정할 수 있습니다.
ORDER BY 기본 구문
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;- column1, column2, …: 결과 집합에 포함할 열을 나타냅니다.
- table_name: 데이터를 가져올 테이블의 이름입니다.
- ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], …: 정렬에 사용할 열과 정렬 방법을 지정합니다. ASC는 오름차순(기본값), DESC는 내림차순을 나타냅니다.
ORDER BY 예시
예를 들어, 다음은 “employees” 테이블에서 사원 이름과 입사일을 입사일 기준으로 내림차순으로 정렬하는 예시입니다.
SELECT employee_name, hire_date
FROM employees
ORDER BY hire_date DESC;마무리
이번 글에서는 SQL의 기본 개념 중 함수, GROUP BY, HAVING, ORDER BY에 대해 다뤄보았습니다. SQL 함수를 사용하여 숫자, 문자열, 날짜 및 시간 등 다양한 데이터 유형을 다루고, GROUP BY를 통해 데이터를 그룹화하며 집계 함수를 활용하여 그룹 단위로 연산을 수행하는 방법을 살펴보았습니다. HAVING 절은 그룹화된 결과에 조건을 적용하여 원하는 그룹을 선별하는 데에 활용되고, ORDER BY를 사용하여 결과 집합을 특정 열을 기준으로 정렬하는 방법을 학습했습니다.
이러한 SQL 기본 개념은 데이터베이스에서 효과적인 데이터 처리와 분석을 위한 중요한 도구들입니다. 함수를 적절히 활용하고 데이터를 그룹화하며 정렬하는 것은 다양한 업무에서 필수적인 능력이 될 것입니다.
