[스파르타 내일배움캠프 / 데이터 분석 트랙] TIL(Today I Learned)_3주차_24.12.13

Today's Goals

1. SQL 꾸준한 연습

 

 

오전 SQL 테스트에서 단단히 멘탈이 털렸다.1시간 안에 해결하라고 하니 왜이렇게 뒤죽박죽인지

 

어찌저찌 답은 냈지만,역시 효율적인 쿼리인가는 의문

 

 

1. SQL 연습

 

SQL 코드카타

---

 

[어제 날짜의 데이터와 비교하는 방법]

• 어제 날짜의 기온보다 오늘 날짜의 기온이 상승한 경우를 추출해야 하는 문제
• 처음 나는 CTE 문을 활용하여 날짜를 생성한 후 비교하려고 했음 > 도저히 생성된 날짜 테이블을 매칭할 방법이 떠오르지 않음
• 날짜를 생성하는 복잡한 과정 없이, 같을 테이블끼리 조인하거나 같은 테이블끼리 비교하여 결과를 낼 수 있음!

#DATEDIFF 사용
--답안1
select wi.id
from weather w1
left join weather w2
on datediff(w1.recordDate, w2.recordDate) = 1
where w2.temperature is not null and w1.temperature > w2.temperature

• 이 때 테이블 조인 결과는 다음과 같음

#DATEDIFF 사용
-- 답안2
SELECT w1.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 AND w1.temperature > w2.temperature

• sql에서 from뒤에 테이블 여러개를 ,로 연결하여 불로올 수 있긴 하지만, 암묵적인 join이 일어난다고 봐야함(하지만 권장되지 않는 방식, join 사용이 가독성과 유지보수 면에서 좋음)
• where절의 조건이 join의 조건이 되는 부분이라고 생각하면 됨

#DATE_ADD 사용
select today.id
from Weather as yesterday 
join Weather as today 
on date_add(yesterday.recordDate, interval 1 day) = today.recordDate
where yesterday.temperature < today.temperature

• 위 조인 결과는 다음과 같음

• 이게 결국 내가 낸 답
• 하루차이가 나는 날짜를 기준으로 join을 해서 온도를 비교하는 방법

 

 

 

SQL QCC 답안 복습

---

 

[윈도우 함수의 종류는 많다!]

• 대륙별 인구수가 가장 많은 도시를 구하는 문제였음

# 내 답안
WITH with_max_pop AS (SELECT a.Name AS CityName, 
							 b.Name AS CountryName, 
                             b.Continent, a.Population, 
                             max(a.Population) over(PARTITION BY b.Continent) AS max_city_pop
                             FROM QCC.`city` a 
                             JOIN QCC.`country` b 
                             ON a.CountryCode = b.Code)

SELECT CityName, CountryName, Continent, Population
FROM with_max_pop
WHERE max_city_pop = Population
ORDER BY Population DESC;

• 당장 생각나는건, with로 가장 많은 인구수를 알 수 있는 가상 테이블을 만들자, 그리고 윈도우함수 max를 이용해야지
• 윈도우 함수 중 순위 함수를 생각해내지 못한게 아쉬움
• 윈도우 함수의 종류에 대해 자주 복습해야겠음

# rank(rownum도 가능) 윈도우 함수 사용
SELECT 
    CityName,
    CountryName,
    Continent,
    Population
FROM (
    SELECT 
        c.Name AS CityName,
        co.Name AS CountryName,
        co.Continent,
        c.Population,
        RANK() OVER(PARTITION BY co.Continent ORDER BY c.Population DESC) AS PopulationRank
    FROM 
        city c
    JOIN 
        country co ON c.CountryCode = co.Code
) ranked_cities
WHERE 
    PopulationRank = 1
ORDER BY 
    Population DESC;

• 이 경우 1위만을 추출하는 것이기 때문에 rank, rownu, denserank 뭐든 상관 없음
• 하지만, 세가지 순위 함수의 차이는 확실히 알아야 함(중복 순위 처리 방법)

# 추가 방법 1 상관서브쿼리
SELECT c.Name AS CityName, co.Name AS CountryName, co.Continent, c.Population
FROM city c
JOIN country co ON c.CountryCode = co.Code
WHERE c.Population = (
        SELECT MAX(c2.Population)
        FROM city c2
        JOIN country co2 ON c2.CountryCode = co2.Code
        WHERE co2.Continent = co.Continent
    )
ORDER BY c.Population DESC;
    

# 추가 방법 2 서브쿼리
SELECT c.Name AS CityName, co.Name AS CountryName, co.Continent, c.Population
FROM city c
JOIN country co 
ON c.CountryCode = co.Code
JOIN (
    SELECT co.Continent, MAX(c.Population) AS MaxPopulation
    FROM city c
    JOIN country co 
    ON c.CountryCode = co.Code
    GROUP BY co.Continent
) max_pop 
ON co.Continent = max_pop.Continent AND c.Population = max_pop.MaxPopulation
ORDER BY c.Population DESC;

• 서브쿼리를 사용하는 또 다른 방법들도 있음
• 세 쿼리의 효율을 비교해보면(처리 속도) 윈도우함수 -  서브쿼리 - 상관서브쿼리 순임