전에 배웠던 PowerBI 전체적인 대시보드 만들기 마무리 과정
-Top N filter
-Multi Row Card
-레이아웃 꾸미기
Historical_inventory 라는 새로운 csv파일 데이터로
inventory 테이블을 생성
데이터 살펴보기
SELECT count(*)
FROM inventory
;
SELECT count()
FROM (
SELECT DISTINCT
FROM inventory i
)i2
;
SELECT DISTINCT File_Type, SoldFlag
FROM inventory i
;
SELECT DISTINCT MarketingType , New_Release_Flag
FROM inventory i
;
SELECT DISTINCT ReleaseYear
FROM inventory i
ORDER BY 1 DESC
;
-- ReleaseYear = 0 인 특수 데이터 : 1건
SELECT *
FROM inventory i
WHERE ReleaseYear = 0
;
-- Sku 개수 133,360
SELECT count(DISTINCT SKU_number) sku_count
FROM inventory i
;
SELECT min(PriceReg), max(PriceReg), avg(PriceReg)
FROM inventory i
;
-- PriceReg = 0 인 데이터 : 2,549건 ( LowNetPrice 참조 )
SELECT count(*)
FROM inventory i
WHERE PriceReg = 0
;
SELECT min(LowNetPrice), max(LowNetPrice), avg(LowNetPrice)
FROM inventory i
;
SELECT File_Type , SoldFlag
, sum(ItemCount) inven_unit
, sum(SoldCount) sold_unit
FROM inventory i
GROUP BY 1,2
ORDER BY 1,2
;
SELECT File_Type , SoldFlag
, sum(ItemCount) inven_unit
, sum(SoldCount) sold_unit
, sum(ItemCount) / (sum(SoldCount)/6) month_of_coverage
FROM inventory i
WHERE SoldFlag = 1
AND File_Type = 'Historical'
GROUP BY 1,2
;
-- 재고 Cost 파악
SELECT File_Type , SoldFlag
, sum(ItemCount) inven_unit
, round(avg(PriceReg),1) avg_price
, ROUND(sum(ItemCount * PriceReg),0) inven_cost
FROM inventory i
WHERE 1=1
AND PriceReg > 0
GROUP BY 1,2
;
