250314 TIL

수이·2025년 3월 14일

Data_6기 pandas python sql 내일배움캠프

🟡 TIL

목록 보기

28/41

개인스터디

동진갓의 판다스 떠먹여주기 1회차

🔗링크

QCC 2회차

문제 1) 글로벌 확장 기회 발굴

풀이

-- 전년대비 GNP감소 
-- GNP, GNPOld 필요
-- 인구 1천만명 이상
-- 이전 연도 GNP 0이거나 NULL 제외
SELECT COUNT(DISTINCT Code)
FROM country
WHERE GNPOld IS NOT NULL 
     AND GNPOld <> 0
     AND GNP < GNPOld
     AND Population >= 10000000

해설

SELECT COUNT(DISTINCT code) AS country_count
FROM qcc.country
WHERE GNPOld <> 0
and GNP - GNPOld < 0
and population >= 10000000

엄청나게 크게 다른 점은 없음!

나는 GNP < GNPOld라고 표현한 부분을
튜터님은 GNP - GNPOld <0 이라고 표현하심
이 말은 즉 GNPOld - GNP > 0 이라고 해도 되는 것

문제 2) 도시개발구역 인구 분석

풀이

처음에 실행했던 쿼리는 이것
근데 결과값에 cnt_city가 포함이 되면 안 되니까 재시도

SELECT District,
       ROUND(AVG(Population)) AS AVERAGE_POPULATION,
       COUNT(1) AS cnt_city 
FROM city 
GROUP BY District
HAVING cnt_city >= 3
ORDER BY AVERAGE_POPULATION DESC

서브쿼리로 감싸써 Having절을 써주면 되지 않을까 생각해서 이렇게 적었는데 오류가 생김!

SELECT District,
       AVERAGE_POPULATION
FROM 
(
    SELECT District,
           ROUND(AVG(Population)) AS AVERAGE_POPULATION,
           COUNT(*) AS cnt_city 
    FROM city 
    GROUP BY District
    ORDER BY AVERAGE_POPULATION DESC
  )a
HAVING cnt_city >= 3

여기서 동진님이 설명을 해주셨는데

SELECT COUNT(1) -- 🟡
FROM 
(
  SELECT District,
         ROUND(AVG(Population)) AS AVERAGE_POPULATION
  FROM city 
  GROUP BY District
  HAVING COUNT(1) >= 3 -- 🔴
  ORDER BY AVERAGE_POPULATION DESC 
)a

🔴 여기서의 HAVING절은 눈에 보이진 않지만 COUNT(1)이라는 컬럼이 뒤에 더 붙어있는 것

🟡 서브쿼리로 감싸서 COUNT(1)을 할 경우 사용할 수 없음 SELECT 절에 없어서!

아무튼 그래서 또 수정해서 최종 제출한 쿼리는 이건데 정답은 나왔지만 또 쉬운 길을 어렵게 돌아간 셈

having은 group by에 의존한다!!! 잊으면 죽는다👊

-- 행정구역 내 도시들의 평균 인구 수
-- 각 구역 별 평균 인구 계산 > 반올림 
-- 도시가 3개 이상 존재하는 District 
-- 평균 인구수 기준 내림차순 정렬

SELECT District,
       AVERAGE_POPULATION
FROM 
(
  SELECT District, 
       ROUND(AVG(Population)) AS AVERAGE_POPULATION,
       COUNT(1) AS cnt_city 
FROM city
GROUP BY District
ORDER BY AVERAGE_POPULATION DESC
)sub
WHERE cnt_city >=3

해설

SELECT district, ROUND(AVG(Population)) AS average_population
FROM qcc.city
GROUP BY district
HAVING COUNT(ID) >= 3
ORDER BY average_population DESC

문제 3) 인기도시 타겟 마케팅

🔗유사한 문제 - 즐겨찾기가 가장 많은 식당 정보 출력하기

풀이

아닛 이건 진짜 WITH 쓰면 된다 하고 휘리릭 작성해봤는데 시간이 너무 모자랐다 마지막에 1분남았을때 한번 더 확인해봤으면 좋았을텐데 사소한 부분을 틀려서 쿼리 실행이 안됨 제발 이러지마😭

-- 각 대륙에서 인구가 가장 많은 도시 출력
-- 대륙 별 / 가장 인구가 많은 도시 < RNAK? ROW_NUMBER? 해당 도시만 조회
-- 도시 정보가 없는 대륙 제외 
-- 인구 기준으로 내림차순 정렬

WITH rnk_ct AS(
SELECT ct.Name AS CITY_NAME,
       ct.CountryCode,
       ct.District
       ct.Population,
       co.Name AS COUNTRY_NAME
       ROW_NUMBER() OVER(PARTITION BY continent ORDER BY Population DESC) AS rnk
FROM city ct
JOIN country co ON ct.CountryCode = c.Code 
)

SELECT CITY_NAME, COUNTRY_NAME, Continent
FROM rnk_ct 
WHERE rnk = 1
ORDER BY Population DESC;

해설 들으면서 셀프로 고쳐본 나의 쿼리..
천천히 확인해보니까 다 보이는데 왜그랬을까?

WITH rnk_ct AS(
SELECT ct.Name AS CITY_NAME,
       ct.CountryCode,
       ct.District, -- 쉼표 빠짐 
       ct.Population,
       co.Name AS COUNTRY_NAME,
       co.Continent, -- 빠짐
       ROW_NUMBER() OVER(PARTITION BY co.Continent ORDER BY ct.Population DESC) AS rnk
FROM city ct
JOIN country co ON ct.CountryCode = co.Code 
) -- `c.Code`로 잘못 작성함

SELECT CITY_NAME, COUNTRY_NAME, CONTINENT, POPULATION -- 빠짐
FROM rnk_ct 
WHERE rnk = 1
ORDER BY Population DESC;

해설세션에서 잘 작성했는데 아쉽다고 하셨다
정신체려이각박한세상속에서

해설

SELECT
    CityName AS city_name,
    CountryName AS country_name,
    Continent AS continent_name,
    Population AS population
FROM (
    SELECT
        c.Name AS CityName,
        co.Name AS CountryName,
        co.Continent,
        c.Population,
        ROW_NUMBER() OVER (PARTITION BY co.Continent ORDER BY c.Population DESC) AS PopulationRank
    FROM qcc.country co
    JOIN qcc.city c ON c.CountryCode = co.Code
) ranked_cities
WHERE
    PopulationRank = 1
ORDER BY
    Population DESC

굳이 WITH를 쓰지 않더라도 이렇게 구할 수 있다. 전체적인 맥락은 내 코드와 비슷함
Name이라는 컬럼명이 겹치기때문에 아예 AS로 별명을 지어서 구분하셨다

데일리퀘스트

SQL - 즐겨찾기가 가장 많은 식당 정보 출력하기
SQL - 식품분류별 가장 비싼 식품의 정보 조회하기
SQL - 모든 레코드 조회하기
Python - 나누어 떨어지는 숫자 배열
Python - 음양 더하기

일기

SQL 코드카타 47-49✅ QCC 참여+해설정리
Python 코드카타 25-26 5주차 복습✅ 기초 트레이닝 Day4❌ 개인과제 제출✅
전처리&시각화 1-2주차✅
오늘도 어김없이 TIL에 등장하는 등장동진님;; 마지막에 시각화 부분도 엄청나게 정리를 열심히 하셨다고 하셔서 구경갔는데 걍 레전드🥲
QCC 점수는 오늘 2/3
3번 문제는 시간이 모자라서 마음이 급했나 컬럼명도 제대로 안 맞추고 쉼표도 빼먹고 아주 난리부르스가 났다 하하 뭐 대차게 틀렸으면 그러려니 하겠는데 쉬운 걸로 틀렸다고 하니까 마음이 아프다🥲 나도 합법도박장 가서 경주마가 되고 싶다 이거야 대빵만하게 적힌 내 닉네임 채찍피티를 널리널리 알리고 싶다 이거야!!!!!!!!! 친구가 이렇게 얘기해서 웃겼다