[SQL] - 프로그래머스 SQL 고득점 KIT SUM, MAX, MIN 오답노트

 

프로그래머스에 SUM,MAX,MIN문을 모두 풀어보았다. 한두문제 정도 어려운 문제가 나왔었는데

전반적으로는 좀 쉽게 나온거같다.

그럼 문제를 빠르게 정리해보도록 하겠다

---

Programmers - 연도별 대장균 크기의 편차 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299310

프로그래머스

문제 설명

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

 

[ 문제 ]

 

분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.

 

[ 예시 ]

 

정답 쿼리

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR,
    (max(size_of_colony) over (partition by year(differentiation_date)) - size_of_colony) as YEAR_DEV
,ID
FROM ECOLI_DATA
order by YEAR, YEAR_DEV

 

풀이 설명

문제 접근 설명 

1.Year( DIFFERENTIATION_DATE) AS YEAR

DIFFERENTIATION_DATE(날짜)에서 연도만 추출해서 YEAR 컬럼으로 보여줍니다.

 

2. MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE)) - SIZE_OF_COLONY AS YEAR_DEV

 

MAX(SIZE_OF_COLONY) OVER (...)

• OVER는 “집계(최대값)를 구하긴 하는데, 행을 뭉개지 말고(=GROUP BY처럼 줄이지 말고) 각 행에 붙여라”라는 뜻이에요.
• PARTITION BY YEAR(DIFFERENTIATION_DATE)
  연도별로 그룹을 나눠서 그 그룹(해당 연도) 안에서 최대값을 구한다
• 즉 , 각행마다 내가 속한 연도의 최대 SIZE_OF_COLONY가 계산되고, 거기서 내 SIZE_OF_COLONY를 빼서 

YEAR_DEV

 

• 그 연도 최고 크기와 내 크기의 차이
• 값이 0이면: 그 연도에서 내가 최대 크기
• 값이 클수록: 그 연도 최대치 대비 내 colony가 더 작음

ID

• 행 식별자 그대로 출력

3. ORDER BY YEAR, YEAR_DEV

• 먼저 연도 오름차순
• 같은 연도 안에서는 YEAR_DEV 오름차순
  • 0이 먼저 오므로, 각 연도에서 가장 큰 colony 행들이 맨 위로 옴
  • 그 다음으로 최대치와 차이가 작은 순서대로 나열

---

 

Programmers - 물고기 종류 별 대어 찾기

문제 설명

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. FISH_INFO 테이블의 구조는 다음과 같으며 ID, FISH_TYPE, LENGTH, TIME은 각각 잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜를 나타냅니다.

 

 

[ 문제 ]

 

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.

물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요.
결과는 물고기의 ID에 대해 오름차순 정렬해주세요.
단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

 

[ 예시 ]

 

정답 쿼리

SELECT FI.ID,FN.FISH_NAME,FI.LENGTH
FROM FISH_INFO AS FI
JOIN FISH_NAME_INFO AS FN
ON FI.FISH_TYPE = FN.FISH_TYPE
WHERE (FI.FISH_TYPE, FI.LENGTH) IN (SELECT FISH_TYPE, MAX(LENGTH)
                                    FROM FISH_INFO
                                    GROUP BY FISH_TYPE)
GROUP BY FI.ID, FN.FISH_NAME, FI.LENGTH
ORDER BY FI.ID ASC

 

풀이 설명

문제 접근 설명 

1) SELECT

 

SELECT FI.ID, FN.FISH_NAME, FI.LENGTH

• 최종 출력 컬럼: 물고기 ID, 물고기 이름, 길이

2) FROM + JOIN

 

FROM FISH_INFO AS FI JOIN FISH_NAME_INFO AS FN ON FI.FISH_TYPE = FN.FISH_TYPE

• 물고기 기록(FI)에 물고기 이름(FN)을 붙이기 위해
• FISH_TYPE 기준으로 내부조인(매칭 되는 것만)

3) WHERE (핵심 필터) 

여기가 조금 햇갈렸다.

 

WHERE (FI.FISH_TYPE, FI.LENGTH) IN ( SELECT FISH_TYPE, MAX(LENGTH) FROM FISH_INFO GROUP BY FISH_TYPE )

• 서브쿼리에서 FISH_TYPE별 최대 길이를 구한다
• 결과 예:
  • • (1, 30) → 1번 타입의 최대 길이 30
    • (2, 18) → 2번 타입의 최대 길이 18
• 바깥쿼리는 (FI.FISH_TYPE, FI.LENGTH)가 이 목록에 포함되는 행만 남긴다

그래서 “각 타입에서 최대 길이인 물고기 행”만 통과한다.
같은 타입에서 최대 길이가 동률인 물고기가 여러 마리면 그 여러 마리 전부 나온다.

4) GROUP BY

 

GROUP BY FI.ID, FN.FISH_NAME, FI.LENGTH

• 현재 SELECT에 집계함수(MAX, COUNT 등)를 쓰지 않기 때문에 사실상 불필요한 경우가 많다
• 다만 일부 환경에서 중복 제거처럼 보이게 하려는 의도였을 수 있는데,
  일반적으로는 GROUP BY 대신 DISTINCT를 쓰거나(중복이 실제로 생길 때만) 아예 제거하는 게 깔끔하다

5) ORDER BY

 

ORDER BY FI.ID ASC

• 결과를 ID 오름차순으로 정렬

요약 예시

• 타입 A 물고기 길이들: 10, 12, 15, 15(동률)
  → 타입 A에서 길이 15인 물고기 2마리 모두 출력

(윈도우 함수 활용한 답)

SELECT ID, FISH_NAME, LENGTH
FROM (
  SELECT
      FI.ID,
      FN.FISH_NAME,
      FI.LENGTH,
      MAX(FI.LENGTH) OVER (PARTITION BY FI.FISH_TYPE) AS MAX_LEN
  FROM FISH_INFO FI
  JOIN FISH_NAME_INFO FN
    ON FI.FISH_TYPE = FN.FISH_TYPE
) t
WHERE LENGTH = MAX_LEN
ORDER BY ID;