Post

내일배움캠프 사전캠프 온라인강의 SQL 5주차

Posted
By Hoon
5 min read

🚀 공부한 내용

값의 제외

1
2
3
4
5

SELECT restaurant_name,
       AVG(rating) AS avg_rating,
       AVG(IF(rating <> 'Not given', rating, null)) AS avg_rating2
FROM food_orders
GROUP BY 1;

mysql에서는 사용할 수 없는 값일 때 해당 값을 0으로 간주

명확하게 연산을 지정해주기 위해 null 문법을 사용

값의 변경

1
2
3
4
5
6
7
8
9
10

SELECT a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       COALESCE(b.age, 20) AS "null 제거",
       b.gender
FROM food_orders a LEFT JOIN customers b ON a.customer_id=b.customer_id
WHERE b.age IS NULL;

COALESCE(a, b) : 두 개의 인자를 받아 a의 값이 NULL 이라면 b로 대체, b의 값이 NULL 이라면 a로 대체

Pivot Table

  • 음식점별 시간별 주문건수 (15 ~ 20시 사이, 20시 주문건수 기준 내림차순)
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    SELECT restaurant_name,
       MAX(IF(hh = '15', cnt_order, 0)) AS "15",
       MAX(IF(hh = '16', cnt_order, 0)) AS "16",
       MAX(IF(hh = '17', cnt_order, 0)) AS "17",
       MAX(IF(hh = '18', cnt_order, 0)) AS "18",
       MAX(IF(hh = '19', cnt_order, 0)) AS "19",
       MAX(IF(hh = '20', cnt_order, 0)) AS "20"
FROM (
  SELECT f.restaurant_name,
         SUBSTR(p.time, 1, 2) AS hh,
         COUNT(*) AS cnt_order
  FROM food_orders AS f INNER JOIN payments AS p ON f.order_id = p.order_id
  WHERE SUBSTR(p.time, 1, 2) BETWEEN 15 AND 20
  GROUP BY 1, 2
) a
GROUP BY 1
ORDER BY 7 DESC;

    Image

  • 성별, 연령별 주문건수 (10 ~ 59세 사이, 연령 순으로 내림차순)
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

    SELECT age,
       MAX(IF(gender = 'male', cnt_order, 0)) AS "male",
       MAX(IF(gender = 'female', cnt_order, 0)) AS "female"
FROM (
  SELECT gender,
         CASE WHEN age BETWEEN 10 AND 19 THEN 10
              WHEN age BETWEEN 20 AND 29 THEN 20
              WHEN age BETWEEN 30 AND 39 THEN 30
              WHEN age BETWEEN 40 AND 49 THEN 40
              WHEN age BETWEEN 50 AND 59 THEN 50
         END AS "age",
         COUNT(*) AS "cnt_order"
  FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
  WHERE age BETWEEN 10 AND 59
  GROUP BY 1, 2
) a
GROUP BY 1
ORDER BY 1 DESC;

    Image

  • 음식 타입별, 연령별 주문건수 (10 ~ 59세 사이)
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

    SELECT cuisine_type,
       MAX(IF(age = '10', cnt_order, 0)) AS "10대",
       MAX(IF(age = '20', cnt_order, 0)) AS "20대",
       MAX(IF(age = '30', cnt_order, 0)) AS "30대",
       MAX(IF(age = '40', cnt_order, 0)) AS "40대",
       MAX(IF(age = '50', cnt_order, 0)) AS "50대"
FROM (
  SELECT cuisine_type,
         CASE WHEN age BETWEEN 10 AND 19 THEN 10
              WHEN age BETWEEN 20 AND 29 THEN 20
              WHEN age BETWEEN 30 AND 39 THEN 30
              WHEN age BETWEEN 40 AND 49 THEN 40
              WHEN age BETWEEN 50 AND 59 THEN 50
         END AS "age",
         COUNT(*) AS "cnt_order"
  FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
  WHERE age BETWEEN 10 AND 59
  GROUP BY 1, 2
) a
GROUP BY 1

    Image

RANK

  • 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

    SELECT cuisine_type,
       restaurant_name,
       cnt_order,
       ranking
FROM (
  SELECT cuisine_type,
         restaurant_name,
         cnt_order,
         RANK() OVER (PARTITION BY cuisine_type ORDER BY cnt_order DESC) AS "ranking"
  FROM (
    SELECT cuisine_type,
           restaurant_name,
           count(*) AS cnt_order
    FROM food_orders
    GROUP BY 1, 2
  ) a
) b
WHERE ranking <= 3;

    Image

SUM

  • 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기
    1
2
3
4
5
6
7
8
9
10
11
12
13

    SELECT cuisine_type,
       restaurant_name,
       cnt_order,
       SUM(cnt_order) OVER (PARTITION BY cuisine_type) AS "sum_cuisine",
       SUM(cnt_order) OVER (PARTITION BY cuisine_type ORDER BY cnt_order) AS "cum_cuisine"
FROM (
  SELECT cuisine_type,
         restaurant_name,
         count(*) AS cnt_order
  FROM food_orders
  GROUP BY 1, 2
) a
ORDER BY cuisine_type, cnt_order;

    Image

날짜 데이터

  • 포맷
    1
2
3
4
5
6

    SELECT DATE(date) AS "date_type",
     DATE_FORMAT(DATE(date), '%Y') AS "년",
     DATE_FORMAT(DATE(date), '%m') AS "월",
     DATE_FORMAT(DATE(date), '%d') AS "일",
     DATE_FORMAT(DATE(date), '%w') AS "요일"
FROM payments;
  • 날짜로 GROUP BY
    1
2
3
4
5
6
7
8

    SELECT DATE_FORMAT(DATE(date), '%Y') AS "년",
       DATE_FORMAT(DATE(date), '%m') AS "월",
       DATE_FORMAT(DATE(date), '%Y%m') AS "년월",
       COUNT(*) AS "주문건수"
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id
WHERE DATE_FORMAT(DATE(date), '%m') = '03'
GROUP BY 1, 2, 3
ORDER BY 1;

🚀 회고

SQL을 활용하여 Pivot Table을 만들 수 있다는 것이 신기했고,
5주차 강의에는 조금 어렵지만 중요한 문법들이 많이 있었던 거 같다.

내일배움캠프
내일배움캠프 사전캠프 TIL