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

这道题应该是牛客网我目前做过的所有SQL练习题目最难的一题了，我一开始觉得无非就是到变种的连续登录题，想用 count(distinct product_id) over(partition by dt rows 6 preceding) 窗口函数做法来写，结果这个版本不支持。

这里多感谢 用户：这题我不会啊 的思路，发现他的这个思路最好理解

首先获取10月1号至10月3号的日期

select distinct(date(event_time)) as dt
    from tb_order_overall
    where date(event_time) 
    between '2021-10-01' AND '2021-10-03'

然后关联tb_product_info，tb_order_overall，tb_order_detail三个表单用来获取店铺901的日期和订单的明细

 select date(event_time) dt,
        b.product_id
        from tb_order_overall a
        inner join tb_order_detail b
        on a.order_id = b.order_id and status = 1
        inner join tb_product_info c
        on b.product_id = c.product_id and shop_id = '901'

利用隐性关联tb_product_info以及tb_order_overll来获取店铺901每日的上架商品数

所谓隐性关联就是利用隐藏的row_num来做关联(这个方法我一开始是真没理解)

select 
        date(event_time) dt,
        count(distinct case when datediff(date(event_time),date(release_time)) >= 0  then product_id end ) sum_product
        FROM tb_product_info,tb_order_overall
        where shop_id = '901'
        group by dt

接下来合并所有前面的结果集来获取国庆头3天的近7日的动销率和滞销率。这里要注意的是在关联t 和 t1的表的时候记得利用datediff 时间函数来获取当前所选日期的前7天的记录明细。

完整代码如下：

with t as(
    select distinct(date(event_time)) as dt
    from tb_order_overall
    where date(event_time) 
    between '2021-10-01' AND '2021-10-03'
),

t1 as (
    select date(event_time) dt,
        b.product_id
        from tb_order_overall a
        inner join tb_order_detail b
        on a.order_id = b.order_id and status = 1
        inner join tb_product_info c
        on b.product_id = c.product_id and shop_id = '901'
),

t2 as (
    select 
        date(event_time) dt,
        count(distinct case when datediff(date(event_time),date(release_time)) >= 0  then product_id end ) sum_product
        FROM tb_product_info,tb_order_overall
        where shop_id = '901'
        group by dt
)

select t.dt,
    round(count(distinct product_id)/avg(sum_product),3) as sale_rate,
    round(1-count(distinct product_id)/avg(sum_product),3) as unsale_rate
    from t 
    left join t1 
    ON datediff(t.dt,t1.dt) between 0 and 6 
    inner join t2 on t.dt = t2.dt
    GROUP BY t.dt
    order by t.dt