[아이티윌 빅데이터 52기] Day 3 SQL 함수 2 그룹 조회
SQL 함수2 그룹 조회
<학습 내용>
-날짜 시간 함수
-형변환 / 조건 함수
-집계 함수
-그룹 조회
-윈도우 함수
날짜 시간 함수
1. 현재 날짜/ 시간 조회 : NOW() CURDATE() CURTIME()
NOW() 현재 날짜와 시간 반환
CURDATE(): 현재 날짜만 반환
CURTIME():현재 시간 반환
*컴퓨터에 내장된 값을 가져오기 때문에 FROM 안 붙음
SELECT NOW() AS 현재_날짜시간, CURDATE() AS 오늘날짜, CURTIME() AS 현재시간;
2,테이블에 저장된 날짜 데이터를 가져오는 DATE() ,TIME()
-데이터 타입이 날짜/시간인 경우에 대해 활용 가능
3. YEAR() MONTH () DAY()
년도 월 일자를 추출
4.DATE_ADD () 날짜 연산
DATE_ADD(DATE, INTERVAL {{N}} {{계산할 단위}})
-계산할 단위에 들어올 수 있는 값들
-마이너스를 달고 쓰면 뺼셈으로 처리
5. DATE_SUB()
DATE_ADD 와 반대로 뺼셈 계산하는 함수,
음수값을 넣으면 더하기처럼 바뀜
6. DATE_DIFF()
-날짜 간의 차이를 계산
-앞의 인수(날짜)가 최신 날짜여야함
-10/2 , 10/1 이렇게
-오늘을 기준으로 빼고 싶을 때, CURDATE, NOW 등을 사용할 수 있음
같은 날짜를 넣었을 때는 계산이 0으로 나옴, 따라서 같은 날짜 간의 차이를 1일로 계산해야하면 +1 필요
EX) 입사일, 근무일 이 같을 떄, 총 근무 일수 > +1 해줘야함
7.DATE_FORMAT()
%Y-%m-%d
where절에서 필터링으로 사용가능
8.QUARTER
분기를 가져오는 함수
<예제 문제>
1.professors 테이블에서 각 교수의 입사일로부터 현재까지 근속연수(년)를 계산하시오.
SELECT name , HIREDATE , CURDATE() , YEAR(HIREDATE) , YEAR(CURDATE()) , YEAR(CURDATE())-YEAR(HIREDATE) , (DATEDIFF(CURDATE(), hiredate) / 365) , FLOOR(DATEDIFF(CURDATE(), hiredate) / 365) AS 근속연수 FROM PROFESSORS;
SELECT name AS 교수이름 , hiredate AS 입사일 , FLOOR(DATEDIFF(CURDATE(), hiredate) / 365) AS 근속연수 FROM professors ORDER BY 근속연수 DESC LIMIT 0, 5;
SELECT HIREDATE, CURDATE() , DATEDIFF(CURDATE(),HIREDATE) ,FLOOR(DATEDIFF(CURDATE(),HIREDATE)/365) AS '근속년수' FROM PROFESSORS ORDER BY 근속년수 DESC ;
2.departments 테이블에서 설립 연도가 30년이상 지난 학과명과 설립 연도를 조회하시오.
SELECT DNAME,ESTABLISHED ,YEAR(CURDATE()) , YEAR(CURDATE()) - ESTABLISHED FROM DEPARTMENTS WHERE YEAR(CURDATE()) - ESTABLISHED>=30;
3.students 테이블에서 생년월일을 'MM월 DD일' 형식으로 변환하여 출력하시오.
SELECT NAME, BIRTHDATE , DATE_FORMAT(BIRTHDATE, '%m월 %d일') FROM STUDENTS;
4.enrollments 테이블에서 수강 신청일로부터 30일 후 날짜를 다음 시험일로 간주하고 출력하시오.
select * , date_add(enroll_date, interval 30 day) as '시험일' from enrollments;
5.students 테이블에서 생년월일 기준으로 나이가 가장 많은 학생의 이름과 나이(년)를 출력하시오
SELECT NAME, BIRTHDATE, CURDATE() , DATEDIFF(CURDATE(),BIRTHDATE)/365 , FLOOR(DATEDIFF(CURDATE(),BIRTHDATE)/365) AS '나이' FROM STUDENTS ORDER BY DATEDIFF(CURDATE(),BIRTHDATE)/365 DESC;
SELECT NAME, BIRTHDATE, CURDATE() , DATEDIFF(CURDATE(),BIRTHDATE)/365 , FLOOR(DATEDIFF(CURDATE(),BIRTHDATE)/365) AS '나이' FROM STUDENTS ORDER BY DATEDIFF(CURDATE(),BIRTHDATE)/365 DESC LIMIT 1;
* 나이 / 근속 년수 등 지난 년도수를 구해야할 때
> 내가 생각한 방식은 year(curdate()) - year(birthdate) , YEAR(CURDATE())-YEAR(HIREDATE)
이런식으로 현재 일자의 년도에서 해당 년도를 빼는 방식
다만 이런 방식이면
CURDATE() 가 2025년 10월 2일이고, HIREDATE 가 2015년 11월 29일 즉, 1년이 안 채워진 상황일 때에도
1년이 채워졌다고 계산되어서 10년으로 처리
이때 만 1년을 기준으로 계산하기 위해서는 일자 단위로 내려와서 실제로 365일이 지났는지를 확인해야함
이떄 접근할 수 있는 방식은 두 날짜를 뺸 다음 365로 나눠서 년도수를 세고, 그 외 소수점을 버리는 것
FLOOR(DATEDIFF(CURDATE(), birthdate) / 365)
이렇게 했을 때 CURDATE() 가 2025년 10월 2일이고, HIREDATE 가 2015년 11월 29일 를 9년으로 추출할 수 있음
<예제 문제2>
1.지금 이 순간을 기준으로 현재 날짜와 시간을 조회하시오
SELECT NOW(),CURDATE(),CURTIME()
2.202번학과에 소속된 교수의 이름,입사일,입사일에서 날짜부분과 시간부분을 따로 나누어 조회하시오
SELECT NAME,DATE(HIREDATE), TIME(HIREDATE) FROM PROFESSORS WHERE DEPARTMENT_ID=202;
3.402번학과에 소속된 교수의 이름,입사년도,입사월,입사일을 구분하여 조회하시오.
SELECT NAME,YEAR(HIREDATE), MONTH(HIREDATE),DAY(HIREDATE) FROM PROFESSORS WHERE DEPARTMENT_ID=402;
4.이번학기의 수강신청이 끝났다. 수강신청 정정은 각 과목별로 수강신청 당일로부터 1주일간 가능하다.
학생들은 과목별로 언제까지 수강신청 정정이 가능한지를 알아보려고한다.
학번,과목번호,수강신청일,수강신청 정정 마감일을 조회하시오
select student_id,
subject_id,
enroll_date,
date_add(enroll_date, interval 7 day) as 정정마감일
from enrollments limit 15;
5.졸업일로부터 1개월전부터 졸업 가운 대여 신청을 할 수 있다고 한다.
졸업 날짜가 확정된 학생의 이름과 졸업 가운 대여가 가능한 시작 날짜를 조회하시오.
select name,
graduation_date ,
date_sub(graduation_date, interval 1 month) as '졸업가운 대여 가능일'
from students
where graduation_date is not null
6.교수에 대해 이름,입사일을 조회하시오.입사일은"YYYY년MM월DD일" 형식으로 출력하시오.
select name, hiredate , date_format(hiredate, '%Y년 %m월 %d일')from professors;
7.2024년 7월 혹은 8월에 입학한 학생의 이름,학년,성별,학적상태,입학일을 입학일이 빠른순으로 조회하시오.
select name,grade,status,admission_date from students
where year(admission_date) = 2024 and month(admission_date) in (07,08)
order by admission_date;
8.교수들의 이름과 직급,입사한 분기를 분기순으로 조회하시오.
select name,position,hiredate from professors
order by quarter(hiredate);
형변환/조건 함수
형변환 함수란? 데이터 타입을 변환 시키는 것
CAST ( A AS TYPE)
CONVERT(A,TYPE)
조건함수란?
IF(조건 , 참, 거짓)
IFNULL(A,0) A 가 NULL 이면 0 반환
NULLIF(A,0) A 가 0 이면 NULL 반환
숫자/문자형 외에 문자형을 날짜형으로 바꿀 떄에도 CAST 사용
(여기서는 FROM 불필요)
WHERE 절에 쓰던 조건을 SELECT 절에서 처리할 수도 있어
<예제문제>
1.학생의 이름(`name`)과 학년(`grade`)을 조회하고,학년에 따라 다음과 같이 "구분"을 표시하는 새로운 컬럼을 만드시오.
->1학년:신입생/2학년:재학생/3학년:재학생/4학년:졸업반
SELECT
NAME,
GRADE,
CASE WHEN GRADE = 1 THEN '신입생'
WHEN GRADE = 2 THEN '재학생'
WHEN GRADE = 3 THEN '재학생'
WHEN GRADE = 4 THEN '졸업반'
ELSE '-' END '구분'
FROM STUDENTS
ORDER BY GRADE ASC;
2.학생의 이름,키,키 상태를 조회하려고 한다.
키 상태는 학생의 키가 170cm 이상이면 NULL, 그보다 작으면 `'작음'` 이라고 표시하되,
이 결과가 NULL일 경우에는 `'경고'` 라는 문자열로 대체한 값이다.
SELECT NAME,
HEIGHT,
IFNULL(IF(HEIGHT>=170,NULL,'작음') ,'경고')
FROM STUDENTS
ORDER BY HEIGHT;
3.학과 테이블에서 학과명(`dname`)과 설립연도(`established`)를 조회하시오.
설립연도는 'YYYY년' 형식의 문자열로 변환하여 '설립연도' 라는 별칭으로 표시하시오.
select dname,established,concat(cast(established as char),'년') from departments;
*데이터 타입 종류에는 varchar 가 없고 char 가 있음
4.학생의이름,성별,성별정보를조회하려고한다.성별정보는`'남'`은`'Male'`,`'여'`는`'Female'`, 그외는`'기타'`로구분하는값이다.성별정보가입력되지않은경우는`'미입력'`으로출력되어야한다.
select name,
gender,
case when gender = '남' then 'Male'
when gender = '여' then 'Female'
WHEN gender is null then '미입력'
else '기타' end as '성별정보'
from students
order by gender , name;
5.학생들의 키와 몸무게를 활용하여 건강지수를 계산하려고한다.
키(cm)의 제곱을 몸무게(kg)로 나눈 값을 소수점 둘째 자리까지 반올림하여 `건강지수`로 출력하고,
그 값이 35 이상이면 `'우수'`, 30 이상이면`'양호'`, 그보다 작으면 `'주의'` 라고 판단하여 `건강등급`으로 함께 표시하시오
select height,
weight,
round(power(height,2)/weight,2) as 건강지수,
case when round(power(height,2)/weight,2) >= 35 then '우수'
when round(power(height,2)/weight,2) >= 30 then '양호'
else '주의' end as '건강등급'
from students
order by height desc,weight desc,건강지수 desc;
집계 함수
여러개의 행을 하나의 값으로 요약
where 절에서 쓸 수 없음
모두 null 을 제외하고 계산
결과 값이 모두 하나 (단일 행 함수)
<연습문제>
1.`students`테이블을 사용하여, 현재 4학년인 학생의 총 수와 졸업(`status`='졸업')한 학생의 총 수를 각각 조회하시오.
SELECT COUNT(ID) FROM STUDENTS WHERE GRADE=4;
SELECT COUNT(ID) FROM STUDENTS WHERE STATUS ='졸업'
*내가 한 방식은 완전 다른 구문을 활용해서 따로 구했는데,
CASE WHEN 절을 COUNT 안에 넣어서 하나의 구문으로 구할 수 있었음
SELECT COUNT(CASE WHEN GRADE=4 AND STATUS = '재학' THEN ID END),
COUNT(CASE WHEN STATUS = '졸업' THEN ID END)
FROM STUDENTS;
2.`professors`테이블에서 직급(`position`)이 '교수'인 분들의 급여(`sal`) 총 합을 조회하시오.
SELECT SUM(SAL) FROM PROFESSORS WHERE POSITION ='교수'
3.`students`테이블에서 모든 학생의 키(`height`)에 대한 평균,최대값,최소값을 각각 조회하시오.
(단,평균키는 소수점 둘째자리에서 반올림하여 첫째자리까지만 표시)
SELECT ROUND(AVG(HEIGHT),1) , MAX(HEIGHT), MIN(HEIGHT) FROM STUDENTS;
4.`enrollments`테이블에서 1002번 과목(`subject_id`=1002)을 수강한 학생들의 최고 성적(`score`)과 최저 성적을 조회하시오.
SELECT MAX(SCORE), MIN(SCORE) FROM ENROLLMENTS WHERE SUBJECT_ID =1002;
5.`departments`테이블에서 '인문사회관'과'경영관'에 위치한 모든 학과(`dname`)의 이름을 콤마로 연결하여 하나의 문자열로 조회하시오.
SELECT GROUP_CONCAT(DNAME) FROM DEPARTMENTS WHERE LOC IN ('인문사회관','경영관');