[ TIL ] Day 54 - SQL 데이터 분석 : 집계, 그룹핑 & 조인, 서브 쿼리, AS & VIEW

🗓️ 수업 일자 : 2025.12.10

✨ 오늘의 수업 평가 :   [ GOOD ]   배움이 팡팡 터진 날 🍿💡

 

복잡하면 복잡 할 수록 너무 재밌는 SQL !!! 

데이터 작업이 정말 신기방기할 따름이다 🤓👍

 

📝  오늘 배운 내용  

- SQL 데이터 분석 : 집계 (수학적 계산) 

- SQL 데이터 분석 : 그룹핑 & 조인

- SQL 데이터 분석 : 서브쿼리

- SQL 데이터 분석 : AS & VIEW

 

---

1. SQL 데이터 분석 - 집계 (수학적 계산) 

1) Aggregation 집계 함수(명령어)

-- COUNT(컬럼) : 컬럼의 개수
SELECT COUNT(*) FROM products; 
SELECT COUNT(id) FROM products; 

-- MAX(컬럼) : 선택한 컬점 중 최댓값 출력 
SELECT MAX(price) FROM products;

-- MIN(컬럼) : 선택한 컬점 중 최솟값 출력
SELECT MIN(price) FROM products;

-- SUM(컬럼) : 선택한 컬점의 전체 합계
SELECT SUM(price) FROM products; 

-- AVG(컬럼) : 선택한 컬점의 평균값
SELECT AVG(price) FROM products;

 

 

2) 사칙연산을 사용 해 직접 계산

-- 사칙연산을 사용하여 직접 계산
-- price(정가), cost(실제 판매가)를 가지고 할인율 계산하기 
SELECT ((price - cost) / price) * 100 FROM products;

 

 

 

 

2. SQL 데이터 분석 - 그룹핑 & 조인

1)  GROUP BY  : 설정 한 컬럼으로 결과 값을 묶어서 표기 , 집계 함수를 사용 한 경우 그룹핑이 꼭 필요 ⭐

                            JOIN과 함께 사용하는 경우, 외부 테이블의 값을 사용 할 때에도 설정 필요

-- 제품 id / 별점 평균 / 총 리뷰수를 
-- reviews 테이블에서 가져오는데 
-- 그룹핑을 "제품 id" 기준으로 진행 

SELECT product_id, AVG(star) AS star_avg, COUNT(*) AS reviews_count
FROM reviews
GROUP BY product_id;

[결과]
 product_id |      star_avg      | reviews_count 
------------+--------------------+---------------
         20 | 3.5000000000000000 |             4
         19 | 3.4000000000000000 |             5
          1 | 4.0000000000000000 |             4
         18 | 3.5000000000000000 |             2
          2 | 4.0000000000000000 |             5
         16 | 4.0000000000000000 |             7
          3 | 4.3333333333333333 |             3
(7 rows)


-- JOIN과 함께 사용 시, 그룹핑 기준 테이블 컬럼과 함께 사용할 외부 테이블의 컬럼을 선언 
SELECT 
    reviews.item_id, 
    AVG(reviews.star) AS item_avg,
    items.name,
    items.price
FROM reviews RIGHT JOIN items
ON reviews.item_id = items.id 
GROUP BY reviews.item_id, items.name, items.price 
-- 기준 테이블이 reviews라서 item 컬럼을 쓰려면 넣어주어야함
HAVING AVG(reviews.star) < (SELECT AVG(star) FROM reviews);

 

 

2)  GROUP BY ... HAVING ...  : 그룹핑 된 결과에 조건을 걸어서 필터링 진행 

-- 제품 id / 별점 평균 / 총 리뷰수를 
-- reviews 테이블에서 가져오는데 
-- 그룹핑을 "제품 id" 기준으로 진행 

SELECT product_id, AVG(star) AS star_avg, COUNT(*) AS reviews_count
FROM reviews
GROUP BY product_id
HAVING AVG(star) >= 4.0 ; 
-- 그룹핑 한 정보 중에서 별점 평균이 4.0 이상인 값만 가져옴

[결과]
 product_id |      star_avg      | reviews_count 
------------+--------------------+---------------
          1 | 4.0000000000000000 |             4
          2 | 4.0000000000000000 |             5
         16 | 4.0000000000000000 |             7
          3 | 4.3333333333333333 |             3
(4 rows)


-- SQL 실행 순서로 인해 HAVING에서 별칭 "star_avg"는 사용 불가 (마지막 단락에서 설명) 
-- HAVING AVG(star) >= 4.0 ; 실행 안됨

 

 

3)  JOIN ... ON ...  

• 가지고 올 테이블과 해당 테이블에 있는 컬럼값을 지정하여 데이터 표기
• 주로 외래키(FK)를 사용해 두 테이블을 연결해서 사용 
• 외래키 설정이 안된 컬럼으로 설정 가능하지만, 의미 없는 데이터가 만들어짐 
• join 조건을 만족하지 못하는 정보에 대해서는 출력하지 않음 
     ex) review 테이블에서 FK userId를 기반으로 정보를 가져오라고 했을때,
           리뷰를 한번도 작성하지 않은 user 정보는 해당 결과에서 확인 할 수 없다 

-- JOIN (기본형) : 가지고 올 테이블과 컬럼값을 선택하여 지정
SELECT product.id, product.name, inventory.product_id, inventory.count 
FROM product JOIN inventory 
ON product.id = inventory.product_id;

[    product 컬럼 영역    ]  [inventory 컬럼 영역]
 id |         name         | product_id | count
----+----------------------+------------+-------
  1 | 소프트 클라우드 쿠션  |          1 |   320
  2 | 라이트브리즈 워터보틀 |          2 |   200
  6 | 문라이트 스킨로션     |          6 |   103
(..생략..)

-- 다중 조인 구문
SELECT products.id, products.name, inventory.count, reviews.star
FROM products 
JOIN inventory ON products.id = inventory.product_id
JOIN reviews ON products.id = reviews.product_id;

 id |          name           | count | star
----+-------------------------+-------+------
  1 | 소프트 클라우드 쿠션     |   320 |    5
  1 | 소프트 클라우드 쿠션     |   320 |    4
  2 | 라이트브리즈 워터보틀    |   200 |    3
  2 | 라이트브리즈 워터보틀    |   200 |    5
  6 | 문라이트 스킨로션        |   103 |    5
  9 | 프레시민트 핸드크림      |   120 |    4

 

 

•  LEFT JOIN  : 사용한 두 테이블 중 왼쪽 테이블의 모든 데이터를 기준으로 정렬됨,
                          오른쪽 테이블 데이터에서 join 조건에 일치하는 데이터가 출력 됨
•  RIGHT JOIN  : 사용한 두 테이블 중 오른쪽 테이블의 모든 데이터를 기준으로 정렬됨,
                            왼쪽 테이블 데이터에서 join 조건에 일치하는 데이터가 출력 됨

-- LEFT JOIN : 두 테이블 중 왼쪽 테이블을 기준으로 정렬
SELECT product.id, product.name, inventory.product_id, inventory.count
FROM product LEFT JOIN inventory
ON product.id = inventory.product_id;

[결과] 
 id |              name             | product_id | count 
----+-------------------------------+------------+-------
  1 | 소프트 클라우드 쿠션           |          1 |   320
  2 | 라이트브리즈 워터보틀          |          2 |   200
  6 | 문라이트 스킨로션              |          6 |   103
  9 | 프레시민트 핸드크림            |          9 |   120
 17 | 스팀버스트 미니청소기          |            |  
 12 | 하모니 블루 블루투스 이어버드  |            |      
 (6 rows)
 
 
 -- RIGHT JOIN : 두 테이블 중 오른쪽 테이블을 기준으로 정렬
 SELECT product.id, product.name, inventory.product_id, inventory.count
FROM product LEFT JOIN inventory
ON product.id = inventory.product_id;

[결과] 
 id |          name         | product_id | count 
----+-----------------------+------------+-------
  1 | 소프트 클라우드 쿠션   |          1 |   320
  2 | 라이트브리즈 워터보틀  |          2 |   200
  6 | 문라이트 스킨로션      |          6 |   103
  9 | 프레시민트 핸드크림    |          9 |   120
 (4 rows)
 
 -- 어떤 기준으로 정렬 되느냐에 따라 빈 값도 나올지 생략될지 결정 됨

 

 

 

3. SQL 데이터 분석 - 서브쿼리 

• 하나의 쿼리 구문 안에 괄호를 사용해 새로운 쿼리 구문을 더 쓰는 것
• 두 개 이상의 쿼리 구문이 합쳐진 구조
•  가장 바깥쪽에 있는 구문을 메인쿼리 / 부모 쿼리로 지칭하고, 괄호 안에 쓴 쿼리를 서브 쿼리 / 자식 쿼리로 지칭 함

-- 메인 쿼리 (부모 쿼리)
SELECT *
FROM products
WHERE id IN (
    -- 서브 쿼리 (자식 쿼리)
    SELECT product_id     
    FROM stocks
)

=> 해석 : (메인쿼리) products 테이블의 모든 데이터를 가져오는데, 
          (서브쿼리) id가 stocks의 product_id 값과 일치하는 데이터를 가져옴


-- 중첩 쿼리 구문
-- 메인 쿼리 
SELECT id, name, price
FROM products
WHERE id IN (
    -- 서브 쿼리 1 
    SELECT product_id
    FROM stocks
    WHERE count > (
        -- 서브 쿼리 2 
        SELECT AVG(count)
        FROM stocks
    )
);

 

 

 

4. SQL 데이터 분석 - AS & VIEW

1)  AS  : 계산한 결과를 보기 위한 일시적인 컬럼 지정 방식, DB에 저장되는 것은 아님

-- AS : 계산한 결과를 보기 위한 일시적인 컬럼 지정 방식, DB에 저장되는 것은 아님
SELECT price, cost, ((price - cost) / price) * 100 AS rate FROM product;

-- 생략하여 사용 가능, 하지만 되도록 AS를 써서 사용
SELECT MAX(price) AS max_price FROM product;
SELECT MAX(price) max_price FROM product;


-- 테이블 명이 긴 경우 축약에 사용 가능 

-- 기본형
SELECT 
  productListDBTables.id, 
  productListDBTables.name, 
  productListDBTables.price 
FROM productListDBTables;

-- 축약형
SELECT p.id, p.name, p.price FROM productListDBTables AS p;

 

 

2)  CREATE VIEW  : 분석한 데이터 테이블을 재사용 가능한 새로운 테이블로 생성

-- VIEW 생성
-- CREATE VIEW 새로운_테이블명 AS 만들 조건
CREATE VIEW product_with_rate AS
SELECT 
    id,
    name,
    price,
    cost,
    ((price - cost) / price) * 100 AS rate
FROM products;


-- VIEW 사용 
SELECT product_id FROM product_with_rate WHERE rate > 60;

 

 

💡AS vs VIEW 비교하기

 

AS = 별칭

• SELECT 결과 화면에서만 잠깐 쓰는 ‘일시 컬럼명’
• DB에 저장되지 않음, 터미널을 종료하면 사라짐 (휘발성 데이터)
• 쿼리를 실행할 때마다 만들어야 함 (동일한 별칭이라 할지라도, 재사용이 불가능) 

VIEW = 저장형 테이블

• SQL 코드 자체를 DB 내부에 오브젝트로 저장 (터미널을 종료해도 그대로 유지)
• 테이블처럼 이름을 가진 논리적 가상 테이블
• DROP VIEW 명령어를 사용해 제거 가능

 

SQL 작업하기 :)

---

 

📃 내일은 뭘 배울까 🤔

- SQL 실행 순서

- SQL 실습