메타 데이터란?
- 데이터에 관한 구조화된 데이터
- 즉, 다른 데이터를 설명해주는 데이터
- 예를 들어, 파일의 저장 날짜, 종류, 태그 등
쿼리의 종류
주로 사용되는 쿼리는 DML, DQL
자주 사용하는 쿼리
쿼리 작성 시 주의사항
쿼리 실행 순서
쿼리 실행 순서는 쿼리를 작성한 순서가 아니다!!!
따라서, as로 명칭을 붙였더라도 실행 순서에 따라 명칭을 이용할 수 없기도 함
Schema 설정
-- 디폴트로 사용할 스키마 설정
USE [schema];
-- 이렇게 하면 from에서 일일이 스키마를 쓰지 않아도 됨~!분석 목표 (실습 목표)
사업을 하기에 가장 알맞은 지역은?
지역별 매출 순위
-- 지역별 매출 순위
select City, ROUND(SUM(SaleDollars)) sales_sum
from city_data
group by City
order by sales_sum desc;지역별, 스토어별 매출 순위
-- 지역별, 스토어별 매출 순위
select City, StoreNumber, ROUND(sum(SaleDollars)) sales_sum
from city_data
group by City, StoreNumber
order by sales_sum desc;
select City, COUNT(DISTINCT StoreNumber), AVG(SaleDollars) as avg_sales_per_one_data,
SUM(SaleDollars) / COUNT(DISTINCT StoreNumber) as avg_sales_per_store
from city_data
group by City
order by avg_sales_per_store desc;store의 총 개수
-- store의 개수
select count(distinct StoreNumber)
from city_data;
select *
from city_data;매출 상위 20위 스토어는 어느 지역에 많을지
select City, StoreNumber, sum(SaleDollars) sales
from city_data
group by City, StoreNumber
order by sales desc
limit 20;또는
select City, StoreNumber, sum(SaleDollars) sales,
row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by City, StoreNumber
order by store_rank
limit 20;또는 (서브쿼리 활용)
select City, count(StoreNumber) high_sale_store_cnt
from (select City, StoreNumber, sum(SaleDollars) sales,
row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by City, StoreNumber) t1 -- 서브쿼리로 사용할 테이블엔 꼭 이름을 붙여주어야 한다
where t1.store_rank <= 20
group by t1.City
order by high_sale_store_cnt desc;또는 (WITH 절 사용)
WITH store_rank_tb as (
select City, StoreNumber, sum(SaleDollars) sales,
row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by City, StoreNumber
)
select City, count(StoreNumber) high_sale_store_cnt
from store_rank_tb
where store_rank <= 20
group by City
order by high_sale_store_cnt desc;매출 상위 20위권 스토으이 비중이 높은 지역은?
WITH store_rank_tb as (
select City, StoreNumber, sum(SaleDollars) sales,
row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by City, StoreNumber
)
select City, count(distinct StoreNumber) total_store_cnt,
sum(case when store_rank <= 20 then 1 else 0 end),
round((sum(case when store_rank <= 20 then 1 else 0 end) / count(distinct StoreNumber) * 100), 2)
from store_rank_tb
group by City;또는 (TEMP TABLE 사용)
create temporary table store_rank_table
select City, StoreNumber, sum(SaleDollars) sales,
row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by City, StoreNumber;
select *
from store_rank_table;TEMP TABLE 이란?
-- MySQL에선 TEMP TABLE을 세션이 종료될 때 삭제함
-- 따라서, 반복해서 재사용되는 쿼리가 있을 경우, TEMP TABLE로 만들어두는게 편함
-- 단점으론 메모리를 차지하고, (스키마에 있는 테이블이 아니기 때문에) 어떤 테이블인지 바로 파악하긴 어렵다는 점결론
-- 안정적인 수익을 얻고 싶다면,
-- 20위권 순위 스토어 비중이 가장 높고, 평균 매출이 높은 MOUNT VERNON 추천
-- 또는 매출이 높은 store의 개수가 많은 DES MOINES 추천어떤 주류를 팔아야 매출이 좋을까?
주류 매출 순위
-- row_number : 1,1,2,3 -> 1,2,3,4
-- rank : 1,1,2,3 -> 1,1,3,4
select t2.ItemDescription
, round(sum(SaleDollars),2)
, rank() over(order by sum(SaleDollars) desc) item_rank
from city_data t1
left join item_meta t2
on t1.ItemNumber = t2.ItemNumber
where t1.City = 'MOUNT VERNON'
or t1.City = 'DES MOINES'
group by t2.ItemDescription; -- ItemDescription = 아이템 이름우리가 선택한 2개의 지역은 유사한 주류 순위를 가지고 있을까?
select
t1.City,
t2.ItemDescription,
sum(SaleDollars),
rank() over(partition by t1.City order by sum(SaleDollars) desc) item_rank
from city_data t1
left join item_meta t2
on t1.ItemNumber = t2.ItemNumber
where t1.City = 'MOUNT VERNON'
or t1.City = 'DES MOINES'
group by t1.City, t2.ItemDescription
order by item_rank;지역별로 몇 개의 아이템들을 팔고 있을까?
select t1.City, count(distinct t1.ItemNumber)
from city_data t1
where t1.City = 'MOUNT VERNON'
or t1.City = 'DES MOINES'
group by t1.City;각 아이템이 매출에서 차지하는 비중은 어느정도일까?
-- 첫번째: city 총 매출이 얼마인지
-- 두번째: city, item별 매출이 얼마이고, 순위가 얼마인지
select item_rank_tb.City,
item_rank_tb.ItemDescription,
item_rank_tb.item_rank,
city_sales_tb.total_sales,
round(item_rank_tb.item_total_sales / city_sales_tb.total_sales * 100, 1) item_sales_ratio
from (
select t1.City, t2.ItemDescription,
sum(SaleDollars) item_total_sales,
rank() over(partition by t1.City order by sum(SaleDollars) desc) item_rank
from city_data t1
left join item_meta t2
on t1.ItemNumber = t2.ItemNumber
where t1.City = 'MOUNT VERNON'
or t1.City = 'DES MOINES'
group by t1.City, t2.ItemDescription
) item_rank_tb
left join
(
select City, sum(SaleDollars) total_sales
from city_data
group by City
) city_sales_tb
on item_rank_tb.City = city_sales_tb.City
order by item_sales_ratio desc;판매 비중 및 거래 업체 분석
두 도시에서, 각각 매출 TOP 10인 주류의 비중은?
with item_sales_table as (
select City, ItemNumber, sum(SaleDollars) item_total_sales
, rank() over(partition by City order by sum(SaleDollars) desc) item_rank
from city_data
where City = 'MOUNT VERNON'
or City = 'DES MOINES'
group by 1, 2
)
select
item_sales_table.City,
round(sum(100 * item_sales_table.item_total_sales / t2.total_sales),1) item_sales_ratio_sum -- 도시별 TOP10 매출 아이템 비중의 합
from item_sales_table
left join (
select City, sum(SaleDollars) total_sales
from city_data
group by 1
) t2
on item_sales_table.City = t2.City
where item_rank <= 10
group by item_sales_table.City결론
-- DES MOINES의 TOP10매출 아이템 비중은 63%,
-- MOUNT VERNON의 TOP10매출 아이템 비중은 88%
-- 즉, DES MOINES에 매장을 열 경우, 매출을 늘리기 위해서는 더욱 다양한 주류를 들이는 노력을 해야 함
-- MOUNT VERNON은 비교적 간단한 주류로도 높은 매출을 기록할 수 있음두 도시에서, TOP20 주류를 입고하려면 각각 몇 개의 도매업체와 계약을 해야 하는가?
-- 몇 개의 도매업체와 계약을 해야하는가?
create temporary table rank_table
select City, ItemNumber, VendorNumber
, sum(SaleDollars) total_sales
, rank() over(partition by City order by sum(SaleDollars) desc) item_rank
from city_data
where City = 'MOUNT VERNON'
or City = 'DES MOINES'
group by 1,2,3;
select City, count(distinct VendorNumber)
from rank_table
where item_rank <= 20
group by City;결론
-- 두 도시에서 계약해야하는 VendorNumber 개수는 거의 비슷함.
-- MOUNT VERNON은 비교적 적은 가지수의 주류가 매출의 대다수를 차지했으므로,
-- MOUNT VERNON에 매장을 여는 것이 매출 관리에 더 편리할 수 있음.