카테고리 없음

데이터분석 > 3주차 > 파트 04 > 0829

나는야석사 2024. 9. 1. 02:35
  1. 실전 데이터 분석 사례 1 - Indian Restaurant Dataset
  2. 점포 수가 1개인 식당을 단일 점포, 2개 이상인 식당을 프렌차이즈라고 정의
  3. 별점 기준 상위 20개 식당은 모두 단일 점포다.
  4. 평균적으로 프렌차이즈의 평점이 단일 점포보다 높다.
  5. 별점 분포 히스토그램
    1. 단일 점포는 분포가 양끝으로 더 넓게 퍼져 있다. -> 양 극단의 값이 더 많다.
/*SELECT COUNT(1) AS cnt, COUNT(DISTINCT restaurant_name) AS cnt_distinct
FROM restaurant*/

-- SELECT restaurant_name, COUNT(1) AS cnt
-- FROM restaurant
-- GROUP BY 1
-- HAVING cnt > 1

-- SELECT *
-- FROM restaurant
-- WHERE restaurant_name IN ('7th Heaven', '1441 Pizzeria', '1944 -The HOCCO Kitchen')
-- ORDER BY restaurant_name

# 레스토랑 이름, 지역, 패스트푸드 여부로 유니크하게 모든 행 구분 가능한지 실행해보는 코드
-- SELECT COUNT(1) AS cnt, COUNT(DISTINCT restaurant_name, location, fast_food_or_not) AS cnt_distinct
-- FROM restaurant

SELECT restaurant_name, COUNT(1) AS cnt, AVG(rating) AS avg_rating, AVG(average_price) AS avg_price, AVG(average_delivery_time) AS avg_delivery_time
FROM restaurant
GROUP BY 1
ORDER BY 3 DESC
-- LIMIT 20

-- WITH counts AS(
-- 	SELECT restaurant_name, COUNT(1) AS cnt
-- 	FROM restaurant
-- 	GROUP BY 1
-- )
-- SELECT CASE cnt
-- 		WHEN 1 THEN 'cnt-1'
-- 		WHEN 2 THEN 'cnt-2'
-- 		ELSE 'cnt-ov3'
-- 		END AS cnt_group, AVG(rating) AS avg_rating
-- FROM counts INNER JOIN restaurant ON counts.restaurant_name = restaurant.restaurant_name
-- GROUP BY 1

-- WITH counts AS(
-- 	SELECT restaurant_name, COUNT(1) AS cnt
-- 	FROM restaurant
-- 	GROUP BY 1
-- )
-- SELECT counts.restaurant_name, cnt, rating
-- FROM counts INNER JOIN restaurant ON counts.restaurant_name = restaurant.restaurant_name

 

/*SELECT COUNT(1) AS cnt, COUNT(DISTINCT restaurant_name) AS cnt_distinct
FROM restaurant*/

-- SELECT restaurant_name, COUNT(1) AS cnt
-- FROM restaurant
-- GROUP BY 1
-- HAVING cnt > 1

-- SELECT *
-- FROM restaurant
-- WHERE restaurant_name IN ('7th Heaven', '1441 Pizzeria', '1944 -The HOCCO Kitchen')
-- ORDER BY restaurant_name

# 레스토랑 이름, 지역, 패스트푸드 여부로 유니크하게 모든 행 구분 가능한지 실행해보는 코드
-- SELECT COUNT(1) AS cnt, COUNT(DISTINCT restaurant_name, location, fast_food_or_not) AS cnt_distinct
-- FROM restaurant

-- SELECT restaurant_name, COUNT(1) AS cnt, AVG(rating) AS avg_rating, AVG(average_price) AS avg_price, AVG(average_delivery_time) AS avg_delivery_time
-- FROM restaurant
-- GROUP BY 1
-- ORDER BY 3 DESC
-- LIMIT 20

-- WITH counts AS(
-- 	SELECT restaurant_name, COUNT(1) AS cnt
-- 	FROM restaurant
-- 	GROUP BY 1
-- )
-- SELECT CASE cnt
-- 		WHEN 1 THEN 'cnt-1'
-- 		WHEN 2 THEN 'cnt-2'
-- 		ELSE 'cnt-ov3'
-- 		END AS cnt_group, AVG(rating) AS avg_rating
-- FROM counts INNER JOIN restaurant ON counts.restaurant_name = restaurant.restaurant_name
-- GROUP BY 1

-- WITH counts AS(
-- 	SELECT restaurant_name, COUNT(1) AS cnt
-- 	FROM restaurant
-- 	GROUP BY 1
-- )
-- SELECT counts.restaurant_name, cnt, rating
-- FROM counts INNER JOIN restaurant ON counts.restaurant_name = restaurant.restaurant_name


-- WITH counts AS(
-- 	SELECT restaurant_name, COUNT(1) AS cnt
-- 	FROM restaurant
-- 	GROUP BY 1
-- )
-- SELECT restaurant.*, cnt
-- FROM counts INNER JOIN restaurant ON counts.restaurant_name = restaurant.restaurant_name


-- SELECT location, COUNT(1) AS cnt
-- FROM restaurant
-- GROUP BY 1
-- ORDER BY 2 DESC
-- LIMIT 20

-- WITH cnts AS(SELECT location, COUNT(1) AS cnt
-- FROM restaurant
-- GROUP BY 1
-- ),
-- res AS (
-- 	SELECT location, PERCENT_RANK() OVER (ORDER BY cnt) AS cnt_rank
-- 	FROM cnts
-- )
-- SELECT location
-- FROM res
-- WHERE cnt_rank > 0.95

-- WITH cnts AS(SELECT location, COUNT(1) AS cnt
-- FROM restaurant
-- GROUP BY 1
-- ),
-- res AS (
-- 	SELECT location, PERCENT_RANK() OVER (ORDER BY cnt) AS cnt_rank
-- 	FROM cnts
-- )
-- SELECT location
-- FROM res
-- WHERE cnt_rank < 0.15

-- SELECT location, AVG(rating) AS avg_rating, AVG(average_price) AS avg_price, AVG(average_delivery_time) AS avg_time,
-- MAX(average_price) AS max_price, COUNT(restaurant_name)
-- FROM restaurant
-- GROUP BY 1
-- ORDER BY 3
-- LIMIT 10


-- WITH base AS(
-- 		SELECT location, south_indian_or_not, north_indian_or_not, fast_food_or_not, street_food, biryani_or_not, bakery_or_not
-- 		FROM restaurant
-- 		WHERE location IN ('Rishikesh', 'Shimla')
-- )
-- SELECT location, COUNT(1) AS tot_cnt, SUM(south_indian_or_not) AS south_cnt,
-- SUM(north_indian_or_not) AS north_cnt, SUM(fast_food_or_not) AS fast_cnt, SUM(street_food) AS street_cnt, SUM(biryani_or_not) AS biryani_cnt, 
-- SUM(bakery_or_not) AS bakery_cnt
-- FROM base
-- GROUP BY 1

-- 
-- 		SELECT location, south_indian_or_not, north_indian_or_not, fast_food_or_not, street_food, biryani_or_not, bakery_or_not
-- 		FROM restaurant
-- 		WHERE location IN ('Rishikesh', 'Shimla')

-- SELECT location, AVG(rating) AS avg_rating, COUNT(1) AS cnt
-- FROM restaurant
-- GROUP BY 1
-- ORDER BY 2 
-- LIMIT 10

WITH base AS(
		SELECT location, south_indian_or_not, north_indian_or_not, fast_food_or_not, street_food, biryani_or_not, bakery_or_not
		FROM restaurant
		WHERE location IN ('Junagadh', 'MOtigari')
)
SELECT location, COUNT(1) AS tot_cnt, SUM(south_indian_or_not) AS south_cnt,
SUM(north_indian_or_not) AS north_cnt, SUM(fast_food_or_not) AS fast_cnt, SUM(street_food) AS street_cnt, SUM(biryani_or_not) AS biryani_cnt, 
SUM(bakery_or_not) AS bakery_cnt
FROM base
GROUP BY 1

 

outlier가 있으면 평균이 그쪽으로 많이 기울기 때문에 중앙값이나 히스토그램을 사용해 보는 것이 좋음

indian_practice.csv
0.81MB
indian_practice1.csv
1.32MB
indian_practice2.csv
2.03MB
indian_practice3.csv
0.00MB
indian_practice4.csv
0.00MB

Global AI, ML, Data Science Salary

22년에 비해 23년에 평균 연봉이 증가한 이유는, 시니어 비중이 높아지고 중간 크기 회사 비중이 높아졌기 때문이다.

-- SELECT work_year, AVG(salary_in_usd) AS usd_salary
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 1

-- SELECT experience_level, AVG(salary_in_usd) AS usd_salary
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- SELECT job_title, AVG(salary_in_usd) AS usd_salary
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- SELECT employee_residence, AVG(salary_in_usd) AS usd_salary
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- SELECT remote_ratio, AVG(salary_in_usd) AS usd_salary
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- SELECT company_size, AVG(salary_in_usd) AS usd_salary
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- WITH bef AS (
-- 	SELECT work_year, experience_level, COUNT(1) AS cnt_2022
-- 	FROM salary
-- 	WHERE work_year = 2022
-- 	GROUP BY 1, 2
-- ),
-- aft AS (
-- 			SELECT work_year, experience_level, COUNT(1) AS cnt_2023
-- 				FROM salary
-- 				WHERE work_year = 2023
-- 				GROUP BY 1, 2
-- )
-- SELECT bef.*, aft.cnt_2023
-- FROM bef INNER JOIN aft ON bef.experience_level = aft.experience_level

-- WITH bef AS (
-- 	SELECT work_year, company_size, COUNT(1) AS cnt_2022
-- 	FROM salary
-- 	WHERE work_year = 2022
-- 	GROUP BY 1, 2
-- ),
-- aft AS (
-- 			SELECT work_year, company_size, COUNT(1) AS cnt_2023
-- 				FROM salary
-- 				WHERE work_year = 2023
-- 				GROUP BY 1, 2
-- )
-- SELECT bef.*, aft.cnt_2023
-- FROM bef INNER JOIN aft ON bef.company_size = aft.company_size

-- SELECT work_year, AVG(remote_ratio) AS avg_remote_ratio
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- SELECT experience_level, AVG(remote_ratio) AS avg_remote_ratio
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- SELECT company_size, AVG(remote_ratio) AS avg_remote_ratio
-- FROM salary
-- WHERE work_year IN (2022, 2023)
-- GROUP BY 1
-- ORDER BY 2

-- WITH bef AS (
-- 		SELECT work_year, if(employee_residence = 'US', 'In-US', 'Out0US') AS residence_group, COUNT(1) AS cnt_2022
-- 		FROM salary
-- 		WHERE work_year = 2022
-- 		GROUP BY 1, 2
-- ),
-- aft AS (
-- 		SELECT work_year, if(employee_residence = 'US', 'In-US', 'Out0US') AS residence_group, COUNT(1) AS cnt_2023
-- 		FROM salary
-- 		WHERE work_year = 2023
-- 		GROUP BY 1, 2
-- )
-- SELECT bef.*, aft.cnt_2023
-- FROM bef INNER JOIN aft ON bef.residence_group = aft.residence_group

-- SELECT CASE
-- 		when job_title LIKE '%Scientist%' then	'S'
-- 		when job_title LIKE '%Director%' then	'D'
-- 		when job_title LIKE '%Engineer%' then	'E'
-- 		when job_title LIKE '%Analyst%' then	'A'
-- 		when job_title LIKE '%Architect%' then	'AC'
-- 		when job_title LIKE '%Consultant%' then	'C'
-- 		when job_title LIKE '%Manager%' then	'M'
-- 		when job_title LIKE '%Specialist%' then	'SP'
-- 		when job_title LIKE '%Practitioner%' then	'P'
-- 		ELSE 'OTHER'
-- 		END AS job_group, AVG(salary_in_usd) AS avg_salary, COUNT(1) AS cnt
-- FROM salary
-- GROUP BY 1
-- ORDER BY 2

-- SELECT CASE
-- 		when job_title LIKE '%Scientist%' then	'S'
-- 		when job_title LIKE '%Director%' then	'D'
-- 		when job_title LIKE '%Engineer%' then	'E'
-- 		when job_title LIKE '%Analyst%' then	'A'
-- 		when job_title LIKE '%Architect%' then	'AC'
-- 		when job_title LIKE '%Consultant%' then	'C'
-- 		when job_title LIKE '%Manager%' then	'M'
-- 		when job_title LIKE '%Specialist%' then	'SP'
-- 		when job_title LIKE '%Practitioner%' then	'P'
-- 		ELSE 'OTHER'
-- 		END AS job_group, AVG(salary_in_usd) AS avg_salary, AVG(remote_ratio) AS avg_remote, COUNT(1) AS cnt
-- FROM salary
-- GROUP BY 1
-- ORDER BY 2

WITH base AS (
SELECT CASE
		when job_title LIKE '%Scientist%' then	'S'
		when job_title LIKE '%Director%' then	'D'
		when job_title LIKE '%Engineer%' then	'E'
		when job_title LIKE '%Analyst%' then	'A'
		when job_title LIKE '%Architect%' then	'AC'
		when job_title LIKE '%Consultant%' then	'C'
		when job_title LIKE '%Manager%' then	'M'
		when job_title LIKE '%Specialist%' then	'SP'
		when job_title LIKE '%Practitioner%' then	'P'
		ELSE 'OTHER'
		END AS job_group, if(employee_residence = 'US', 'In-US', 'Out-US') AS residence_group
FROM salary
),
g_1 AS (
		SELECT job_group, residence_group, COUNT(1) AS group_cnt
		FROM base
		GROUP BY 1, 2
),
g_2 AS (
		SELECT job_group, COUNT(1) AS tot_cnt
		FROM base
		GROUP BY 1
)
SELECT g_1.job_group, residence_group, group_cnt, group_cnt / tot_cnt AS ration
FROM g_1 INNER JOIN g_2 ON g_1.job_group = g_2.job_group

global1csv.csv
0.00MB
global2csv.csv
0.00MB
global3csv.csv
0.00MB
salaries.csv
0.48MB
salaries.json
2.30MB

 

NBA Players

동명이인 선수들이 존재 -> 데이터 중복

구글 스프레드시트로 차트 시각화

평균 신장과 몸무게는 감소 추세, 시즌 내에 소화하는 게임 수는 증가 추세 -> 키가 큰 사람이 무거운 경우가 많음

평균 득점과 어시스트는 증가 추세, 리바운드는 유지 -> 득점 증가하면 어시스트도 증가함(팀플레이이기 때문)

예전보다 덩치가 작은 선수들이 득점을 하는 경우가 증가하는 추세임

 

드래프트 1라운드, 10순위 이내에 뽑힌 선수들의 키와 나이 추세는 감소

예전과 달리 득점력이 좋다면 드래프트에서 순위가 높게 뽑힘

nba.sql
0.00MB
nba1.csv
0.00MB
nba2.csv
0.00MB
nba3.csv
0.00MB
all_seasons.csv
1.50MB

 

강의요약

  1. 데이터 조회의 기본 : SELECT, FROM, WHERE (= 무엇을, 어디에서, 어떤 조건으로)
    1. 비교 연산자 : =, <>, >, <
    2. 논리 연산자 : AND, OR, NOT, IN
  2. 정렬과 집계
    1. GROUP BY
    2. ORDER BY
    3. 집계 함수 : AVG, SUM, COUNT
    4. .HAVING : 그룹화 된 결과에 대해 필터링
  3. 기초 SQL 함수
    1. 문자열 : CONCAT, SUBSTR, CHAR_LENGTH
    2. 숫자 : ROUND, ABS, MOD, COALESCE
  4. (Optional) DDL, DML
    1. DDL : CREATE, ALTER, DROP
    2. DML : SELECT, INSERT, UPDATE, DELETE

 

  1. 다양한 JOINS
    1. JOIN :  두 개 이상의 테이블을 특정 key를 기준으로 결합하는 것
    2. INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
    3. CROSS JOIN, SELF JOIN
  2. UNION, UNION ALL
  3. WITH
  4. Subquery

 

  1. 타임스탬프 함수
    1. 데이터 타입 : STRING, DATE, DATETIME, TIMESTAMP
    2. NOW, YEAR, HOUR, WEEKDAY
    3. 날짜 형식화 : STR_TO_DATE, DATE_FORMAT
    4. 날짜 연산 : DATE_ADD, DATEDIFF, TIMEDIFF, TIME_TO_SEC
  2. 타입 변환
    1. CAST, CONVERT
  3. 조건문
    1. IF
    2. CASE WHEN
  4. 그 외 유용한 함수 : RANK, PERCENT_RANK, LEAD, LAG

5강 : 다양한 데이터 타입 다루기

  1. 숫자
  2. 문자
  3. 이진
  4. Array
    1. JSON_EXTRACT
  5. Key-value
  6. JSON_EXTRACT

6강 : 효율적인 SQL 코드 작성하기

  1. 테이블을 집합으로 생각하기
    1. 필터링으로 최대한 작게 만들어 놓고 JOIN, 집계 연산 수행
  2. *, % 사용 지양하기
    1. LIMIT, 파티션 조건 사용
  3. 데이터 타입 잘 확인하기
  4. JOIN 시 유의할 점
  5. 코드 가독성 높이기

 

7강 : SQL을 활용한 데이터 분석

  1. Indian Restaurant Dataset
    1. 프렌차이즈 여부에 따른 별점의 분포 확인
    2. 상관 계수
    3. 가격과 평점에 영향을 주는 변수 확인
  2. Global AI, ML Data Science Salary
    1. 그룹별 평균 비교
    2. 연봉에 영향을 주는 변수 확인
  3. NBA Players
    1. 시간의 흐름에 따른 평균 신장, 몸무게, 게임 수 확인
    2. 시간의 흐름에 따른 상위권 선수들의 지표 확인