# 思路

-- 根据product_tb，计算备货值和总SKU

-- 根据sales_tb，计算GMV和有销售的SKU

-- 联结两表进行计算，using(style_id)

## 踩坑点

1. 在售SKU=备货SKU-已售SKU
2. SKU=item_id的个数。不是item_id的种类数即不是count(distinct item_id )

select 
	style_id,
	round(num/(cnt-num) *100,2) as 'pin_rate(%)',
	round(GMV/total*100,2) as 'sell-through_rate(%)'
from (
	select 
			style_id,
			sum(sales_num) as num ,
			sum(sales_price) as GMV
	from sales_tb
	left join product_tb using(item_id)
	group by style_id
) t1 -- 销售表
left join (
	select 
		style_id,
		sum(inventory) as cnt,
		sum(tag_price*inventory) as total
	from product_tb
	group by style_id
) t2 -- 备货表
using(style_id)

错误代码：

select 
	style_id,
	round(item_id_cnt/cnt *100,2) as 'pin_rate(%)',
	round(GMV/total*100,2) as 'sell-through_rate(%)'
from (
	select 
			style_id,
			count(distinct item_id) as item_id_cnt,
			sum(sales_price) as GMV
	from sales_tb
	left join product_tb using(item_id)
	group by style_id
) t1 -- 销售表
left join (
	select 
		style_id,
		count(distinct item_id) as cnt,
		sum(tag_price*inventory) as total
	from product_tb
	group by style_id
) t2 -- 备货表
using(style_id)