문제 4번
SELECT mp2.order_id,
mp2.payment_type,
mp2.payment_value,
a.avg_payment
FROM marketer_payments AS mp2
JOIN
(
SELECT payment_type,
AVG(payment_value) AS 'avg_payment'
FROM marketer_payments AS mp
GROUP BY payment_type
) AS a
ON mp2.payment_type = a.payment_type
WHERE
mp2.payment_value>a.avg_payment
ORDER BY
payment_type, payment_value desc
결과
6번
SELECT mp.order_id,
mp.payment_type,
mp.payment_value,
CASE WHEN payment_value>=a.avg_pay THEN 'YES'
ELSE 'No' END AS 'outlier'
FROM marketer_payments AS mp
JOIN(
SELECT payment_type,
ROUND(AVG(payment_value) * 1.5, 2) AS avg_pay
FROM marketer_payments mp1
GROUP BY payment_type
) AS a ON a.payment_type=mp.payment_type
결과
깨달은 점.
서브쿼리를 만들었으면 알리아스를 주고 활용해야함.
CASE WHEN payment_value>=ROUND(AVG(payment_value) * 1.5, 2) THEN 'YES'
ELSE 'No' END AS 'outlier'
이렇게 알리아스를 안주고 다시 avg를 SELECT절에 사용할 경우, GROUP BY를 안해주면 전체의 평균을 구하는 상황이 발생함!
