[성동1기 전Z전능 데이터 분석가] Day 25

• COALESCE: 안의 인자들 중 첫 번째로 온 Non-Null값을 반환하는 함수
  • syntax: select column1, COALESCE(column1,0) from TABLE
• RANK OVER: 데이터들의 순위를 출력하기 위해 사용하는 함수
  • syntax: RANK() OVER (Order by column2 desc) ranking(순위열의 별칭을 설정)
  • EX)
    SELECT Studentname, Subject, Marks, (DENSE_)RANK() OVER(ORDER BY Marks desc) RANK
    FROM ExamResult
    ORDER BY RANK;
• PARTITION BY: RANK OVER를 사용할 때 GROUP BY 처럼 사용할 수 있다.
  • EX)
    SELECT Studentname, Subject, Marks, RANK() OVER(PARTITION BY Subject ORDER BY Marks desc) RANK
    FROM ExamResult
    ORDER BY RANK;
• JOIN: 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어내는 SQL 절
  • INNER JOIN, LEFT JOIN 등이 존재한다.
• SUB Query: 한 쿼리문 안에 SELECT 문이 여러 개 존재할 수 있다.
  • SELECT, FROM, WHERE 문에서 사용할 수 있다.
  • FROM문에서 SUB Query를 이용해 새로운 테이블처럼 사용할 수 있다.
  • WHERE 문에서 IN을 사용하여 조건으로 사용할 수 있다.
• VIEW: '가상'의 테이블을 만들어내지만 실제론 데이터를 저장하고 있지 않다.
  • syntax: CREATE(OR REPLACE) VIEW view_name AS 
    

---

• SQL 쿼리 실습

# VIEW연습

create or replace view example as

select warehouse_code,warehouse_name,product_code,product_name,product_category_name,sum(stock_quantity) as "누적입고량"

from 공판장품목별입출고현황

where product_category_name ="(냉동)" and 공판장품목별입출고현황.warehouse_name like "%물류센터"

group by 1,2,3,4,5

having sum(stock_quantity)>=1000

order by sum(stock_quantity) desc

 

# 7.물류센터별로 각 상품의 누적 입고량, 누적 출고량, 누적 재고량을 나타내는 테이블을 만들어보세요.

select a.warehouse_code,

a.warehouse_name,

a.product_code,

a.product_name,

a.product_category_name,

sum(a.stock_quantity) as '누적입고량',

sum(a.shipment_quantity) as '누적출고량',

sum(b.inventory_quantity) as '누적재고량'

from 공판장품목별입출고현황 a

inner join 공판장품목별재고현황 b

on a.warehouse_code = b.warehouse_code

and a.product_code = b.product_code

and a.product_category_name = b.product_category_name

group by 1,2,3,4,5

order by a.warehouse_code desc

 

# 8.각 수산물 품목의 누적출고량이 해당 수산물 품목의 누적입고량을 초과하는 경우에 해당 수산물 품목에 대한 정보를 확인하세요.

select

product_code,

product_name,

sum(stock_quantity) as '누적입고량',

sum(shipment_quantity) as '누적출고량'

from 공판장품목별입출고현황 a

group by 1,2

having sum(stock_quantity)<sum(shipment_quantity)

 

# 9.각 수산물 품목의 누적 출고량이 해당 수산물 품목의 누적 입고량을 가장 많이 초과하는 수산물 품목에 대한 정보를 확인하세요.

select

product_code,

product_name,

sum(stock_quantity) as 누적입고량,

sum(shipment_quantity) as 누적출고량,

abs(sum(stock_quantity) - sum(shipment_quantity)) as 차이

from 공판장품목별입출고현황

group by 1, 2

order by 5 desc

limit 1

;

 

# 10. 누적 입고량이 1000이상이고 누적 출고량이 500 미만인 품목 중에서 현재 재고량이 가장 많은 품목의 정보와 일자를 확인하는 SQL 쿼리를 작성하세요.

select

a.warehouse_code 공판장코드,

a.warehouse_name 공판장명,

a.stock_date 기준일자,

a.product_name 품목명,

a.product_code 수산물품목코드,

coalesce (b.inventory_quantity,0) 현재재고량,

sum(a.stock_quantity) 누적입고량,

sum(a.shipment_quantity) 누적출고량

from 공판장품목별입출고현황 a

join 공판장품목별재고현황 b

on a.warehouse_code = b.warehouse_code

and a.product_name = b.product_name

and a.product_code = b.product_code

and a.stock_date = b.stock_date

group by 1,2,3,4,5,6

having sum(a.stock_quantity) >= 1000 and sum(a.shipment_quantity) < 500

order by 6 desc

 

# 11. 각 상품별로 누적 입고량이 가장 많은 두개의 공판장 정보를 가져오는 쿼리를 작성하세요.

select *

from (

select

warehouse_name 공판장명,

product_name 품목명,

sum(stock_quantity) 누적입고량,

rank()over (partition by product_name order by sum(stock_quantity) desc) 입고량순위

from 공판장품목별입출고현황

group by 1,2

) a

where 입고량순위 <= 2 and 누적입고량 != 0

;