블리자드 게임 오버워치의 신규 유저가 오버워치에 적응하기 전까지 플레이하기에 가장 적당한 영웅은 무엇인가?
[ 오버워치 테이블 생성]
create table overwatch (
heroes varchar2(50),
skill_name varchar2(100),
headshot_dps number(5),
singleshot_dps number(5),
nanoboost_dps number(5),
damage_boosting_beam number(5),
heal_per_sec number(5),
ammo number(5),
reload_time number(5),
fire_rate_per_second number(5),
life number(3),
armour number(3),
shield number(3),
role varchar2(10));
[ 신규유저의 불안요소인 난이도 컬럼을 추가 후 데이터 갱신 ]
- 난이도 데이터 출처 : http://overwatch.guide/heroes
alter table overwatch
add level_of_difficulty number(2);
update overwatch
set level_of_difficulty = 3
where heroes in ('ANA','DOOMFIST','GENJI','HANZO','LUCIO','MEI','SOMBRA','ZARYA','ZENYATTA');
update overwatch
set level_of_difficulty = 2
where heroes in ('D.VA','JUNKRAT','MCCREE','MOIRA','ORISA','SYMMETRA','BRIGITTE',
'TORBJORN','TRACER','WIDOWMAKER','WINSTON');
update overwatch
set level_of_difficulty = 1
where heroes in ('BASTION','MERCY','PHARAH','REAPER','REINHARDT','ROADHOG','SOLDIER:76');
[ 영웅명, 초당 데미지, 데미지 순위, 전체 데미지 평균,
평균 생명력, 생명력 순위, 전체 생명력 평균를 담은 뷰 생성]
create or replace view overwatch_beginners
as
select o.heroes,
o.damage_per_sec,
dense_rank() over(order by o.damage_per_sec desc) rnk_damage,
avg(o.damage_per_sec) over() avg_damage,
v.whole_life,
dense_rank() over(order by v.whole_life desc) rnk_life,
avg(v.whole_life) over() avg_life,
o.role
from
(select heroes,
avg(singleshot_dps*fire_rate_per_second*decode(level_of_difficulty,1,1,2,0.9,0.8)) as damage_per_sec,
role
from overwatch
where singleshot_dps is not null
group by heroes,
role
) o
, (select heroes,
avg(life+nvl(armour,0)+nvl(shield,0)) as whole_life
from overwatch
group by heroes
) v
where o.heroes = v.heroes ;
![](https://t1.daumcdn.net/cfile/cafe/990B80395AF8147C14)
[ 가장 적당한 영웅을 도출하기 위한 쿼리문 ]
select rnk.heroes,
(select damage_per_sec
from overwatch_beginners
where heroes = rnk.heroes
) damage,
(select whole_life
from overwatch_beginners
where heroes = rnk.heroes
) life,
( select role
from overwatch_beginners
where heroes = rnk.heroes) role,
dense_rank() over(order by sumrnk asc) ranking
from
(select heroes,
sum(rnk_damage+rnk_life) sumrnk
from overwatch_beginners o
where o.whole_life >= (select avg(life+nvl(armour,0)+nvl(shield,0))
from overwatch)
or o.damage_per_sec >= (select avg(singleshot_dps*fire_rate_per_second*decode(level_of_difficulty,1,1,2,0.9,0.8))
from overwatch)
group by heroes
) rnk;
신규유저들이 fps 게임인 오버워치에 적응하기 위해서는 적당한 딜 가능 유무, 생존 유무가 중요한 요소이다.
그러므로 초당 데미지의 순위, 그리고 평균 생명력의 순위의 합을 구해 최소인 순으로 나열해 보았을 때
탱크 역할군인 디바와 수비영웅인 바스티온이 적당함을 알 수 있었다.
서포트군 영웅들 중에서 힐량에 따라 순위를 매기고 확인하시오.
select heroes, heal, rank() over(order by heal
desc) 순위
from (select heroes,
round(avg(nvl(heal_per_sec,0))) heal
from overwatch
where role = 'SUPPORT'
group by heroes);
첫댓글 http://overwatch.guide/heroes/bastion/