본문 바로가기

SQL

 (17)
SQL데이터 분석 KPI 지표 수립 프로젝트 온라인 쇼핑몰 서비스 평가를 위한 KPI(Key Performance Indicator) 지표를 수립할 것이다. 프로젝트 설명 온라인 쇼핑몰은 더 많은 매출과 더 많은 고객수가 증가로 평가 받는다. 그리하여 온라인 몰 서 비스에서는 고객, 상품, 매출 데이터를 분석하여 고객의 이탈을 막으면서 더 많은 매출을 올리기 위 해 현재 서비스가 잘 발전하고 있는지에 대한 분석 지표가 필요하다. SQL 문법을 이용하여 주어진 데이터에서 여러가지 KPI 지표를 출력하는 SQL 쿼리를 작성한다. 서비스의 핵심은 고객과 매출이다. 1) 고객의 등급 구분 : 고객의 서비스 이용 데이터 (총구매금액)을 기준으로 5등급으로 구분하고 1등급에 해당하는 고객의 랭킹, 고객아이디, 이름, 성별, 나이, 주 소, 서비스 이용데이터(..
SQL데이터 분석 KPI 지표 수립 실습 (프로덕트 분석 심화) 이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해 추출할 것이다. 실습1 2020년 7월에 우리 신규유저가 하루 안에 결제로 넘어가는 비율을 구하라 그리고 결제까지 보통 몇분이 소요되는지 구하라 우선 신규 유저 가입일과 최초 구매일을 구해야 한다. 신규 유저 가입일은 created_at 컬럼을 사용하고 최초 구매일은 purchased_at 컬럼에 min 함수를 사용하여 처음 구매한 날을 추출한다. 두개의 컬럼은 다른테이블에 존재하기에 left join을 통해 테이블을 병합하고 on절에 구매일을 가입일+1일 보다 작게 함으로써 하루안에 결제로 넘어가는 비율을 알 수 있다. where절에 7월 조건을 넣고 select 절에 timediff함수를 사용하여 결제까지의 시간을 구..
SQL데이터 분석 KPI 지표 수립 실습 (매출 관련 추가 분석 문제 모음) 이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해 추출할 것이다. 실습1 2020년 7월 일별 매출의 전일 대비 증감폭,증감률을 구해주세요 우선 증감폭을 알기 위해 현재행과 이전행의 비교가 필요하다. 여기서는 lag함수를 이용해 이전 행을 가져온다.2020년 7월의 일별 매출은 with문을 사용하여 임시테이블 처럼 사용할 것이다.이는 옵티마이저가 인라인 뷰나 임시테이블로 판단한다. 우선 date_format을 사용하여 일별 구분을 해준다.sum()으로 revenue를 구하고where문으로 날짜를 구분한다. 여기까지 with문으로 tbl_revenue라는 테이블로 만들고만든 테이블을 이용하여 lag함수로 증가폭과 증감률을 계산한다. 실습2 7월에 일별로 구매 금액 기준으로..
SQL데이터 분석 KPI 지표 수립 실습 (유저 세그먼트별 문제 모음) 이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해 추출할 것이다. 유저들을 나이 또는 성별로 구분짓고 파티션을 나눠서 나타낼 것이다. 실습1 전체 유저의 demographic(인구 통계학적 변동/경향/요인)을 알고 싶다. 성, 연령별로 유저 숫자를 알려주세요. 성별은 하나로 묶고 연령은 5세 단위로 묶고 유저수가 높은 순서로 보여주세요 case when 문을 이용하여 성별과 나이를 나눈다. 성별을 먼저 알아보니 보시다 시피 빈칸이 존재한다. 빈칸을 is null 문으로 구분해봤지만 나오지 않았고 이럴때 사용하는 방법이 length(gender)
SQL데이터 분석 KPI 지표 수립 실습 (날짜,시간 별 분석 문제 모음) 이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해 추출할 것이다. 실습1 2020년 7월의 평균 DAU를 구해주세요 DAU란 하루 동안 해당 서비스를 이용한 순수한 이용자 수를 나타내는 지표 이다. 저번 회차 실습때와 마찬가지로 날짜별 분석을 통해 값을 추출한다. 날자계산을 위해 date_format 함수를 사용하여 날짜를 추출한다. date_format 사용시 format의 유의하여 사용한다. 자료가 많으니 실무자들도 많이 까먹고 구글링하여 검색한다고 한다. 아래는 format 모음이다. from 절 인라인뷰를 사용하여 day당 사용자 수를 파악하고 평균을 낸다다만 해당 자료의 문제로 시간이 9시간 씩 멀어져 있어 9시간을 뺀 후 시간 계산을 하였다강사님의 말로는 여러..
SQL데이터 분석 KPI 지표 수립 실습 (서비스 이해 문제 모음) 이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해 추출할 것이다. 갑자기 실습 난이도가 확 올라간 것을 느낄 수 있는 회차였다. 실습1 2020년 7월의 총 Revenue를 구해주세요 Revenue 란 수익을 뜻하고 7월의 Revenue 이니 7월 수익을 추출해볼 것이다. 수익 데이터를 추출하기 위해 purchase 테이블을 사용한다. 집계함수 sum을 사용하여 총 이익 값을 계산할 것이고 where절에 Date 필터를 입혀 7월의 날짜값을 입력한다. fastcampus 데이터베이스에서 가져왔기 때문에 from 절 입력하는 구매목록 테이블에 붙였다. 다음실습 부터는 use 를 사용하여 간단히 생략하겠다. 실습2 2020년 7월의 MAU를 구해주세요 mau란 한 달 동안 ..
SQL데이터 분석 첫걸음 교육(고급 기능) 사실 이번 day의 제목이 알아두면 좋은 고급 기능이라는데 사실 sqld 공부하면서 너무 익혀뒀던 기능들이라 익숙하기도 했고 그 동안의 실습이 기본적이긴 했구나 라는 생각을 하게 되었다. 하지만 sqld 공부할 때에는 직접 손으로 쿼리를 작성하면서 하지는 않았기에 직접 실습할 때에는 마냥 쉽지는 않았던 거 같다. 본론으로 들어와서 이번 실습에서 여러 기능을 소개해 주었는데 첫번째로는 데이터 수정하기 이다. 생각해보니 막상 이전 실습에서는 원하는 데이터를 가져오는 것에 집중했지 수정하는 실습은 거의 안했었다. 물론 배우긴 했었지만 실습은 따로 없었다. 바로 UPDATE SET WHER 절인데 쿼리 양식 UPDATE [테이블 이름] SET [컬럼 이름] = [새 값] WHERE 조건식; 으로 UPDATE 뒤..
SQL데이터 분석 첫걸음 실습 (서브 쿼리) day9 은 조건에 조건 더하기 SELECT절의 서브 쿼리 FROM절의 서브 쿼리 WHERE절의 서브 쿼리 의 항목들을 조건에 조건 더하기 라는 주제로 교육을 마저 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 오늘은 여러 테이블의 데이터를 한 번에 조회해 볼 것이다. 주어지는 테이블은 이 테이블을 참고 할 것이다. 먼저 서브쿼리의 특징을 먼저 정리하고 시작하겠다. § 하나의 쿼리 내 포함된 또 하나의 쿼리를 의미합니다. § 서브 쿼리는 반드시 괄호 안에 있어야 합니다. § SELECT, FROM, WHERE, HAVING, ORDER BY 절에 사용 가능합니다. § INSERT, UPDATE, DELETE 문에도 사용 가능합니다. § 서브쿼리에는 ; (세미 콜론)을 붙이지 않아도 됩니다..
SQL데이터 분석 첫걸음 실습 (UNION, UNION ALL) day8 은 여러 테이블 한번에 다루기 데이터에 데이터 더하기 데이터에서 데이터 빼기 의 항목들을 여러 테이블 한번에 다루기 라는 주제로 교육을 마저 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 오늘은 여러 테이블의 데이터를 한 번에 조회해 볼 것이다. 주어지는 테이블은 이 테이블을 참고 할 것이다. 실습1 내 포켓몬과 친구의 포켓몬에 어떤 타입들이 있는지 중복 제외하고 같은 타입은 한 번 씩만 가져와 주세요. 먼저 여기서 UNION, UNION ALL 의 특징을 정리하자면 § [쿼리 A] UNION [쿼리B] 또는 [쿼리 A] UNION ALL [쿼리B] 형식으로 사용합니다. § [쿼리 A]와 [쿼리 B]의 결과 값을 합쳐서 보여줍니다. § UNION은 동일한 값은 제외하고 보여주며,..
SQL데이터 분석 첫걸음 실습 (JOIN) day7 는 데이터 합치기 기준으로 테이블 합치기 한쪽을 기준으로 테이블 합치기 다양한 방식으로 테이블 합치기 의 항목들을 데이터 합치기라는 주제로 교육을 마저 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 오늘은 데이터를 그룹화해서 통계를 내 볼 것이다. 주어지는 테이블은 이 테이블을 참고 할 것이다. 실습1 포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 이름, 공격력, 방어력을 한번에 가져와 주세요. 이 때, 포켓몬 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요. 만약, 포켓몬의 능력치 데이터를 구할 수 없다면, NULL을 가져와도 좋습니다 mypokemon컬럼에 있는 name이 사용되니 그 방향대로 left join을 사용한다. 기본적 left join 문법은 SELECT [컬..
SQL데이터 분석 첫걸음 실습 (IF, CASE, 함수 만들기) day6 는 조건 만들기 여러 조건 한번에 만들기 함수 만들기 의 항목들을 규칙 만들기라는 주제로 이제는 기본보다는 조금 난이도가 있는 교육을 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 오늘은 함수를 만들고 사용하고 복습하는 시간을 가져보겠다. 주어지는 테이블은 이 테이블을 참고 할 것이다. 실습1 공격력과 방어력의 합이 120보다 크면 ‘very strong’, 90보다 크면 ‘strong’, 모두 해당 되지 않으면 ‘not strong’를 반환하는 함수 ‘isStrong’을 만들고 사용해주세요. 조건1: attack과 defense를 입력값으로 사용하세요. 조건2: 결과값 데이터 타입은 VARCHAR(20)로 해주세요. 함수를 생성할 때 필요한 구문이 있다 SET GLOBAL lo..
SQL데이터 분석 첫걸음 실습 (GROUP BY, HAVING) day5 는 데이터 그룹화하기 그룹에 조건 주기 다양한 그룹 함수 알아보기 쿼리 실행 순서 알아보기 의 항목들을 데이터 그룹화하기라는 주제로 기본적인 교육을 마저 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 오늘은 데이터를 그룹화해서 통계를 내 볼 것이다. 주어지는 테이블은 이 테이블을 참고 할 것이다. 실습1 포켓몬 테이블에서 이름(name)의 길이가 5보다 큰 포켓몬들을 타입(type)을 기준으로 그룹화하고, 몸무게(weight)의 평균이 20 이상인 그룹의 타입과, 몸무게의 평균을 가져와 주세요. 이 때, 결과는 몸무게의 평균을 내림차순으로 정렬해 주세요 length함수를 사용하여 이름의 길이를 구한다 group by를 사용하여 type기준으로 그룹화하고 having 절을 이용하여..
SQL데이터 분석 첫걸음 실습 (ORDER BY, RANK() OVER, 각종 함수) day4 는 데이터 줄 세우기 데이터 순위 만들기 문자형 데이터 정복하기 숫자형 데이터 정복하기 날짜형 데이터 정복하기 의 항목들을 원하는 데이터 만들기라는 주제로 기본적인 교육을 마저 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 이번 day 실습은 데이터를 요청대로 만들어보는 실습 위주로 진행한다. 이 테이블을 참고하여 실습을 진행 해보겠다. 실습1 포켓몬 테이블에서 포켓몬의 이름과 이름의 글자 수를 이름의 글자 수로 정렬해서 가져와 주세요. (정렬 순서는 글자 수가 적은 것부터 많은 것 순으로 해주세요.) lenght 함수를 이용하여 글자수를 가져오고 그 기준으로 순서를 나열한다. 적은 것 부터 많은 것 순으로 해야하는데 원래는 ASC가 order by 절 뒤에 들어가야 하지만 생략..
SQL데이터 분석 첫걸음 실습 (WHERE, 각종 연산자) day3 는 조건에 맞는 데이터 가져오기 비교 연산자 논리 연산자 기타 주요 연산자 문자형 데이터 다루기 NULL 데이터 다루기 의 항목들을 조건에 맞는 데이터 가져오기라는 주제로 기본적인 교육을 마저 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 이번 day 실습은 조건에 맞는 데이터를 가져오는 실습 위주로 진행한다. 기본적으로 where 절을 익히는 day 이다. 이 테이블을 참고하여 실습을 진행 해보겠다. 실습1 이브이의 타입을 가져와 주세요 where 절을 이용하여 조건 생성 후 가져오는 실습이다. 실습2 캐터피의 공격력과 방어력을 가져와 주세요 실습1과 선택 쿼리 수 제외하고는 같은 실습이다. 실습3 몸무게가 6kg보다 큰 포켓몬들의 모든 데이터를 가져와 주세요 조건에 비교연산자..
SQL데이터 분석 첫걸음 실습 (DISTINCT, AS) day2 는 데이터 가져오기 테이블에서 데이터 가져오기 별명 붙이기 데이터 일부만 가져오기 중복 제거하기 의 항목들을 데이터 가져오기라는 주제로 기본적인 교육을 마저 진행하였다. 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 실습1 123 곱하기 456을 가져와 주세요 이는 SELECT 절만으로도 가능한 쿼리이다. MySQL에서 곱하기 기호(×)는 * 로 표현한다. (숫자 * 숫자) 실습2 2310 나누기 30을 가져와 주세요 이 또한 SELECT 절만으로도 가능한 쿼리이다. MySQL에서 나누기 기호(÷)는 / 로 표현합니다. (숫자 / 숫자) 실습3 ‘피카츄’라는 문자열을 ‘포켓몬’이라는 이름의 컬럼 별명으로 가져와 주세요 이 실습도 마찬가지로 SELECT 절만으로도 가능한 쿼리이다. 이번 실습에..
SQL데이터 분석 첫걸음 실습 (SELECT, CREATE, INSERT INTO VALUES) day1 은 데이터 타입 알아보기 (숫자형, 문자형, 날짜형), 기본적인 데이터 다뤄 보기 테이블 만들고 변경하기 테이블 지우기 데이터 삽입, 삭제, 수정하기 의 항목들을 데이터베이스 다루기라는 주제로 기본적인 교육을 진행하였다 오늘 배운 교육을 바탕으로 실습을 진행해 보겠다. 실습1 테이블을 만들고 데이터를 넣어볼 것이다. CREATE, INSERT 문을 활용하는 것이고 ‘포켓몬’ (pokemon) 데이터베이스와 ‘나의 포켓몬’ (mypokemon) 테이블을 만들고, 캐터피, 피카츄, 이브이의 포켓몬 번호, 영문 이름, 타입 데이터를 넣어줄것이다. STEP1. 포켓몬 데이터베이스를 만들어 주세요. (데이터베이스 이름 : pokemon) STEP2. 포켓몬 데이터베이스 안에 나의 포켓몬 테이블을 만들어 ..

티스토리툴바