[MySQL] 프로그래머스 SQL 고득점 Kit - SUM, MAX, MIN

 

갑자기 생각났는데 SQLD 시험 보기 전에 이거 하고 갈걸....

합격하긴 했지만 공부가 재밌었을려나

https://school.programmers.co.kr/learn/courses/30/parts/17043

프로그래머스

 

 

---

1. 가격이 제일 비싼 식품의 정보 출력하기

-- 코드를 입력하세요
SELECT * FROM FOOD_PRODUCT ORDER BY PRICE DESC LIMIT 1;

SELECT * 
FROM FOOD_PRODUCT 
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

• 좋은 코드는 아니지만 주제에 맞게 MAX() 와 서브쿼리를 이용했다.

 

2. 최댓값 구하기

-- 코드를 입력하세요
SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME DESC LIMIT 1;

• DATETIME의 경우 내림차순으로 하면 최신날짜순으로 정렬한다.

 

3. 최솟값 구하기

-- 코드를 입력하세요
SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME LIMIT 1;

 

 

4. 동물 수 구하기

-- 코드를 입력하세요
SELECT COUNT(*) FROM ANIMAL_INS;

 

 

5. 중복 제거하기

-- 코드를 입력하세요
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS WHERE NAME IS NOT NULL;

• 중복 제거는 DISTINCT 를 사용한다. COUNT안에 사용할 수 있다.(집계함수에는 서브쿼리, 수식, 컬럼 등..사용 가능)

 

6. 가장 비싼 상품 구하기

-- 코드를 입력하세요
SELECT MAX(PRICE) MAX_PRICE FROM PRODUCT;

 

 

7. 조건에 맞는 아이템들의 가격의 총합 구하기

-- 코드를 작성해주세요
SELECT SUM(PRICE) TOTAL_PRICE FROM ITEM_INFO WHERE RARITY='LEGEND';

 

 

8. 물고기 종류 별 대어 찾기

SELECT F.ID, FN.FISH_NAME, F.LENGTH
FROM FISH_INFO F
JOIN FISH_NAME_INFO FN ON F.FISH_TYPE = FN.FISH_TYPE
WHERE F.LENGTH = (SELECT MAX(F2.LENGTH)
                  FROM FISH_INFO F2
                  WHERE F2.FISH_TYPE = F.FISH_TYPE)
ORDER BY F.ID;

• GROUP BY를 생각했는데, 그냥 FISH_TYPE별 MAX값을 비교하면 된다.

SELECT F.ID, FN.FISH_NAME, F.LENGTH
FROM FISH_INFO F
JOIN FISH_NAME_INFO FN ON F.FISH_TYPE = FN.FISH_TYPE
JOIN (
    SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LENGTH
    FROM FISH_INFO
    GROUP BY FISH_TYPE
) AS MaxFish ON F.FISH_TYPE = MaxFish.FISH_TYPE AND F.LENGTH = MaxFish.MAX_LENGTH
ORDER BY F.ID;

• GROUP BY를 이용하여 처리하는 방식. 더 복잡한듯?
• 서브쿼리에서 FISH_TYPE으로 GROUP BY 후 LENGTH를 SELECT하고 FISH_NAME_INFO와 JOIN한다.  동시에 MAX_LENGTH를 가진 것만 추가로 JOIN. 

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

 

• 난 이게 제일 간단한 것 같다. 첫번째랑 크게 다르지는 않지만 GROUP BY를 활용하는게 더 이해하기 쉬운거같음

 

9. 잡은 물고기 중 가장 큰 물고기의 길이 구하기

SELECT CONCAT(FORMAT(MAX(LENGTH), 2), 'cm') AS MAX_LENGTH
FROM FISH_INFO;

• CONCAT()을 이용하여 출력 시 문자열을 결합해 출력할 수 있따.
• FORMAT()을 이용하여 출력 소수점을 조절할 수 있다.(2 이하)

 

10. 연도별 대장균 크기의 편차 구하기

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 1,2

• PARTITION BY를 이용하면 SQL에서 윈도우 함수를 사용할 때 데이터를 특정 기준으로 나눠서 각 파티션 내에서 별도의 계산을 할 수 있다.
  • 즉 같은 연도에 속하는 대장균 끼리의 그룹을 형성하고, 이 파티션 내의 MAX()값을 추출한 후 SIZE_OF_COLONY를 뺀다.

SELECT 
    YEAR(E.DIFFERENTIATION_DATE) AS YEAR,
    MAX_E.MAX_SIZE - E.SIZE_OF_COLONY AS YEAR_DEV,
    E.ID
FROM 
    ECOLI_DATA E
JOIN 
    (SELECT 
         YEAR(DIFFERENTIATION_DATE) AS YEAR,
         MAX(SIZE_OF_COLONY) AS MAX_SIZE
     FROM 
         ECOLI_DATA
     GROUP BY 
         YEAR(DIFFERENTIATION_DATE)
    ) MAX_E ON YEAR(E.DIFFERENTIATION_DATE) = MAX_E.YEAR
ORDER BY 
    YEAR, YEAR_DEV;

• GROUP BY를 이용한 방식. 참고로 MySQL에서는 GROUP BY에 명시된 컬럼만 집계함수에 사용할 수 있다.(1055, "Expression #2)
• 서브쿼리에서 YEAR별로 GROYP BY 한 후, 연도별 MAX_SIZE값을 추출한다. 그리고 YEAR을 기준으로 JOIN한다.
• 편차는 SELECT 절에서 연산해주기

 

 

---

회고

생각보다 GROUP BY를 사용하게 되면 코드가 더 복잡해진다.

최대한 집계함수와 서브쿼리+JOIN을 이용하는 방식이 더 간단한 것 같다....

 

갑자기 궁금해져서 찾아본 WINDOW함수와 집계함수의 차이점

• WINDOW 함수 : 각 행에 대해서 계산한다. 따라서 반드시 OVER 절과 함께 어떤 행(데이터)을 기준으로 계산할 지 명시해야 한다. OVER 절 안에는 PARITION BY 나 ORDER BY 등을 지정할 수 있다.
  • RANK() : 순위 매기기
  • ROW_NUMBER() : 순차적인 번호 매기기
  • SUM() : 윈도우 내 합
  • LAG() : 이전 행의 값
  • LEAD() : 다음 행의 값
• 집계 함수 : 데이를 그룹화 하여 각 그룹별 계산을 수행한다.(또는 전체) 필요시 GROUP BY와 함께 사용되며, 집계 함수에 대한 조건이 필요하면 HAVING 절과도 함께 사용된다.
  • SUM() : 합
  • MIN() : 최솟값
  • MAX() : 최댓값
  • COUNT() : 갯수
  • AVG() : 평균값