기초 SQL 정리 3

1. 보고싶은 데이터 요약하기

1. 집계 함수

• COUNT
  • COUNT(*)의 경우 NULL 값까지 포함
  • COUNT(name)의 경우 NULL 값 미포함
• SUM
• AVG
  • NULL을 0으로 계산하고 싶으면, SUM(name)/COUNT(*)식으로 작성
• MIN, MAX

  2. GROUP BY & HAVING

• 공급자별로 가격의 평균을 구하는 쿼리

  SELECT supplierid, AVG(price), SUM(price)/COUNT(*)
  FROM products
  GROUP BY supplierid
  

• 공급자의 공급 물건별 가격 평균을 구하는 쿼리

  SELECT supplierid, categoryid, AVG(price), SUM(price)/COUNT(*)
  FROM products
  GROUP BY supplierid, categoryid
  

• 정렬까지 한 경우

  SELECT supplierid, categoryid, AVG(price), SUM(price)/COUNT(*)
  FROM products
  GROUP BY supplierid, categoryid
  ORDER BY AVG(price) DESC
  

• GROUP BY에 조건걸기 & 별칭 붙이기

  SELECT supplierid, categoryid, AVG(price) AS avg_price
  FROM products
  GROUP BY supplierid, categoryid
  HAVING avg_price >= 100
  

해커랭크 문제풀이

집계 함수

1. Revising Aggregations - Averages

   SELECT AVG(population)
   FROM city
   WHERE district="California"
   

2. Revising Aggregations - The Sum Function

   SELECT SUM(population)
   FROM city
   WHERE district="California"
   

3. Revising Aggregations - The Count Function

   SELECT COUNT(id)
   FROM city
   WHERE population > 100000
   

4. Average Population

   SELECT FLOOR(AVG(population))
   FROM city
   

5. Population Density Difference

   SELECT MAX(population)-MIN(population)
   FROM city
   

6. Weather Observation Station 4

   SELECT COUNT(city)-COUNT(DISTINCT city)
   FROM station
   

GROUP BY

1. Top Earners

   SELECT months*salary AS earnings, COUNT(*)
   FROM employee
   GROUP BY earnings
   ORDER BY earnings DESC
   LIMIT 1
   

2. 조건문 쓰기

조건문 기본 구조 (조건의 순서가 중요함)

SELECT CASE
			WHEN categoryid=1 THEN '음료'
            WHEN categoryid=2 THEN '조미료'
            ELSE '기타'
       END
FROM products

조건이 여러개인 경우 -> AND

SELECT CASE
			WHEN categoryid=1 AND supplierid=1 THEN '음료'
            WHEN categoryid=2 THEN '조미료'
            ELSE '기타'
       END AS 'categoryname', *
FROM products

GROUP BY 사용도 가능함

SELECT CASE
			WHEN categoryid=1 AND supplierid=1 THEN '음료'
            WHEN categoryid=2 THEN '소스'
            ELSE '이외'
       END AS new_category, AVG(price)
FROM products
GROUP BY new_category

문제풀이

Type of Triangle (Hackerrank)

SELECT CASE
            WHEN A=B AND B=C THEN 'Equilateral'
            WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'
            WHEN A=B OR B=C OR A=C THEN 'Isosceles'
            ELSE 'Scalene'
        END
FROM triangles

3. 피봇 테이블

SELECT CASE
			WHEN categoryid=1 THEN price
            ELSE NULL
       END
FROM products

리트코드 문제 (피봇 테이블)

1. Reformat Department Table

   SELECT id
     , SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS Jan_Revenue
     , SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS Feb_Revenue
     , SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS Mar_Revenue
     , SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS Apr_Revenue
     , SUM(CASE WHEN month='May' THEN revenue ELSE NULL END) AS May_Revenue
     , SUM(CASE WHEN month='Jun' THEN revenue ELSE NULL END) AS Jun_Revenue
     , SUM(CASE WHEN month='Jul' THEN revenue ELSE NULL END) AS Jul_Revenue
     , SUM(CASE WHEN month='Aug' THEN revenue ELSE NULL END) AS Aug_Revenue
     , SUM(CASE WHEN month='Sep' THEN revenue ELSE NULL END) AS Sep_Revenue
     , SUM(CASE WHEN month='Oct' THEN revenue ELSE NULL END) AS Oct_Revenue
     , SUM(CASE WHEN month='Nov' THEN revenue ELSE NULL END) AS Nov_Revenue
     , SUM(CASE WHEN month='Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
   FROM department
   GROUP BY id
   

solvesql 문제

식사 금액과 팁의 관계 (GROUP BY 문제)

SELECT day
        , SUM(tip) AS tip_daily
        , SUM(total_bill) as revenue_daily
        , SUM(tip)/SUM(total_bill)*100 AS tip_revenue_pct
FROM tips
GROUP BY day