1. 내장함수, NULL, 비교문
1. SQL 내장 함수
숫자 함수
ABS 함수
절댓값을 구하는 함수
SELECT(숫자)
ROUND 함수
반올림한 값을 구하는 함수
ROUND(숫자, 인자) -> 인자 : 몇 번째 자리에서 반올림 할지
인자가 음수면 소수점 위에서 반올림!
ex) ROUND(12345, -2) = 12300
위의 함수들은 숫자들을 가지고 바로 연산하기 때문에, FROM 안 씀
숫자 함수의 연산
숫자 함수에는 직접 숫자 입력 / 열 이름 사용 모두 가능
ex) ROUND(SUM(saleprice)/COUNT(*), -2)
문자 함수
REPLACE 함수
문자열을 치환하는 함수
ex) REPLACE(bookname, '야구', '농구') : bookname에 '야구' 문자열을 모두 '농구'로 바꿈
LENGTH, CHAR_LENGTH 함수
LENGTH( ) : 바이트 수를 가져오는 함수 (알파벳은 1byte, 한글은 3byte)
CAHR_LENGTH( ) : 문자의 수를 가져오는 함수 (주의 - 빈칸도 문자!)
날짜, 시간 함수
SELCET 절에서만 사용함
날짜와 시간 부분을 나타내는 인수는 format으로 표기
날짜, 시간 함수 사용시 주의할 점
1) DBMS 마다 이름, 동작, 의미가 다름
2) 타임존 문제
NOW() 나 CURRENT_TIMESTAMP 는 DB 서버의 타임존을 기준으로 반환
서버나 지역이 다르면 시간이 어긋날 수 있음
3) 날짜 포맷 출력
MySQL -> DATE_FORMAT()
Oracle -> TO_CHAR()
로 포맷 지정
4) NULL 처리
날짜 컬럼이 NULL 이면 함수 적용 시 에러 발생함
-> 기본값을 지정하는 습관 들이자 (IFNULL())
5) 성능 고려 : SELECT 에서 조회용으로 사용
검색, 정렬에서는 함수로 속성을 변환하지 않기
함수로 컬럼을 변환하면 함수 결과를 새로 계싼하고 풀 테이블스캔이 발생할 수 있음
아예 값을 지정하기 보다는 범위 조건을 쓰는 것이 유리함
- MariaDB에서의
NOW()와SYSDATE()
NOW(): 쿼리 실행 시작 시점의 시간을 반환 -> 같은 쿼리 내에서는 항상 동일한 값
SYSDATE(): 함수 호출 순간의 시스템 시간을 반환 -> 같은 쿼리여도 호출 시점마다 값 달라짐
SELECT NOW(), SLEEP(5), NOW(),
SYSDATE(), SLEEP(5), SYSDATE();=> NOW()는 두 결과가 같고, SYSDATE()는 둘이 5초 차이남
=> 이 쿼리를 실행하면 10초 뒤에 결과 나옴(5초 SLEEP 두 번)
ADDDATE
ADDDATE(date, interval) : 지정한 날짜에 day(일) 또는 interval(시간)을 더해 새로운 날짜를 반환하는 함수
ex)
SELECT orderid '주문번호', orderdate '주문일',
ADDDATE(orderdate, INTERVAL 10 DAY) '확정'
FROM orders;각 날짜에 10일씩 추가
format
STR_TO_DATE, DATE_FORMAT
STR_TO_DATE : CHAR -> DATE
DATE_FORMAT : DATE -> CHAR
SYSDATE
MySQL 데이터베이스에 설정된 현재 날짜와 시간을 반환하는 함수
실무에서 자주 쓰이는 DB 함수
매출 리포트 ) ROUND() + FORMAT()
SELECT FORMAT(ROUND(SUM(total_price), 0), 0) AS total_sales
FROM orders;마일리지 계산 ) TRUNCATE() : 반올림 없이 처리 가능
SELECT TRUNCATE(usable_amount / 100, 0) * 100 mileage_rounded
FROM members;100원 단위로 절삭
복리 계산 ) POW() + ROUND()
SELECT memer_id, ROUND(balance * POW(1 + interest_rate, 3), 2) AS future_balance
FROM savings_account;문자열 숫자를 숫자로 바꿔 계산 ) CAST()
SELECT product_name, CAST(price AS DEMICAL(10, 2)) * quantity AS total_price
FROM product_list;특정 숫자 범위 판단 ) GREATEAT, LEAST
SELECT user_id, LEAST(GREATEST(score,0), 100) AS score_clamped
FROM user_scores;0~100 범위 밖 점수는 보정
데이터 정제할 때 매우 유용한 패턴
2. NULL 값 처리
NULL 값
- NULL 값은 0, 빈 문자, 공백 등과 다 다름
- 비교 연산자로 비교할 수 없음
NULL 값 연산과 집계 함수
- NULL + 숫자 = NULL
- 집계 함수를 계산할 때 NULL이 포함된 행은 집계에서 빠짐
- 해당하는 행이 없으면 :
SUM,AVG= NULL /COUNT= 0
IFNULL 함수
NULL 값을 임의의 다른 값으로 변경 가능
ex) IFNULL(phone, '연락처없음')
-> phone값이 NULL이면 '연락처없음'으로 출력
3. 행번호 출력
변수는 이름 앞에 @ 붙임
치환문에는 SET과 := 사용
++ LIMIT 로 투플 개수 제한 가능
4. CASE WHEN
조건에 따라 다른 값을 반환
IF-THEN-ELSE 구조와 비슷함
CASE
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
...
ELSE 기본값
END AS 별칭1) 단순 조건
책 가격 분류
2) 집계함수와 함께
년도별 매출 구하기
월별 매출 합계를 high-mid-low로 분류하여 나타내기
2. 부속질의
하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의
두 테이블을 연관시킬 때 조인 vs 부속질의 : 데이터의 형태와 양에 따라 달라짐
=> 응답시간이 짧은 것 선택!
1. 중첩질의 (WHERE 부속질의)
보통 데이터를 선택하는 조건 혹은 술어와 같이 사용됨
비교 연산자
부속질의가 반드시 단일 행, 단일 열 반환
참인 경우에만 주 질의에 해당 열을 출력
Q. 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 나타내시오
Q. 각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 나타내시오
- 상관 부속질의 : 부속질의 중에서도 메인 쿼리의 컬럼을 참조해서 메인 쿼리의 각 행마다 실행되는 경우
집합 연산자
IN 연산자 : 주질의의 속성값이 부속질의에서 제공한 결과 집합에 있는지 확인
NOT IN 연산자 : 반대로 값이 존재하지 않으면 참
Q. 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오
한정 연산자
ALL, SOME, ANY
Q. 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 판매금액을 보이시오
존재 연산자
EXISTS, NOT EXISTS
Q. EXISTS 연산자를 사용하여 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오
2. 스칼라 부속질의 (SELECT)
부속질의의 결과 값을 단일 행, 단일 열의 스칼라값으로 반환함
결과가 없는 경우에는 NULL 출력
스칼라 부속질의는 SELECT문과 함께 UPDATE문에서도 사용할 수 있음
-> 새로운 속성에는 NULL 값이 저장되어 있음
-> 데이터를 입력하기 위해서는 모든 속성값을 수정해야 함
Q. 고객이름과 고객별 판매액 출력
3. 인라인 뷰 (FROM)
뷰 : 기존 테이블로부터 일시적으로 만들어진 가상 테이블
Q. 고객번호가 2 이하인 고객의 판매액을 나타내시오 (고객 이름과 고객별 판매액 출력)
4. CTE
WITH CTE명 AS (SELECT ...)
복잡한 SQL 쿼리 내에서 일시적인 결과 집합(임시 테이블) 정의
메인 쿼리에서 일반 테이블처럼 재사용하거나, 재귀 쿼리 구현에 활용됨
쿼리 실행 시에만 존재하며, 데이터베이스에 영구적으로 저장되지 않음
SELECT sub.custid, sub.name, sub.bookname, sub.saleprice
FROM (
SELECT o.custid, c.name, b.bookname, o.saleprice
FROM orders o
JOIN customer c ON o.custid = c.custid
JOIN book b ON o.bookid = b.bookid) sub
WHERE sub.saleprice > 10000; |
v
WITH order_details AS (
SELECT o.custid, c.name, b.bookname, o.saleprice
FROM orders o
JOIN customer c ON o.custid = c.custid
JOIN book b ON o.bookid = b.bookid)
#WITH 에는 세미콜론 안 붙임
SELECT custid, name, bookname, saleprice
FROM order_details
WHERE saleprice > 10000;인라인뷰를 CTE로 변환
CTE 변환 후 다른 조건이나 분석도 가능
5. 윈도우 함수
테이블의 행과 행 간의 관계를 정의하여 테이터를 윈도우로 그룹화하여 사용하는 함수
- 각 행에 대해 집계나 순위 계산 결과를 추가하여 사용
GROUP BY와 달리 행읙 ㅐ수를 유지하면서 그룹 내 계산 결과를 각 행에 표시- 복잡한 조인 없이 행 간 계산, 순위 매기기, 누적합계 등을 수행할 때 유용
OVER()절과 함께 사용되어PARTITION BY와ORDER BY로 범위와 순서를 지정
SELECT 함수명() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명)
FROM 테이블명;PARTITION BY : 계산을 수행할 그룹을 나눔
ORDER BY : 그 그룹 안에서 계산을 수행할 순서를 정함
주요 윈도우 함수 유형
1) 순위 함수 : ROW_NUMBER(), RANK(), DENSE_RANK() ...
// 동점자가 있을 경우 - RANK: 1 1 2 3 / DENSE_RANK: 1 1 3 4
2) 집계 함수 : SUM(), AVG(), COUNT(), MAX(), MIN() ...
3) 분석/값 함수 : LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE() ...
Q. 출판사별 도서의 매출 순위를 구하고 출판사별, 매출 순위별 구하기
실행순서 : JOIN -> GROUP BY -> 집계 -> 윈도우 함수 -> SELECT -> ORDER BY
Q. 고객별 총매출 집계
1) GROUP BY
SELECT cs.custid, cs.name, SUM(od.saleprice) AS total_sales
FROM orders od
JOIN customer cs
ON od.custid= cs.custid
GROUP BY cs.custid, cs.name
ORDER BY total_sales DESC;2) 인라인 뷰
SELECT sub.custid, sub.name, sub.total_sales
FROM (
SELECT cs.custid, cs.name, SUM(od.saleprice) AS total_sales
FROM orders od
JOIN customer csON od.custid= cs.custid
GROUP BY cs.custid, cs.name) sub
ORDER BY sub.total_sales DESC;3) CTE
WITH sales_summary AS (
SELECT cs.custid, cs.name, SUM(od.saleprice) AS total_sales
FROM orders od
JOIN customer csON od.custid= cs.custid
GROUP BY cs.custid, cs.name
)
SELECT custid, name, total_sales
FROM sales_summary
ORDER BY total_sales DESC;4) 윈도우 함수
SELECT cs.custid, cs.name, SUM(od.saleprice) AS total_sales,
RANK() OVER (ORDER BY SUM(od.saleprice) DESC) AS sales_rank
FROM orders od
JOIN customer csON od.custid= cs.custid
GROUP BY cs.custid, cs.name;문제 풀이
Q1. USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래 상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬해주세요
SELECT board_id, writer_id, title, price,
case
when status = 'SALE' then '판매중'
when status = 'RESERVED' then '예약중'
when status = 'DONE' then '거래완료'
end as status
FROM used_goods_board
WHERE created_date = '2022-10-05'
ORDER BY board_id DESC;SELECT 부속질의 사용
-> 거래 상태를 case when 사용해서 표현
Q2. `USED_GOODS_BOARD`와 `USED_GOODS_REPLY`테이블에서 2022년 10월에 작성되 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시물 제목을 기준으로 오름차순 정렬해주세요
SELECT b.title, b.board_id, r.reply_id, r.writer_id, r.contents,
DATE_FORMAT(r.created_date, '%Y-%m-%d') AS created_date
FROM used_goods_board b JOIN used_goods_reply r ON b.board_id = r.board_id
WHERE b.created_date LIKE '2022-10-%'
ORDER BY r.created_date asc, b.title asc;두 테이블 JOIN 한 후 2022년 10월에 작성된 게시물 출력
주의할점
1) 날짜에 대해서
2022-10-% : 한 부분만 비어있는거 찾을 때는 % 사용!!
% 포함되는 경우엔 = 말고 LIKE 사용하는거 잊지 말기
예시에 있는 날짜가 %Y-%m-%d 형태여도, SELECT문에서 DATE_FORMAT 사용해서 제시된 형태로 고쳐주어야 함
2) 여러 개의 테이블과 동일한 이름의 속성이 나오는 경우
헷갈리기 쉽다.. 처음에도 정확하지 못한 테이블 매칭으로 틀렸음
어느 테이블의 컬럼을 추출해야하는지 문제 정확하게 읽고 표시하기
Q3. '경제' 카테고리에 속하는 도서들의 도서ID, 저자명, 출판일 리스트를 출력하는 SQL문을 작성해주세요. 결과는 출판일을 기준으로 오름차순 정렬해주세요
SELECT b.book_id, r.author_name, date_format(b.published_date, '%Y-%m-%d') as published_date
FROM book b JOIN author r ON b.author_id = r.author_id
WHERE b.category = '경제'
ORDER BY published_date asc;Q4. PRODUCT테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리)별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요
SELECT LEFT(product_code, 2) as category, count(product_id) as product
FROM product
GROUP BY category
ORDER BY category;LEFT 함수 사용해서 속성값의 앞 2자리만 가져옴
Q5. FOOD_ORDER 테이블에서 2022년 5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요. 출고여부는 2022년 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력하고, 결과는 주문 ID를 기준으로 오름차순 정렬해주세요
SELECT order_id, product_id,
date_format(out_date, '%Y-%m-%d') as out_date,
case
when out_date <= '2022-05-01' then '출고완료'
when out_date > '2022-05-01' then '출고대기'
else '출고미정'
end as '출고여부'
FROM food_order
ORDER BY order_id;SELECT 부속질의 사용 (WHERE문은 필요 없었음)
Q6. REST_INFO 테이블에서 음식 종류별로 즐겨찾기 수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬 해주세요.
SELECT food_type, rest_id, rest_name, favorites
FROM rest_info
WHERE (food_type, favorites) in (SELECT food_type, max(favorites)
FROM rest_info
GROUP BY food_type)
ORDER BY food_type desc;food_type과 favorites를 쌍으로 짝지어 최댓값을 구함
-> 최대 점수를 가진 식당의 이름과 ID가 정확하게 매칭되어 출력되도록 하기 위해
GROUP BY에 food_type을 하면 원하는 행이 정확하게 출력되지 않음
-> 정확히 최댓값을 가진 행을 지정해준 후 출력
Q7. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중'이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요
SELECT car_id,
max( case
when '2022-10-16' between start_date and end_date
then '대여중'
else '대여 가능'
end) as availability
from car_rental_company_rental_history
group by car_id
order by car_id desc;처음에 MAX 함수를 포함하지 않고 작성했다가 틀렸음
이 SQL문에서 MAX 함수를 빼면 여러 번 제시된 car_id에 대한 기록이 반영되지 않기 때문에, 꼭 MAX를 붙여서 하나의 car_id에 대해 '대여중'과 '대여 가능'이 동시에 존재한다면, '대여중'을 우선적으로 출력할 수 있도록 함
SELECT car_id,
case
when car_id in (
select car_id
from car_rental_company_rental_history
where '2022-10-16' between start_date and end_date)
then '대여중'
else '대여 가능'
end as availability
from car_rental_company_rental_history
group by car_id
order by car_id desc;이렇게 작성할 수도 있다
'지금 빌려가고 있는 자동차 명단'을 먼저 추출한 후, 이 중 car_id가 존재하는지를 확인하는 방식
주의할점)
BETWEEN은 양끝도 포함한다!!
<=, >=와 동일
Q8. 2022년 1월의 도서 판매 데이터를 기준으로 저자별, 카테고리별 매출액을 구하여, 저자ID, 저자명, 카테고리, 매출액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순으로 정렬해주세요
SELECT a.author_id, a.author_name, b.category,
sum(s.sales*b.price) as total_sales
FROM book b
JOIN author a ON b.author_id = a.author_id
JOIN book_sales s ON b.book_id = s.book_id
WHERE s.sales_date like '2022-01-%'
GROUP BY a.author_id, a.author_name, b.category
ORDER BY a.author_id, b.category desc;처음에 GROUP BY 빼먹어서 틀렸었음
내가 가장 헷갈려 하는 유형 중 하나인 것 같다 어떻게 묶어서 출력해야할지 꼭 한번 더 생각해보기
Q9. USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.
SELECT u.user_id, u.nickname,
concat(u.city, ' ', u.street_address1, ' ', u.street_address2) as '전체주소',
concat(left(u.tlno, 3), '-', mid(u.tlno, 4, 4), '-', right(u.tlno, 4)) as '전화번호'
FROM used_goods_board b JOIN used_goods_user u ON b.writer_id = u.user_id
GROUP BY u.user_id
HAVING COUNT(b.board_id) >= 3
ORDER BY u.user_id desc;CONCAT 함수 사용해서 문자열 가공
주소 : CITY, STREET_ADDRESS1, STREET_ADDRESS2 사이에 공백을 하나씩 넣어서 함침
전화번호 : LEFT, MID, RIGHT 함수 사용해서 주어진 전화번호를 3개로 쪼개고, 사이에 하이픈 하나씩 넣어줌
전화번호의 경우 SUBSTR 함수를 사용하거나, INSERT 함수를 사용할 수도 있었을 듯
각 함수 쓸 때 인자에 어떤거 들어가는지 잘 알고 사용하기
