오늘은 실전문제 3번을 풀었습니다.
문제가 많지만 그래도 풀긴 했으니 올려보고 오류가 뭔지 알아보도록 하겠습니다.
우선 3번입니다.
첫번째 문제에서 저는
select round(100*(sum(cnt_repair)/sum(system_issue)),2) as '정비 이력이 존재하는 차량의 비율'
from
(SELECT
vt.vehicle_id,
vr.repair_id,
CASE
WHEN vr.repair_id is not NULL AND vr.repair_id <> 'not given' THEN 1
ELSE 0
END AS cnt_repair,
CASE
WHEN vibration_level > 6 THEN 1
WHEN brake_response > 300 THEN 1
WHEN engine_temp < 70 OR engine_temp > 110 THEN 1
ELSE 0
END AS system_issue
FROM
vehicle_test_logs vt
LEFT JOIN
vehicle_repairs vr ON vt.vehicle_id = vr.vehicle_id
)a
이렇게 코드를 짯는데 팀원들이랑 같이 본 결과 select 뒤에 distinct 를 붙여줘야 한다더라고요 ㅠㅠ
요런 식으로 select 뒤에 붙여줘야 하는건데 중복된 값들을 제거해주는 방식입니다.
만약 distinct를 안붙이면 vehicle_id 가 같은 중복된 차량이 결과에 추가 됩니다.
예시로 수리를 한 차량수를 세고 싶을때 distinct를 붙이지 않는다면 똑같은차량도 여러번 인식되기 때문에 distinct를 붙여줘야 이 경우를 제거해줄 수 있다는 겁니다.
반대로 총 차량을 방문횟수등 중복되는것들도 처리하고 싶을때는 distinct를 써주면 안되겠죠.
select sum(a.cnt_vibration_level),
sum(a.cnt_brake_response),
sum(a.cnt_engine_temp)
from
(select case when vr.repair_id is not null and vr.repair_id<>'not given' then 1 else 0 end as cnt_repair,
if (vt.vibration_level>6,1,0) as cnt_vibration_level,
if (vt.brake_response>300,1,0) as cnt_brake_response,
if (vt.engine_temp<70 or vt.engine_temp>110,1,0) as cnt_engine_temp
FROM
vehicle_test_logs vt
LEFT JOIN
vehicle_repairs vr ON vt.vehicle_id = vr.vehicle_id
)a
where a.cnt_repair=1
다음 문제입니다.
이것도 조원들과 정답이 달랐습니다.
저는 repair_id만 있으면 수리를 한것이니 고장난 센서가 여러개일경우 ex) 진동 and 브레이크 를 같이 수리되었을 거라고 생각했는데 힌트를 열어보니....
이렇게 되어있더군요....
테이블의 description을 보고 고장 발생원인과 정비내용이 일치한 건수를 조사하는 것이였습니다.
아무튼 힌트는 꼭 미리미리 열어봐야겠습니다.
select count(a.repair_id)
from
(SELECT
vt.vehicle_id,
vr.repair_id,
CASE
WHEN vr.repair_id is not NULL AND vr.repair_id <> 'not given' THEN 1
ELSE 0
END AS cnt_repair,
CASE
WHEN vibration_level > 6 THEN 1
WHEN brake_response > 300 THEN 1
WHEN engine_temp < 70 OR engine_temp > 110 THEN 1
ELSE 0
END AS system_issue
FROM
vehicle_test_logs vt
LEFT JOIN
vehicle_repairs vr ON vt.vehicle_id = vr.vehicle_id
)a
where a.system_issue=0
이렇게 코드를 짯는데 이건 앞서말씀드린 distinct만 붙여주면 될 것 같습니다.
select count(1)
from
(select case when vr.repair_id is not null and vr.repair_id<>'not given' then 1 else 0 end as cnt_repair,
if (vt.vibration_level>6,1,0) as cnt_vibration_level,
if (vt.brake_response>300,1,0) as cnt_brake_response,
if (vt.engine_temp<70 or vt.engine_temp>110,1,0) as cnt_engine_temp
FROM
vehicle_test_logs vt
LEFT JOIN
vehicle_repairs vr ON vt.vehicle_id = vr.vehicle_id
)a
where a.cnt_vibration_level+a.cnt_brake_response+a.cnt_engine_temp>=2
이것도 distinct 붙여주면....
다음부터 꼭꼭 distinct를 붙여줍시다.
마지막 느낀점
조원들과 함께 문제를 풀고 비교해보니 어느쿼리가 좋은지 분석할 수 있게 되네요.
조원들이 쓴 with 절도 새로 알게 되는등 혼자 공부하는 것보다 이렇게 서로 상의하며 하는 프로젝트가 재밌는것 같습니다.