by DB공부 [SQL Query] mysql testdome [2020.09.19 15:50:51]
현재 SQL 공부 중에 아래 사이트에서 문제는 푸는데요, 도저히 뭐가 문제인지 모르겠습니다 ㅠㅠ
https://www.testdome.com/questions/sql/regional-sales-comparison/36141
현재 아래처럼 풀었는데 무엇이 문제인지 도움을 받을 수 있을까요?
select R.name,
ifnull(sum(SA.amount) / count(SA.employeeId), 0) as average,
(select ifnull(sum(SA.amount) / count(SA.employeeId), 0) as temp
from regions as R
left join states as ST
on R.id = ST.regionId
left join employees as E
on ST.id = E.stateId
left join sales as SA
on E.id = SA.employeeId
group by R.id
order by temp desc
limit 1) - ifnull(sum(SA.amount) / count(SA.employeeId), 0) as difference
from regions as R
left join states as ST
on R.id = ST.regionId
left join employees as E
on ST.id = E.stateId
left join sales as SA
on E.id = SA.employeeId
group by R.id
음 그러게요. 답은 맞는데 4개중에 1개 틀리다고 나오네요.
문제에서 의도하는게 뭔지 잘 모르겠네요. 제가 작성한 것도 마찬가지네요.
select name, avg, first_value(avg) over(order by avg desc)-avg difference from ( select a.name, coalesce(avg(d.amount),0) avg from regions a inner join states b on a.id = b.regionid left outer join employees c on b.id = c.stateid left outer join sales d on c.id = d.employeeid group by a.name ) t
1. Group By 구문
- id 로 그룹핑 하고 name 을 출력하는 것은 MySQL 에서 만 허용되는 비표준 구문입니다.
- 에러가 안난다고 해서 무분별하게 사용하기 보다는, 표준 구문 사용하시기 바랍니다.
2. 평균을 구하는 부분에서
- / count(SA.employeeId) 를 한다면? 0 으로 나누는 오류가 발생할 수 있습니다.
- 굳이 sum / count 할 필요 없이 avg 로 구하면 간단합니다.
- 다만 null 의 경우 평균에서 제외되기 때문에 평균을 구하기 전에 0으로 치환할 필요가 있습니다.
3. 스칼라서브쿼리 사용부분은
- 인라인뷰(from절의 서브쿼리)로 내리는게 좋을 듯 합니다.
- 분석함수를 사용할 수 있으면 더 좋습니다.
SELECT a.name , AVG(IFNULL(amount, 0)) avg_amt , MAX(AVG(IFNULL(amount, 0))) OVER() - AVG(IFNULL(amount, 0)) diff FROM regions a LEFT OUTER JOIN states b ON a.id = b.regionId LEFT OUTER JOIN employees c ON b.id = c.stateId LEFT OUTER JOIN sales d ON c.id = d.employeeId GROUP BY a.name ;