[성동1기 전Z전능 데이터 분석가] 25일차 SQL.5

 

Rank

rank() over()rank

 

PARTITION BY

rank over 사용할 때 사용하는 group by같은 역할

rank() over(partition by ~order by ~ DESC)rank

 

join 두 개 이상의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 SQL절(clause)

inner join은 두 테이블을 조인할 때 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.

left join  외부조인은 한쪽에만 결과가 있어도 나온다.

 

sub query

한 쿼리 안에 select 문이 여러 개있는 쿼리

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문

실무에서 많이 사용한다.

select, from, where 절에 사용가능

from에서의 sub query 하나의 테이블처럼 사용

테이블처럼 사용되므로, 열 이름과 테이블명을 꼭 기입

as 명시

where에서의 sub query in을 사용하여 조건으로 사용

 

view

가상의 테이블 (실제로 데이터를 저장하고 있지는 않음)

create view 이름 as

 

문제를 풀어보자!

9. 물류센터별로 각 상품의 누적입고량, 누적출고량, 누적 재고량을 나타내는 테이블을 만들어보세요. 공장판코드, 공장판명, 품목코드, 품목명, 상태가공분류명, 누적입고량, 누적출고량, 누적재고량

select
a.warehouse_code,
a.warehouse_name,
a.product_code,
a.product_name,
a.product_category_name,
sum(a.stock_quantity),
sum(a.shipment_quantity),
sum(b.inventory_quantity),
coalesce(sum(b.inventory_quantity),0)
from 공판장품목별입출고현황 a
left join 공판장품목별재고현황 b
on a.warehouse_code = b.warehouse_code
and a.warehouse_name = b.warehouse_nameand a.product_code =b.product_codeand a.product_name =b.product_nameand a.product_category = b.product_category
group by a.warehouse_code, a.warehouse_name, a.product_code, a.product_name, a.product_category_name
order by a.warehouse_name DESC

 

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

select product_code, product_name, sum(stock_quantity) as '누적입고량', sum(shipment_quantity) as '누적출고량' from 공판장품목별입출고현황
group by product_code, product_name
having 누적출고량 > 누적입고량

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

select product_code, product_name, sum(stock_quantity) as '누적입고량', sum(shipment_quantity) as '누적출고량', coalesce (sum(shipment_quantity)-sum(stock_quantity),0) as 차이
from 공판장품목별입출고현황
group by 1,2
having 누적출고량 > 누적입고량
order by 차이 desc

 

 

12. 누적입고량이 1000 이상이고, 누적출고량이 500 미만인 품목 중에서 현재 재고량이 가장 많은 품목의 정보와 일자를 확인하는 SQL 쿼리를 작성하세요. 공장판코드, 공장판명, 기준일자, 수산물품목코드, 품목명, 누적입고량, 누적출고량, 현재 재고량

select a.warehouse_code, a.warehouse_name, a.stock_date, a.product_code, a.product_name, sum(stock_quantity),sum(shipment_quantity), b.inventory_quantity
from 공판장품목별입출고현황 a
left join 공판장품목별재고현황 b
on a.warehouse_code = b.warehouse_code and a.stock_date = b.stock_date and a.product_code = b.product_code and a.product_category =b.product_category 
group by warehouse_code, warehouse_name, stock_date, product_code, product_name, inventory_quantity
having sum(stock_quantity) >= 1000 and sum(shipment_quantity) <500
order by b.inventory_quantity DESC

13. 각 상품별로 누적 입고량이 가장 많은 두 개의 공판장 정보를 가져오는 쿼리를 작성하세요. 공장판코드, 공장판명, 기준일자, 수산물품목코드, 품목명, 누적입고량, 누적출고량, 현재재고량

SELECT
*
FROM (
SELECT
A.product_code AS 수산물품목코드,
A.product_name AS 품목명,
A.warehouse_code AS 공판장코드,
A.warehouse_name AS 공판장명,
SUM(B.stock_quantity) AS 누적입고량,
SUM(B.shipment_quantity) AS 누적출고량,
RANK() OVER(PARTITION BY A.product_code ORDER BY SUM(B.stock_quantity) DESC) AS 순위
FROM 공판장품목별재고현황 A
LEFT JOIN 공판장품목별입출고현황 B
ON A.warehouse_code = B.warehouse_code AND A.product_code = B.product_code
GROUP BY 1, 2, 3, 4
having SUM(B.stock_quantity) != 0
) AS C
WHERE 순위 <= 2;

 

이 문제는 너무 어렵다... 이따 다시 해보자...