CodeKata

SQL

122. Delete Duplicate Emails

※ For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.
After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.

• 작성한 쿼리

DELETE
  p1
FROM
  Person p1
  JOIN Person p2
  ON p1.email = p2.email
WHERE
  p1.id > p2.id
;

참고할 만한 다른 풀이

• GROUP BY, HAVING

DELETE P.*
FROM Person AS P, (
    SELECT email, min(id) AS minId
    FROM Person
    GROUP BY email HAVING count(*) > 1
) AS Q
WHERE P.email = q.email AND id > Q.minId

• WINDOW FUNCTION

with r as (
  select
    id
    , row_number() over (
      partition by email 
      order by id
    ) as rnk 
  from
    Person
)
delete
from 
  Person p
where
  p.id in (
    select
      id 
    from
      r 
    where 
      id =p.id
      and rnk>1
    )

• LEFT JOIN

DELETE
  p1
FROM 
  Person p1 
  LEFT JOIN (
    SELECT
      MIN(id) id_min
    FROM 
      Person
    GROUP BY
      email
    ) AS p2
  ON p1.id = p2.id_min
WHERE
  p2.id_min IS NULL

• Cartesian Product

delete p1 from person p1,person p2 
where p1.email=p2.email and p1.id>p2.id;

더 알아보기

123. Second Highest Salary

• 작성한 쿼리

WITH dense_rank_salary AS (
  SELECT
    salary
    , DENSE_RANK() OVER (
      ORDER BY salary DESC  
    ) AS rnk
  FROM
    Employee
)
, second_rnk AS (
  SELECT
    DISTINCT salary
  FROM
    dense_rank_salary
  WHERE
    rnk = 2
)
SELECT
  IF(count(*)>=1, (TABLE second_rnk), NULL) AS SecondHighestSalary
FROM
  Employee
;

참고할 만한 다른 풀이

• MAX

select 
  Max(salary) as SecondHighestSalary
from 
  employee 
where 
  salary not in (
    select
      max(salary)
    from
      employee
    )
;

SELECT  MAX(SALARY) AS SecondHighestSalary FROM EMPLOYEE WHERE SALARY <>(SELECT MAX(SALARY) FROM EMPLOYEE);

SELECT MAX(salary) AS SecondHighestSalary 
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

• LIMIT, OFFSET

SELECT 
    (SELECT DISTINCT salary
     FROM employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET 1) AS secondHighestSalary

If you wondering why this not work:

Select distinct Salary as SecondHighestSalary
from Employee order by salary desc
limit 1 offset 1;

The difference lies in how SQL handles empty result sets. In the single SELECT, if no row is found, nothing is returned.
In the nested SELECT, when the subquery returns no result, it explicitly returns NULL, which the outer query then displays.

• IFNULL

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

• COALESCE

-- 1:
select coalesce(
    (select max(salary) from employee 
        where salary < (select max(salary) from employee)), null) as SecondHighestSalary;

-- 2:
select coalesce(
    (select distinct salary from employee
        order by salary desc limit 1, 1), null) as SecondHighestSalary;

Python

51. 푸드 파이트 대회

• 작성한 코드

def solution(food):
    answer = ''
    for i in range(1, len(food)):
        answer += str(i) * (food[i]//2)
    return answer + '0' + answer[::-1]

참고할 만한 다른 풀이

def solution(food):
    answer ="0"
    for i in range(len(food)-1, 0,-1):
        c = int(food[i]/2)
        while c>0:
            answer = str(i) + answer + str(i)
            c -= 1
    return answer

def solution(food):
    answer = ''
    for i,n in enumerate(food[1:]):
        answer += str(i+1) * (n//2)
    return answer + "0" + answer[::-1]

def solution(food):
    first = ''.join(str(foodNumber) * (quantity // 2) for foodNumber, quantity in enumerate(food))
    second = first[::-1]
    answer = first + '0' + second
    return answer

라이브 세션

머신 러닝 빌드업 3회차
머신 러닝 특강: 분류분석

SDL

f1-score 및 군집화 관련

회고

• 머신 러닝 배운 거 복습하는 게 생각보다 시간이 오래 걸려서 오전에 계획했던 통계학 복습은 하지 못해 아쉬움
  • 내일 일정이 좀 비니까 통계학 복습 꼭 하기!
• 팀 프로젝트가 점점 다가오는데 너무 걱정된다…
  • 어떤 데이터에 어떤 분석을 쓰면 되는지, 어떤 검증을 하면 되는지 대략적인 흐름을 이해해 두자