[ᴘʀᴏɢʀᴀᴍᴍᴇʀꜱ] SQL - 데이터 분석(집계함수, NULL, 중복처리, GROUPING)

NewHa·2023년 11월 5일
programmerssql
1

ᴘʀᴏɢʀᴀᴍᴍᴇʀꜱ

목록 보기
6/8
post-thumbnail

🫥 프로그래머스 lv2 이후의 문제들은 'JOIN'을, lv3 이후의 문제들은 '서브 쿼리'를 사용하는 문제가 정말 많습니다. 이 글 이후에 해당 부분을 설명하는 용도로 적은 lv3 문제들은 참고만 하셨다가 공부한 후에 푸시는 게 좋습니다!⭐️⭐️⭐️

☀️ 집계함수와 산술함수

🌿 집계함수 (Aggregate Function)

특정 컬럼의 여러 row의 값들을 동시에 고려해서 특정 값을 집계해 구해주는 함수입니다.
대표적으로 COUNT, MAX, MIN, AVG 등이 있습니다. 집계함수는 사용될 수 있는 쿼리문의 영역이 매우 다양하고 자주 사용됩니다.

COUNT (갯수)

-- 해당 컬럼의 갯수를 집계하는 합수입니다.
SELECT COUNT(컬럼명) FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 동물 수 구하기 (lv.1)

  • 동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 문제로 count를 사용해 수를 구할 수 있습니다.
SELECT COUNT(*) FROM animal_ins
-- 이때 count는 null값을 제외하고 카운팅합니다.

MAX (최댓값)

-- 해당 컬럼에서 최댓값을 구하는 함수입니다.
SELECT MAX(컬럼명) FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 가장 비싼 상품 구하기 (lv.1)

  • 판매 중인 상품 중 가장 높은 판매가를 출력하는 문제입니다.
-- price(판매가) 컬럼 중 가장 높은 가격을 찾아 출력합니다.
SELECT MAX(price) FROM product

MIN (최솟값)

-- 해당 컬럼에서 최솟값을 구하는 함수입니다.
SELECT MIN(컬럼명) FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 최솟값 구하기 (lv.2)

  • 동물 보호소에 가장 먼저 들어온 동물을 찾기 위해 가장 빠른 날짜를 찾습니다. 
-- 가장 빠른 날짜는 곧 날짜를 수로 생각했을 때 가장 작은 값을 의미합니다.
SELECT MIN(datetime) FROM animal_ins;

AVG (평균값)

-- 해당 컬럼의 평균값을 구하는 함수입니다.
SELECT AVG(컬럼명) FROM 테이블명;
-- 이때 null 값이 있다면 해당 값을 제외하고 평균값을 구하게 됩니다.
-- 예를 들어 학생들의 정보가 담긴 테이블에서 평균키를 출력하고 싶다면 다음과 같이 적습니다.
SELCET AVG(height) FROM student_info;

SUM (합계)

-- 해당 컬럼의 row들의 값을 모두 더한 합계를 구하는 함수입니다.
SELECT SUM(컬럼명) FROM 테이블명;
-- 예를 들어 판매된 물건의 총 수량을 구하는 문제라면 다음과 같이 적을 수 있습니다.
SELECT SUM(amount) FROM sales;
  • 이외에도 STD(표준편자를 구하는 함수) 등 여러가지 집계함수가 있지만, 위에 적힌 대표적인 함수들이 주로 사용됩니다.

🌿 HAVING

집계함수를 사용하며 집계할 값에 조건을 줄 때는 WHERE이 아닌 HAVING을 사용해야 합니다. 더 자세한 내용은 그룹핑에서 다루겠습니다.

SELECT 필드명 FROM 테이블명
GROUP BY 필드명 혹은 표현식
HAVING where조건식
  • WHERE 조건식과 동일한 형태의 조건식을 사용할 수 있고, 반드시 GROUP BY 뒤에 위치해야 합니다.

🫥 프로그래머스 SQL 코딩테스트 - 재구매가 일어난 상품과 회원 리스트 구하기 (lv.2)

  • 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성하는 문제입니다. 
SELECT user_id, product_id FROM online_sale
GROUP BY user_id, product_id
-- 동일한 회원이 동일한 상품을 재구매 했다면 갯수가 2개 이상일 것입니다.
HAVING count(*) >= 2

🌿 산술함수 (Mathematical Function)

특정 컬럼의 각 row값 마다 단술한 산술 연산을 해주는 함수입니다.

ROUND (반올림)

-- 해당 컬럼 내의 값들을 반올림해서 출력하는 함수입니다.
SELECT ROUNT(컬럼명) FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 평균 일일 대여 요금 구하기 (lv.1)

  • 자동차들의 평균 일일 대여 요금을 구하면서 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해서 출력하는 문제입니다.
SELECT round(avg(daily_fee)) as AVERAGE_FEE FROM car_rental_company_car

FLOOR (내림)

-- 해당 컬럼 내의 값들을 정수로 바꿔서 출력하는 함수입니다.
SELECT FLOOR(컬럼명) FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 가격대 별 상품 개수 구하기 (lv.2)

  • 컬럼명을 price_group으로 지정해서 만원 단위의 가격대 별로 상품개수를 출력하면서 각 구간의 최소금액으로 가격대 정보를 표시하는 문제입니다.
-- 문제가 복잡해 보이지만, price의 만원단위 아래의 숫자를 모두 floor로 내리라는 것입니다.
-- 저는 0.0001로 나눈 값에 다시 10000을 곱해서 만원단위의 숫자를 만들었습니다.
SELECT (FLOOR(price * 0.0001) * 10000) as price_group, count(*) as products FROM product

CEIL (올림)

-- 내림과 반대로 해당 컬럼 내의 값들을 올림하는 함수입니다.
SELECT CEIL(컬럼명) FROM 테이블명;

ABS (절대값)

-- 해당 컬럼 내의 값들을 절대값으로 바꿔서 출력하는 함수입니다.
SELECT ABS(컬럼명) FROM 테이블명;

SQRT (제곱근)

-- 해당 컬럼 내의 값들을 제곱근(루트) 값으로 바꿔서 출력하는 함수입니다.
SELECT SQRT(컬럼명) FROM 테이블명;
  • 이 외에도 다양한 산술함수가 있지만, 위에 나열한 정도만 쓰이는 것 같습니다.

☀️ NULL값 처리

NULL은 특정 칼럼에서 값이 존재하지 않는 상태, 정보가 없다는 상태를 나타내는 값입니다. 숫자 0이나, 빈 문자열 혹은 false의 의미가 아니라는 점을 주의해야 합니다.

  • 따라서 존재하지 않으므로 산술연산을 할 수 없습니다. NULL에는 어떤 연산을 해도 NULL입니다. 어떤 처리를 해도 값이 없을 뿐입니다.

🌿 NULL 조회

NULL 값은 IS NULL로 조회할 수 있습니다.

WHERE 컬럼명 IS NULL
-- 'IS NULL'은 NULL 값이 있는 지 묻는 조건으로, 해당 컬럼에서 NULL 값인 row만 조회합니다.
WHERE 컬럼명 IN NOT NULL
-- NULL을 제외하고, NULL이 아닌 값인 row를 조회하려면 'IS NOT NULL'을 사용합니다.
-----------------------------------------------------------
WHERE 컬럼명 = NULL
-- 👆🏻 ❌ null 값은 연산이 되지 않기 때문에 비교연산자를 사용할 수 없습니다.
-- 이 경우, true일 수 없어 아무런 row도 출력되지 않습니다.

🫥 프로그래머스 SQL 코딩테스트 - 나이 정보가 없는 회원 수 구하기 (lv.1)

  • 나이 정보가 없는(null값인) 회원이 몇 명인지 출력하는 문제입니다.
SELECT count(*) as USERS FROM user_info
WHERE age IS NULL

🫥 프로그래머스 SQL 코딩테스트 - 이름이 있는 동물의 아이디 (lv.1)

  • 이름이 있는 동물의 ID를 조회하는 SQL 문을 작성하는 문제입니다.
SELECT animal_id FROM animal_ins
WHERE name IS NOT NULL

🌿 NULL 대체

개발자가 아닌 사람이 봤을 때 null이란 값은 생소할 수 있습니다. 따라서 협업을 위해 null값을 알아볼 수 있는 값으로 대체해야 하는 경우들이 있습니다.

CASE 조건문

SELECT
	(CASE WHEN 컬럼명 IS NULL THEN '대체어'  -- null 값이라면 대체어로 변경하고
    	  ELSE 컬럼명  -- 아니라면 그대로 출력
     END) AS 컬럼명(혹은 새컬럼명)
FROM 테이블명;

🪴 컬럼의 값 변환하기

CASE 문

  • CASE 조건문은 NULL값을 대체할 뿐만 아니라 특정 컬럼에서 값의 내용을 바꿀 때 일반적으로 사용되는 조건문입니다. 
-- 예를 들어, 성별이 'W'와 'M'으로 표현되어 있는 걸 '여', '남'으로 바꿔서 출력하고 싶다면 다음과 같이 작성합니다.
SELECT (CASE WHEN gender = 'W' THEN '여'
            WHEN gender = 'M' TEHN '남'
        END) AS gender
FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 조건별로 분류하여 주문상태 출력하기 (lv.3)

  • 5월 1일을 기준으로 출고여부를 조회하면서 출고여부는 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력하는 문제입니다.
SELECT
    (CASE
    WHEN out_date <= '2022-05-01' THEN '출고완료'
    WHEN out_date > '2022-05-01' THEN '출고대기'
    ELSE '출고미정'
	END) AS '출고여부'
FROM food_order

CONCAT

concatenate의 줄임말로 '연결하다'라는 뜻입니다. 파라미터로 넣은 값들을 이어서 하나의 컬럼으로 만들 수 있습니다.

-- 예를 들어 height(키) 값에 'cm'를 붙이고 ','를 넣고 weight(몸무게) 값에 'kg'을 붙여서 
-- 두 컬럼을 합쳐서 표현하고 싶을 때는 다음과 같이 작성합니다.
SELECT CONCAT (height, 'cm', ', ', weight, 'kg') AS '키와 몸무게' FROM student_info;
-- '168cm, 60kg' 처럼 나오게 됩니다.

🫥 프로그래머스 SQL 코딩테스트 - 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 (lv.3)

  • 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하면서 첨부파일의 경로를 '/home/grep/src/게시글 ID/파일 ID+파일 이름.파일 확장자'형식으로 출력하는 문제입니다. CONCAT을 사용해 파일 경로를 만듭니다.
SELECT CONCAT('/home/grep/src/', F.board_id, '/', file_id, file_name, file_ext) AS file_path
-- '/home/grep/src/B0008/MOV_000008photo1.avi' 와 같이 나오게 됩니다.

IFNULL

IFNULL함수로 CASE문과 동일하게 작성할 수 있습니다.

SELECT IFNULL(컬럼명, '대체어') AS 컬럼명(혹은 새컬럼명) FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 경기도에 위치한 식품창고 목록 출력하기 (lv.1)

  • 이전에 LIKE 연산자를 소개하며 풀었던 문제인데, 사실 이 문제는 NULL을 대체하는 조건이 있는 문제입니다.
  • 경기도에 위치한 창고의 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 출력하는 문제입니다.
SELECT address, IFNULL (freezer_yn, 'N') AS freezer_yn
FROM food_warehouse
WHERE address like "%경기%"
  • 위의 SQL문을 IFNULL이 아닌 CASE문으로 작성하면 다음과 같습니다.
SELECT address,
	(CASE WHEN freezer_yn IS NULL THNE 'N'
         LESE freezer_yn
    END) AS freezer_yn
FROM food_warehouse
WHERE address like "%경기%"

COALESCE

'합치다'라는 뜻으로, '컬럼명'과 '대체어' 두 가지 파라미터를 받아서, NULL 값이 아니라면 그 값을 돌려주고, NULL 값이면 두번째 파라미터로 받은 '대체어'를 반환합니다.

SELECT COALESCE(컬럼명, '대체어') FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 경기도에 위치한 식품창고 목록 출력하기 (lv.1)

  • 위의 문제를 COALESCE를 사용해 작성하면 다음과 같습니다.
SELECT address, COALESCE(freezer_yn, 'N') AS freezer_yn
FROM food_warehouse
WHERE address like "%경기%"

🪴 IFNULL 과 COALESCE 의 차이

IFNULL

  • ISNULL과 IFNULL은 MySQL에만 있는 함수입니다.
  • 두 개의 파라미터만 넣을 수 있어 한 가지 인자만 처리할 수 있습니다.

COALESCE

  • SQL 표준 함수입니다.
  • 여러 개의 파라미터를 넣을 수 있어 다중 인자를 처리할 수 있습니다.
COALSECE (val1, val2, val3...) 
-- val1 값이 null 이면 val2 값을 반환하고, val2 값도 null 이면 val3을 반환하는 식으로 처리할 수 있습니다.

👉🏻 따라서 유연하게 사용가능하고, SQL 표준인 COALESCE 함수를 쓰는 것이 더 좋습니다.


☀️ 중복값 처리

중복되는 값을 제외하고 고유한 값만 보기 위해 중복되는 값을 처리하는 방법으로 DISTINCT 와 GROUP BY 두 가지의 방법이 있습니다. 여기서는 DISTINCT만 다루고 GROUP BY는 GROUPING으로 따로 다루겠습니다.


🌿 DISTINCT

중복값을 제거하기 위해 나온 함수로, 중복값을 제거하고 고유값만 나열해줍니다. 구현이 간단하기 때문에 많이 사용되지만 Temp Tablespace에 임시로 저장하는 방식이라 메모리에 부하를 줄 수 있습니다.

-- 해당 컬럼내의 중복값들을 제거하고 고유한 row들만 출력합니다.
SELECT DISTINCT(컬럼명) FROM 테이블명;

🫥 프로그래머스 SQL 코딩테스트 - 중복 제거하기 (lv.2)

  • 동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 문제로, 동물들의 이름이 중복되는 값을 제거하고 count 하는 문제입니다.
SELECT count(DISTINCT(name)) FROM animal_ins
  • 해당 문제를 GROUP BY로 푸는 SQL문은 서브쿼리 게시물에서 다룹니다.

☀️ GROUPING

row들을 원하는 대로 여러 개의 그룹으로 묶어주는 함수입니다.

SELECT 컬럼명 FROM 테이블명
GROUP BY 필드명 혹은 표현식

  • 컬럼별로 혹은 조건표현식이라면 조건에 맞는 row끼리 묶어서 조회할 수 있습니다. 이 때 여러개의 컬럼으로 그룹핑을 해 좀 더 세부적으로 그룹을 나눌 수도 있습니다.

  • 그룹으로 묶어 줄 때 집계함수나 연산함수를 사용할 수도 있습니다.

  • 그룹핑 후에 집계함수를 사용하면 그룹별로 집계하게 됩니다. 이 때 SELECT절에는 그룹핑에 사용한 컬럼과 집계함수만 사용할 수 있습니다. 그룹핑에 사용하지 않은 컬럼을 SELECT할 수 없습니다. 각 그룹의 해당 컬럼의 어느 값을 어느 row에서 가져와야 할 지 결정할 수 없기 때문에 '그룹핑을 기준으로 사용되지 않은 컬럼이 SELECT에 존재하면 안된다'는 에러가 발생합니다. 대신 집계함수의 인자로 사용하는 건 괜찮습니다. 각 그룹에서 모든 row들의 집계값을 구하면 되기 때문에 특정 row값이 필요 없기 때문입니다.

  • 그룹핑을 한 데이터에 조건을 걸어 특별한 그룹만 조회하고 싶다면 WHERE문이 아닌 HAVING문을 사용해야 합니다. HAVING은 '~을 가지고 있는'이라는 뜻을 가진 대로 해당 조건을 가진 그룹만 보여주게 됩니다.(WHERE문을 사용하면 '해석가능한 SQL문이 아니'라는 에러가 발생합니다.)

🪴 WHERE문과 HAVING문

둘 다 조건이라 비슷해 보이지만 목적이 다릅니다.

  • WHERE문은 row들을 조회할 때 조건을 설정하는 구문입니다.
  • HAVING문은 이미 조회된 row들을 그룹핑을 해 생성된 그룹을 필터링하는 구문입니다.

🫥 프로그래머스 SQL 코딩테스트 - 고양이와 개는 몇 마리 있을까 (lv.2)

  • 고양이와 개가 각각 몇 마리인지 조회하기 위해 고양이와 개의 그룹으로 나누어 카운트 해야합니다.
SELECT animal_type, count(animal_type) as count FROM animal_ins
GROUP BY animal_type

🫥 프로그래머스 SQL코딩테스트 - 동명 동물 수 찾기 (lv.2)

  • 이름이 없는 동물은 집계에서 제외하고 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성하는 문제입니다.
SELECT name, count(name) as count FROM animal_ins
WHERE name IS NOT NULL
GROUP BY name
HAVING count(name) >= 2
  • 여기서 보면 name 컬럼에 null값이 있는 row를 제외하고 불러온 table에서 name별로 그룹핑을 합니다. 즉, WHERE문은 이후에 처리할 테이블을 불러올 때 조건을 걸어서 불러오도록 합니다. 이렇게 불러온 테이블을 이 후에 처리과정에서 그룹핑을 하며 HAVING으로 조건을 걸어 그룹을 필터링하는 것입니다. 적용 순서와 목적이 분명히 다릅니다.

☀️ SELECT문의 작성·실행 순서

작성순서실행순서
SELECTFROM : 어느 테이블을 대상으로 할 것인지를 먼저 정합니다.
FROMWHERE : 해당 테이블에서 특정 조건을 만족하는 row들만 선별합니다.
WHEREGROUP BY : row들을 그룹핑하고 그룹별로 하나의 row로 표현합니다.
GROUP BYHAVING : 그룹핑 후 조건을 만족하는 그룹만 선별합니다.
HAVINGSELECT : *라면 모든 컬럼을, 아니라면 작성한 특정 컬럼을 조회합니다.
ORDER BYORDER BY : 각 row를 기준에 따라 정렬합니다.
LIMITLIMIT : 조회된 row 중 갯수를 추립니다.
profile
NewHa
백 번을 보면 한 가지는 안다 👀
이전 포스트

[ᴘʀᴏɢʀᴀᴍᴍᴇʀꜱ] SQL - 데이터 조회(SELECT, WHERE, ORDER BY)

다음 포스트

[ᴘʀᴏɢʀᴀᴍᴍᴇʀꜱ] SQL - 테이블 합치기(JOIN)

0개의 댓글