[사전캠프 11일차] 아티클 스터디⑧, SQL 공부, SQLD 공부

아 바쁘다 바빠.

 

오늘 한 일은,

• 여덟 번째 아티클 스터디 진행하기
• SQL 공부
  • 개인 퀘스트(달리기반) - [SQL 실전!] Lv4.단골 고객님 찾기
• SQLD 공부
  • [SQLD 자격증 챌린지] 4주차

 

---

 

아티클 스터디 ⑧: 데이터 분석가에게 코딩테스트가 필요할까요?

오늘 읽은 아티클:

데이터 분석가에게 코딩테스트가 필요할까? | 요즘IT

 

나는 사실 해당 과정을 마케터로서의 취업을 준비하며 수강 중이라 데이터 분석가로서 취업을 준비하는 데 코딩 테스트도 준비해야 하는 줄 몰랐다. 그래서 데이터 분석가로 취업하는 데 코딩 테스트를 준비해야 하는지, 또 준비해야 한다면 어떻게 준비할지보다, 나에겐 각 조직에서 데이터 분석가가 어떤 업무를 하는지 실제 사례들을 들어준 부분이 더 관심이 갔다.

250207 아티클 스터디를 하며 개인 요약 한 일부.

데이터 분석에 필요한 역량으로 제시한 ✔문제 정의, ✔데이터 분석에 대한 이해, ✔타 직군과의 협업 등은 어떤 글을 읽으나 항상 중요하다고 모든 데이터 분석가들이 말한다. 오늘은 그 역량들이 왜 이렇게 중요한지를 아티클 속에서 제시한 소방청, 서울시, 오바마 캠프의 사례를 통해 실제로 행한 업무 내용을 대략적으로나마 알게 됨으로써 실감할 수 있었다. 사실 마케팅팀도 회사 규모가 그리 크지 않는 한 실무에서 할 법한 업무 내용으로도 보였다. 고객군을 세그먼트로 분류해 A/B테스트를 통해 더 나은 캠페인을 진행하고, 어떤 위치/어느 시기에 빈번히 발생하는 일이 무엇인지 파악해 그에 따른 선제적인 조치를 어떻게 취할지 고민하고, 이 과정에서 필요한 데이터를 타 부서에 요청하는 등 이런 내용은 마케팅팀에서 캠페인을 기획하고 광고 소재를 제작할 때도 필요한 과정이다. 그래서 오늘 아티클을 통해 사실 마케팅에서 데이터 분석 능력을 활용하는 능력이 활용될 수 있음을 이전보다 선명하게 그려볼 수 있는 시간임과 동시에 더 많은 사례들을 알아보고 싶어졌다(그렇다, 관련 책을 찾아서 읽어보고 싶다는 말이다). 그리고 이런 여러 사례들을 알아본다면 추후에 본캠프 때 어떤 프로젝트를 진행하게 될진 모르겠지만 많은 도움을 얻을 수 있지 않을까 싶다.

 

---

 

SQL 공부: 개인 퀘스트(달리기반) - [SQL 실전!]

Lv4. 단골 고객님 찾기

Orders 테이블

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2024-01-01	150
102	2	2024-01-03	200
103	1	2024-01-04	300
104	3	2024-01-04	50
105	2	2024-01-05	80
106	4	2024-01-06	400

Customers 테이블

CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	USA
4	David	Canada

Q1. 고객별 주문 건수와 총 주문 금액을 조회하기 
       (출력 결과엔 고객 이름, 주문 건수, 총 주문 금액을 포함, 단 주문한 적 없는 고객 결과도 포함.)

SELECT c.CustomerName
       COUNT(1) OrderCount
       SUM(o.TotalAmount) TotalSpent
FROM Orders o LEFT JOIN Customers c ON o.CustomerID=c.CustomerID
GROUP BY 1

우선 처음에 이렇게 쿼리를 입력했다.

주문한 적도 없는 고객 결과까지 포함해야 하니까 LEFT JOIN으로 두 테이블을 묶어줬고, 필요한 컬럼을 SELECT문에 하나씩 적어갔다. 

마지막으로 COUNT()와 SUM()을 고객별로 계산해야 하니 GROUP BY를 추가했다.

 

그리고 정답 코드를 확인했더니 TotalSpent 컬럼을 계산하는 쿼리에서 NULL값일 경우 0으로 대체하게 COALESCE()를 사용했다.

그래서 SUM을 사용할 때 NULL값이 존재하면 에러가 떠서 실행이 안 되는지 궁금해서 찾아봤다.

 

[참고] [SQL] 집계와 서브쿼리 - COUNT, DISTINCT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, 서브쿼리

찾아보니, SUM()에서 NULL값은 무시하고 계산한다. 그래서 NULL값을 0으로 대체하든 안 하든 같은 결과값이 나올 것이다. 

단 AVG()에선 문제가 발생할 수도 있을 것 같다. 왜냐하면 AVG() 역시 NULL값을 무시하고 계산하기 때문에 에러 메시지가 뜨진 않을 것이다. 그렇지만 이 경우 평균의 분모 값이 NULL값을 0으로 대체했을 때와 그렇지 않을 때 도출될 평균값이 달라지기 때문에 이 경우는 유의해야겠다.

 

아무튼, 일단 위의 쿼리로 제출했다!

 

Q2. 나라별로 고객별 총 주문 금액을 계산했을 때, 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하기

SELECT c.Country
       c.CustomerName Top_Customer
       SUM(o.TotalAmount) Top_Spent
FROM Orders o LEFT JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY 1, 2
ORDER BY 1, 3 DESC LIMIT 1

나는 우선 머리를 짜내서 이렇게 작성했다.

 

LEFT JOIN으로 두 테이블을 묶고

SELECT문에 필요한 컬럼들을 적는데 고객별로 총 주문 금액을 사용해야 하니 SUM()을 사용했다.

그리고 해당 계산은 나라별로, 또 고객별로 이뤄지니 GROUP BY를 이용해 적어주고

마지막으로 ORDER BY를 이용해 총 주문 금액이 큰 순으로 정렬해서(=내림차순) 1행만 도출하도록 작성했다.

 

정답은 확인했는데.. 이게 뭐죠.....? 왜 이렇게 길게 작성한 거죠..

......

...하

울지 말고 일어나서 이제 오답을 체크하자.

 

정답 코드를 보니까 내 코드는 (실행해 본 적은 없지만) 아마 저렇게 작성하면 국가별로 UK 한 줄, USA 한 줄, Canada 한 줄 해서 출력되는 게 아니라 그냥 이 중에서 가장 총 주문 금액이 많은 한 줄만 뜰 것 같다(고 추측해본다, 왜냐하면 나는 실행해보질 않았으니...)

 

그래서 서브쿼리를 사용해서 SQL을 수정한다.

SELECT c.Country
       c.CustomerName Top_Customer
       SUM(o.TotalAmount) Top_Spent
FROM Orders o LEFT JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY 1, 2
HAVING SUM(o.TotalAmount) = (
        SELECT 
            MAX(SumSpent)
        FROM 
            (SELECT 
                 SUM(o2.TotalAmount) SumSpent
             FROM 
                 Customers c2 LEFT JOIN Orders o2 ON c2.CustomerID = o2.CustomerID
             WHERE c2.Country = c.Country
             GROUP BY c2.CustomerID) a
        )

[참고] 그룹합수, HAVING, 단일 및 다중 서브쿼리

HAVING절.. 찾아보기도 하고 제시된 코드가 무슨 뜻인지도 알겠는데.. 저게 제일 단순한 건지 의문이어서 개운하지가 않다.

(아 직접 DBeaver로 실행해보면서 하는 게 맘 편한데 내 노트북엔 뭘 더 설치하기가 겁나는데 또 뭔가를 설치하라니요.

그냥 서버에 있는 데이터로만 문제를 내주거나 아니면 해당 테이블 내용을 추가해주거나 해주지... 아쉽다.)

 

---

 

SQLD 공부: [SQLD 자격증 챌린지] 수강하기

성능 데이터 모델링

성능 데이터 모델링

• 성능 데이터 모델링 = 데이터베이스 성능 향상을 목적으로 하는 작업
• 데이터베이스에 생길 수 있는 문제
  • 매우 빠른 템포로 자주 발생하는 쿼리문이 비효일적이면 DB 전체 성능에 영향을 줄 수 있음
  • 애초에 테이블 설계 자체가 잘못됐을 수 있음
  • 테이블 내부에 원하는 정보가 없어서 불가피하게 다른 테이블에서 정보를 가져와야 할 수도 있음
• => 이런 문제들을 해결하기 위해 하는 작업이 성능 데이터 모델링이며 앞으로 배울 정규화 등 여러 방법을 통해 함

성능 데이터 모델링 수행시점

• 데이터베이스 분석 및 설계 단계에서부터 준비해야 함 시간이 지나서 수행할수록 더 많은 비용이 들어간다
  • 어떤 트랜잭션이 비즈니스 로직의 핵심인지 파악하고 사용자 업무 처리에 중요성을 보이는지 분석해야 함
    *트랜잭션: 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위

성능 데이터 모델링의 고려사항

• ⑴정규화를 정확하게 수행
• ⑵데이터베이스 용량 산정 수행
• ⑶데이터베이스에서 발생되는 트랜잭션의 유형을 파악
• ⑷데이터베이스의 용량과 트랜잭션 유형에 따라 반정규화 수행
• ⑸이력모델, PK/FK, 슈퍼 타입/서브 타입의 조정
• ⑹성능 관점에서 데이터 모델 검증

정규화

정규화(Normalization)

• 정규화 : 데이터에 대한 중복성을 제거하여 성능을 향상시키는 것 테이블을 쪼갠다
• 조회하고자 하는 데이터가 여러 테이블에 분산되어 있지 않으면 조회 과정에서의 불필요한 연산을 줄일 수 있기 때문에 성능 향상에 기여함
• 정규화된 테이블에서 데이터를 조회할 때 성능이 향상될 수도 혹은 아닐 수도 있음  
  정규화를 한다고 해서 무조건 성능이 향상되는 거 아님
• 데이터의 일관성을 유지하고 데이터의 중복을 방지하며 데이터의 유연성을 유지하기 위해 데이터를 분해하는 과정 

정규화 용어

• 정규화 : 작은 단위의 테이블로 나눠가는 과정
• 정규형(NF) : 정규화된 결과물
• 함수적 종속성 : 어제 공부했다. 거기 써놨으니 보도록.
• 결정자 : 함수적 종속성에서 결정짓는 요소 Y는 X에 함수적 종속성을 가질 때, X를 결정자라 함
• 다치 종속 : (궁금하면 노션 교재 가서 봐라)

정규화의 이점

• 데이터의 유연성 : 높은 응집도와 낮은 결합도 원칙에 충실해짐
  *응집도 : 요소들이 서로 관련되어 있는 정도 (높을수록 좋다)
  *결합도 : 요소들 간 상호 의존하는 정도 (낮을수록 좋다 (∵결합도가 높으면 시스템 구현 및 유지보수가 어렵기에))
• 데이터의 재활용성 : 정규화의 결과로 개념에 대한 재활용 가능성이 증가함
• 데이터의 중복 최소화 : 속성이 한 번만 포함되기 때문에 데이터의 중복이 최소화 (제1정규화 생각하면 뭔 소린지 앎)

정규화 이론

제1정규화

• 한 속성에 여러 개의 속성값 넣지 마

좌 - 제1정규형을 위반한 모습 (연락처 컬럼에 값이 2개 들어가있음) / 우 - 제1정규형을 만족한 모습

제2정규화

• 제1정규화를 만족시키고서 PK 속성이 아닌 모든 컬럼은 PK 전체에 종속시킴 
  
• = PK 중 일부의 칼럼에만 종속되는 칼럼이 있으면 안 됨
  
  • ↖∵특정한 데이터를 갱신하려고 할 때 갱신 이상 현상이 발생할 가능성이 크기 때문

좌 - 제2정규형을 위반한 경우 / 우 - 제2정규형을 만족한 경우

제3정규화

• 제2정규화를 만족시키고 일반 속성 간에도 함수 종속 관계가 존재하지 않아야 함 = 이행적 함수 종속성 제거할 것

좌 - 제3정규형을 위반한 경우 / 우 - 제3정규형을 만족한 경우

정규화 유형

• 제1정규화
• 제2정규화
• 제3정규화
• 보이스 - 코드 정규화 (BCNF)
• 제4정규화
• 제5정규화

도표로 보는 정규화 유형

 

반정규화

반정규화

• 반정규화 : 정규화를 수행하지 않는 모델 = 정규화에 반대되는 행위를 하는 것
• 처음엔 정규화를 염두에 두고 데이터베이스를 구축하고 설계함
  → 그런데 운영하다보니 반정규화의 필요가 생겨남
  → 그래서 성능 향상(특히 조회=읽기 성능)을 위해 데이터가 중복되는 것을 감수하고도 반정규화를 수행함
  즉, 처음부터 반정규화를 염두에 두고 데이터베이스를 구축한다? 아니다!!
        정규화가 나쁜 것이다? 아니다!!
• 반정규화 기법 : ①테이블의 중복, ②칼럼의 중복, ③관계의 중복
• 반정규화 절차
  • 반정규화는 반드시 적용해야 하는 요소가 아님!!
  • 따라서 먼저 반정규화 대상을 조사하고, 다른 방법이 있는지 검토하고, 그 후에 반정규화를 적용함

반정규화 절차

반정규화 기법

• 1️⃣ 테이블 반정규화
  • 테이블 병합 데이터를 중복 저장하게 되지만 조회 과정의 성능 향상을 위해 수행
    • 1:1 관계 테이블 병합
    • 1:M 관계 테이블 병합
    • 슈퍼 / 서브 타입 테이블 병합
  • 테이블 분할 특정 테이블을 여러 개의 테이블로 나눔 
    • 수직 분할 칼럼 단위
    • 수평 분할 행 단위, 특정 값을 기준으로(= 특정 값의 범위에 따라) 테이블을 분할
  • 테이블 추가
    • 중복 테이블 추가 동일한 테이블 구조를 중복하여 원격 조인을 제거하여 성능을 향상
    • 통계 테이블 추가 SUM, AVG, COUNT 등을 미리 수행하고 계산
    • 이력 테이블 추가
    • 부분 테이블 추가
• 2️⃣ 칼럼 반정규화
  • 중복 칼럼 추가 조인 연산으로 인한 성능 저하를 방지하지 위해 중복 칼럼을 추가하여 조인 연산을 수행하지 않도록
  • 파생 칼럼 추가 계산하는 값을 미리 계산한 칼럼을 따로 구성
  • 이력 테이블 칼럼 추가
• 3️⃣관계 반정규화
  • 중복 관계 추가 여러 경로에 걸쳐 테이블 조인을 하는 경우 조인 연산 자체를 줄여서 조회 성능을 향상시키는 방법

 

---

벌써 다음주가 사전캠프가 끝이라니.

 

아 근데 온라인 강의 분량 실화인가 싶다.

물론 제가 사전캠프 시작일로부터 일주일 뒤에 들어오긴 했지만, 일주일 더 일찍 들어왔다고 해서 [파이썬 문법 기초], [엑셀보다 쉽고 빠른 SQL], [SQLD 자격증 챌린지], [ADSP 자격증 챌린지]를 전부 수강할 수 있을 거 같지 않다..

지금 [파문기], [엑쉽빠]는 완강했고 [SQLD]를 4주차까지 들은 이 시점에서 일주일 더 준다고 하여 [SQLD] 15주차까지 다 듣고 [ADSP] 1주차부터 15주차까지 다 들을 수는 없기 때문이다... (지금도 새벽 2시임.. 그래서 사실 본캠프 들어가서 12시간 공부한다는 건 큰 걱정 안 된다^^)

 

아무튼, 사전캠프 마무리하는 다음주도 아자아자~ 하는 데까지 하는 거지 뭐. 일단 하는 게 중요한 거 아니겠어?