8일차

Suhyeon Lee·2024년 10월 11일
0

TimeTable

CodeKata

SQL

47. 모든 레코드 조회하기

  • 작성한 쿼리
SELECT
  *
FROM
  animal_ins
ORDER BY
  animal_id
;
  • 모든 컬럼을 직접 명시해도 됨
SELECT
  animal_id
  , animal_type
  , datetime
  , intake_condition
  , name
  , sex_upon_intake
FROM
  animal_ins
ORDER BY
  animal_id
;

48. 즐겨찾기가 가장 많은 식당 정보 출력하기

-작성한 쿼리: 중첩(일반) 서브쿼리

SELECT
  food_type
  , rest_id
  , rest_name
  , favorites
FROM
  rest_info
WHERE
  (food_type
  , favorites) IN (
  SELECT
    food_type
    , MAX(favorites) AS favorites
  FROM
    rest_info
  GROUP BY
    food_type
  )
ORDER BY
  food_type DESC
;

'Operand should contain 1 column(s)' ERROR

  • 피연산자는 1개의 열을 포함해야 한다는 뚯
  • WHERE절에서 아래처럼 IN 안에 서브쿼리를 넣었는데 해당 오류가 떴음
/*전략*/
 WHERE favorites IN (
   SELECT
     food_type
     , MAX(favorites)
   FROM
     rest_info
   GROUP BY
     food_type
   )
 /*후략*/

→ 'IN' 안에서의 SELECT문은 단일컬럼을 반환해야 하므로 WHERE 절에서 조건을 건 컬럼 만큼 SELECT를 해야 한다고 함!
→ WHERE 절에서 서브쿼리를 사용할 시 조건을 건 컬럼을 SELECT절에 그대로 넣자

  • GROUP BY 없이도 가능
SELECT
  food_type
  , rest_id
  , rest_name
  , favorites
FROM
  rest_info ri
WHERE
  (food_type
  , favorites) IN (
  SELECT
    food_type
    , MAX(favorites) AS favorites
  FROM
    rest_info max 
  WHERE ri.food_type = max.food_type
  )
ORDER BY
  food_type DESC
;

다른 풀이

  1. 인라인 뷰 서브쿼리
SELECT
  ri.food_type
  , ri.rest_id
  , ri.rest_name
  , ri.favorites
FROM
  rest_info ri
  JOIN (
    SELECT
      food_type
      , MAX(favorites) AS favorites
    FROM
      rest_info
    GROUP BY
      food_type
    ) AS mf
    ON ri.food_type = mf.food_type
    AND ri.favorites = mf.favorites
ORDER BY
  food_type DESC
;

ON과 USING

  • USING 은 INNER JOIN 경우에 권장
    • USING 은 뒤에 추가적으로 AND 조건을 붙일 수 없어 OUTER JOIN 시에 주의해야 함
  1. WITH절을 이용한 CTE(Common Table Expression)
WITH cte AS (
  SELECT
    food_type
    , MAX(favorites) AS favorites
  FROM
    rest_info
  GROUP BY
    food_type
)
SELECT
  ri.food_type
  , rest_id
  , rest_name
  , ri.favorites
FROM
  rest_info ri
  JOIN cte c
  ON ri.food_type = c.food_type
  AND ri.favorites = c.favorites
ORDER BY
  ri.food_type DESC
;
  • CTE를 WHERE절에서 서브쿼리로 활용해도 됨

    추가로 알아두면 좋은 내용

  • 2개 이상의 CTE를 작성할 땐 WITH는 첫 번째 CTE 앞에 딱 1번만 적으면 됨
  • 여러 개의 CTE는 콤마로 구분
  1. WINDOW 함수, PARTITION BY
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM(
SELECT
FOOD_TYPE, REST_ID, REST_NAME, FAVORITES,
ROW_NUMBER() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC ) AS ROW_NUM
FROM REST_INFO) AS T
WHERE ROW_NUM = 1
ORDER BY FOOD_TYPE DESC
;
  • WINDOW 함수(순위) 활용

    • RANK() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) AS 별칭
      • PARTITION BY: 행을 그룹화하는 데 사용
      • ORDER BY: 행의 순위를 결정
        → 동점에 같은 순위 부여 & 동점자 수 만큼 순위 밀림
    • DENSE_RANK() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) AS 별칭
      → 동점에 같은 순위 부여 & 같은 순위 부여 후 +1
    • ROW_NUMBER() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) AS 별칭
      → 동점에 같은 순위 부여하지 않음(무조건 하나씩 증가)
  • 참고하면 좋을 내용

    • "MAX(),MIN(),SUM() 같은 집계 함수를 사용할 경우 SELECT에 집계에 필요한 컬럼만 존재하거나 그게 아니라면 최대값을 계산하기 위해 비교해야하는 값들로 별도의 서브 쿼리를 작성해야 합니다."
    • GROUP BY에 정의한 컬럼만 SELECT절에서 그대로 사용할 수 있고, 정의되지 않은 컬럼의 경우에는 반드시 집계 함수로 처리를 해줘야 한다.
      • 그냥 food_type으로 group by를 하면 각 음식 종류의 최대값만 가지고 오지 않고 맨 처음에 존재하는 음식 종류의 첫 행만을 가지고 옵니다. select * from rest_info로 테이블을 보시고 결과랑 비교해보세요.
      • MAX 함수가 앞의 3개 FOOD_TYPE, REST_ID,REST_NAME 컬럼과 나란한 행을 불러오기 위해선 그룹이 3개 다 지정되어야 합니다. 그러나 그렇게 해서는 FOOD TYPE을 기준으로 한 값을 불러 올 수 없습니다.
    • HAVING은 GROUP BY 이후 행에 대한 필터링을 진행한다. 즉, 집계된 조건을 기반으로 필터링하기 위한 조건이 필요하다. ('HAVING 조건식'의 형태)
    • RANK()는 행마다 순위를 매겨야 하므로 부하가 걸려 대용량 데이터에서는 성능적으로 부족한 부분이 있으니 GROUP BY와 MAX() 활용하는 걸 추천

49. 식품분류별 가장 비싼 식품의 정보 조회하기

  • 작성한 쿼리
SELECT
  category
  , price
  , product_name
FROM
  food_product
WHERE 
  (category
  , price) IN (
    SELECT
      category,
      MAX(price) AS price
    FROM
      food_product
    WHERE
      category IN ('과자', '국', '김치', '식용유')
    GROUP BY
      category
    )
ORDER BY
  price DESC
;

다른 풀이

  • WITH 활용
WITH CATEGORY_MAX AS (
    SELECT CATEGORY, MAX(PRICE) MAX_PRICE
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
)
SELECT P.CATEGORY, M.MAX_PRICE,	P.PRODUCT_NAME
FROM FOOD_PRODUCT P JOIN CATEGORY_MAX M 
ON P.CATEGORY = M.CATEGORY AND P.PRICE = M.MAX_PRICE
WHERE P.CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY 2 DESC
;
  • 중첩 서브쿼리(GROUP BY 미사용)
SELECT
  CATEGORY
  , PRICE
  , PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE 1=1 
  AND CATEGORY IN ('과자', '국', '김치', '식용유')
  AND PRICE = ( 
    SELECT 
      MAX(PRICE)
    FROM 
      FOOD_PRODUCT AS F 
    WHERE F.CATEGORY = FOOD_PRODUCT.CATEGORY
  )
ORDER BY 
  PRICE DESC
;
/* 서브쿼리에서 현재 카테고리와 일치하는 행만 고려해야 하기 때문에
F.CATEGORY = FOOD_PRODUCT.CATEGORY를 사용하여 
현재 검토 중인 카테고리에 속하는 행들만 서브쿼리에서 고려함 */
  • JOIN
SELECT
  f2.category
  , max_price
  , product_name
FROM
  food_product f1
  JOIN (
    SELECT
      category
      , MAX(price) AS max_price
    FROM
      food_product
    GROUP BY
      category
  ) AS f2
  ON f1.category = f2.category
WHERE
  f1.price = f2.max_price
  AND f2.category IN ('과자', '국', '김치', '식용유')
ORDER BY
  max_price DESC
;
  • 윈도우 함수 사용
SELECT
    CATEGORY
    , PRICE
    , PRODUCT_NAME
FROM
    (
    SELECT
        *
        , RANK() OVER(PARTITION BY CATEGORY ORDER BY PRICE DESC) RNK
    FROM
        FOOD_PRODUCT
    WHERE
        CATEGORY IN ('과자', '국', '김치', '식용유')
    ) A
WHERE
    RNK = 1
ORDER BY
    PRICE DESC
;

python

7. 두 수의 나눗셈

  • 작성한 코드
solution = lambda num1, num2 : int (num1 / num2 * 1000)

→ int로 형변환 하는 이유는 소수점 아래를 제거하기 위함
1000 * num1 // num2으로 식을 작성하면 처음부터 몫만 계산하기 때문에 형변환 없이도 값을 얻을 수 있음!
(미미한 차이이지만 int에 비해 처리 시간도 빠르다고 함)

참고할 만한 내용: 지역변수 answer의 활용에 대하여

-- 1번
def solution(num1, num2):
    return int(num1 / num2 * 1000)

-- 2번
def solution(num1, num2):
    answer = int(num1 / num2 * 1000)
    return answer

Q. 1번 코드에선 지역변수 answer를 활용하지 않았는데요, 혹시 지역변수가 없으면 좋은 점이 있을까요?
A. 변수를 저장하기 위해선 비용이 듭니다. 비용이 늘면 시스템 성능의 저하가 올 수 있습니다. 또한 함수화 된 코드는 굳이 변수에 담지 않더라도 return 값으로 주면, 차후에 x = solution(someting) 같은 형태로 불러와서 사용이 가능하니, 재사용이 없는 함수 내 지역변수는 굳이 변수에 담지 않는 것을 추천합니다.

ADsP 강의

4주차

    1. 분석 마스터플랜 수립 프레임워크
    1. 분석 거버넌스 체계 수립

TIP

리차드 튜터님

SQL 쿼리에 HAVING절이 있는 경우 SELECT절에 집계 함수(Aggregated Function)이 반드시 포함되어야 할까요?

  • SQL에서 HAVING 절을 사용하는 경우
    • 일반적으로 집계가 수행된 후에 적용되며 집계 함수(예: COUNT(), SUM(), AVG() 등)를 기반으로 GROUP BY 작업의 결과를 필터링하는 데 사용됩니다.
  • 따라서 집계 함수의 결과를 필터링하기 위해 HAVING 절을 사용하는 경우 해당 집계 함수도 SELECT 절에 포함되어야 합니다.
SELECT department, COUNT(employee_id) 
FROM employees 
GROUP BY department 
HAVING COUNT(employee_id) > 5;
  • 단, 예외가 있습니다. 최종 출력에 표시할 필요가 없는 집계 값을 기준으로 필터링할 때는 SELECT 절에 집계 함수를 포함하지 않고 HAVING 절을 사용할 수 있습니다.
SELECT department 
FROM employees 
GROUP BY department 
HAVING COUNT(employee_id) > 5;
  • 따라서 SELECT 절과 HAVING 절 모두에 집계 함수를 포함하는 것이 일반적이지만, 집계 결과를 표시하지 않고 집계 결과만 기준으로 필터링해야 하는 경우에는 반드시 필요한 것은 아닙니다.

팀 과제 발표회

  • 2조: DIVVY 모빌리티 서비스 분석 ★
    • DIVVY에 대한 설명이 있어 좋았음
      • 배경 설명
      • 문제점: 잦은 비용 인상 정책, 시스템 개선, 안전 문제
    • 사용자 경험 만족도 향상을 목적으로 데이터 분석
        1. 시스템 개선
          → 데이터에 NULL값이 너무 많아 당황스러웠다
        1. 안전 문제
          → 자전거 속력을 얻기 위해 데이터 활용
          : 출발 위도, 경도와 도착 위도, 경도의 차이값을 실제 거리로 변환
          → 새벽 시간 이용객의 속력이 너무 높다는 것을 확인
          → 새벽 4시 ~ 6시 이용자에게 알림이나 메시지 보내기
          → 기본 요금제의 요금 책정 방식을 바꾸기: 시간비례 지불 방식이 과속을 부를 수 있으니 요금제 개선으로 안전 문제를 해결할 수 있을 것
  • 1조: 이커머스 데이터
    • 선택 이유: 조에 이커머스 도메인 관심 있는 사람이 많았음
    • 분석 방법 설명해 줘서 좋았음
      • 이벤트별로 나눠서 제품 구매액 합계와 평균을 일별, 시간별, 요일별로 분석
    • 표준편차 확인하는 건 생각 못 해본 부분이라 도움이 되었음
  • 10조: 이커머스 데이터
    • "구매전환율" 증대 인사이트
      • 전체 구매 전환율
    • 퍼널 분석
      • 고객이 유입, 전환에 이르기까지의 주요 단계를 수치로 확인
      • view → cart → purchase
    • 저가 상품의 view → cart 전환율 높음
    • 전환에 소요되는 시간도 중요한 분석 요소가 됨!
      • 구매를 망설였다는 뜻으로 받아들일 수 있음
    • "심리적 저항감": 고가 제품을 구매하겠다고 마음먹으면 그 이후는 쉬움.
    • 저가제품은 카트에는 쉽게 넣지만 실제 구매까지는 시간이 오래 걸림
  • 11조 ★★
    • 장바구니 고객 push알림
    • 충성 고객 쿠폰 및 멤버십 알림 발송
    • 브랜드 할인 기획전
    • 유저벌 선호 브랜드 분석
    • 요일별, 시간대별 분석
  • 13조 ★★★
    • CVR, CTR
    • 브랜드별 이벤트 전환율
    • 가격대별 이벤트 전환율
      • view를 거치지 않고 바로 cart로 진행된 경우에 대한 처리를 고민한 부분이 인상깊음 → 검색해서 바로 장바구니에 넣을 수 있지 않나?
    • 이커머스 사이트 정체까지 조사해서 놀랍다
      • 네일아트 관련 뷰티 업체라고 함(러시아 소재)
  • 6조
    • 브랜드 별 카트 제거율 확인이 인상깊었음
    • 장바구니 제거 이벤트를 중심으로 가격대별, 브랜드별, 시간별로 분석한 게 좋은 접근이라고 생각이 들었음
    • 사용자수 대비 구매 취소 많은 브랜드, 가격 경쟁력, 월요일 출퇴근 시간
  • 3조
    • 5개월 데이터를 모두 사용하신 점이 포인트
    • 블랙 프라이데이
    • view에서 cart로 넘어가는 시간 대비 매출 전환율
    • 수면시간 분석 측면이 WOW point
  • 7조
    • 효과적인 노출 전략 세우기
    • 결측치 언급이 좋았음
      • NULL로 인식되지 않은 빈칸이 있는데 해당 데이터 확인하고 지우는 과정을 보여줘 좋았음
      • 파이썬을 쓰셨다...! 대단
    • 구매 전환율, 주 구매시간 → 광고비 대비 노출 최적화
  • 12조
    • 이커머스 플랫폼에 취업한 신입 데이터 분석가의 입장에서 시작하는 점이 포인트
    • CVR 중점으로 분석
      • 구매까지 이어지도록 어떻게 할 것인지
    • 구매 전환율이 30% 이하인 고객을 대상으로 ab테스트를 하는 걸 제안
      • 타켓고객 절반으로 나눠 한쪽만 매일 10시 장바구니 알람 보내기
      • 결과는 3,4월 결과로 파악할 수 있을 것
    • 최우수 브랜드
  • 4조: 시카고 공유 자전거 분석
    • 데이터 탐색(meta data)
      : 자전거 유형별, 회원별 이용 현황
      • 자전거 종류 3가지 중 오류가 있는 docked_bike 제거 이유 설명 좋았음
    • 장소 관련: 자전거 출발역 & 도착역
      • 가장 많이 하차, 탑승한 곳 → 관광명소, 해변
      • 출발역, 도착역 조합: 시작과 끝 같은 경우, 시작-끝 & 끝 시작
    • 위도, 경도를 이용한 거리 → 피타고라스 정리를 쓰셨다고 함
    • 시간 관련
      • 월간 이용량
      • 평균이용시간 → 날씨의 영향, 전기자전거 vs 클래식자전거
    • 추가 가설 및 분석 제안 부분이 인상 깊음
      • 외지인, 관광객 정보
  • 5조: 이커머스 → runail사에 타임 프로모션 제안
    • 접근 방식이 다른 조와 달라서 눈에 띄고 좋았음
    • 해당 회사의 경우 관심은 보이지만 직접적인 결제로 이어지지 않은 데이터가 많아 사측에 현상 개선을 제안한다는 접근 방식이 매우 흥미로움
    • 어떤 프로모션이 좋을까?
      • 시간
      • 어떤 제품(카테고리 ID)
    • 10시, 18시에 특정 제품(52번, 154번, 315번) 프로모션 진행을 제안
  • 8조: 의료비 데이터
    • 흡연이 가장 큰 영향을 미친다
    • 비만과 의료비 / 나이와 의료비 / 흡연 여부 / 부양가족 수
      • 부양가족 수는 유의미한 차이가 없어 이후 분석에서 제외
    • 흡연과 비만이 문제다...
  • 14조
    • 이벤트별 분석을 통한 구매전환율
    • 구매전환율과 객단가 → 음의 상관관계
    • 전환소요시간
      • 고민시간(장바구니 → 구매 걸린 시간)

팀 과제를 안일하게 한 나... 반성해야 할 점이 너무 많다...

  • 왜 당연하게 미국일 거라고 생각했을까?
  • 앞으로는 분석을 진행하며 섣부른 추측은 하지 않는 게 좋겠다
  • 선입견 가지고 시작하지 않기!
    • 당연하다고 생각하지 말고 정확하게 알아본 뒤에 진행하기~

튜터님 코멘트

  1. 결측치 처리
  2. 개념적인 부분을 놓치지 않아야 함
  • 최빈값 대체, 최댓값 대체 → 카테고리에는 적용할 수 없으니 주의
  1. 평균의 위험성을 항상 인지
  • "중앙값"을 활용해요: 관측값 및 분포를 확인할 수 있음
  1. WITH 구문에 대하여
  • JOIN, DISTINCT는 연산이 많이 걸리니까 WITH문에 메모리에 올려두고 필요할 때 사용하면 연산 소모를 줄일 수 있어 좋음
  1. 결측치 한 번 더
  • 데이터 유실, 똑같은 게 두 번 찍히는(중복) 경우가 현업에는 많아서 주의가 필요

데이터의 탐색적 분석, EDA (Exploratory Data Analysis)는 거시적으로 ‘우리가 사용할 데이터는 어떻게 생겼는지’를 면밀히 파악하는 과정입니다. 크게 결측치처리, 이상치처리, 시각화로 구성됩니다. 이는 SQL 에서 NVL, TRIM, IFNULL등 함수사용을 통해 진행해볼 수 있습니다. 통계적 기법, 추가 핸들링을 위해 주로 Python 라이브러리를 활용하게 됩니다.
데이터분석은 물론 숫자를 기반으로 해석되고, 통계적 기법, 머신러닝 등이 활용되지만 개념적인 의미를 놓치지 말아야 합니다. (예를들어 카테고리 값이 비어있을 때, 이를 최빈값대체를 할 수 없다 등)
평균을 대부분 잘 구해주셨으나, 우리는 평균이 가지는 위험성을 인지해야 합니다. 이를테면, 10점, 10점, 10점, 10점, 100점이 있다고 가정해보겠습니다. 이렇게 합계를 구하게 되면 140/5 = 28점이 평균으로 계산되는데, 대부분의 학생들은 10점으로 분포되어 있습니다. 이처럼 평균은 관측값으로 주요 활용되지만, 그 분포를 파악하는 데에 한계점이 있습니다.
MySQL 에서는 PERCENT_RANK 라는 함수를 통해 중앙값이라는 개념을 구할수 있는데, 중앙값이란 데이터를 크기에 따라 나열했을 때, 가장 중앙에 있는 수를 의미합니다. 즉 10점이 됩니다. 해당 내용은 차주에 학습할 예정으로, 참고해주시면 좋을 것 같습니다.
With 구문은 하나의 테이블을 여러 번 조회할 때 사용됩니다. JOIN, DISTINCT 등의 FUNCTION은 다소 많은 연산량을 차지하게 됩니다. 우리가 자주 사용하는 테이블을 임시로 저장해 두고(메모리에 올려두고), 필요한 경우에 따라 데이터를 가져올수 있게 된다는 점을 기억해주시면 좋겠습니다.
결측치에 대한 부분을 많이 다뤄주셨는데, 실제로 데이터 수집 과정에서 미리 data pipeline 을 설계하고 수집하기 때문에 추가적인 업데이트가 어려운 것이 현실입니다. 데이터가 중복적재 되거나, 유실되는 경우는 꽤 많다고 첨언드리겠습니다.
SQL 문법은 대단히 어렵지는 않습니다. 다만, 원하는 데이터를 추출하기 위해 query 를 정확하게 작성하는 것이 중요합니다.
SQL 은 분명 데이터분석가의 가장 기본적인 스킬입니다. QUERY 라는 질의어 중 가장 대중화 되어있죠. 대부분의 데이터분석가 코딩테스트는 sql로 진행됩니다. 코드카타를 9-10시에 꼭 풀어주시길 바랍니다.
코딩을 위해 VisualStudio Code 를 추천드립니다. 탭을 통해 sql을 구조적으로 작성하시는 데 도움이 될 것 같습니다.

profile
2 B R 0 2 B

0개의 댓글