题解 | #店铺901国庆期间的7日动销率和滞销率#
店铺901国庆期间的7日动销率和滞销率
http://www.nowcoder.com/practice/e7837f66e8fb4b45b694d24ea61f0dc9
select dt1,round(count(distinct if(timestampdiff(day,dt,dt1) between 0 and 6, tb1.product_id,null))/count(distinct if(dt1>=date(release_time),tb3.product_id,null)),3) sale_rate, 1-round(count(distinct if(timestampdiff(day,dt,dt1) between 0 and 6, tb1.product_id,null))/count(distinct if(dt1>=date(release_time),tb3.product_id,null)),3) unsale_rate from (select date(event_time) dt1 from tb_order_overall having dt1 between '2021-10-01' and '2021-10-03') tb2 , (select b.product_id,date(event_time) dt from tb_order_overall a left join tb_order_detail b on a.order_id=b.order_id left join tb_product_info c on b.product_id=c.product_id where shop_id=901) tb1 left join tb_product_info tb3 on tb1.product_id=tb3.product_id where shop_id=901 group by dt11.注意即使10.1-10.3某天店铺901无销量也得输出--通过抓取 tb_order_overall 表中有销售日期实现。
2.核心是求近7天销售的商品种类数，通过笛卡尔积把表1（10.1-10.3任何有销售的日期）和表2（每天商品销售情况）先连接，通过timestampdiff(day,表2日期，表1日期）的值来判断是否是近7天销售的商品
3.判断当日在售商品数，通过count(if())来实现
投递Oracle等公司7个岗位 >
华为主管面102人在聊