TIL. (Today I Learned)

Day8. 데이터리터러시/UNION/JOIN / TIL.20240424

체대이터 2024. 4. 24. 21:45

2주차 3일째 TIL 가보입시다~

 

 

To do list.

 

-SQL코드카타

-데이터리터러시 강의 1-2까지

-SQLD 2일차

-SQL 강의 / 과제

 

+시간남으면 파이썬강의 


데이터리터러시 강의 ~1-2

 

1-1 데이터리터러시

 

데이터리터러시란

데이터를 읽는 능력, 이해, 비판적 분석, 의사소통에 활용

수집과 데이터 원천, 활용법, 핵심지표 이해

 

=>데이터 리터러시는 올바른 질문을 던질 수 있도록 만들어줌

 

🚩데이터분석에 대한 착각?

=> 툴만 배우는 것이 정답이 아니다

 

심슨의 역설

-심슨의 패러독스란 '부분'에서 성립한 대소 관계가 그 부분들을 종합한 '전체'에 대해서는

성립하지 않는 모순

 

시각화를 활용한 왜곡

-자료의 표현 방법에 따라서 해석의 오류 여지가 존재

 

샘플링 편향

-전체를 대표하지 못하는 편향된 샘플 선정으로 인해 오류가 발생

 

상관관계와 인과관계

상관관계- 두 변수가 얼마나 상호의존적인지

인과관계-원인과 결과가 명확한 것

 

=> 상관관계는 항상 인과관계가 아닌 것을 항상 유의해야함 / 모두 활용하여 합리적으로 판단

 

데이터 분석 접근법

문제 및 가설 정의(생각) => 데이터 분석(작업) => 결과 해석 및 액션 도출(생각)

 


 

1-2 문제 정의

 

문제정의란?

데이터 분석 프로젝트의 성공을 우한 초석

현상에 대한 명확하고 구체적인 진술

프로젝트의 목표를 설정하고 분석방향을 결정

 

문제정의: 매출을 어떻게 늘릴 수 있을까?

-문제 정의는 했지만 모호함

 

=> 고친 문제정의: 구매전환율이 감소했고 고객층의 전환율을 2%에서 5%로 늘리자

                              어떤 마케팅 전략을 사용해야할까?

 

문제정의 방법론

 

MECE(Mutually Exclusive, Collectively Exhaustive)

문제 해결과 분석에서 널리 사용되는 접근 방식

문제를 상호 배타적이면서 전체적으로 포괄적인 구성요소로 나누는 것

 

로직 트리(Logic Tree)

MECE 원칙을 기반으로 복잡한 문제를 더 작고 관리하기 쉬운 하위 문제로 분해하는데 사용

상위 문제로부터 시작하여 하위 문제로 계층적 접근

일반적으로 도표 형식으로 표현

 

+로직트리(Cheat Sheet)

 

문제정의의 핵심

so what? 그래서 무엇을 말할 것인지

why so? 왜 그렇게 말할 수 있는지

도식화한 로직 트리가 위 법칙에 맞는지 확인해보기

 

문제정의 팁

결과를 공유하고자 하는 사람 누구?

결과를 통해 원하는 변화

경영자의 입장에서 보려고 노력

많은 사람들과 의견공유

혼자서 오래 고민해보는 시간 갖기

 


SQL 강의 / 과제

 

UNION 함수

=> 두 개의 테이블이 수직결합한 형태 / 컬럼과 그 형태가 같아야함 열의갯수, 순서가 모든 쿼리에 동일

 

UNION 중복X

UNION ALL 중복O

 

JOIN 함수

=>두 개의 테이블 합치기

 

공통컬럼 찾기

PK 기본키 FK외래키

 

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN(합집합) - MYSQL에서는 지원 X -> LEFT JOIN UNION RIGHT JOIN 합쳐서 표현

 

 

과제

 

1)case when 구문과 join 함수를 사용하여, users 테이블을 기준으로, 결제를 한 유저와 결제를 하지 않은 유저를 추출해주세요.

 

SELECT

            CASE WHEN p.game_account_id IS NOT NULL THEN '결제함' ELSE '결제안함' END AS gb,

            COUNT(DISTINCT u.game_account_id) AS usercnt

FROM

users u

LEFT JOIN

payment p ON u.game_account_id = p.game_account_id

GROUP BY

CASE WHEN p.game_account_id IS NOT NULL THEN '결제함' ELSE '결제안함' end ;

 

2)

  • 서버번호가 2 이상인 데이터와 결제방식이 CARD 인 경우를 join해 주시고
  • game_account_id 를 기준으로 game_actor_id 갯수를 중복값없이 세어주시고, actorcnt 으로 컬럼명을 명시해주세요.
  • pay_amount 값을 더해주시고, sumamount 으로 컬럼명을 명시해주세요.
  • having 을 사용하지 않고, subquery 사용으로 game_actor_id 갯수가 2 이상인 경우만 추출해주세요. 결과값은 아래와 같아야 합니다. (전체결과 중 일부입니다.)

select *

from

(

select a.game_account_id,

           count(distinct game_actor_id) as actor_cnt,

           sum(pay_amount) as sumamount

from

(

select game_account_id,

          game_actor_id

from basic.users

where serverno>=2 )as a

inner join

(

select game_account_id,

          pay_amount

from basic.payment

where pay_type='CARD' )as b

on a.game_account_id=b.game_account_id

group by a.game_account_id )as a

where actor_cnt>=2

 

3)

  • user 테이블에서 game_account_id, date, serverno 를 추출한 데이터와 매출 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 join 을 진행해주세요.
  • 그 다음, datediff 함수를 사용해 결제일자-접속일자를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.
  • 마지막으로, 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주세요. 다만, 평균 datediff 컬럼은 정수 형태로 출력되어야 합니다. 또한, 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. (전체결과 중 일부입니다.)

select serverno,

          round(avg(diffdate),0)as avgdiffdate

from

(

select a.game_account_id,

          datediff(date_format(date2,('%Y-%m-%d')) ,`date`) as diffdate,

          serverno

from

(

select game_account_id,

          `date`,

           serverno

from basic.users )as a

inner join

(

select game_account_id,

           date2

from

(

select game_account_id,

          max(approved_at)as date2

from basic.payment

group by game_account_id )as b

group by game_account_id )as c

on a.game_account_id=c.game_account_id )as d

where diffdate>=10

group by serverno

order by serverno desc

 

 

 


Check point.

 

 

SQL코드카타 의문

 

SELECT i.ANIMAL_ID,
               i.ANIMAL_TYPE,
               i.NAME
from ANIMAL_INS i join ANIMAL_OUTS o on i.ANIMAL_ID  = o.ANIMAL_ID
where (i.SEX_UPON_INTAKE like 'Intact%')
and (o.SEX_UPON_OUTCOME like 'Neutered%' or o.SEX_UPON_OUTCOME like'Spayed%' )
order by 1

 

=> 코드카타 36번 조인 문제인데 select 부분 ANIMAL_ID 앞에 i. or o.  을 붙여서 찾고자하는 필드값을 명확하게 지정해준다.

=> 두 개의 테이블을 조인할때 필드가 중복으로 있는 경우는 어떤 필드에서 가져와야할지 지정해줘야 하기때문에 앞에 i. 등 명명 해준다

 


 

SELECT BOOK_ID,
               AUTHOR_NAME,
              date_format(PUBLISHED_DATE,'%Y-%m-%d')
from BOOK b join AUTHOR a on b.AUTHOR_ID = a.AUTHOR_ID
where CATEGORY = '경제'
order by PUBLISHED_DATE

 

=> 코드카타 37번 조인문제에서는 selec 부분 BOOK_ID 앞에 b or a 를 왜 안붙여도 되는가>???

     그리고 date_format(PUBLISHED_DATE,'%Y-%m-%d') 형식을 왜 지정하는가? 이미 테이블에서 나온 날짜랑

    답에서 요구하는 날짜형식이 같은데 굳이 지정할 필요가 있는가?

=> 코드실행을 해보자. 그럼 테이블에 있는 날짜는 샘플 데이터이기 때문에 날짜형식이 다를 수도 있어서 코드실행 후 날짜 형식이 구하려는 값과 다를 경우에 데이트 포맷으로 형식을 맞춰준다

 


 

SELECT ORDER_ID,
               PRODUCT_ID,
              DATE_FORMAT(OUT_DATE,'%Y-%m-%d') ,
              CASE WHEN OUT_DATE <='2022-05-01' THEN '출고완료'
              WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
              ELSE '출고미정' END
FROM FOOD_ORDER
ORDER BY 1

 

=>코드카타 38번 문제 DATE_FORMAT(OUT_DATE,'%Y-%m-%d') , 테이블 날짜형식과 정답의 날짜가 예시에 똑같더라도 날짜형식은 지정해줘야 하나보다

=>위와 동일

 


WHERE
GROUP BY 전 데이터 필터링
HAVING
GROUP BY 후 결과값을 가지고 데이터 필터링

 

 having 절이 헷갈려 개념 한번 더 가지고 와봤습니다..

 

예제문제까지 보면서 머리에 기억🙉)

  • 1️⃣ 나이가 31세 이상이고,
  • 2️⃣ 성별을 기준으로 평균 나이를 구하고,
  • 3️⃣ 평균 나이가 41초과인 경우

select 성별, avg(나이)as avg_age 2️⃣

from basic.theglory

where 나이>=311️⃣

group by 성별 2️⃣

having AVG_AGE>41 3️⃣


회고

join과제를 깊이 들어가니 어려움을 느낀다. 힌트도 없어서 뭐 어떡해야할지.. 과제만 하다가 시간을 다 보낸듯;;