SQL 구문

• 각 절의 역할 및 실행 순서

1. FROM
   테이블이나 뷰 등 데이터를 가져올 소스를 정의합니다.
   조인(JOIN)이 포함될 경우, 테이블 간 관계를 설정하고 데이터를 병합합니다.
2. WHERE
   조건을 사용해 데이터의 행을 필터링합니다.
   행 단위로 조건이 평가됩니다.
   조건이 참(TRUE)인 데이터만 다음 단계로 전달됩니다.
3. GROUP BY
   데이터를 그룹으로 묶습니다.
   각 그룹에 대해 집계 함수(예: SUM, AVG)가 적용됩니다.
4. HAVING
   GROUP BY로 생성된 그룹에 대해 조건을 적용합니다.
   집계 결과를 기준으로 필터링합니다.
5. SELECT
   최종적으로 반환할 열을 정의합니다.
   계산된 열이나 별칭을 사용할 수 있습니다.
6. DISTINCT
   중복된 데이터를 제거합니다.
   결과 데이터에서 동일한 행이 여러 번 반환되는 경우 제거됩니다.
7. ORDER BY
   데이터를 정렬합니다.
   기본적으로 오름차순(ASC), 명시적으로 내림차순(DESC)으로 정렬 가능합니다.
8. LIMIT 또는 FETCH
   반환할 행의 개수를 제한합니다.

DATE

DATE_FORMAT

• 날짜를 원하는 형식의 문자열로 변환
  - %Y: 4자리 연도 (예: 2024)
  - %y: 2자리 연도 (예: 24)
  - %m: 2자리 월 (예: 01부터 12)
  - %c: 1자리 또는 2자리 월 (예: 1부터 12)
  - %d: 2자리 일 (예: 01부터 31)
  - %e: 1자리 또는 2자리 일 (예: 1부터 31)
  - %H: 2자리 시간 (24시간 형식, 예: 00부터 23)
  - %h: 2자리 시간 (12시간 형식, 예: 01부터 12)
  - %i: 2자리 분 (예: 00부터 59)
  - %s: 2자리 초 (예: 00부터 59)
  - %p: AM 또는 PM

SELECT DATE_FORMAT(DATE_COLUMN, '%Y-%m-%d') AS formatted_date
FROM POSTS;
# 결과 : 2024-11-11

YEAR , MONTH, QUARTER

• 특정 연도와 월에 해당하는 데이터를 필터링

SELECT TITLE
FROM POSTS
WHERE YEAR(DATE_COLUMN) = 2022
  AND MONTH(DATE_COLUMN) = 10;

SELECT 
   CONCAT('Q', QUARTER(DIFFERENTIATION_DATE)) AS QUARTER, 
   COUNT(*) AS ECOLI_COUNT
FROM 
   ECOLI_DATA
GROUP BY QUARTER(DIFFERENTIATION_DATE)
ORDER BY QUARTER(DIFFERENTIATION_DATE);

DATEDIFF

• 날짜 차이 계산

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY 1
HAVING AVERAGE_DURATION >=7
ORDER BY 2 DESC, 1 DESC

계산

FLOOR

• 소수점 이하를 버리고 주어진 숫자보다 작거나 같은 가장 큰 정수를 반환
• 숫자를 특정 간격으로 그룹화하거나 데이터를 분류할 때 유용

SELECT FLOOR(15.7) AS FLOOR_VALUE;

# r 
15

SELECT FLOOR(-8.3) AS FLOOR_VALUE;

# r 
-9

NA

COALESCE

• 지정된 컬럼이 NULL일 때 대체값을 반환

SELECT 
    WAREHOUSE_ID, 
    NAME, 
    ADDRESS, 
    COALESCE(FREEZER_AVAILABLE, 'N') AS FREEZER_AVAILABLE
FROM 
    FOOD_WAREHOUSE
WHERE 
    ADDRESS LIKE '경기도%'
ORDER BY 
    WAREHOUSE_ID ASC;

와일드 카드 / 글자 찾기

• LIKE 연산자와 함께 문자열 패턴을 검색할 때 사용되는 특수 문자

% (퍼센트)

• 설명: 0개 이상의 문자와 일치
• 용도: 문자열의 앞뒤 또는 중간에 위치해, 원하는 위치에서 문자가 없어도 되고, 여러 문자와 일치할 수 있음

# LIKE '강원도%' : "강원도"로 시작하는 모든 문자열과 일치
# LIKE '%공장' : "공장"으로 끝나는 모든 문자열과 일치
# LIKE '%식품%' : 문자열에 "식품"이 포함된 모든 항목과 일치

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID

_ (언더스코어)

• 설명: 하나의 문자와 정확히 일치
• 용도: 특정 위치에서 하나의 문자가 필요한 경우에 사용

# LIKE '_강원도' : 첫 번째 자리에 임의의 한 문자가 있고 두 번째 위치부터 "강원도"가 오는 문자열과 일치
# LIKE '강원도_공장' : "강원도" 다음에 한 글자가 오고, 그 뒤에 "공장"이 있는 문자열과 일치
# LIKE '식품공장___' : "식품공장" 뒤에 정확히 3글자가 있는 문자열과 일치

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도_공장';

ESCAPE

• ESCAPE 키워드는 특수 문자를 일반 문자로 인식하도록 변경하는 역할을 함.
• SQL에서 패턴 검색 시 _, % 등의 와일드카드를 문자 그대로 찾을 때 필수.

SELECT *
FROM users
WHERE username LIKE '%\_%' ESCAPE '\';

INSTR(문자열, '찾을 문자') = 0 or 1

• 찾는 문자가 있으면 1 / 없으면 0 도출

SELECT DISTINCT page_location
FROM ga
WHERE INSTR(page_location, '_') = 0
ORDER BY page_location;

결과값에 문자열 추가

CONCAT

SELECT CONCAT(MAX(LENGTH), 'CM') AS MAX_LENGTH
FROM FISH_INFO;

SUBSTRING

• 문자 나누기
• 아래는 전화번호 예시

CONCAT(SUBSTRING(U.TLNO, 1, 3), '-', SUBSTRING(U.TLNO, 4, 4), '-', SUBSTRING(U.TLNO, 8))

JOIN / UNION

JOIN

• INNER JOIN: 두 테이블에서 조인 조건을 만족하는 행만 반환

• LEFT JOIN (또는 LEFT OUTER JOIN): 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에서 일치하는 행이 있으면 함께 반환. 오른쪽에 일치하는 값이 없으면 NULL로 표시

• RIGHT JOIN (또는 RIGHT OUTER JOIN): 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블에서 일치하는 행이 있으면 함께 반환. 왼쪽에 일치하는 값이 없으면 NULL로 표시

• FULL OUTER JOIN: 두 테이블의 모든 행을 반환하며, 일치하는 항목이 없는 경우 NULL로 표시 (일부 데이터베이스에서만 지원)

• 2개 테이블 조인

SELECT M.USER_ID, M.NAME, O.ORDER_ID, O.ORDER_DATE
FROM MEMBERS AS M
INNER JOIN ORDERS AS O ON M.USER_ID = O.USER_ID;

• 여러 테이블 조인

SELECT M.USER_ID, M.NAME, O.ORDER_ID, O.ORDER_DATE, P.PRODUCT_NAME
FROM MEMBERS M
INNER JOIN ORDERS O ON M.USER_ID = O.USER_ID
INNER JOIN PRODUCTS P ON O.PRODUCT_ID = P.PRODUCT_ID;

UNION

• 두 개 이상의 SELECT 쿼리 결과를 하나로 합치는 연산자
• UNION (중복 제거) // UNION ALL (중복 허용)
• SELECT 문에 동일한 컬럼 개수와 데이터 타입이 있어야 함
• ORDER BY는 마지막 SELECT가 아니라 전체 결과에 적용

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

JOIN 결과에 대한 활용

• 서브쿼리 : JOIN 결과를 테이블로 간주하여 '임시 테이블'로 활용할 때

SELECT JT.USER_ID, JT.PRODUCT_ID
FROM (
    SELECT M.USER_ID, O.PRODUCT_ID
    FROM MEMBERS M
    INNER JOIN ORDERS O ON M.USER_ID = O.USER_ID
) AS JT
ORDER BY JT.USER_ID, JT.PRODUCT_ID DESC;

• 뷰: JOIN 결과를 지속적으로 사용해야 하는 경우

CREATE VIEW JOINED_TABLE AS
SELECT M.USER_ID, O.PRODUCT_ID
FROM MEMBERS M
INNER JOIN ORDERS O ON M.USER_ID = O.USER_ID;

SELECT USER_ID, PRODUCT_ID
FROM JOINED_TABLE
ORDER BY USER_ID, PRODUCT_ID DESC;

조건문

WHERE 절

• 용도: 테이블에서 개별 행을 필터링할 때 사용
• 적용 시점: GROUP BY가 실행되기 이전에 실행
• 사용 대상: 그룹화되지 않은 개별 행에 적용
• 예시: 총 주문량이 3000 이상인 아이스크림 맛을 필터링

SELECT FLAVOR, TOTAL_ORDER
FROM ICECREAM_SALES
WHERE TOTAL_ORDER > 3000;

HAVING 절

• 용도: 그룹화된 결과에 조건을 적용할 때 사용
• 적용 시점: GROUP BY가 적용된 이후에 실행
• 사용 대상: GROUP BY로 그룹화된 집계 결과에 적용
• 예시: 총 주문량이 3000 이상인 맛별 주문 수량을 필터링

SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_SALES
FROM ICECREAM_SALES
GROUP BY FLAVOR
HAVING SUM(TOTAL_ORDER) > 3000;

(GROUP BY)

• 특정 컬럼을 기준으로 행들을 그룹화하여 집계할 때 사용
• 집계 함수(예: COUNT, SUM, AVG, MAX, MIN 등)

CASE WHEN ~ ELSE ~ END

• 조건에 따라 다른 값을 반환할 때 사용하는 조건문

SELECT PT_NAME, GEND_CD,
       CASE 
           WHEN GEND_CD = 'M' THEN '남성'
           WHEN GEND_CD = 'F' THEN '여성'
           ELSE '기타'
       END AS GENDER
FROM PATIENT;

SELECT 
    ROUND(AVG(CASE
        WHEN LENGTH < 10 THEN 10
        WHEN LENGTH IS NULL THEN 10 
        ELSE LENGTH
    END),2) AS AVERAGE_LENGTH
FROM FISH_INFO

CTE (Common Table Expression)

• 일시적인 이름이 지정된 결과 집합
• 복잡한 서브쿼리를 별도의 이름으로 정의하여 메인 쿼리에서 재사용 가능
• 데이터베이스에 저장되지 않고, 쿼리가 실행되는 동안만 유지
• WITH cte_name AS (...)

WITH TopSales AS (
    SELECT employee_id, SUM(sales) AS total_sales
    FROM sales_data
    GROUP BY employee_id
    HAVING total_sales > 10000
)
SELECT *
FROM TopSales
ORDER BY total_sales DESC;

윈도우 함수

• 윈도우 함수는 GROUP BY와 다르게, 각 행마다 그룹 내의 데이터와 비교하거나 참조할 수 있도록 해줌
• OVER() 절과 함께 사용
• PARTITION BY는 윈도우 함수를 그룹화(분할)하는 역할
• ORDER BY는 각 그룹 내에서 순서를 결정
• 즉, 같은 그룹(PARTITION BY) 내에서 정렬된 순서(ORDER BY)를 기준으로 윈도우 함수가 동작
• 윈도우 함수는 Group by 없이 사용
  
  
  
  

LEAD() & LAG()

SELECT 
    region, 
    salesperson, 
    sale_date,
    sales_amount,
    LAG(sales_amount) OVER (PARTITION BY region ORDER BY sale_date) AS prev_sales,
    LEAD(sales_amount) OVER (PARTITION BY region ORDER BY sale_date) AS next_sales
FROM sales;

• LAG(sales_amount) → 현재 행의 이전 행의 sales_amount 값을 가져옴.
• LEAD(sales_amount) → 현재 행의 다음 행의 sales_amount 값을 가져옴.
• PARTITION BY region → 지역(region)별로 나눠서 계산.
• ORDER BY sale_date → 날짜 순서대로 LAG()와 LEAD() 적용.

RANK() / PERCENT_RANK()

• 주어진 기준(ORDER BY)에 따라 각 행에 순위를 부여하며, 동일한 값에는 동일한 순위를 부여
• 중복된 순위 뒤의 순위가 건너뛰는 방식. 예를 들어, 두 행이 1등이면 다음 순위는 3등.

RANK() OVER (PARTITION BY column_name ORDER BY column_name ASC|DESC)

• RANK(): 순위를 계산.
• OVER: 윈도우 함수를 지정하는 절.
• PARTITION BY (선택 사항): 그룹화 기준을 지정. 그룹별로 순위 계산.
• ORDER BY: 순위 계산을 위한 정렬 기준. 필수 항목.

제한

LIMIT

• 결과의 개수를 제한

# 상위 5개의 행을 조회
SELECT NAME
FROM ANIMAL_SHELTER
ORDER BY INTAKE_DATE ASC
LIMIT 5;

OFFSET

# 6번째부터 5개의 행을 조회
SELECT NAME
FROM ANIMAL_SHELTER
ORDER BY INTAKE_DATE ASC
LIMIT 5 OFFSET 5;

다시 풀어보기

• Select
  - 서울에 위치한 식당 목록 출력하기
• SUM, MAX, MIN
  - 연도별 대장균 크기의 편차 구하기
  - 물고기 종류 별 대어 찾기
• String, Date
  - 특정 옵션이 포함된 자동차 리스트 구하기
  - 자동차 대여 기록에서 장기/단기 대여 구분하기
  - 루시와 엘라 찾기
  • 조건에 부합하는 중고거래 상태 조회하기
  • 카테고리 별 상품 개수 구하기
  • 분기별 분화된 대장균의 개체 수 구하기
  • 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 (다시)
  • 오랜 기간 보호한 동물(2) (다시)
  • 조건에 맞는 사용자 정보 조회하기 (다시)
  • 자동차 대여 기록 별 대여 금액 구하기 (다시)
  • 취소되지 않은 진료 예약 조회하기 (다시)
• GROUP BY
  - 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
  • 진료과별 총 예약 횟수 출력하기
  • 동명 동물 수 찾기
  • 입양 시각 구하기(1)
  • 가격대 별 상품 개수 구하기
  • 물고기 종류 별 잡은 수 구하기
  • 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
  • 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
  • 카테고리 별 도서 판매량 집계하기(위 문제와의 차이 확인)
  • 즐겨찾기가 가장 많은 식당 정보 출력하기
  • 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
  • 저자 별 카테고리 별 매출액 집계하기
  • 식품분류별 가장 비싼 식품의 정보 조회하기
  • 입양 시각 구하기(2)
  • 언어별 개발자 분류하기
• join
  • 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
  • 5월 식품들의 총매출 조회하기
  • 주문량이 많은 아이스크림들 조회하기
  • 그룹별 조건에 맞는 식당 목록 출력하기
  • 없어진 기록 찾기
  • 오랜 기간 보호한 동물(1)
  • 상품을 구매한 회원 비율 구하기

• solvesql
  - 카테고리 별 매출 비율
  • 가구 판매의 비중이 높았던 날 찾기 (다시)
  • 최근 올림픽이 개최된 도시
  • 우리 플랫폼에 정착한 판매자 1
  • 두 테이블 결합하기
  • 우리 플랫폼에 정착한 판매자 2
  • 다음날도 서울숲의 미세먼지 농도는 나쁨
  • 제목이 모음으로 끝나지 않는 영화
  • 언더스코어(_)가 포함되지 않은 데이터 찾기
  • 게임을 10개 이상 발매한 게임 배급사 찾기
  • 기증품 비율 계산하기 (SQL 최적화)
  • 최대값을 가진 행 찾기
  • 3년간 들어온 소장품 집계하기
  • 복수 국적 메달 수상한 선수 찾기
  • 할부는 몇 개월로 해드릴까요
  • 지역별 주문의 특징
  • 배송 예정일 예측 성공과 실패
  • 멘토링 짝꿍 리스트
  • 게임 평점 예측하기 1
  • 폐쇄할 따릉이 정류소 찾기 2
  • 버뮤다 삼각지대에 들어가버린 택배
  • 쇼핑몰의 일일 매출액
  • 점검이 필요한 자전거 찾기