2024-03-22

 

1. 오늘 배운 것

 

테이블 내 데이터에 NULL 값이 있고, NULL 을 제외하거나, 사용해야할 경우

 

SELECT restaurant_name 
     , AVG(rating) avg_rating -- null 을 포함해야할때
     , AVG(IF(rating <> 'Not given', rating, NULL)) avg_rating2 -- null 을 제외해야할때 
     -- 필요에 따라 선택해서 사용
FROM food_orders
GROUP BY 1

 

IF(rating <> 'Not given', rating, NULL) -- 자주 쓰는 문구

: rating 컬럼이 'Not given' 이면 rating 값을, 그게 아니라면 NULL 값 입력

 

AVG 함수는 NULL 값이 있을 경우 없는 것으로 계산한다.AVG(rating) avg_rating: 사용할 수 없는 값('Not given')이 남아있어 0으로 간주

 

 

null 값이 아니라 다른 값으로 대체 하는 문법

COALESCE(컬럼명, 값1) null 값일 때 값1으로 대체

 

 

상식적이지 않은/예상외의 값일때(ex.오입력 등)

case when 문을 사용하여, 다른 값으로 지정하여 계산

CASE WHEN age<15 THEN 15
     WHEN age>80 THEN 80
     WHEN age END "범위를 지정해준 age"

 

 

 

• 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

① 음식점별, 시간별 주문건수를 만들어준다.

SELECT f.restaurant_name 
     , SUBSTR(p.time, 1, 2) hh
     , COUNT(1) cnt_order
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id 
WHERE SUBSTR(p.time, 1, 2) BETWEEN 15 AND 20
GROUP BY 1, 2

+

00시로 통일 하기 위해서 SUBSTR 로 바꿔줌

음식점별, 시간별로 그룹화

 

② Pivot Table 로 나눠준다

select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15", 
       max(if(hh='16', cnt_order, 0)) "16",
       max(if(hh='17', cnt_order, 0)) "17",
       max(if(hh='18', cnt_order, 0)) "18",
       max(if(hh='19', cnt_order, 0)) "19",
       max(if(hh='20', cnt_order, 0)) "20"
-- 피봇뷰를 깔끔하게 만들어주기 위해서는 최댓값을 마지막에 붙여줘야한다.
-- / 15시 일때 주문건수를 넣어줘
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

 

+

max(if(hh='15', cnt_order, 0)) "15"

 

피봇뷰를 깔끔하게 만들어주기 위해서는 최댓값(max) 를 붙여줘야한다

* 15시 일때 주문건수 중 가장 큰 값을 입력해줘 인 것으로 예상..

 

 

윈도우 함수

: 묶어주는 이미지, 음식점 별로 순위를 매겨줄때, 누적합 등

 

rank() over (parition by ~ order by ~ ): 특정 기준으로 순위를 매겨주는 기능sum(A) over (parition by ~ order by~): 합계의 비율을 구하거나, 누적합을 구해주는 기능

 

 

• 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

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) ranking
      -- rank 괄호안에 생략가능, rank 는 합계를 구하는 것처럼 대상이 있는 것이 아님
 FROM
 (
 SELECT cuisine_type 
      , restaurant_name 
      , COUNT(1) cnt_order
 FROM food_orders fo 
 GROUP BY 1, 2
 ) a
 ) b -- 랭크한 결과물을 한번 더 서브쿼리로
 WHERE ranking <= 3

+

함수 사용법 !

RANK () OVER (PARTITION BY cuisine_type ORDER BY cnt_order DESC) ranking

 

① 음식타입, 상점 별로 주문건수를 구한다

② rank 함수로 순위를 매겨준다

③ 위에서 3순위만 구한다

 

• 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

SUM(cnt_order) OVER(PARTITION BY cuisine_type) sum_cuisine 
-- 그룹바이를 하지 않아도 윈도우함수로 사용가능(음식점별 주문 합계)
SUM(cnt_order) over(partition by cuisine_type order by cnt_order) cum_cuisine
-- 순차적으로 정렬하여 누적합을 구하기 위해 order by (낮은순정렬로 누적합)

+

함수 사용법

SUM(cnt_order) OVER(PARTITION BY cuisine_type)

주문 합계

SUM(cnt_order)  OVER(PARTITION BY  cuisine_type ORDER BY cnt_order)

누적합 (낮은 순 정렬로)

* 높은 순 정렬을 하고 싶다면 DESC 붙이기

 

 

 

날짜 형식 지정

(시간만 남겨줘, 월만 남겨줘, 일자만 남겨줘)

 날짜형식으로 지정 : <DATE(문자형 컬럼) 함수>

 

DATE_FORMAT 

%w

요일을 뜻한다.

일요일: 0

월요일: 1

 

 

---

 

2. 문제풀이

• 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

SELECT *
FROM food_orders

SELECT *
FROM customers c 

SELECT cuisine_type
     , MAX(IF(age = '10', cnt_order, 0)) "10대"
     , MAX(IF(age = '20', cnt_order, 0)) "20대"
     , MAX(IF(age = '30', cnt_order, 0)) "30대"
     , MAX(IF(age = '40', cnt_order, 0)) "40대"
     , MAX(IF(age = '50', cnt_order, 0)) "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 age
     , COUNT(1) 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
ORDER BY 1
) a
GROUP BY 1
ORDER BY 1

+

① 음식 타입, 연령별로 그룹화

② Pivot Table 로 나눠준다

 

" MAX 함수 잊지 말고 사용 "

 

 

---

 

• 테이블의 모든 NULL 값 제외

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null 
order by age

 

Q. customer_id 컬럼의 null 값 제외만 적었는데도 모든 테이블 내의 null 이 제외되는지?

A. 조인을 했기 때문에 모든 테이블의 행이 제외되는 것 같다..

혹시나 해서 WHERE 절에 다른 컬럼도 넣어봤으나 동일 했음

확실하진 않음, 매니저님 답이 오면 확인 할 것!

 

 

 

Q. 한글인데 '작은따옴표' 로 별명을 지정해도, 쿼리는 정상적으로 실행이 되었는데 이유는?

A. 파이썬에서는 문제 생길 수 있다고 함.

"큰따옴표" 안에 적혀있는 단어는 고유명사가 되어 이후 찾을때에 지정한 단어 그대로 작성해야한다고 함!

 

* 다시한번 정리

별명 >

특수문자, 한글 사용 시: " "큰따옴표

WHERE >

문자를 사용 할 때는: ' ' 작은 따옴표

 

+ 

 

지난번 팀원과 이야기 하다 나온 부분

NOT NULL  =  LIKE %%

매니저님께 여쭤보니 프로그래머스 오류였거나, 지금은 사라진 옛날 방식일 수도 있다고 함

NULL 값을 사용해야할 때에는

① IS NULL

② IS NOT NULL

을 사용 할 것!

 

오늘 알게 된 꿀팁!

DBeaver 에서는 한줄 띈 후에 쿼리문을 작성하면 다른 쿼리문으로 인식하고

커서를 두고 실행할 수 있다!

 

이제 일일히 주석처리 하지 않고그냥 띄어쓰기 하면 된당!!