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

-- 首先题意理解, 动销率 = (一定期间内) 有成交的商品种类 / 上架的商品种类
--             滞销率 = 1 - 动销率
-- 所以实际情况与销量无关,只要看每天都卖了哪些产品（总表status != 2）
-- 以及有哪些产品上架了（上架时间早于或等于统计期间的最后一天）
-- 最后考虑的是,我要作为参考期间的日期是什么时候

with tb_sales_product as -- 901店铺每天销售产品清单
         (
             select distinct date(event_time) event_date, product_id sales
             from tb_order_detail
                      inner join tb_order_overall using (order_id)
                      inner join tb_product_info using (product_id)
             where shop_id = 901 and status != 2
         )
, tb_onsale_product as -- 901店铺所有产品的上架时间(确定统计当天产品是否在售)
    (
        select distinct date(release_time) release_date, product_id onsale
        from tb_product_info
        where shop_id = 901
    )

, tb_dt as  -- 只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。
            -- 可以认为只要销售总表里有的日期就要列出,然后题目统计21年国庆前3天,作为筛选条件
            -- 注意日期去重
    (
        select distinct date(event_time) dt
        from tb_order_overall
        where date(event_time) between '2021-10-01' and '2021-10-03'
        and status != 2
    )

-- 题目应该是想考察join on条件的用法
-- 但说实话我不知道这是不是真题,如果有哪个傻子业务让数据分析师或者工程师在SQL里求这玩意,怕是脑子有病
-- 这玩意哪怕用Excel都可以直接看 在售商品种类 / 滞销商品种类 然后做个图就好,BI更是轻松完成
select dt,
       round(count(distinct sales) / count(distinct onsale), 3)
           as sale_rate,
       round(1 - count(distinct sales) / count(distinct onsale), 3)
           as unsale_rate
from tb_dt

-- join语法的本质是笛卡尔积(共产生 A表行数 * B表行数 条记录)
-- 即对于<日期表tb_dt>和<历史销售产品清单tb_sales_product>实际产生3 * 6 = 18条记录
-- on 条件保留符合条件的记录
-- 销售日期event_date 在统计期间[dt-6,dt]之间的
-- 例如10-01的6条数据中,有09-30/10-01在这个区间内,保留2条
-- 例如10-02的6条数据中,有09-30/10-01/10-02在这个区间内,保留4条
-- 例如10-01的6条数据中,有09-30/10-01/10-02/10-03在这个区间内,保留6条
    left join tb_sales_product on event_date between date_sub(dt, interval 6 day ) and dt

-- 同上,只要 <上架日期release_date> <= <统计期间的最大值dt> (本题没有下架日期)
-- 故,哪怕产品在10-01这天上架,也要统计
-- 如考虑下架情况可使用如下语句(下架时间offline_time)
--  left join tb_onsale_product on release_date <= dt and date(offline_time) >= date_sub(dt, interval 6 day)
    left join tb_onsale_product on release_date <= dt
group by dt
order by dt;