2024-01-17

오늘은 프로젝트가 다음날 발표인지라 마무리 짓는다고 파이썬으로 시각화 관련으로 이것저것 많이 하였는데 마찬가지로 정신없어서 TIL에 적을 용도로 정리해놓지는 못하였다 그리고 겨우겨우 밤에 코드카타 한문제 푼게 다이다

SQL 코드카타

 

1211. Queries Quality and Percentage(SQL)(case when 한 것을 나누기 계산하다 에러, round, case when, with, left join)

https://leetcode.com/problems/queries-quality-and-percentage/description/

quality는 rating/position의 평균으로, rating이 3미만인 경우는 poor query로 판단, 그리고 poor query percentage는 poor query의 갯수/전체 쿼리 갯수 각 비율은 소수 둘째자리까지 반올림하여 표현하여 답하는 문제이다(query_name이 null이면 제외→이부분은 문제에 안나와있음, test case에 있었음)

1차 시도

SELECT q.query_name,
        ROUND(sum(q.rating/q.position)/COUNT(q.result),2) quality,
        ROUND(100*(SELECT COUNT(q_s.result) FROM Queries q_s 
            WHERE q_s.rating < 3 GROUP BY q_s.query_name
            HAVING q_s.query_name = q.query_name)
            /COUNT(q.result),2) poor_query_percentage
FROM Queries q
GROUP BY q.query_name

우선 단순하게 바로 계산되는 부분은 바로 계산하였고, (poor쿼리에 대해서) select 서브 쿼리를 활용하여 바로 계산해주었다

#test case2
| query_name | result        | position | rating |
| ---------- | ------------- | -------- | ------ |
| lfdxfi     | qduxwfnfozvsr | 2        | 5      |
| meayln     | prepggxrpnrvy | 1        | 1      |
| phqghu     | wcysyycqpevik | 1        | 2      |
| rcvscx     | mznimkkasvwsr | 1        | 4      |
| lfdxfi     | kycxfxtlsgyps | 2        | 2      |

#out
| query_name | quality | poor_query_percentage |
| ---------- | ------- | --------------------- |
| lfdxfi     | 1.75    | 50                    |
| meayln     | 1       | 100                   |
| phqghu     | 2       | 100                   |
| rcvscx     | 4       | null                  |

하지만 이렇게 할 경우 where에 의해 rating이 3미만인 경우만 존재하는 쿼리의 경우 걸러지게 되는데 메인쿼리에 비교하여 계산 시 걸려졌기에 null값으로 반환된다 따라서 오답으로 나오게 되었다.

SELECT q_s.query_name, COUNT(q_s.result) FROM Queries q_s 
            WHERE q_s.rating < 3 GROUP BY q_s.query_name
#out
| query_name | COUNT(q_s.result) |
| ---------- | ----------------- |
| meayln     | 1                 |
| phqghu     | 1                 |
| lfdxfi     | 1                 |

살짝 긴가민가해서 위와 같이 서브쿼리만을 따로 실행시켜주어 확인하였다

2차 시도

처음에는 with하고는 똑같이 했는데 case when으로 조건에 따라 값 다르게 나오게 한뒤 거기에 또 나누기를 해주었는데(전에도 유사한 상황에서 syntax error가 났었던 것 같다) 이번에 그러면서 중간 중간 혼동해서 테이블명.을 잘못적은 부분도 같이 존재해 수정하면서 전에 안됬던 것 같다 생각됨과 동시에 굳이 그렇게 안하고 case로 null이면 0 아니면 계산 이런 식으로 하면 되겠다 생각하여 아래와 같이 작성해주었다

WITH poor_query AS(
    SELECT q_s.query_name, COUNT(q_s.result) cnt
    FROM Queries q_s 
    WHERE q_s.rating < 3 
    GROUP BY q_s.query_name
)
SELECT q.query_name,
        ROUND(sum(q.rating/q.position)/COUNT(q.result),2) quality,
        CASE WHEN p.cnt IS NULL THEN 0 
        ELSE ROUND(100*(cnt/COUNT(q.result)),2) 
        END poor_query_percentage
FROM Queries q
LEFT JOIN poor_query p ON q.query_name = p.query_name
GROUP BY q.query_name;

그 결과 왠만한 tc들은 다 통과했는데 살짝 어이없는 tc가 나타나 오답판정내었다

#test case3
| query_name | result           | position | rating |
| ---------- | ---------------- | -------- | ------ |
| null       | Golden Retriever | 1        | 5      |
| null       | German Shepherd  | 2        | 5      |
| null       | Mule             | 200      | 1      |
| Cat        | Shirazi          | 5        | 2      |
| Cat        | Siamese          | 3        | 3      |
| Cat        | Sphynx           | 7        | 4      |

문제에는 쿼리이름이 null로 되어있을 수 있다는 내용이 없었는데 존재해서 살짝 어이가 없음을 느끼면서 메인쿼리문에 WHERE q.query_name IS NOT NULL을 추가해주니 바로 통과되었다

WITH poor_query AS(
    SELECT q_s.query_name, COUNT(q_s.result) cnt
    FROM Queries q_s 
    WHERE q_s.rating < 3 
    GROUP BY q_s.query_name
)
SELECT q.query_name,
        ROUND(sum(q.rating/q.position)/COUNT(q.result),2) quality,
        CASE WHEN p.cnt IS NULL THEN 0 
        ELSE ROUND(100*(cnt/COUNT(q.result)),2) 
        END poor_query_percentage
FROM Queries q
LEFT JOIN poor_query p ON q.query_name = p.query_name
WHERE q.query_name IS NOT NULL
GROUP BY q.query_name;

런타임도 꽤 상위권에 속하는 550ms가 나왔다 쿼리에 대한 설명은 크게 새로운 부분은 없었고, 프로젝트로 인해 밤에 풀기 시작하여 작성하고 있으므로 생략하겠다.

시간이 많이 늦은 관계로 오늘은 엄청 간단히만 적고 끝내도록하겠다