2025.02.27 본_캠프 8일차

1.데이터 아키텍처? 쉽게 배워봅시다

• 요약 :

데이터 아키텍처는 데이터를 효과적으로 수집, 저장, 처리, 관리하는 구조와 방법을 설계하는 과정이다.

• 주요 포인트 :
  • 데이터는 OLTP, 기업 애플리케이션, 서드파티, 웹 로그, IoT 등 다양한 원천에서 생성된다.
  • 데이터를 활용 가능하도록 변환하는 과정인 ETL(추출, 변환, 적재)이 중요하다.
  • 데이터를 저장하는 공간으로 데이터 웨어하우스(구조화 데이터)와 데이터 레이크(비정형 포함)가 있다.
  • 특정 부서별 맞춤형 데이터를 제공하는 데이터 마트가 활용된다.
  • 데이터는 비즈니스, 개발, 보안, 공공, 의료 등 다양한 분야에서 가치 있게 활용된다.
• 핵심 개념 :
  • 데이터 아키텍처: 데이터를 효과적으로 관리하기 위한 구조와 설계 방식
  • ETL : 데이터를 원천에서 추출,변환,적재하는 과정
  • 데이터 웨어 하우스 : 정형 데이터를 저장하는 중앙 저장소
  • 데이터 레이크 : 다양한 형식의 데이터를 저장하는 공간
  • 데이터 마트 : 특정 부서나 목적에 맞춘 데이터 웨어하우스의 부분 집합
• 용어 정리 :
  • OLTP : 온라인 트랜잭션 데이터를 처리하는 데이터베이스
  • ERP : 기업의 자원 관리 시스템
  • CRM : 고객 관리 시스템
  • BI : 데이터를 분석해 비즈니스 인사이트를 제공하는 기법
• 관련 사례 :

  • VMS Solutions의 AWS 기반 데이터 아키텍처 구축

    • VMS Solutions는 AWS와 협력하여 데이터 수집, 분석, 보관 등 전 과정에서 데이터를 효과적으로 활용하고 안전하게 보호하는 환경을 구축했습니다. 이를 통해 AI 기술 도입을 용이하게 하고 데이터 보안을 강화했습니다.
    • 아마존 데이터 아키텍처

  • IBM의 데이터 패브릭 아키텍처

    • 다양한 데이터 소스와 환경에 분산된 데이터를 통합하여 효율적으로 관리하고 활용할 수 있도록 지원합니다. 이를 통해 기업은 데이터의 복잡성을 줄이고, 데이터에 대한 접근성을 높이며, 비즈니스 인사이트를 도출하는 데 도움을 받고 있습니다.
    • IBM 데이터 패브릭 아키텍처

    ---

2. SQL_코드타카

1. 특정 옵션이 포함된 자동차 리스트 구하기

문제 설명

자동차 대여 회사의 CAR_RENTAL_COMPANY_CAR 테이블에서 네비게이션 옵션이 포함된 자동차 리스트를 조회한다.
자동차 ID 기준 내림차순 정렬해야 한다.

내 SQL 쿼리

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC;

쿼리 분석

• OPTIONS LIKE '%네비게이션%' → OPTIONS 컬럼에서 네비게이션이 포함된 자동차 필터링
• ORDER BY CAR_ID DESC → 자동차 ID 기준 내림차순 정렬

---

2. 건에 부합하는 중고거래 상태 조회하기

문제 설명

USED_GOODS_BOARD 테이블에서 2022-10-05에 등록된 중고거래 게시물의 ID, 작성자 ID, 제목, 가격, 거래 상태를 조회한다.
STATUS 값에 따라 한글로 변환하여 출력해야 한다.

내 SQL 쿼리

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
       CASE 
           WHEN STATUS = 'SALE' THEN '판매중'
           WHEN STATUS = 'RESERVED' THEN '예약중'
           WHEN STATUS = 'DONE' THEN '거래완료'
       END STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE,'%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID DESC;

쿼리 분석

• CASE 문을 사용해 STATUS 값 변환 (SALE → 판매중, RESERVED → 예약중, DONE → 거래완료)
• DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05' → 날짜 필터링
• ORDER BY BOARD_ID DESC → 게시글 ID 기준 내림차순 정렬

---

3. 환자 진료 예약 목록 조회하기

문제 설명

PATIENT, DOCTOR, APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 목록을 조회한다.
진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시를 조회해야 한다.

내 SQL 쿼리

SELECT a.APNT_NO, p.PT_NAME, p.PT_NO, a.MCDP_CD, d.DR_NAME, a.APNT_YMD
FROM APPOINTMENT a 
LEFT JOIN DOCTOR d ON a.MDDR_ID = d.DR_ID
LEFT JOIN PATIENT p ON a.PT_NO = p.PT_NO
WHERE a.APNT_CNCL_YN = 'N' 
  AND DATE_FORMAT(a.APNT_YMD,'%Y-%m-%d') = '2022-04-13'
ORDER BY a.APNT_YMD;

쿼리 분석

• LEFT JOIN을 사용하여 환자 정보, 의사 정보, 예약 정보 결합
• DATE_FORMAT(a.APNT_YMD, '%Y-%m-%d') = '2022-04-13' → 날짜 필터링
• WHERE a.APNT_CNCL_YN = 'N' → 취소되지 않은 예약만 조회
• ORDER BY A.APNT_YMD → 진료예약일시 기준 오름차순 정렬

---

4. 자동차 대여 기록에서 장기/단기 대여 구분하기

문제 설명

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 9월 대여 기록을 조회한다.
대여 기간이 30일 이상이면 '장기 대여', 그렇지 않으면 '단기 대여'로 구분해야 한다.
결과는 대여 기록 ID 기준 내림차순 정렬해야 한다.

내 SQL 쿼리

SELECT HISTORY_ID,
       CAR_ID,
       DATE_FORMAT(START_DATE,'%Y-%m-%d')  START_DATE,
       DATE_FORMAT(END_DATE, '%Y-%m-%d') END_DATE,
      CASE WHEN DATEDIFF(END_DATE, START_DATE) >= 29 THEN '장기 대여'
           ELSE '단기 대여' END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC; 

쿼리 분석

• DATEDIFF(END_DATE, START_DATE) >= 29 → 대여 기간이 30일 이상이면 '장기 대여'로 분류
• DATE_FORMAT(START_DATE, '%Y-%m') LIKE '2022-09' → 2022년 9월의 대여 기록만 필터링
• ORDER BY HISTORY_ID DESC → 대여 기록 ID 기준 내림차순 정렬

---

5. 년, 월, 성별 별 상품 구매 회원 수 구하기

문제 설명

USER_INFO와 ONLINE_SALE 테이블에서 년, 월, 성별 별 상품 구매 회원 수를 조회한다.
결과는 년, 월, 성별을 기준으로 오름차순 정렬해야 한다.

내 SQL 쿼리

SELECT DATE_FORMAT(S.SALES_DATE, '%Y') YEAR,
       DATE_FORMAT(S.SALES_DATE, '%m') MONTH,
       U.GENDER,
       COUNT(DISTINCT S.USER_ID) USERS
FROM USER_INFO U
JOIN ONLINE_SALE S ON U.USER_ID = S.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, U.GENDER
ORDER BY YEAR, MONTH, U.GENDER;

쿼리 분석

• DATE_FORMAT(S.SALES_DATE, '%Y'), DATE_FORMAT(S.SALES_DATE, '%m') → 연도와 월 추출
• JOIN을 사용해 USER_INFO와 ONLINE_SALE 테이블 결합
• COUNT(DISTINCT S.USER_ID) → 회원별 중복 구매 제거하여 구매 회원 수 계산
• WHERE U.GENDER IS NOT NULL → 성별 정보 없는 회원 제외
• ORDER BY YEAR, MONTH, U.GENDER → 연, 월, 성별 기준 오름차순 정렬

---

정리

오늘 푼 5문제를 통해 JOIN, GROUP BY, CASE, DATEDIFF(), COUNT(DISTINCT) 활용법을 익혔다.
앞으로도 실전 SQL 문제를 풀면서 실력을 키워 나가자.

---

SQL 문제 풀이 정리 (JOIN 활용 및 응용 문제)

오늘 학습한 SQL 문제들을 정리한다. 각 문제의 조건과 풀이, 그리고 간단한 설명을 포함한다.

---

1. 결제 유저와 비결제 유저 구분하기 (JOIN 기본 문제)

문제 개요

• users 테이블을 기준으로 payment 테이블을 LEFT JOIN하여 결제 여부를 구분.
• CASE WHEN을 사용하여 결제 유저와 비결제 유저를 나눔.
• gb를 기준으로 유저 수(usercnt)를 계산.

사용한 SQL

SELECT 
    CASE WHEN p.game_account_id IS NULL THEN '결제안함'
         ELSE '결제함' 
    END gb,
    COUNT(DISTINCT u.game_account_id) usercnt
FROM basic.users u 
LEFT JOIN basic.payment p 
ON u.game_account_id = p.game_account_id
GROUP BY 1;

쿼리 설명

1. LEFT JOIN을 사용하여 users 테이블을 기준으로 payment 테이블과 조인.
2. CASE WHEN을 사용해 결제 내역이 있는지 확인하여 분류.
3. COUNT(DISTINCT game_account_id)를 사용해 각 그룹별 유저 수를 계산.
4. GROUP BY 1을 통해 결제 여부별로 그룹화.

---

2. 서버번호별 결제 유저의 캐릭터 수 및 총 결제금액 구하기 (JOIN 응용1)

문제 개요

• users 테이블에서 serverno >= 2인 유저만 선택.
• payment 테이블에서 결제 방식이 CARD인 데이터만 필터링.
• game_account_id를 기준으로 game_actor_id(캐릭터 ID)를 중복 없이 카운트.
• pay_amount를 총합하여 sumamount 계산.
• HAVING을 사용하지 않고 actor_cnt >= 2 조건을 적용하여 필터링.

사용한 SQL

SELECT *
FROM (
    SELECT u.game_account_id,
           COUNT(DISTINCT u.game_actor_id) actor_cnt,
           SUM(p.pay_amount) sumamount
    FROM (
            SELECT * 
            FROM basic.users
            WHERE serverno >= 2 
        ) u 
    LEFT JOIN (
            SELECT *
            FROM basic.payment 
            WHERE pay_type = 'CARD'
        ) p 
    ON u.game_account_id = p.game_account_id 
    GROUP BY u.game_account_id
) t
WHERE t.actor_cnt >= 2
ORDER BY t.sumamount DESC;

쿼리 설명

1. LEFT JOIN을 사용하여 users 테이블과 payment 테이블을 결제 방식이 CARD인 경우만 조인.
2. COUNT(DISTINCT game_actor_id)를 사용해 유저가 가진 캐릭터 개수를 중복 없이 계산.
3. SUM(pay_amount)를 사용해 유저별 총 결제 금액(sumamount)을 계산.
4. actor_cnt >= 2인 데이터만 서브쿼리에서 필터링.
5. ORDER BY sumamount DESC로 총 결제금액이 높은 순서로 정렬.

---

3. 첫 접속일과 마지막 결제일을 비교하여 평균 이용 일수 구하기 (JOIN 응용2)

문제 개요

• users 테이블에서 game_account_id, first_login_date, serverno 추출.
• payment 테이블에서 각 game_account_id별 가장 최근 결제일(date2) 추출.
• INNER JOIN 수행 후, 첫 접속일보다 마지막 결제일이 큰 경우만 선택.
• DATEDIFF()를 사용하여 diffdate(마지막 결제일 - 첫 접속일) 계산.
• serverno별 평균 diffdate를 ROUND()로 정수 변환하여 출력.
• diffdate >= 10인 경우만 필터링.

사용한 SQL

SELECT serverno,
       ROUND(AVG(diffdate)) avgdiffdate
FROM (
    SELECT u.game_account_id, 
           u.first_login_date, 
           u.serverno, 
           p.date2,
           DATEDIFF(p.date2, u.first_login_date) diffdate
    FROM (
            SELECT game_account_id, first_login_date, serverno
            FROM basic.users
        ) u 
    INNER JOIN (
            SELECT game_account_id, MAX(approved_at) AS date2
            FROM basic.payment
            GROUP BY game_account_id
        ) p 
    ON u.game_account_id = p.game_account_id
    WHERE u.first_login_date < p.date2
) t
WHERE diffdate >= 10
GROUP BY serverno
ORDER BY serverno DESC;

쿼리 설명

1. users 테이블에서 필요한 컬럼(game_account_id, first_login_date, serverno)을 가져옴.
2. payment 테이블에서 MAX(approved_at)을 사용하여 유저별 가장 최근 결제일(date2)을 찾음.
3. INNER JOIN을 수행하여 game_account_id 기준으로 데이터를 매칭.
4. WHERE first_login_date < date2 조건을 추가하여 첫 접속일보다 최근 결제일이 큰 경우만 필터링.
5. DATEDIFF(date2, first_login_date)를 사용하여 diffdate(이용 기간)를 계산.
6. ROUND(AVG(diffdate))를 사용하여 서버별 평균 이용 일수를 정수로 변환.
7. WHERE diffdate >= 10을 추가하여 이용 기간이 10일 이상인 경우만 선택.
8. ORDER BY serverno DESC를 사용하여 서버번호 기준 내림차순 정렬.

---

정리

오늘 학습한 SQL 문제들은 JOIN 활용, 집계 함수 사용, 조건별 필터링, 서브쿼리 활용 등의 개념을 포함했다.

학습 포인트

1. LEFT JOIN과 INNER JOIN의 차이점

   • LEFT JOIN: 모든 데이터를 유지하면서 조인된 데이터가 없으면 NULL 반환.
   • INNER JOIN: 양쪽 테이블에서 일치하는 데이터만 유지.

2. CASE WHEN을 사용한 데이터 그룹화

   • CASE 문을 활용하여 결제 유저와 비결제 유저를 나눔.

3. 집계 함수 활용 (COUNT(DISTINCT), SUM(), AVG())

   • COUNT(DISTINCT game_actor_id): 중복 없이 캐릭터 수 세기.
   • SUM(pay_amount): 유저별 총 결제 금액 계산.
   • AVG(diffdate): 평균 이용 기간 계산 (반올림 처리).

4. 서브쿼리를 활용한 HAVING 대체 방법

   • HAVING 없이 WHERE 절에서 actor_cnt >= 2을 필터링.

5. DATEDIFF()를 활용한 날짜 차이 계산

   • DATEDIFF(date2, first_login_date) AS diffdate를 사용하여 이용 기간을 계산.

6. 결과 정렬 (ORDER BY)

   • sumamount 기준 내림차순 (ORDER BY sumamount DESC).
   • serverno 기준 내림차순 (ORDER BY serverno DESC).