【类型】：经典题时间段

【场景】：已知日期和天数，得到某时间段（某个日期7天内的时间段）的某些指标

【分类】：时间函数、多表连接

分析思路

难点：

1.如何处理“只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。”的条件

新学到：

不用窗口函数，而是利用条件来确定某时间段（某个日期7天内的日期）：datediff(dt_3, dt_7) between 0 and 6

（1）统计2021-10-01到2021-10-03之间有销售记录的日期

题目要求：只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。所以要统计2021-10-01到2021-10-03之间有销售记录的日期，因为有销售记录的才输出。

（2）统计店铺901产品的购买记录

三表连接把购买记录输出

  • [使用]：join using()

（3）计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序

动销率 = 有销量的商品/已上架总商品数；滞销率 = 1- 动销率

  • [使用]：left join

  • [使用]：datediff(dt_3, dt_7) between 0 and 6

最终结果

select 查询结果 [创作者;等级;连续回答天数]
from 从哪张表中查询数据[多表]
group by 分组条件 [创作者;等级]
having 判断条件 [连续回答问题的天数大于等于3]
order by 对查询结果排序 [创作者升序];

求解代码

错误代码

偷懒想要使用union暴力连接，发现不满足题目中的条件"只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。"

因为union的使用前提就是知道哪一天输出哪一天不输出，但是这个使用union时不能确定，所以还是得使用多表连接。

#计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序
with
    temp1 as(
        select
            '2021-10-01' as dt,
            round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as sale_rate, #有销量的商品
            1-round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as unsale_rate
        from tb_order_overall
        left join tb_order_detail using(order_id)
        left join tb_product_info using(product_id)
        where date(event_time) between '20210925' and '20211001'
        and shop_id = '901'
        and status = 1
    )
    ,temp2 as(
        select
            '2021-10-02' as dt,
            round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as sale_rate, #有销量的商品
            1-round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as unsale_rate
        fmysqlrom tb_order_overall
        left join tb_order_detail using(order_id)
        left join tb_product_info using(product_id)
        where date(event_time) between '20210926' and '20211002'
        and shop_id = '901'
        and status = 1
    )
    ,temp3 as(
        select
            '2021-10-03' as dt,
            round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as sale_rate, #有销量的商品
            1-round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as unsale_rate
        from tb_order_overall
        left join tb_order_detail using(order_id)
        left join tb_product_info using(product_id)
        where date(event_time) between '20210927' and '20211003'
        and shop_id = '901'
        and status = 1
    )
select *
from temp1
union
select *
from temp2
union
select *
from temp3

方法一

with子句

with
    temp as(
        #统计2021-10-01到2021-10-03之间有销售记录的日期
        select distinct
            date(event_time) as dt_3
        from tb_order_overall
        where date(event_time) between '20211001' and '20211003'
        and status = 1
    )
    ,temp1 as(
        #统计店铺901产品的购买记录
        select
            date(event_time) as dt_7,
            product_id
        from tb_order_overall
        join tb_order_detail using(order_id)
        join tb_product_info using(product_id)
        where shop_id = '901'
        and status = 1
        group by dt_7,product_id
    )

#计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序
#动销率 = 有销量的商品/已上架总商品数
select
    dt_3,
    round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as sale_rate, #有销量的商品
    1-round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as unsale_rate
from temp
left join temp1 on datediff(dt_3, dt_7) between 0 and 6
group by dt_3
order by dt_3

方法二

多表连接

#计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序
##动销率 = 有销量的商品/已上架总商品数
select
    dt_3,
    round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as sale_rate, #有销量的商品
    1-round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = '901'),3) as unsale_rate
from(
    #统计2021-10-01到2021-10-03之间有销售记录的日期
    select distinct
        date(event_time) as dt_3
    from tb_order_overall
    where date(event_time) between '20211001' and '20211003'
    and status = 1
) temp
left join(
    #统计店铺901产品的购买记录
    select
        date(event_time) as time,
        product_id
    from tb_order_overall
    join tb_order_detail using(order_id)
    join tb_product_info using(product_id)
    where shop_id = '901'
    and status = 1
    group by time,product_id
) temp1 on datediff(dt_3, time) between 0 and 6
group by dt_3
order by dt_3