[스파르타 내일배움캠프 / 데이터 분석 트랙] TIL(Today I Learned)_2주차_24.12.02

Today's Goals

1. SQL 쿼리 작성 익숙해지기 - 코드카타 레벨3, SQL 라이브 강의

2. Python(파이썬) 문법 정리

3. SQL로 데이터 기초 분석 하기(2주차_Team Project) - 데이터 파악

 

 

 

1. SQL 쿼리 작성 익숙해지기

 

SQL 문법에 대한 기억은 거의 되살렸다.

처음 배울 때도 느꼈지만, JOIN과 서브쿼리를 익숙하게 사용하는게 가장 중요한 것 같다.

코테의 두려움을 없애자!!

 

코드카타 SQL

---

[쿼리 작성 시 유의할 것 : 날짜 타입]

• 최종 결과에서 요구하는 데이터 형태를 잘 파악할 것
• DATE 데이터의 디폴트 값은 시간까지 나오기 때문에 날짜만 필요하다면 DATE_FORMAT 필요
• 날짜 타입 데이터의 대소 비교가 필요한 경우, 기준 날짜에 ' '(따옴표) 사용 필요

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE,
       CASE WHEN OUT_DATE < '2022-05-02' THEN '출고완료'
            WHEN OUT_DATE >= '2022-05-02' THEN '출고대기'
            WHEN OUT_DATE IS NULL THEN '출고미정'
       END AS "출고여부"
FROM FOOD_ORDER
ORDER BY ORDER_ID

 

 

[최댓값 찾기 > MAX와 LIMIT의 결과가 다른 이유]

-- 비싼 값 찾기 : LIMIT
SELECT product_id, product_name, product_cd, category, price
FROM food_product
ORDER BY price DESC
LIMIT 1;

-- 비싼 값 찾기 : MAX
SELECT product_id, product_name, product_cd, category, MAX(price)
FROM food_product;

 

• MAX 사용 시 동작 방식은 다음과 같음

      ① MAX(price)를 사용해 테이블에서 가장 큰 가격 값을 계산
      ② 동시에, product_id, product_name, product_cd, category를 선택

• MySQL에서는 GROUP BY 없이 집계 함수(MAX)와 일반 열을 함께 선택하면 오류 가능성 있음. MySQL이 지정되지 않은 열의 값을 임의로 반환하기 때문
• MAX 함수는 GROUP BY 없이 사용하지 않길 추천, 꼭 쓰려면 서브쿼리 사용 필요

SELECT product_id, product_name, product_cd, category, price
FROM food_product
WHERE price = (SELECT MAX(price) FROM food_product);

 

 

 

 

SQL 라이브 강의

---

[JOIN]

• 테이블과 테이블을 특정 컬럼 기준, 옆으로 합치는 것
• MySQL에는 FULL OUTER JOIN 기능이 없음
• JOIN의 디폴트 값은 INNER JOIN, 쿼리에서 INNER JOIN 사용시 JOIN만 써도 됨
• SELF JOIN : 고객 간 매칭이 필요할 때

SELECT c1.연령대, 
       c1.고객ID AS 고객1_ID, 
       c1.이름 AS 고객1_이름, 
       c1.성별 AS 고객1_성별, 
       c2.고객ID AS 고객2_ID, 
       c2.이름 AS 고객2_이름, 
       c2.성별 AS 고객2_성별
FROM customers c1
INNER JOIN customers c2
ON c1.연령대 = c2.연령대
AND c1.성별 != c2.성별
AND c1.고객ID < c2.고객ID; #중복제거!!

 

 

[UNION]

• 테이블과 테이블을 위아래로 합치는 것
• SELCET문의 컬럼 개수와 데이터 형식이 동일해야 함
• UNION은 기본적으로 중복제거 값을 반환하고, UNION ALL은 중복제거 없이 모두 합치기만 함
• FULL OUTER JOIN 구현 : LEFT JOIN + UNION + RIGHT JOIN

 

 

[서브 쿼리]

 

ⓐ 반환 타입에 따른 분류

• 스칼라 반환 : select, where, having 절에서 사용, 스칼라 값(숫자 or 문자열 등) 반환
• 다중 행 반환 : 1개의 컬럼에 대해서 여러 행이 반환되는 것, 파이썬의 리스트와 같은 역할을 함(IN,ANY,ALL 과 함께 사용)
• 다중 열 반환 : from, join에서 사용, n개의 열과 n개의 행(가상의 또 다른 테이블) 반환

 

ⓑ 사용 위치에 따른 분류

• SELECT 절 : 단일값 반환, 외부쿼리에 새로운 파생변수 생성

SELECT customer_id,
       (SELECT COUNT(*) 
       FROM orders 
       WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

• FROM 절(인라인 뷰) : 가상 테이블을 추가로 만들어 사용

SELECT customer_id, SUM(payment_value) AS total_payment
FROM (
    SELECT customer_id, payment_value
    FROM payments
    WHERE payment_date >= '2023-01-01'
) AS recent_payments
GROUP BY customer_id;

• WHERE 절 : 비교연산자 또는 논리연산자와 함께 사용

-- 고객의 나이가 모든 고객의 평균 나이보다 큰 경우
SELECT customer_id
FROM customers
WHERE age > ( 
    SELECT AVG(age)
    FROM customers
);

--서브쿼리 결과값 중 하나라도 매칭되면 true
SELECT customer_id
FROM customers
WHERE customer_id IN ( --서브쿼리 결과값 중 하나라도 매칭되면 true
    SELECT customer_id
    FROM orders
    WHERE order_status = 'shipped'
);

 

• ALL은 결과 모든 값에 대해 조건 만족, ANY는 결과 중 하나라도 조건 만족(IN은 하나라도 "일치"하는 것임으로 차이 존재), EXISTS는 결과가 하나라도 있기만 하면 조건 만족으로 

 

ⓒ 의존성에 따른 분류

• 비상관 서브쿼리 : 외부쿼리와 독립적으로 1번만 실행 > 내부쿼리가 외부쿼리에 사용됨
• 상관 서브쿼리 : 내부쿼리가 외부쿼리의 데이터를 참조, 외부쿼리의 매 행(결과)을 서브쿼리로 던져줘서 최종적인 조건에 맞는 결과를 반환 하는 것
• 성능 저하 이슈 완화를 위해 join 또는 window함수로 변환

-- 상관 서브쿼리의 예1
SELECT order_id, customer_id, order_amount
FROM orders o1
WHERE order_amount > (
    SELECT AVG(order_amount)
    FROM orders o2
    WHERE o1.customer_id = o2.customer_id
);

-- 상관 서브쿼리의 예1: JOIN으로 변환
SELECT o1.order_id, o1.customer_id, o1.order_amount
FROM orders o1
JOIN (
    SELECT customer_id, AVG(order_amount) AS avg_order_amount
    FROM orders
    GROUP BY customer_id
) avg_orders
ON o1.customer_id = avg_orders.customer_id
WHERE o1.order_amount > avg_orders.avg_order_amount;

-- 상관 서브쿼리의 예2
SELECT customer_id, 
       (SELECT COUNT(*) 
        FROM orders 
        WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

-- 상관 서브쿼리의 예2: JOIN으로 변환
SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

-- 상관 서브쿼리의 예2: 윈도우 함수로 변환!
SELECT customer_id, COUNT(order_id) OVER(PARTITION BY customer_id) AS order_count
FROM orders;

• 여기서 윈도우 함수는? 집계함수와 유사한 작업으로, 쿼리의 행 집합에 대해 수행, 집계함수가 쿼리 행을 하나의 결과 행으로 그룹화 하는 것과 달리 윈도우 함수는 쿼리의 각 행에 대해 결과 생성
• 윈도우는 다음 강의에서 계속!

 

 

 

 

2. 파이썬 문법

 

[len]

• 길이 구하는 것(글자 수 세기와 같은)

 

 

[파이썬에서 n번째 요소는?]

text = 'abcdefghijk'
result = text[3:8]
print(result)

• [3:8]은 3번부터 8번 전까지를 의미(3이상 8미만), 파이썬의 글자 순서는 0부터 세기 때문에 첫 글자는 0번째
• [:]는 text 전체를 복사하는 의미가 됨

 

 

[split]

• X.split('@')[0 or 1] : X를 @문자를 기준으로 나누는데, @기준 왼쪽 덩어리를 출력하고 싶다면 [] 안에 0, 오른쪽 덩어리를 출력하고 싶다면 1 (나뉜 후 순서상 첫번째, 두번째 요소이므로)

 

 

[sort]

• SQL의 order by 기능이며, desc 정렬을 의미하는 것이 sort(reverse=True) > True가 디폴트임

 

 

[list]

• 값의 집합이자 나열이라고 할 수 있음
• 리스트의 중첩된 사용 > 리스트의 요소가 리스트가 될 수 있음

a = [1, 2, [2, 3], 0]
print(a[2])      # [2, 3]
print(a[2][0])   # 2

• append(추가값) : 리스트에 괄호 안의 값이 추가됨

 

 

[딕셔너리]

• 키-값 쌍으로 구성된 집합이며, 딕셔너리 그 자체가 리스트의 요소가 되기도 함

a_dict = {'name' : 'bob', 'age' : 27, 'friend' : ['영희', '철수']}
result = a_dict['friend'][1]
print(result) #철수

• a_dict['height'] = 100을 하면 a_dict라는 딕셔너리에 키-값>height-100 이 추가되는것

 

 

[조건문 if]

• if 조건 : 프린트할 결과 else : 프린트할 결과
• 조건이 여러개 필요하다면 중간에 elif를 추가하여 적용 가능
• 프린트할 결과가 n개 일 때, tab을 이용하여 n개 줄의 시작 위치를 맞춰야 함(한줄 더 띄우거나, 시작 위치를 맞추지 않으면 한 조건에 속하지 않는 것으로 해석)

 

 

[반복분 for]

• for문의 기본 형식과 심화 형식은 다음과 같음

-- 기본
fruits = ['사과', '배', '감', '귤']

for fruit in fruits:
    print(fruit)

-- 심화
people = [
    {'name': 'bob', 'age': 20},
    {'name': 'carry', 'age': 38},
    {'name': 'john', 'age': 7},
    {'name': 'smith', 'age': 17},
    {'name': 'ben', 'age': 27},
    {'name': 'bobby', 'age': 57},
    {'name': 'red', 'age': 32},
    {'name': 'queen', 'age': 25}
]

for person in people:
    if person['age'] > 20:
        print(person['name'])

 

 

[for 문에서 사용하는 enumerate, break]

for i, fruit in enumerate(fruits):
    print(i, fruit)
    if i == 4:
        break

• fruits 라는 리스트 안의 한 값을 선택한 것이 fruit, 각 fruit 값에 i라는 순서 숫자 부여, 특정 순서에서 limit을 거는 것(디버깅이 필요할 때 많이 사용)

 

 

[A += b]

• 기존 값 A에 b를 더해준다는 의미

-- 예1
num_list = [1, 2, 3, 6, 3, 2, 4, 5, 6, 2, 4]

count = 0

for num in num_list:
    if num % 2 == 0:
        count += 1

print(count)

-- 예2
a = [1, 2, 3]

a += [2, 7] #a.append([])와 같은 결과
print(a)     # [1, 2, 3, 5, [1, 2], 2, 7]

 

 

[함수]

• def 함수이름(변수) : 실행 내용

 

 

 

 

3. SQL 팀 과제 - 데이터 파악

 

[데이터베이스 이름 변경]

• 바로 이름 변경 불가, DB dump - 새 DB 생성 - DB restore

 

 

[EDA]

• null 값 체크, 중복 값 체크, 최대최소평균 등 찾기 > 파이썬 이용한 EDA가 직관적이고 쉽다는 것이 느껴짐
• 알고싶은 테이블간, 컬럼간 상관관계 등 쿼리 작성이 필요한 질문과 가설 수립이 우선되어야 할 것 같음