오늘도 데이터분석

[문제]

https://leetcode.com/problems/nth-highest-salary/

[풀이]

- 서브쿼리 풀이 

   case 문 

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
 RETURN (
      SELECT CASE WHEN COUNT(sub.salary) < N THEN NULL 
                  ELSE MIN(sub.salary) 
              END 
      FROM(
      SELECT DISTINCT salary
      FROM employee
      ORDER BY salary DESC 
      LIMIT N ) sub
 );
END

   if 함수 

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
 RETURN (
      SELECT IF( COUNT(sub.salary) < N , NULL , MIN(sub.salary))
      FROM(
      SELECT DISTINCT salary
      FROM employee
      ORDER BY salary DESC 
      LIMIT N ) sub
 );
END

- LIMIT OFFSET 풀이

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
     SET N = N-1;
 RETURN ( 
     SELECT DISTINCT salary 
     FROM employee
     ORDER BY salary DESC 
     LIMIT 1 OFFSET N
 );
END

[문제]

Weather Observation Station 9 문제 바로 가기 

 마지막 글자가 모음으로 끝나지 않는 도시이름 추출 

도시이름은 중복되면 안됨 

[풀이]

SELECT DISTINCT city 
FROM station
WHERE city NOT REGEXP ('^[a,e,i,o,u].*')

[문제]

weather observation station 8 문제 바로가기 

첫글자, 마지막 글자가 모음인 도시 이름을 추출

도시 이름은 중복 안됨

[풀이]

SELECT DISTINCT city 
FROM station 
WHERE city REGEXP ('^[a,e,i,o,u].*[a,e,i,o,u]$')

[문제]

rank scores 문제 바로 가기 

[풀이]

mssql 윈도우함수 이용해서 풀이 

SELECT score
     , DENSE_RANK() OVER (ORDER BY score DESC) rank
FROM scores
ORDER BY score DESC

[문제]

department top three salaries 문제 바로 가기 

[풀이]

- 윈도우함수 DENSE_RANK() 이용

* 리트코드 MySQL은 윈도우함수가 안돼서 MSSQL로 풀이

SELECT Department
      ,Employee
      ,Salary
FROM (
SELECT de.name AS Department
     , em.name AS Employee
     , em.salary AS Salary
     , DENSE_RANK() OVER (PARTITION BY departmentID ORDER BY salary DESC) drank
FROM employee em
    INNER JOIN department de ON de.id = em.departmentID) rank
WHERE rank.drank <= 3

[문제]

Consecutive Numbers 문제 바로 가기 

[풀이]

- 셀프조인 2번하는 방식으로 풀이 

(1)

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
     INNER JOIN logs l2 ON L1.id = l2.id - 1
     INNER JOIN logs l3 ON l2.id = l3.id - 1
WHERE l1.num = l2.num 
  AND l1.num = l3.num

(2)

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
     INNER JOIN logs l2 ON L1.id + 1 = l2.id 
     INNER JOIN logs l3 ON l1.id + 2 = l3.id 
WHERE l1.num = l2.num 
  AND l1.num = l3.num

- MS SQL 윈도우 함수 풀이

SELECT DISTINCT co.num AS ConsecutiveNums 
FROM(
    SELECT num
     , LEAD(num) OVER (ORDER BY id) AS lead 
     , LEAD(num, 2) OVER (ORDER BY id) AS lead2
    FROM Logs) co
WHERE co.num = co.lead 
AND co.num = co.lead2

[문제]

department-highest-salary 문제 바로 가기

[풀이]

SELECT de.name Department
     , em.name Employee
     , em.salary Salary
FROM employee em
    JOIN (
      SELECT departmentID, MAX(salary) max
      FROM employee
      GROUP BY departmentID) hi ON hi.departmentID = em.departmentID 
                                AND hi.max = em.salary 
    JOIN department de ON em.departmentID = de.id

- 윈도우 함수 MAX() 이용 풀이 

SELECT d.name AS Department
     , s.name AS Employee
     , maxsalary AS Salary
FROM(
    SELECT name
     , salary
     , departmentID
     , MAX(salary) OVER (PARTITION BY departmentID) AS maxsalary
    FROM employee) s
INNER JOIN department d ON d.id = s.departmentID
WHERE s.salary = s.maxsalary;

[문제]

Delete Duplicate Emails 문제 바로가기

[풀이]

- 서브쿼리 

DELETE 
FROM person
WHERE id NOT IN (
    SELECT sub.min_id
    FROM (
        SELECT email, MIN(id) min_id
        FROM person 
        GROUP BY email
    )sub)

DELETE, UPDATE는 하위 절에서 동일한 테이블 사용 못함. 그래서 sub테이블 만들어줌

참고 : https://dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html

- JOIN

DELETE p1
FROM person p1
    INNER JOIN person p2 ON p1.email = p2.email
WHERE p1.id > p2.id