2024-01-20~2024-01-21

SQL 코드카타

 

619. Biggest Single Number(SQL)(with, count, max)

https://leetcode.com/problems/biggest-single-number/description/

가장 큰 단일 숫자를 출력하는 문제이다. 가장 큰 숫자가 단일숫자가 아니라면 null값으로 표시한다.

#이렇게 하면 아마 잘 안될 듯하여 테스트 삼아서 해봤는데
SELECT m_s.num
FROM MyNumbers m_s
WHERE COUNT(m_s.num) = 1

#역시 Invalid use of group function 런타임에러 남

GROUP BY 쓰고 HAVING에서 COUNT 사용, 그리고 MAX는 살짝 왜 안되는 거지 싶긴 한데(사실 count도 비슷한 것 같긴 하지만) 집계함수 그냥 임의로도 그룹 안 짓고 좀 되면 안되나..?(이게 젤 헷깔리는 부분인 것 같다 집계함수쪽에서) →아 초반에도 헷깔려 했는데 where에서 집계함수를 못썻다! (컬럼에 쓰는 것은 그냥 쓸 수는 있지만 의도와 조금 다를 가능성이 있다)

그래서 지금 방식이 두 가지가 떠올랐다 각각 임시 테이블로 해서 각각 최고 크기 구하는 것과 한 개 짜리 구하는 것 만들어서 join하는 방식, 그냥 먼저 갯수가 한개인 것들 필터링해서 최대값을 가져오는 방식이다 둘 다 그리 복잡해 보이지는 않아서 둘 다 시도해보려고 한다. →처음에는 이해하기를 제일 큰 값을 가져온는데 가장 큰값이 여러개면 null로 리턴하는 것으로 해석했었다

서브쿼리 이용하는 방식

SELECT MAX(m.num) num
FROM MyNumbers m
WHERE COUNT(m.num) = 1
#이러면 where절에 count 못써서 에러
#HAVING으로 쓰면 전체 세어버려서 원하는 대로 안됨
#희안하게 Max는 그냥 써짐

결국 다른 방식이 없어서 서브쿼리사용

SELECT MAX(m.num) num
FROM MyNumbers m
WHERE (SELECT COUNT(m_s.num) FROM MyNumbers m_s 
        WHERE m.num = m_s.num) = 1

 

우선 임시테이블 두개 만들어서 합치는 방식

WITH single AS (
    SELECT m_s.num
    FROM MyNumbers m_s
    GROUP BY m_s.num
    HAVING COUNT(m_s.num) = 1
),
max_n AS (
    SELECT m_m.num
    FROM MyNumbers m_m
    GROUP BY m_m.num
    HAVING MAX(m_m.num) = m_m.num
)
SELECT MAX(s.num) num
FROM max_n m
LEFT JOIN single s ON m.num = s.num

이것으로 통과 되긴했는데 불필요한 부분이 보이는 듯 해서 개선 가능할 듯하여 좀 더 개선을 시도해 보았다

#메인쿼리 부분 이렇게 해주니 한개 나오는게 아니라 여러개 나와서 
#max_n부분이 똑바로 안되는 것 같아서 확인해보겠음
SELECT m.num
FROM max_n m
JOIN single s ON m.num = s.num

| num |
| --- |
| 8   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
#아... 생각해보니 num 그룹별로 젤 큰것 했었으니 의미없이 존재해버렸던 것

그래도 초반 with 두개 해서 join하는 것과 효율을 한번 비교해볼려고 해볼려했으나(한개만 선언해서 바로 max하는게 빠를 것이 당연해보였지만 그냥 궁금해서 비교해보고 싶었었다)

WITH single AS (
    SELECT m_s.num
    FROM MyNumbers m_s
    GROUP BY m_s.num
    HAVING COUNT(m_s.num) = 1
),
max_n AS (
    SELECT MAX(m_m.num) num
    FROM MyNumbers m_m
)
SELECT m.num
FROM max_n m
JOIN single s ON m.num = s.num

하지만 이렇게 해주니 생각해보면 당연하긴 한데 제일 큰 값이 여러개라 single에 없으면 아무값도 나오지 않는게 당연했다 그래서 비교를 해볼 수 조차 없을 듯하여 그냥 바로 하는 것으로 마무리 지었다.

WITH single AS (
    SELECT m_s.num
    FROM MyNumbers m_s
    GROUP BY m_s.num
    HAVING COUNT(m_s.num) = 1
)
SELECT MAX(s.num) num
FROM single s

 

1045. Customers Who Bought All Products(SQL) (Having 서브쿼리)(NOT EXISTS, EXISTS와 관련하여 자세한 내용, IN과 비교, INNER JOIN과 비교→성능 효율 판단하기 좋은 정보)

https://leetcode.com/problems/customers-who-bought-all-products/

모든 상품을 구매한 고객의 id를 출력하는 문제이다.

SELECT c.customer_id
FROM Customer c
GROUP BY c.customer_id
HAVING COUNT(DISTINCT c.product_key) = (SELECT COUNT(p.product_key) FROM Product p);

처음에 생각한게 파이썬 처럼 customer_id별로 리스트처럼 만들고 거기에 상품 목록에 있는 데이터와 비교해서 그 리스트에 포함되지 않는 데이터가 존재하면 그 id는 제외하는 식으로 해볼 수 있나 생각했는데 함수를 뭐라고 검색해야할지 모르겠어서 다른 사람들은 기발한 생각가지고 한게 있나 살펴봤는데 어렴풋 들던 생각과 비슷하게 그냥 전체 product테이블의 product_key갯수와 customer_id에 대해 그룹을 짓고 각 그룹별로 distinct product_key의 갯수를 비교하여 같은 id에 대해서만 출력해주는 방식을 대부분 한 듯했다(그렇게 많이 살펴보지는 않았고 한 3, 4개 정도 살펴봤는데 전부 이 방식이였다.) 혹시 내가 모르는 함수같은게 또 처음 아이디어대로 하는 방식이 있을까 생각하여 검색해보려했지만 검색할만한 키워드가 떠오르지 않아서 chatGPT를 통해 좀더 상세히 설명해주고 작성하게 하였더니 아래와 같은 쿼리를 작성해주었다

SELECT DISTINCT c.customer_id
FROM Customer c
WHERE NOT EXISTS (
    SELECT p.product_key
    FROM Product p
    WHERE p.product_key NOT IN (
        SELECT c2.product_key
        FROM Customer c2
        WHERE c2.customer_id = c.customer_id
    )
);

이 쿼리에 대해 설명을 하자면 먼저 서브쿼리안에 또 서브쿼리가 있는 이중서브쿼리? 느낌이라 복잡하여 먼저 서브쿼리에 대해서 먼저 설명하고 전체 쿼리에 대해 다시 설명하는 방식으로 설명을 적으면 먼저 가장 아래에 있는 서브쿼리(not in 절 부분) customer테이블에서 product_key를 출력하는 쿼리인데 대신 메인쿼리의 customer_id에 해당하는 customer_id에 대해서만 product_key를 출력하여 한 개의 열로 이루어진 테이블을 만들어낸다(약간 리스트 같은 느낌)(다르게 의미상으로만 말하자면 각 customer_id에 대하여 구매한 product_key목록이라고 생각하면 될 것 같다) 그리고 그 위 NOT EXISTS절의 서브 쿼리는 product테이블에서 product_key를 출력하는데 앞서 구했던 해당 customer가 구매한 상품에 속하지 않는 상품의 목록을 출력해준다(의미상으로 말하자면 각 customer가 구매하지 않은 상품목록이라고 생각하면 될듯하다) 그래서 이제 서브쿼리부분에 대해선 설명했으니 메인쿼리에 대해 설명하자면 customer테이블에서 구매하지 않은 product_key가 한개도 존재하지 않는 데이터에 대해서만 필터링하여 중복되지 않은 customer_id를 출력하는 쿼리이다. (EXISTS는 연산자의 일종이라 생각이 된다 ~에 존재한다로 참거짓을 판별하여 참거짓을 리턴해주는 듯하다.)

결과적으로 EXISTS는 서브쿼리 연산자라고 주로 불리는 듯하며, 서브쿼리에서 반환된 결과가 비어 있지 않은 경우에 대해서만 외부에서 해당행을 출력해준다.

EXISTS에 대하여 IN, inner join에 대해 비교
→결론적 보통 IN과 EXISTS 둘 다 사용 가능한 경우 EXISTS가 빠른 경우가 더 많다
EXISTS는 WHERE에 있지만 select를 하고 그다음 순서로 파악한다는 듯하다
→inner join과 비교해서 중복되는 데이터가 많을 경우 EXISTS를 이용한 서브쿼리 사용이 더 빠를 수도 있다

(참고한 곳)

 

1731. The Number of Employees Which Report to Each Employee(SQL)

https://leetcode.com/problems/the-number-of-employees-which-report-to-each-employee/description/

관리자의 id와 이름, 관리자에게 보고하는 인원수와 그 평균나이(한자리수까지 반올림하여 표현)을 출력하는 문제이다.

우선 처음에 EXISTS 써볼까 하는 생각에 쿼리를 작성하면서 생각하니 reports_count와 average_age를 구해줘야 해서 저 방식대로 해줄려면 서브쿼리를 또 추가로 써줘야만 가능할 듯해서 일단은 가능한 것으로 구현하기 위해 서브쿼리를 많이 써서 해주었다

SELECT e.employee_id, e.name, 
        (SELECT COUNT(e_s.employee_id) FROM Employees e_s
        WHERE e.employee_id = e_s.reports_to) reports_count
        ,(SELECT ROUND(AVG(e_s2.age)) FROM Employees e_s2
        WHERE e.employee_id = e_s2.reports_to) average_age
FROM Employees e
WHERE EXISTS(SELECT distinct e2.reports_to
            FROM Employees e2
            WHERE e2.reports_to IS NOT NULL
            AND e.employee_id = e2.reports_to)
ORDER BY e.employee_id

join으로도 구현을 시도해보았다

WITH manager AS (
    SELECT e2.employee_id,
            e2.reports_to,
            COUNT(e2.employee_id) reports_count,
            ROUND(AVG(e2.age)) average_age
    FROM Employees e2
    WHERE e2.reports_to IS NOT NULL
    GROUP BY e2.reports_to
)
SELECT e.employee_id, e.name,
        m.reports_count,
        m.average_age
FROM manager m 
JOIN Employees e ON m.reports_to = e.employee_id
ORDER BY e.employee_id

그렇게 한 것으로 우선 with로 먼저 Employee테이블을 reports_to IS NOT NULL인 데이터를 가져와 reports_to로 그룹을 지어주고 employee_id, reports_to, COUNT(e2.employee_id), ROUND(AVG(e2.age))를 출력한 테이블을 선안하고 그 테이블과 employee테이블을 m.reports_to = e.employee_id로 join해주고 e.employee_id, e.name, m.reports_count, m.average_age를 출력하는 쿼리이다.

select m.employee_id, m.name, count(e.employee_id) as reports_count, round(avg(e.age)) as average_age
from Employees e join Employees m
on e.reports_to = m.employee_id
group by employee_id
order by employee_id;

그리고 group 랑 order에서 어디테이블의 컬럼을 쓰는지 안적어줬는데 저 두개는 컬럼alias로 해도 됬었기에 아마 생략해준듯하며 m.employee_id를 의미하는듯 하다.

 

1789. Primary Department for Each Employee(SQL)(여러 방식 풀이 시도, union, 조건에 in, EXISTS, 서브쿼리 조건, join, with)

https://leetcode.com/problems/primary-department-for-each-employee/description/

각 직원의 id와 primary department를 출력하는 문제이다.

SELECT *
FROM Employee e
GROUP BY e.employee_id
HAVING e.primary_flag = 'Y'

처음에 속한 부서가 한개인 건 직원id로 그룹한 뒤에 Having으로 갯수 1인 것으로 필터링해주면 되었는데 그룹을 하고나서 Y인 것만 골라서 하기엔 안되지 않나라는 생각에 테스트해보았는데 역시 내 예상대로 되지 않았다 각 그룹별로 첫행에 대해서 판단해버리기 때문에 첫행이 Y일때만 출력되는 것을 확인하였다

SELECT e.employee_id, e.department_id
FROM Employee e
GROUP BY e.employee_id
HAVING COUNT(e.department_id) = 1
UNION
SELECT e2.employee_id, e2.department_id FROM Employee e2 
WHERE e2.primary_flag = 'Y'

그럼 그냥 1개인 얘들 데이터에 Y인 행에 대해서 결과만 추가해주면 되지 않나라는 생각이 들어서 union을 이용해 해주었더니 잘 해결되었다.  

#in을 써서 해두신 분이 있길래 살짝 내가 표시했던 방식으로 수정한것
#그리고 굳이 메인쿼리의 grouping은 할 필요가 없을듯하여 제외했음
SELECT e.employee_id, e.department_id
FROM Employee e
WHERE e.primary_flag = 'Y' OR e.employee_id in (
    SELECT e2.employee_id
    FROM Employee e2
    GROUP BY e2.employee_id
    HAVING COUNT(*) = 1
)

#참고한 원본 쿼리
SELECT employee_id, department_id
FROM Employee
WHERE primary_flag = 'Y' OR employee_id in (
    SELECT employee_id
    FROM Employee
    GROUP BY employee_id
    HAVING COUNT(*) = 1
)
GROUP BY employee_id

쿼리의 구성을 대강 설명하면 where을 통해 조건을 primary_flag = 'Y'와 직원id가 서브쿼리의 결과물에 있는 id인가 를 확인하는 방식이며, 서브쿼리는 직원id로 그룹지어서 해당되는 데이터 값이 1개뿐이면 직원id를 출력하는 쿼리이다.

이것을 in 대신 EXISTS를 사용하여 작성해주면 아래와 같으며, 차이점이라면 EXISTS는 메인쿼리의 데이터에 대해 연결해주어 거기에 대해서만 확인되게 연결해주어야한다.

SELECT e.employee_id, e.department_id
FROM Employee e
WHERE e.primary_flag = 'Y' OR EXISTS (
    SELECT e2.employee_id
    FROM Employee e2
    GROUP BY e2.employee_id
    HAVING COUNT(*) = 1 AND e.employee_id = e2.employee_id
)

그리고 join으로도 될 것 같아서 직접 짜보기보다는 join으로 하신 분 있나 확인하는 식으로 했다(사실join으로 하는게 성능은 더 안 좋을 듯해서 그리고 깔끔하게 짤 아이디어가 떠오르지 않아서 다른 분들 한 것 중 join방식으로 한 것을 참고하였다)

#내방식대로 살짝 수정한 것
WITH cnt1 AS (
    SELECT e2.employee_id, COUNT(e2.department_id) cnt FROM Employee e2
    GROUP BY e2.employee_id
    )
SELECT e.employee_id, e.department_id
FROM Employee e
JOIN cnt1 c1 ON e.employee_id = c1.employee_id
WHERE e.primary_flag = (CASE WHEN c1.cnt = 1 THEN e.primary_flag ELSE 'Y' END);
#END안적어주면 error남

#원본쿼리
with cte1 as (select employee_id, count(department_id) as cnt from Employee group by employee_id)
select e.employee_id, e.department_id
from Employee e
join cte1 c1
on e.employee_id = c1.employee_id
where primary_flag = (case when c1.cnt = 1 then e.primary_flag else 'Y' end);

결론적으론 그렇게 큰 데이터에 대해서 안해주는 것인지 union을 통해 합쳐주는 것이 가장 성능이 빨라 보였고(그런데 빠르다 좋다 판단하는게 조금 불확실한 점은 있는 듯하다) 그뒤로는 조금 애매했는데 일단 제출 runtime이나 beats를 보면 in으로 한 것이 Exists로 한 것보다 좀 더 좋았던 것 같다. 그리고 join이 가장 효율이 안좋게 나왔던 것 같았다.

 

610. Triangle Judgement(SQL) (각 행에 대하여 최대값Greatest, 각 행에 대한 합(+사용), case when, if, *하여 전체 테이블 컬럼에 추가로 컬럼표시)

https://leetcode.com/problems/triangle-judgement/

세개의 선분에 대한 길이를 주는데 이것으로 삼각형을 만들 수 있는지 출력하는 문제이다.

(추가로 삼각형이 될 수 있을려면 만족해야하는 조건은 가장 긴 길이가 나머지 두 개의 길이의 합보다 작아야한다.)

아래는 우선 각 행에 대하여 합을 구하거나 최댓값을 구하고 싶은데 관련하여 해본적이 없어 하는 법에 대해 찾아본 결과를 적어두었다

#각 행에 대하여 가장 큰 값 구하는 방식 1
SELECT GREATEST(x, y, z) AS max_value
FROM Triangle t;

#각 행에 대하여 가장 큰 값 구하는 방식 2
SELECT
  CASE
    WHEN x >= y AND x >= z THEN x
    WHEN y >= x AND y >= z THEN y
    ELSE z
  END AS max_value
FROM Triangle t;

#각 행에 대해서 합 구하는 방법
SELECT x + y + z AS row_sum
FROM Triangle t;

WITH tri_max_update AS (
    SELECT t.x, t.y, t.z,
            GREATEST(x, y, z) max_len
    FROM Triangle t
)
SELECT tmu.x, tmu.y, tmu.z,
        CASE WHEN (tmu.x + tmu.y + tmu.z)> 2*tmu.max_len THEN "Yes"
        ELSE "No" END triangle
FROM tri_max_update tmu;

그래서 둘을 활용하여 먼저 각 x,y,z값과 그중 가장 큰 값을 출력하는 tri_max_update테이블로 선언하였다 그런 뒤 그 테이블에 대하여 각 x, y, z값과 삼각형의 조건을 만족하는지에 따라 Yes와 No를 출력하는 triangle컬럼을 추가해주었다 (추가로 삼각형의 조건을 주어진 세 길이의 합과 가장 큰 길이 *2를 비교하여 세 길이의 합이 더 긴 경우에 대해 삼각형이 된다고 판단해주었는데 수학적으로 조금 친숙하신? 분들은 바로 이해가 가셨겠지만 조금 더 자세히 적어보자면 세 길이의 합은 x+y+z이고 가장 긴 길이가 z라고 할 때 z**2와 비교를 하게 된다 이는 이항을 해서 z를 정리해주면 x+y와 z를 비교하는 것과 동일하기 때문에 번거롭게 추가로 세변의 길이의 합-가장 긴 길이와 가장 긴 길이를 비교하도록 하지 않고 바로 위와 같은 방식으로 확인해주었다)

다른 분들은 어떻게 했나보니 나보다 빠른 runtime을 가지신 분들 것을 보니 따로 최대값을 구해주지 않고 두개의 길이의 합과 나머지 하나를 비교해서 3경우에 모두 만족하는 경우 삼격형이다라는 방식을 사용한 듯하였다.

SELECT x,y,z, 
    CASE
        WHEN x + y > z
        AND x + z > y
        AND z + y > x
        THEN "Yes"
        ELSE "No"
    END AS triangle
FROM Triangle;

SELECT
    *,
    IF (
        x + y > z
        AND x + z > y
        AND y + z > x,
        'Yes',
        'No'
    ) AS triangle
FROM
    Triangle;

#다른분이 한 것이긴한데 한줄로 간략히 쓰면
SELECT *,
       IF(x < y + z AND y < x + z AND z < x + y, 'Yes', 'No') triangle
FROM Triangle;

그리고 내가 익숙히 써왔던 case when의 방식과 if의 방식으로 쓰신 분이 있었는데 if는 익숙하지 않아 무슨 차이가 있을 까 싶어 찾아보았지만 별차이는 없는 것 같았다(단순히 case는 여러 조건과 그 경우에 따른 값을 줄 수 있고, if는 하나의 경우에 대해 그럴경우, 아닐경우에 대해서만 값을 표시할 수 있다는 정도? 위와 같이 하나의 조건에 대하여 그럴 경우 아닐경우로 표시하는 경우는 별 차이가 없는 듯했다)

그리고 추가 여담으로 if로 쓰신 분 방식에 *, 컬럼을 해줘서 추가하는 방식도 가능하구나 알게되었다(사실 잘 쓰진 않을 듯하다 편리해보이지만 튜터님께 들었던 바로는 *로 하면 무슨 컬럼들이 있는지 직접 일일히 확인해야하는 불편함이 발생할 수 있다고 가능하면 모든 열을 표시하더라도 직접 적어주는 것을 권장하셨기에 그 이야기를 들은 후로 가능하면 실천해주고 있기 때문이다.)

 

180. Consecutive Numbers(SQL) (3개 join, 3개 연속하여 확인)

https://leetcode.com/problems/consecutive-numbers/description/ 

적어도 3번이상 연속한 숫자를 출력하는 문제이다.

SELECT DISTINCT l1.num ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l1.id+1 = l2.id
JOIN Logs l3 ON l1.id+2 = l3.id
WHERE l1.num=l2.num AND l1.num = l3.num

처음에는 4번이상 된 부분에 대해 중복되지 않게 해야지 생각은 해두었지만 우선 3번이상 연속하는지 확인하는 작업이 우선적으로 되야해서 확인한다고 DISTINCT를 빼었따가 제출할 때 4번 연속할 시에 3번을 기준으로 잡았기에 중복으로 2번 나와 불통하였기에 중복을 제외하기 위해 DISTINCT를 사용하였다

쿼리에 대해서 간단히 설명하면 같은 테이블 logs에 대하여 join을 해주는데 id에 대하여 l1을 기준으로 l1.id에 대해 1이라면 l2.num은 2번의 num, l3.num은 3번의 num이 나타날 수 있다록 매칭 시켜주는 방식이 l2.id=l1.id+1로, l3.id=l1.id+2로 join해주었다 그런 뒤 3값이 같은 경우에 대해 출력해주었다.

추가로 DISTINCT 성능관련해서 (comparte to group by) 찾아보았는데

간단하게는 유니크하게 중복하지 않도록 값을 가져올려는 의도라면 distinct가 더 빠르다 group by는 정렬이 추가로 포함되기에 더 느리며 포함된 데이터가 많다면 좀 더 부하를 줄 것으로 생각된다. (그리고 join과 정렬 둘다 해줘야하는 경우라면 가능한 먼저 index를 가지고 정렬을 먼저 해주고 join을 해주는게 좋은듯하다 join을 한 결과에는 indext가 없으므로 항상 using filesort라는 가장 느린 성능을 보여주는 부분이라고 본 것 같은데 이것을 하게 된다고 한다.)