팀스터디

기초분석

• 마케팅을 잘 몰라서 찾아봤는데, 오늘 나온 얘기들이 결론적으로는 ‘사용자 행동분석’으로 귀결됨 → 목표는 경험개선
• 우리가 가지고 있는 정보로 확인할 수 있는 부분은 다음과 같음
  • 사용자 경로 : 사용자가 처음 플랫폼에 들어와서 행동을 완료하거나 이탈할 때까지의 흐름.
  • 이탈 지점 : 용자가 특정 지점에서 행동을 중단하는 단계
• 데이터 양이 워낙 방대해서 큰 틀에서 사용자 행동분석을 잡으면 되지 않겠나 생각함

참고자료 - 사용자 행동 분석방법 7가지
참고자료2 - 이커머스 데이터 분석 유형 5가지

• 결론적으로는 목표를 [vip 사용자 이탈 막기] 를 주제로 잡고 각자 요리조리 데이터 만져와보기로 함

• vip의 기준도 모호한 것 같아서 기준도 생각해보기로 했다.

개인스터디

SQLD 자격증 챌린지 1주차

SQL & SQLD란?

SQLD 자격증 이해하기

• 1과목 : 데이터 모델링의 이해

  • 데이터 모델링의 이해
  • 데이터 모델과 성능

• 2과목 : SQL 기본 및 활용

  • SQL 기본
  • SQL 활용
  • 관리 구문

• 합격 기준 : 총 60점 이상

• 과락 기준 : 과목별 40% 미만

• 취득 후 1.5년 지난 시점에 보수 교육(온라인 강의) 수강 시 영구 취득 전환

• ORACLE(+ SQL SERVER) 기반 출제

데이터자격검정 홈페이지

SQL의 정의

SQL(Structured Query Language)은 데이터베이스를 직접적으로 액세스할 수 있는 언어

• 구성
  • 데이터 정의 (Data Definition)
  • 데이터 조작 (Data Manipulation)
  • 조작 결과를 조작하거나 취소 (Transaction Control)
  • 접근 권한 제어 (Data Control)

데이터와 데이터베이스

데이터

• 데이터는 정보
  • 저장이나 처리에 효율적인 형태로 변환된 정보
    → '잘' 저장하고 관리하는 기술이 필요

데이터베이스와 DBMS

• 데이터베이스 : 잘 조직화된 데이터의 모음
• DBMS : 이렇게 모아놓은 데이터들을 관리하는 프로그램

각각의 DBMS마다 사용하는 SQL이 조금씩 다름
큰 골자는 비슷하나 디테일에서의 차이
표준 SQL을 준수 + 커스텀

예제로 익히는 SQL 3회차

숙제 피드백

🔗2회차 숙제

문제1 - 조건절 where 구문의 활용

select game_account_id, game_actor_id, serverno
from basic.users a 
where first_login_date >'2023-01-01'

문제2 - 조건절 where 구문의 응용

내가 작성한 코드

SELECT first_login_date,
	   ip_addr,
	   exp,
	   zone_id
FROM users u 
WHERE `level` > 10
AND (serverno != 1)
AND (etc_str2 = '시즌패스' OR etc_str2 = '레벨업 패키지')
AND (etc_str1 = '상점에서 구매')
ORDER BY first_login_date DESC
;

정답코드

select first_login_date, ip_addr, exp, zone_id
from basic.users 
where (level>10)
	and (serverno!=1)
	and (etc_str2 in ('레벨업 패키지', '시즌패스'))
	and (etc_str1='상점에서 구매')
order by first_login_date, ip_addr desc
;

IN을 기억하자!✅✅✅

문제3 - 조건절 case when 구문의 활용

내가 작성한 코드

SELECT game_actor_id,
	   `level`,
	   CASE WHEN `level` BETWEEN 1 AND 10 THEN '1~10Lv 이하'
	   	    WHEN `level` BETWEEN 11 AND 20 THEN '11~20Lv 이하'
	   	    WHEN `level` BETWEEN 21 AND 30 THEN '21~30Lv 이하'
	   	    WHEN `level` BETWEEN 31 AND 40 THEN '31~40Lv 이하'
	   	    WHEN `level` BETWEEN 41 AND 50 THEN '41~50Lv 이하'
	   	    WHEN `level` BETWEEN 51 AND 60 THEN '51~60Lv 이하'
	   	    WHEN `level` BETWEEN 61 AND 70 THEN '61~70Lv 이하'
	   	    WHEN `level` BETWEEN 71 AND 80 THEN '71~80Lv 이하'
	   	    WHEN `level` BETWEEN 81 AND 90 THEN '81~90Lv 이하'
	   	    ELSE '91~100Lv'
	   	    END 'levelgroup',
	  first_login_date 
FROM users
ORDER BY first_login_date DESC 
;

정답코드 1

select game_actor_id, level,
case when level <=10 then '1~10Lv 이하'
	 when level <=20 then '11~20Lv 이하'
	 when level <=30 then '21~30Lv 이하'
	 when level <=40 then '31~40Lv 이하'
	 when level <=50 then '41~50Lv 이하'
	 when level <=60 then '51~60Lv 이하'
	 when level <=70 then '61~70Lv 이하'
	 when level <=80 then '71~80Lv 이하'
	 when level <=90 then '81~90Lv 이하'
	 else '91~100Lv' end as levelgroup, first_login_date
from basic.users 
order by first_login_date desc 

CASE WHEN 절에서 먼저 걸린 조건1level <= 10을 먼저 필터링 하고 조건2 level <=20으로 넘어간다는 걸 한번 더 짚어주셨음

BETWEEN을 사용해도 괜찮으나 굳이 복잡해질 필요가 없음!✅✅✅

+) level 자동으로 변환이 돼서 일종의 예약어인지 여쭤봤었는데, 좋은 질문이라고 해주셨다! 😎 낄낄

SQL 집계함수 : COUNT, MAX, MIN, SUM, AVG

집계함수	기능
COUNT	테이블의 행 수 반환
SUM	테이블의 열 합계 반환
AVG	테이블의 열 평균 반환
MIN	테이블의 열 최소값 반환
MAX	테이블의 열 최대값 반환

#1. 전체 데이터 갯수 세기
select count(*)as cnt 
from basic.theglory 

#2. 전체 데이터 평균 나이 구하기
select avg(나이)as avg_age 
from basic.theglory 

#3. 전체 데이터 최대 나이 구하기
select max(나이)as max_age 
from basic.theglory 

#4. 전체 데이터 최소 나이 구하기
select min(나이)as max_age 
from basic.theglory 

#5. 전체 데이터 나이합계 구하기
select sum(나이)as sum_age 
from basic.theglory 

#6. 여러 집계함수의 사용
select count(*)as cnt
, avg(나이)as avg_age 
, max(나이)as max_age
, min(나이)as min_age
, sum(나이)as sum_age
from basic.theglory 

SQL 그룹화 : GROUP BY, HAVING

GROUP BY

집계함수에 그룹(기준)이 더해진 개념
특정 컬럼을 기준으로 데이터를 요약해서 비교하고 싶을때 사용

# 집계함수와 GROUP BY 절을 함께 사용하는 SQL문
select 기준컬럼, 집계함수(조건컬럼을 기준으로 여러개의 집계함수 동시사용 가능)   
from 테이블명
where 조건 #(생략가능)
group by 기준컬럼
;

# 주의)기준컬럼과 집계함수를 작성하고 GROUP BY 절을 사용하지 않을 때 에러가 발생하는 예
select 기준컬럼, 집계함수(조건컬럼을 기준으로 여러개의 집계함수 동시사용 가능)   
from 테이블명
where 조건 #(생략가능)
;

1️⃣SELECT 뒤 기준컬럼(나라, 성별, 레벨..등등) 작성
2️⃣ 집계함수(COUNT, MAX, MIN, AVG, SUM) 작성
3️⃣ WHERE 절 뒤 GROUP BY 기준컬럼 작성 (WHERE 절은 생략 가능합니다.)

주의할 점
1️⃣, 2️⃣번 작성 후, 3️⃣번을 작성하지 않을 경우 에러가 발생됩니다.
그 이유는, 데이터를 SELECT할 때, 2️⃣번 집계함수는 여러 데이터로부터 하나의 행을 반환하지만, 1️⃣번 기준컬럼은 N 개의 값을 반환하기 때문에, 3️⃣번 GROUP BY 절을 사용하여 기준컬럼 당 값 1개를 반환 할 수 있도록 명시해야 합니다.
✨참고) 작동순서: FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

HAVING

WHERE절은 GROUP BY전 데이터 기준으로 필터링
HAVING절은 GROUP BY후 결과값을 기준으로 필터링

# 집계함수, GROUP BY절, HAVING을 함께 사용하는 SQL문

select 기준컬럼, 집계함수(조건컬럼을 기준으로 여러개의 집계함수 동시사용 가능) 
from 테이블명
where 조건 #(생략가능) 1️⃣
group by 기준컬럼 2️⃣
having 조건식 3️⃣
;

SQL : SUB QUERY ✅

컬럼들을 활용한 많은 연산(사칙, 집계, 윈도우 함수, 포맷팅)을 하는 경우, SUB QUERY 는 이를 순차적(구조적)으로 기록하는 역할을 수행

• 서브쿼리(안쪽에 위치한 쿼리) → 메인쿼리(바깥쪽에 위치한 쿼리) 순으로 실행

• () 안에 SELECT FROM 반드시 명시

• 서브쿼리 마지막에 ; 기호 사용 불가

• ORDER BY 사용 불가

예시

✅ 보라색 괄호 → 노란색 괄호 순으로 처리

SUB QUERY의 종류

• 중첩 쿼리
  • WHERE절에서 사용
  • 서브쿼리의 결과에 따라 달라지는 조건절
• 스칼라 서브쿼리
  • SELECT 절에서 사용
  • 하나의 컬럼처럼 사용
• 인라인 뷰 가장 많이 사용✅
  • FROM 절에서 사용
  • 하나의 테이블처럼 사용
  • AS 구문을 사용하여 명칭을 반드시 기재해야 함

숙제

문제 1 - 집계함수의 활용
조건1) 서버별, 월별 게임계정id 수를 중복값 없이 추출해주세요. 월은 첫 접속일자를 기준으로 계산해주세요. 월은 yyyy-mm의 형태로 추출해주세요.

SELECT SUBSTR(first_login_date, 1, 7) AS login_month,
	   serverno,
	   count(DISTINCT game_account_id) AS cnt_account
FROM basic.users
GROUP BY SUBSTR(first_login_date, 1, 7),
	     serverno;

문제 2 - 집계함수와 조건절의 활용
조건1) group by 를 활용하여 first_login_date별 게임캐릭터수를 중복값 없이 구하고,
조건2) having 절을 사용하여 그 값이 10개를 초과하는 경우의 첫 접속일자 및 게임캐릭터id 개수를 추출해주세요.

SELECT first_login_date,
       COUNT(DISTINCT game_actor_id) AS cnt_actor_id
FROM basic.users
GROUP BY first_login_date
HAVING cnt_actor_id > 10;

문제 3 - 집계함수와 조건절의 활용2
조건1) group by 절을 사용하여 서버별, 유저구분(기존/신규) 게임캐릭터id수를 구해주세요. 중복값을 허용하지 않는 고유한 갯수로 추출해주세요.
조건2) 기존/신규 기준→ 첫 접속일자가 2024-01-01 보다 작으면(미만) 기존유저, 그렇지 않은 경우 신규유저
조건3) 또한, 서버별 평균레벨을 함께 추출해주세요.

SELECT serverno,
	   CASE WHEN first_login_date < '2024-01-01' THEN '기존유저'
	        ELSE '신규유저'
	        END AS 'usergroup',
	   COUNT(DISTINCT game_actor_id) AS cnt_actor_id,
	   AVG(`level`) AS avg_level
FROM basic.users
GROUP BY serverno, usergroup;

문제 4 - SubQuery의 활용
2번 문제를 having 이 아닌 인라인 뷰 subquery 를 사용하여, 추출해주세요.
조건1) 문제2번을 having 이 아닌 인라인 뷰 서브쿼리를 사용하여 추출해주세요.

SELECT first_login_date, 
	   cnt_actor_id
FROM
(
SELECT first_login_date,
	   COUNT(DISTINCT game_actor_id) AS cnt_actor_id
FROM basic.users
GROUP BY first_login_date
) AS subquery
WHERE cnt_actor_id > 10ㅔ

문제 5 - SubQuery의 응용
조건1) 레벨이 30 이상인 캐릭터를 기준으로, 게임계정 별 캐릭터 수를 중복값 없이 추출해주세요.
조건2) having 구문을 사용하여 캐릭터 수가 2 이상인 게임계정만 추출해주세요.
조건3) 인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임계정 개수를 중복값 없이 추출해주세요.

SELECT cnt_actor_id, 
	   COUNT(DISTINCT game_account_id) AS cnt_account_id
FROM
(
SELECT game_account_id, 
	   COUNT(DISTINCT game_actor_id) AS cnt_actor_id
FROM basic.users
WHERE `level` >= 30
GROUP BY game_account_id
HAVING cnt_actor_id >= 2
)subquery
GROUP BY cnt_actor_id

데일리퀘스트

SQL - 최솟값 구하기
SQL - 어린 동물 찾기
Python - 짝수의 합
Python - 배열의 평균값

---

일기

• ADSP 8주차 복습❌
  시험 치고 왔다고 이렇게 다시 보기 싫을 일이야?
  단순 보기 싫은 게 전부는 아니지만 흑흑
  기초분석 관련 자료 넣고 이것저것 찾아보느라 시간을 많이 보내버렸다 내일은 꼭!

• SQLD 1주차✅
  이미 SQL 코드카타도 하고 있고 이론적인 부분은 크게 신경 쓸 일 없을 거라 생각했는데 이것도 만만치 않은 시험이구나 생각했다 얘는 바로 코앞에 닥쳤을때 하지 말고🤣 지금부터 기출 풀어봐야지! 책 사고 자료 인쇄하기 !

• SQL 코드카타 9-10✅ 숙제제출✅
  SQL이 진짜 나랑 잘 맞나..? 어려운 부분 없었다 아EZ요

• Python 코드카타 9-10✅
  간단한 문젠데 어렵게 생각하지 말 것! EZ ..는 아니고 E..요..

• 기초분석스터디 주제선정참여✅
  조장님이 오늘 안 계셔서 얘기의 방향을 잡는 것이 꽤나 힘들었다 조장의 길은 어렵구나

2주차부터 덜컥 몬가 본격적인 과제가 나온 것 같아서 당황했쥐만..? 어케 보면 바로 실무에 투입될 수 있게 사고력을 길러주는 것 같아서 굉장히 재밌다

DB 테이블 불러오는 것에 좀 애먹었는데 무아튜터님께 가니 바로 해결해주셨다. 쏘 카인드. 감사합니다!

• Interger 타입 컬럼은 길이가 길어지면 Bigint로 바꾸기
• Bigint unsigned는 양수만 취급하는 것
  부호 / 음수 부분 없이 표현되므로 표현 범위가 늘어난다!

이제 여태까지 배운 쿼리를 열심히 요리조리 써먹어볼 차례다😗 내일까지 한번 잘 고민해봐야지!