오늘 한 것 간략하게
1. 머신러닝 소프트 랜딩 세션 수강
2. 아티클스터디(A/B테스트 : p-value, 유의수준)
3. 스탠다드반(Transpose, pivot_table, melt, stack, unstack)
4. SQL window function, QCC 2회차 복습
5. ADsp 5, 6주차 수강(R 기본 문법, 데이터 전처리)
6. 통계야 놀자~~ (A/B테스트, 유의수준, p-value)
7. 데이터문해력 3챕터 읽기
SQL(WINDOW FUNCTION 연습)
🌟 연습 문제 1: 서버별 최고 레벨 유저 찾기
각 서버에서 가장 높은 레벨을 가진 유저의 게임 계정 ID, 레벨, 서버 번호를 출력하세요.
#1. RANK() 사용
select game_account_id, level, serverno
from (
select game_account_id, level, serverno,
RANK() over(partition by serverno order by level desc) as rnk
from basic.users
) a
where rnk =1
#2. WITH 사용
with max_lv as #서버별 최고 레벨 구하기
(select serverno,
max(level) as max_lv
from basic.users
group by serverno
)
select u.game_account_id, u.level, u.serverno
from basic.users u
join max_lv m
on u.serverno = m.serverno #서버별
AND u.level = m.max_lv #최고 레벨을 가진 유저만 출력됨
order by serverno;
주의할 점!
❌ON u.serverno = m.serverno 까지만 하면 해당 서버의 모든 유저가 출력됨
⭕️ON u.serverno = m.serverno AND u.level = m.max_level
이렇게 해야 최고 레벨을 가진 유저가 출력됨
🌟 연습 문제 2: 유료 유저들의 평균 레벨 구하기
payment 테이블에서 결제한 적이 있는 유저들만 대상으로, 서버별 평균 레벨을 구하세요.
with paid_users as
(select distinct game_account_id
from basic.payment
where pay_type is not null
)
select u.serverno,
avg(u.level) as avg_lv
from basic.users u
JOIN paid_users pu
ON u.game_account_id = pu.game_account_id
group by u.serverno
order by u.serverno;🌟 연습 문제 3: 월별 신규 유저 수 구하기
각 달(YYYY-MM 형식)별로 users 테이블에서 첫 접속일을 기준으로 신규 유저 수를 계산하세요.
#with 없이 풀기
select date_format(date,'%Y-%m') as first_login,
count(game_account_id)
from basic.users
group by date_format(date,'%Y-%m')
order by first_login
#굳이 with 쓰기
WITH monthly_users AS (
SELECT DATE_FORMAT(date, '%Y-%m') AS first_login,
game_account_id
FROM basic.users
)
SELECT first_login,
COUNT(game_account_id) AS user_count
FROM monthly_users
GROUP BY first_login
ORDER BY first_login;QCC 2회차 3번 문제 복습
문제: 마케팅 팀은 각 대륙에서 인구가 가장 많은 도시를 분석하여 주요 타겟 시장을 선정하고자 합니다.
-
대륙별 가장 인구가 많은 도시를 찾아야 합니다.
-
각 대륙별로 인구가 가장 많은 도시를 찾고, 해당 도시만 조회해야 합니다.
-
도시 정보가 없는 대륙은 제외해주세요.
-
결과는 인구 기준으로 내림차순 정렬해야 합니다.
#아마도 정답^^;;
select CITY as CITY_NAME,
COUNTRY as COUNTRY_NAME,
CONTINENT as CONTINENT_NAME,
city_population_rnk.population as POPULATION
FROM
(
select ci.name CITY,
co.name COUNTRY,
co.continent CONTINENT,
ci.population,
row_number() over(partition by co.continent order by ci.population desc) RNK
from country co join city ci on co.code=ci.countrycode
) city_population_rnk
WHERE RNK = 1
ORDER BY POPULATION desc출력 결과
#WITH 사용하고 싶다.......어찌 풀지..?
#1차 시도
with max_city as #(인구 수가 가장 많은 도시 구하기)
(select distinct countrycode,
name,
max(population) max_population
from city
group by countrycode
)
select m.name as CITY_NAME,
co.name as COUNTRY_NAME,
co.continent as CONTINENT,
m.max_population as POPULATION,
rank() over(partition by continent order by population desc) as rnk
FROM country co
JOIN max_city m
ON co.code=m.countrycode1차 시도 실패!!!
-
MAX(population)을 구하면 가장 인구가 많은 도시의 인구 수만 가져올 뿐, 해당 도시 이름(name)을 정확하게 매칭하지 않음.
즉, name 컬럼이 올바른 도시인지 보장되지 않음. -
DISTINCT는 필요 없음.
GROUP BY countrycode를 하면 자동으로 중복이 제거됨.
#2차 시도
with max_city as
(select countrycode,
name,
population,
max(population) max_population #???이건 쓰지도 않음ㅋㅋㅋㅋ
from city
group by countrycode
)
SELECT CITY_NAME AS cityname,
COUNTRY_NAME AS countryname,
CONTINENT AS continent_name,
POPULATION AS high_population
FROM (
SELECT m.name AS CITY_NAME,
co.name AS COUNTRY_NAME,
co.continent AS CONTINENT,
m.population as POPULATION,
ROW_NUMBER() OVER (PARTITION BY co.continent ORDER BY m.POPULATION DESC) AS rnk
FROM country co
JOIN max_city m
ON co.code = m.countrycode
) ranked
WHERE rnk = 1
ORDER BY high_population DESC;출력결과
출력결과는 똑같..😱😱
하긴 with는 그냥 달려만 있지 똑같이 쓰긴 했다.....
괜히 with 쓰겠다고 까불다가 불지옥맛만 봤다ㅋㅋㅋㅋㅋ앞으로는 괜히 with 쓰겠다고 까불지 말아야지 ㅠㅠ
계속 하면서도 with가 필요가 없는데..? 라는 생각이 들었다..ㅋㅋㅋㅋㅋ
📌WITH를 쓰려면 명확한 이유(예: 서브쿼리를 여러 번 사용해야 할 때)가 필요.
얘야. 넌 아직 with를 쓰기엔 나약하단다.. 조급해하지 말렴🤣
내일 QCC 잘 풀 수 있을까..ㅎㅎㅎ걱정된다. 그래도 오늘은 나름 즐겁게 공부했다. 내일 결과가 어떻게 나오든 너무 낙담하지 말고 하나 더 배운다고 생각하고 열심히 하자~ 아자자~~
