12 Fri

데이터 분석을 위한 고급 SQL

서브쿼리

• 쿼리 안에 들어가는 쿼리

• SELECT, FROM, WHERE절에 다 사용 가능

  • SELECT는 누적합을 구할 때 많이 사용

FROM절 서브쿼리

SELECT daily_stats.week, AVG(daily_stats.incidents_daily)
FROM (
    SELECT week, date, COUNT(incident_id) AS Incidents_daily
    FROM crimes
    GROUP BY week, data
    ) daily_stats
GROUP BY daily_stats.week

• 각 주와 날짜 별로 범죄 발생을 정리한 테이블이 존재한다.

• 주와 날짜로 정리한 테이블에서 다시 날짜 별로 범죄 발생 건수를 보는 쿼리이다.

• 이 때 하루 평균 범죄 발생 건수를 알기 위해 AVG를 썻지만, 특정 날짜에 대한 범죄 발생이 없을 경우 이를 0으로 간주하는 것이 아니라 NULL 데이터로 인식하기 때문에 평균을 구할 때 이를 유의해야 한다.

  • EX) 1+ 2+ 3+0/4 이 아닌 1+2+3/3 으로 계산하기 때문에 유의

WHERE절 서브쿼리

SELECT *
FROM Crimes
WHERE date = (SELECT MIN(date) FROM Crimes)

SELECT *
FROM Crimes
WHERE date IN (SELECT date FROM Crimes
    ORDER BY date DESC LIMIT 5)

• 1~3 : date가 = 로 비교되고 있으므로 서브 쿼리문의 결과가 하나여야 한다.

• 5~8 : 5개의 결과물이 나오므로 IN 을 사용하는 모습

• MIN(date) : 가장 오래된 날짜, MAX(date) : 가장 최신 날짜

HackerRank TopEarners

select salary * months, count(*)
from employee
where salary * months =
    (select max(salary * months) from employee )
group by salary * months

또는 having 절을 이용해서

select salary * months as earnings, count(*)
from employee
group by earnings
having earnings =
    (select max(salary * months) from employee)

HackerRank Challenges

select h.hacker_id, h.name, count(*) cnt
from hackers as h
    inner join challenges as c on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having cnt = (select max(cnt)
            from (
                select hacker_id, count(*) cnt
                from challenges
                group by hacker_id ) sub)
    or cnt in (select cnt
            from (
                select hacker_id, count(*) cnt
                from challenges
                group by hacker_id ) sub
            group by cnt
            having count(*) = 1)
order by cnt desc, h.hacker_id

With

• 위 코드를 보면 반복되는 코드가 반복된다. 재사용 하기 위한 문법으로 With 를 제공한다.

WITH Counter AS(
    SELECT h.hacker_id, h.name, count(*) cnt
    FROM hackers AS h
    INNER JOIN challenges AS c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id, h.name
)

SELECT Counter.hacker_id, Counter.name, Counter.cnt
FROM Counter
WHERE cnt = (SELECT MAX(cnt) From Counter)
OR cnt IN (SELECT cnt
            FROM Counter
            GROUP BY cnt
            HAVING COUNT(*) = 1)
ORDER BY Counter.cnt DESC, Counter.hacker_id

BETWEEN을 조인조건으로 쓰기

HackerRank The Report

select
    case when g.grade < 8 then null
    else s.name end as name,
    g.grade, s.marks
from students as s
    inner join grades as g on s.marks between g.min_mark and g.max_mark
order by g.grade desc, name, s.marks

2개 이상 셀프조인하기

LeetCode 180

# Write your MySQL query statement below
select distinct l.num as ConsecutiveNums
from logs as l
    inner join logs as l_next on l.id + 1 = l_next.id
    inner join logs as l_nnext on l.id + 2 = l_nnext.id
where l.num = l_next.num and l.num = l_nnext.num