+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
This table may have duplicate rows.
This table contains information collected from some queries on a database.
The position column has a value from 1 to 500.
The rating column has a value from 1 to 5. Query with rating less than 3 is a poor query.
We define query quality as:
The average of the ratio between query rating and its position.
We also define poor query percentage as:
The percentage of all queries with rating less than 3.
Write a solution to find each query_name, the quality and poor_query_percentage.
Both quality and poor_query_percentage should be rounded to 2 decimal places.
Return the result table in any order.
The result format is in the following example.
with poor_count AS(
SELECT query_name, COUNT(*) AS poor
FROM Queries
WHERE rating < 3
GROUP BY query_name
)
SELECT q.query_name, ROUND(sum(rating/position)/COUNT(*), 2) AS quality, ROUND(COALESCE(poor,0) *100 /COUNT(*),2) AS poor_query_percentage
FROM Queries q
LEFT JOIN poor_count p ON q.query_name = p.query_name
GROUP BY q.query_name
일단 하나하나 해보는데 시간이 많이 걸렸고
quality 계산과 poor_query_percentage를 계산하기 위한 with 절이 필요했었음
다 좋은데
ROUND(COALESCE(poor,0) 100 /COUNT(),2)
COALESCE 용법에 대해 필요!
: 여러개의 값 중에서 NULL이 아닌 첫 번째 값을 반환
즉, NULL이 아니면 그 값을, 모두 NULL이면 마지막 대체값을
ex1 기본사용)
SELECT COALESCE(NULL, NULL, 'hello', 'world'); -- 결과: 'hello'
왼쪽부터 오른쪽으로 차례대로 값들을 검사하면 처음 나오는 NULL이 아닌 값 반환
SELECT COALESCE(NULL, NULL, NULL); -- 결과ex2) 테이블 컬럼에서 NULL 대체
SELECT COALESCE(middle_name, 'N/A') AS middle_name
FROM Employees;
<<< middle_name이 NULL이면 'N/A'를 반환함ex3) 계산에서 NULL 방지
SELECT employee_id,
salary + COALESCE(bonus, 0) AS total_income
FROM Employees
<<< bonus가 NULL이면 0으로 처리 계산 정상 작동
NULL 그대로 두면 salaray+NULL-> NULL이 돼서 값 자체가 사라짐ex4) LEFT JOIN에서 누락된 값 처리
SELECT e.name, COALESCE(s.sales_amount, 0) AS total_sales
FROM Employees e
LEFT JOIN Sales e ON e.id = s.employee_id
<<< LEFT JOIN이후 매칭 안되는 행은 0으로 바꿔줌ex5) 다중 컬럼 중 값 우선순위 주기
SELECT COALESCE(work_phone, mobile_phone, home_phone, 'No phone') AS contact
FROM Customers;
<<< work → mobile → home 순으로 우선 적용, 다 NULL이면 'No phone'