题解 | #店铺901国庆期间的7日动销率和滞销率#

1.首先计算tb_product_info表，得到901店铺的product_id的数量cnt，然后拼接product_id,shop_id得到临时表c
2. a表tb_order_overall、b表tb_order_detail、c表依次左关联,取a.event_time,b.product_id,c.shop_id， c.cnt字段(901店铺的product_id的数量)，得到临时表t
3.对临时表t按照product_id进行分组，对event_time进行排序，并且限制date(t.event_time)>='2021-09-25'，国庆期间的近7天也不需要09-25之前的数据。得到临时表t2
4.对t2表新增1个字段flag,当满足t2.rn=1 且 t2.shop_id='901'，则赋值t2.product_id，这样就避免了后续重复计算相同的product_id，并且不会把除901以外的其他店铺商品计算入内，得到临时表t3
5.对t3表，新增字段cnt_sales，计算近7天的已售商品种类的数量，得到表t4
6.对临时表t4计算售出商品的种类数量/901商铺上架的商品种类的数量，以及未售出的商品的种类数量/901商铺上架的商品种类数量，然后去重即可。
select distinct t4.dt,round(t4.cnt_sales/t4.cnt,3) as sale_rate
,round((t4.cnt-t4.cnt_sales)/t4.cnt,3) as unsale_rate
from(
	select from_unixtime(unix_timestamp(date(t3.event_time))-518400) as dt1
	,date(t3.event_time) as dt,t3.*
	,count(t3.flag) over(order by unix_timestamp(date(t3.event_time)) range between 518400 preceding and current row) as cnt_sales
	from(
		select t2.*,case when t2.rn=1 and t2.shop_id='901' then t2.product_id end as flag
		from(
			select t.*,row_number() over(partition by product_id order by event_time asc) as rn
			from(
				select
				a.event_time,b.product_id,c.cnt,c.shop_id
				from tb_order_overall a
				left join tb_order_detail b
				on a.order_id=b.order_id
				left join (select product_id,shop_id
								  ,(select count(product_id) from tb_product_info as cnt where shop_id='901' group by shop_id) as cnt 
						   from tb_product_info
                           ) c
				on b.product_id=c.product_id
			)t
			where date(t.event_time)>='2021-09-25'
		)t2
	)t3
)t4
where t4.dt between '2021-10-01' and '2021-10-03'
order by t4.dt asc;