2024-01-26

SQL 코드카타

 

196. Delete Duplicate Emails(SQL) (delete, 단순 출력이 아닌 데이터 자체에서 삭제시키는 것)

https://leetcode.com/problems/delete-duplicate-emails/description/

데이터 테이블에서 중복되는 이메일에 대해 최소id만을 남기고 나머지는 모두 삭제하는 문제이다.

SELECT p.id, DISTINCT p.email
FROM Person p

#You have an error in your SQL syntax; check the manual that corresponds 
#to your MySQL server version for the right syntax to use near 'DISTINCT p.email
#FROM Person p' at line 2

우선 이런 식으로 단순하게도 되나 싶어 해보았는데 당연히 안되었다

그외 무조건 출력이 원본 테이블로 나오길래 뭔가 이상해서 문제를 다시 읽어보니 select로 골라서 보여주는 것이 아닌 delete를 써라고 하였다

WITH condition AS (
    SELECT p.email, MIN(p.id) AS min_id
    FROM Person p
    GROUP BY p.email
    HAVING COUNT(*) > 1)

DELETE p
FROM Person p
JOIN condition t ON p.email = t.email AND p.id <> t.min_id

#leetcode에선 temp가 색 흰색으로 나와서 함수없구나 하고 썼는데 
#노션에서는 또 temp쓰니까 파랗게 되서 condition으로 써줬는데 leetcode에선 또 파랗게 변함

우선 삭제할 때 필터로 쓸 조건용으로 테이블을 하나 만들어주는데email로 그룹을 나눠서 email과 그 그룹에 해당하는 갯수가 1개 보다 많이 있는 그룹의 가장 작은 id를 출력하는 테이블을 만들어준다. 그뒤 원본 테이블과 email을 공통컬럼으로 하는데 id가 min_id와 다른 것을 조건으로 join해준뒤 해당하는 부분들을 person으로 부터 삭제해준다

또 추가로 하다가 살짝 이해된 부분이 혼동이와서 확인하다가 떠오른 아이디어인데 Having을 빼고 단순히 min을 바로 구해서 동일하게 join해줘도 같은 결과가 나올듯하다

WITH temp AS (
    SELECT p.email, MIN(p.id) AS min_id
    FROM Person p
    GROUP BY p.email)

DELETE p
FROM Person p
JOIN temp t ON p.email = t.email AND p.id <> t.min_id

쿼리는 위와 같으며 아마 이렇게 해주는게 한과정을 덜 거치게되니 더빠르지 않을까 생각이 드는데 튜터님께 질문하여 확인해봐야겠다

 

176. Second Highest Salary(SQL) (null글자로 표시여부, case when, ifnull, from 서브쿼리, dense_rank, where서브쿼리, limit offset, 두번째로 큰값 출력)

https://leetcode.com/problems/second-highest-salary/description/

두번째로 높은 salary 액수를 출력하는 문제이다.

SELECT CASE 
            WHEN s.salary IS NULL THEN "null"
            ELSE s.salary 
        END SecondHighestSalary
FROM(   
    SELECT e.id, e.salary,
            DENSE_RANK() OVER(ORDER BY e.salary DESC) rk
    FROM Employee e
    ) s
WHERE s.rk=2

이런식으로 하니 뭔가 버그 난 것처럼 내가 추가해준 케이스 3까지 포함해서 1과 3은 결과가 맞게나왔는데 틀렸다고표시되었다 그리고 출력으로 null이 표시 되지 않는다

SELECT IFNULL(s.salary, NULL) SecondHighestSalary
FROM(   
    SELECT e.id, e.salary,
            DENSE_RANK() OVER(ORDER BY e.salary DESC) rk
    FROM Employee e
    ) s
WHERE s.rk=2

SELECT s.salarySecondHighestSalary
FROM(   
    SELECT e.id, e.salary,
            DENSE_RANK() OVER(ORDER BY e.salary DESC) rk
    FROM Employee e
    ) s
WHERE s.rk=2

뭔가 where부분에서 딱 저렇게 할려니 null로 안적히고 그냥 아무것도 리턴 되지 않은 형태로 나오는 것 같은데 잘 모르겠다.. 이 부분은 질문을 드려 확인 받아야 할 것 같다

SELECT MAX(e.salary) SecondHighestSalary
FROM Employee e
WHERE e.salary < (SELECT MAX(e2.salary) FROM Employee e2)

해결 방식을 모르겠어서 다른 사람들이 한 것을 참고하여 따라해보고 마무리 지었다

서브쿼리를 이용해 최대값보다 작은 salary에 대해서만 가져와서 그중에서 최대값을 출력해주는 방식이다.

SELECT(SELECT DISTINCT
    Salary 
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1)AS SecondHighestSalary;

Limit는 단순히 몇개 출력한다만 알고있었는데 이번에 limit offset에 대해 다시 찾아보다 보니

기존에 쓰던 limit 숫자 로 숫자 개 만큼 출력하는 방식 외에도 limit 숫자1, 숫자2하면 숫자1번째 부터 숫자2개 출력하기 할 수 있다

그리고 offset은 limit 숫자1, 숫자2 같이 쓰는 역할 인듯한데 limit 숫자2 offset 숫자1하면 동일한 결과가 나온다고 한다

그리고 성능에 관련하여 별다른 조치를 취하지않고 그냥 바로 offset을 사용하게 되면(limit만으로 offset 주는 경우도 동일) 출력은 offset부터 limit에 준 값대로 갯수가 나오지만 실제 연산은 offset이전까지 데이터에 대해서도 다 처리는 해주지만 출력에만 나오지 않을 뿐이라 offset이 커지면 연산이 느려진다고 한다

그래서 offset이 클 경우 no offset 방식으로 하는 것이 훨씬 좋다는 듯 하다 where절등으로 미리 offset에 해당되게 데이터를 줄여두면 그렇게 사용가능하다는 듯하다 그외 추가로 최적화 방법이 있는 것 같기도 하지만 바로 눈에 들어오지는 않아서 다음에 필요할 경우 다시 더 자세히 알아보기로 하고 마무리를 지었다

 

1484. Group Sold Products By The Date(SQL) (group_concat, 각 조건(그룹)별로 해당하는 값 하나로 합치기)

https://leetcode.com/problems/group-sold-products-by-the-date/description/

각 날짜별로 팔린 상품 종류수와 종류의 이름들을 출력하여 날짜순으로 정렬하는 문제이다.

SELECT 
        a.sell_date, 
        COUNT(DISTINCT a.product) num_sold,
        GROUP_CONCAT(DISTINCT a.product) products
FROM 
        Activities a
GROUP BY 
        a.sell_date
ORDER BY
        a.sell_date

전에 저런 식으로 한번에 하는 concat이 있었던 것 같아서 다시 찾아보니 group_concat이라고 있었다 우선 GROUP_CONCAT은 우선 그룹별로 묶어주기위한 기준을 정해주기 위하여 group by가 없어도 작동은 가능하지만 그냥 첫행에 대하여 모든 ( )안에 넣어준 컬럼의 값들을 다 하나로 적어서 출력하기에 거의 필수적으로 쓰여야만 원하는 대로 기준별로 하나로 합쳐 보여줄 수 있는 것 같다. 

추가로 GROUP_CONCAT의 옵션에는 GROUP_CONCAT(컬럼 ORDER BY 컬럼 SEPARATOR ‘구분기호’ ) 이런 식으로 쓰이는 듯하다 (ORDER BY 와 SEPARATOR는 생략이 가능하며, SEPARATOR는 생략시 기본 구분기호를 , 로 사용한다) ORDER BY는 말 그대로 합쳐줄 값을 그 값 내에서 정렬해서 입력할 수 있는 듯하다.

추가로 의문이 드는 점이 ,로 쓸 때도 separator ‘,‘로 적어주는 것이 좋을지가 궁금하다

그리고 GROUP_CONCAT안의 order by에 해당 컬럼말고 다른 컬럼 기준으로도 정렬이 가능은 한지 궁금하다(아마 안될것 같기는 하다 말이 안되는 듯하여서)

 

오늘은 드디어 git hub 특강을 들을 수 있었다 그래서 드디어 어느 정도 제대로 git hub를 활용할 수 있을 듯하다 그래서 배운 내용을 혼자서 복습하면서 확인하는 과정을 거쳤다(내용 관련해서는 실습위주 강의여서 따라하면서 적기까지는 진행속도상 적을 수 없어서 못적어서 나중에 녹화가 다시 올라오면 보고 그것을 토대로 내용을 적어서 정리해야할 듯하다)

오늘 SQL 코드카타는 3문제 전부 써본적이 없던 생소한 함수들을 쓰게 되었는데 그탓에 예상보다 푸는데 시간이 좀 소요되었다

그리고 데이터 전처리 및 시각화 (,통계적 검증?은 튜터님이 과제내용에 해두셔서 내가 직접해야할 부분은 없었긴하다) 과제를 어제 다 해두었던 것을 다시한번 확인하고 제출하였다

그리고 통계 강의를 다시 들으면서 복습을 하였으나 오늘 그동안 쌓였던 피로가 갑자기 터진 것인지 몸상태가 별로 안좋고 너무 집중이 안되어 생각보다 복습진도를 너무 못냈다

오늘은 너무 상태가 별로라 판단되어 일찍 쉬러 가야할듯하여 여기서 평소보다 빠르게 마무리 짓도록 하겠다