店铺901国庆期间的7日动销率和滞销率_

Mysql

这题我不会啊

with t as # 取日期 10-01 到 10-03
(select date(event_time) dt
 from tb_order_overall 
 where date(event_time) between '2021-10-01' and '2021-10-03'),
 
 t1 AS # 901商店已成交订单中，每个下单日期里的 product_id
 (select date(event_time) dt,tod.product_id
 FROM tb_order_detail tod JOIN tb_order_overall too ON tod.order_id = too.order_id and status = 1
                          JOIN tb_product_info tpi ON tod.product_id = tpi.product_id and shop_id = '901'),
                          
t2 as # 计算 每个日期下 901商店的 在售商品总数
(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 t1.product_id) / sum_product ,3) as sale_rate, 
             ROUND(1- (count(distinct t1.product_id) / sum_product ),3) as unsale_rate
from t left join t1 ON datediff(t.dt,t1.dt) between 0 and 6 
            JOIN t2 on t.dt = t2.dt
group by t.dt
ORDER by t.dt

发表于 2021-12-20 19:25:31 回复(14)

Mysql

刷题冲冲冲！

根据题目描述 “只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。” ，因此要先获得10-01到10-03之间有成交记录的日期。再获得901店铺的订单信息（日期和商品）。使用datediff()和外联结，将10-01到10-03各天往前7天的901店铺成交信息都归为该天的成交结果。最后按天分类计算有成交记录的商品占901店铺上架产品数的比重。

with t1 as (
select distinct date(event_time) dt
from tb_order_overall
where date(event_time) between '2021-10-01' and '2021-10-03'),
t2 as (
select date(too.event_time) dt, tod.product_id
from tb_order_detail tod join tb_order_overall too on tod.order_id = too.order_id
                         join tb_product_info tpi on tod.product_id = tpi.product_id
where tpi.shop_id = 901 and too.status = 1
)

select t1.dt,
       round(count(distinct t2.product_id)/(select count(product_id) from tb_product_info where date(release_time) <= t1.dt and shop_id = 901),3) sale_rate,
       round(1-count(distinct t2.product_id)/(select count(product_id) from tb_product_info where date(release_time) <= t1.dt and shop_id = 901),3) unsale_rate
from t1 left join t2 on datediff(t1.dt,t2.dt) between 0 and 6
group by t1.dt
order by t1.dt

发表于 2021-12-31 13:33:20 回复(9)

小白船

实话说，这题目真的是出得跟shit一样。这题目描述，问国庆前三天的7日的动销率，但是只要这三天中某一天没有销量，就不需要计算这一天。我去，可是它问的是7日的动销率，如果仅仅是这一天没销量，那前六天就不用计算了？做了很久，提交的时候才发现这个问题，笨死了

发表于 2021-12-23 15:29:28 回复(10)

Mysql

Queen_D

一、每个有下单的日期与下单商品id

select date(t2.event_time) as dt,
       t3.product_id
from tb_order_overall t2
join tb_order_detail t3
on t2.order_id=t3.order_id
join tb_product_info t1
on t1.product_id=t3.product_id and t1.shop_id=901

2021-09-30|8002
2021-10-02|8003
2021-10-03|8003
2021-10-03|8001

二、截止到每一天的在售商品数

select date(event_time) as dt,
       count(distinct case when datediff(date(event_time),release_time)>=0 then product_id end) as onsale_cnt
                    #这里的distinct不要丢！
from tb_product_info,tb_order_overall
where shop_id=901
group by dt

2021-09-30|3
2021-10-01|3
2021-10-02|3
2021-10-03|3

三、拼接国庆三天和每一天对应的前六天的商品售出情况

select *
from
(select date(event_time) as dt
from tb_order_overall 
where date(event_time) BETWEEN '2021-10-01' and '2021-10-03') as a
left JOIN
(select date(t2.event_time) as dt,
       t3.product_id
from tb_order_overall t2
join tb_order_detail t3
on t2.order_id=t3.order_id
join tb_product_info t1
on t1.product_id=t3.product_id and t1.shop_id=901) as b
on datediff(a.dt,b.dt) BETWEEN 0 and 6
JOIN
(select date(event_time) as dt,
       count(case when datediff(date(event_time),release_time)>=0 then product_id end) as onsale_cnt
from tb_product_info,tb_order_overall
where shop_id=901
group by dt) as c
on a.dt=c.dt

2021-10-01|2021-09-30|8002|2021-10-01|3
2021-10-02|2021-10-02|8003|2021-10-02|3
2021-10-02|2021-09-30|8002|2021-10-02|3
2021-10-03|2021-10-02|8003|2021-10-03|3
2021-10-03|2021-10-03|8003|2021-10-03|3
2021-10-03|2021-10-03|8001|2021-10-03|3
2021-10-03|2021-09-30|8002|2021-10-03|3
四、计算

select a.dt,
       round(count(distinct b.product_id)/onsale_cnt,3) as sale_rate,
       round(1-(count(distinct b.product_id)/onsale_cnt),3) as unsale_rate
from
(select date(event_time) as dt
from tb_order_overall 
where date(event_time) BETWEEN '2021-10-01' and '2021-10-03') as a
left JOIN
(select date(t2.event_time) as dt,
       t3.product_id
from tb_order_overall t2
join tb_order_detail t3
on t2.order_id=t3.order_id and t2.status=1
join tb_product_info t1
on t1.product_id=t3.product_id and t1.shop_id=901) as b
on datediff(a.dt,b.dt) BETWEEN 0 and 6
JOIN
(select date(event_time) as dt,
       count(distinct case when datediff(date(event_time),date(release_time))>=0 then product_id end) as onsale_cnt
from tb_product_info,tb_order_overall
where shop_id=901
group by dt) as c
on a.dt=c.dt
group by a.dt
order by a.dt

发表于 2022-02-03 17:32:19 回复(4)

Mysql

yyqf1740

1.关键坑在于即使10.1-10.3某天店铺901无销量也得输出。
2. left join 用的多了，把最原始的笛卡尔积(cross join）忽略了，也可以用 select * from tb1,tb2 实现。

3.核心是求近7天销售的商品种类数，通过笛卡尔积把表1（10.1-10.3任何有销售的日期）和表2（每天商品销售情况）先连接，通过timestampdiff(day,表2日期，表1日期）的值来判断是否是近7天销售的商品

3.判断当日在售商品数，通过count(if())来实现

4.如果窗口函数能支持 count(distinct product_id) over ...就好了（个人吐槽）

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 dt1

发表于 2022-01-11 16:47:41 回复(1)

Mysql

酸菜鱼土豆大侠

【类型】：经典题时间段

【场景】：已知日期和天数，得到某时间段（某个日期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

发表于 2022-11-06 23:52:04 回复(2)

PythonNoo6

当数据源不是每天都有数据且要获取近N天有数据行的COUNT，两个表join时ON条件设置为 datediff(a.dt,b.dt) BETWEEN 0 and N

发表于 2022-02-16 09:46:34 回复(1)

Mysql

于夜

这题目的需求写的也太烂了

1、统计2号又说不需要

2、不统计2号又说没统计

发表于 2021-12-29 17:41:06 回复(5)

Mysql

一个好懒的人

发现滑动窗口用不了以后，就用union暴力连接了

然后我发现

把10-02的记录填上以后，

预期输出是没有10-02号的；

把10-02的记录删掉以后，

预期输出是有10-02号的🙃

真就不能取巧呗hhh

发表于 2022-05-17 16:17:12 回复(5)

Mysql

给一个让我瞠目的算法

with t1 as (
    select date(B.event_time) dt, A.product_id product_id, C.release_time release_time
    from tb_order_detail A left join tb_order_overall B on A.order_id = B.order_id
    left join tb_product_info C on A.product_id = C.product_id
    where C.shop_id = 901
    union
    select '2021-10-01' as dt, null as product_id, null as release_time --确保一定有这三个日期
    union 
    select '2021-10-02' as dt, null as product_id, null as release_time
    union
    select '2021-10-03' as dt, null as product_id, null as release_time
)

select distinct t2.dt dt, round((
    select count(distinct product_id) from t1
    where timestampdiff(SQL_TSI_DAY, date(t1.dt), t2.dt) between 0 and 6
    )/(
        select count(distinct product_id) from tb_product_info
        where shop_id = 901 and release_time <= t2.dt
    ), 3) sale_rate, 
    round(1- (
    select count(distinct product_id) from t1
    where timestampdiff(SQL_TSI_DAY, date(t1.dt), t2.dt) between 0 and 6
    )/(
        select count(distinct product_id) from tb_product_info
        where shop_id = 901 and release_time <= t2.dt --保证产品上架
    ), 3) unsale_rate
from t1 t2
where t2.dt between '2021-10-01' and '2021-10-03' 
and t2.dt in (
    select date(event_time) dt from tb_order_overall
) --确保当天存在一个商店有销量，不然不输出这一天
order by dt asc;

写完发现解答区很多方法都欠考虑，又得不考虑当天是否有销量，有的不考虑产品的上架日期

发表于 2023-02-10 12:00:07 回复(0)

Mysql

wjan.eyre

繁琐版题解：
①先设置10月1-3日的日期
②计算这三日每日的在售商品总数
③计算这三日每日的在售商品中存在销量的商品数
注释：如果这个三日没有销量，则不显示这一天的动销率和滞销率（第三个测试用例）
with tm as
(
select '2021-10-01' dt
union select '2021-10-02' dt
union select '2021-10-03' dt
)
select t3.dt, round(t4.sale_products/t3.curr_product_cnt,3) sale_rate,
round(1-t4.sale_products/t3.curr_product_cnt,3) unsale_rate
from(
select dt,count(distinct sale_product) curr_product_cnt
from
(
select dt, if(date(b.release_time)<=dt,product_id,null) sale_product
from tm
join (select * from tb_product_info where shop_id='901') b on 1=1 order by dt
)t1 group by dt
)t3,
(
select dt, count(distinct sale_product) sale_products from
(
select tm.dt,c.product_id,date(b.event_time) sale_dt,
if((datediff(tm.dt,date(b.event_time)) between 0 and 6),c.product_id,null) sale_product
from tm
right join tb_order_overall b on 1=1
left join tb_order_detail c
on b.order_id=c.order_id
where c.product_id in (select distinct product_id from tb_product_info where shop_id='901')
order by tm.dt
)t2 group by dt
)t4
where t3.dt=t4.dt and t3.dt in (select distinct date(event_time) from tb_order_overall)
group by t3.dt

发表于 2021-12-17 15:42:11 回复(2)

Mysql

War张

with t1 as (
    -- 首先找到需要输出哪几天的数据（只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。）
    select 
        distinct date(event_time) dt 
    from tb_order_overall 
    where date(event_time) between '2021-10-01' and '2021-10-03'
),
t2 as (
    -- 统计国庆头3天，每天的近7天里，有销量的商品数量
    select
        t1.dt,
        count(distinct if(shop_id = 901,od.product_id,null)) cnt
        -- 为了实现“只要当天任一店铺有任何商品的销量就输出该天的结果,即使店铺901当天的动销率为0”
        -- 这一条件就不能在where中将其他店铺的记录删去，可以放在if表达式中，不是901则不计。
    from t1
    left join tb_order_overall oo
    on datediff(t1.dt,date(event_time)) between 0 and 6
    left join tb_order_detail od 
    on oo.order_id = od.order_id
    left join tb_product_info pi 
    on od.product_id = pi.product_id
    where status = 1
    group by t1.dt
),
t3 as (
    -- 统计国庆头三天里，每天在售商品的数量
    select
        t1.dt,
        count(if(t1.dt >= release_time,product_id,null)) total_cnt
    from t1,tb_product_info
    where shop_id = 901
    group by tt
)
select
    t2.dt,
    round(cnt / total_cnt,3) sale_rate,
    1 - round(cnt / total_cnt,3) unsale_rate
from t2,t3
where t2.dt = t3.dt
order by t2.dt;

发表于 2023-08-06 23:33:24 回复(0)

Mysql

颠仔

这句话是这一整道题目的大坑，一定要注意

	
	
		只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。
	


with t as 
(
    select
        order_id,
        uid,
        event_time,
        "2021-10-01" sign
    from tb_order_overall
    where date_format(event_time,"%Y-%m-%d") <= "2021-10-01" and date_format(event_time,"%Y-%m-%d") >= "2021-09-25"
    union all
    select
        order_id,
        uid,
        event_time,
        "2021-10-02" sign
    from tb_order_overall
    where date_format(event_time,"%Y-%m-%d") <= "2021-10-02" and date_format(event_time,"%Y-%m-%d") >= "2021-09-26"
    union all 
    select
        order_id,
        uid,
        event_time,
        "2021-10-03" sign
    from tb_order_overall
    where date_format(event_time,"%Y-%m-%d") <= "2021-10-03" and date_format(event_time,"%Y-%m-%d") >= "2021-09-27"
)
select 
    sign dt,
    round(count(distinct if(shop_id=901,product_id,null))/(select count(1) from tb_product_info where shop_id=901),3) sale_rate,
    round(1 - count(distinct if(shop_id=901,product_id,null))/(select count(1) from tb_product_info where shop_id=901),3) unsale_rate
from 
(
    select 
        t.order_id,
        uid,
        event_time,
        sign,
        tb_order_detail.product_id,
        shop_id,
        release_time
    from t
    inner join tb_order_detail on t.order_id = tb_order_detail.order_id
    inner join tb_product_info on tb_order_detail.product_id = tb_product_info.product_id
) tmp
where shop_id = 901&nbs***bsp;(shop_id !=901 and sign in (select date_format(event_time,"%Y-%m-%d") from tb_order_overall))
group by sign
order by sign ASC

发表于 2022-07-30 23:20:18 回复(0)

Mysql

要瘦的三岁

with #最近发现with用起来真方便

#第一步提取出国庆三天的日期，我一开始还以为手动打出呢

t1 as
(select distinct date_format(event_time,'%Y-%m-%d') as dt_3
from tb_order_overall where date_format(event_time,'%Y-%m-%d') in ('2021-10-01','2021-10-02','2021-10-03')
)

,

#第二步计算各个日期，各个产品被购买的次数

t2 as
(select date_format(a.event_time,'%Y-%m-%d') as dt_7,product_id,count(a.id) as sale_num
from tb_product_info c
left join
(select b.id,b.event_time,d.product_id
from
tb_order_overall b
left join tb_order_detail d
using(order_id)
) a
using(product_id)
where c.shop_id = '901'
group by 1,2
)

,

#第三步每个国庆日期，关联到他对应的前7天，然后顺便计算一下每个产品被购买的次数，这里注意一下，要用左连接，我一开始用内连接，会导致没有销售记录的日期被过滤掉

t3 as
(select dt_3,product_id,sum(sale_num) as if_sale
from t1
left join t2
on datediff(dt_3, dt_7) between 0 and 6
group by 1,2
)

#准备完成，计算最终结果，统计有购买计数的/info表里901总的商品数

select dt_3 as dt,
round(
sum(case when if_sale >= 1 then 1 else 0 end)
/
(select count(distinct product_id) from tb_product_info where shop_id = '901'),3) as sale_rate,

1 - round(
sum(case when if_sale >= 1 then 1 else 0 end)
/
(select count(distinct product_id) from tb_product_info where shop_id = '901'),3) as unsale_rate
from t3
group by 1
order by 1
#欢迎各位提点啊

发表于 2022-03-15 21:06:54 回复(1)

Mysql

ushbjjfds

# 题目条件中的”误区“：
# 以10-02为例，如果在tb_order_overall表中存在10-02当天的购买记录，无论买的是不是901店铺的商品，结果中都要显示该日期的条目
# 反之，如果在tb_order_overall表中不存在10-02当天的购买记录，那么就不要显示该日期的条目

# 商品id、店铺id和售出日期
# 这一步不能筛选是901售卖的，否则接下来会略过上述提及的情况
with tmp1 as (
    select distinct t1.product_id,
    t1.shop_id,
    date(t2.event_time) as dt
    from tb_product_info as t1
    join tb_order_overall as t2 
    inner join tb_order_detail as t3
    on t1.product_id=t3.product_id and t2.order_id=t3.order_id
    order by dt),

#某日期前店铺901上架的商品数
tmp2 as (
    select distinct date(t1.event_time) as release_dt, count(distinct t2.product_id) as cnt
    from tb_order_overall as t1,tb_product_info as t2
    where date(t2.release_time)<=date(t1.event_time)
    and t2.shop_id=901
    group by release_dt)

# 这一步不能使用where语句筛选是901售卖的商品，否则会失去发生了“售卖非901店铺商品”的日期的条目
# 作为替代，在count中使用if语句作为筛选
select * from (
    select distinct t1.dt,
    round(count(distinct if(t2.shop_id!=901,null,t2.product_id))/t3.cnt,3) as sale_rate,
    1- round(count(distinct if(t2.shop_id!=901,null,t2.product_id))/t3.cnt,3) as unsale_rate
    from tmp1 as t1,tmp1 as t2,tmp2 as t3
    where t2.dt<=t1.dt 
    and timestampdiff(day,t2.dt,t1.dt)<=6
    and t3.release_dt=t1.dt
    group by t1.dt) as tmp
where dt between '20211001' and '20211003'

发表于 2022-03-10 15:05:08 回复(0)

Mysql

好想找个活养家

/*问题：请计算店铺901 WHERE shop_id=901
在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，
按日期升序排序。    ORDER BY DATE(event_time) ASC
注：
动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。      COUNT(DISTINCT product_id)/BIT_COUNT(DISTINCT produc_id)
滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。
需要获取每天出售了哪些商品，继而退出前七天的商品种类数 可以用where exists 来表示。
*/
WITH tmp AS
     (SELECT DATE(too1.event_time) AS sale_day, tod1.product_id ,tpi1.shop_id
        FROM tb_order_detail AS tod1 LEFT JOIN tb_order_overall AS too1
	      ON tod1.order_id = too1.order_id
		     LEFT JOIN tb_product_info AS tpi1
	      ON tod1.product_id = tpi1.product_id) -- 这就是一个简单的链接查询，把三个表链接起来了
SELECT   t1.sale_day
       , ROUND( COUNT(DISTINCT CASE WHEN t2.shop_id=901 THEN t2.product_id ELSE NULL END) 
    -- 为了实现“只要当天任一店铺有任何商品的销量就输出该天的结果”这一条件就不能在where中将其他店铺的记录删去，可以放在CASE 表达式中，不是901则不计。
               /(SELECT COUNT(DISTINCT product_id) FROM tb_product_info WHERE shop_id=901 AND DATE(release_time)<=t1.sale_day),3) AS sale_rate
    -- 一个子查询查询901店铺当天一共上架了多少商品
       , ROUND(1-COUNT(DISTINCT CASE WHEN t2.shop_id=901 THEN t2.product_id ELSE NULL END)
               /(SELECT COUNT(DISTINCT product_id) FROM tb_product_info WHERE shop_id=901 AND DATE(release_time)<=t1.sale_day),3) AS unsale_rate
    -- 动销率和滞消率相加正好等于1
  FROM tmp AS t1 LEFT JOIN tmp AS t2
    ON t2.sale_day BETWEEN DATE_SUB(t1.sale_day, INTERVAL 6 DAY) AND t1.sale_day
    -- 用自连接的方法获得前7天的产品销售情况。
 WHERE t1.sale_day BETWEEN '2021-10-01' AND '2021-10-03'
 GROUP BY t1.sale_day
 ORDER BY t1.sale_day ASC

发表于 2022-01-08 15:34:57 回复(1)

鱼街

with main as (
-- 清洗数据：得到所有店铺中，10月3日之前在售的商品的 售出时间在国庆头七天到10月3日的数据
    select tpi.shop_id      shop_id      -- 店铺id
         , tpi.product_id   product_id   -- 商品id
         , date_format(tpi.release_time, '%Y-%m-%d') release_time -- 商品上架时间
         , date_format(too.event_time, '%Y-%m-%d')   event_time   -- 用户购买时间
    from tb_product_info tpi
            left join tb_order_detail tod on tpi.product_id = tod.product_id
            left join tb_order_overall too on tod.order_id = too.order_id
    where date(event_time) between '2021-09-25' and '2021-10-03'
      and date(release_time) <= '2021-10-03'
      and status = '1'
)
, tb_dt as (
-- 筛选出 所有店铺中，在10月1~3日 有售出商品的日期
    select distinct
        date_format(event_time, '%Y-%m-%d') dt
    from tb_order_overall
    where date(event_time) between '2021-10-01' and '2021-10-03'
)
, tmp as (
-- 选出901店铺的 product_id商品，在 dt的前七天是否上架了，是否售出过
    select
        product_id
        ,dt
        ,max(if(datediff(dt, event_time) between 0 and 6, 1, 0)) 'is_shelf'  -- 是否售出过
        ,max(if(release_time<=dt, 1, 0)) 'is_sale'  -- 是否上架了
    from main join tb_dt
    where shop_id='901'
    group by product_id, dt
)
select
    dt
    ,round(shelf_cnt/sale_cnt, 3) sale_rate
    ,1 - round(shelf_cnt/sale_cnt, 3) unsale_rate
from (
    select
        dt
        , sum(is_sale) sale_cnt  -- 售出的商品种类
        , sum(is_shelf) shelf_cnt -- 上架商品的数目
    from tmp
    group by dt
) t

1. 清洗数据：得到所有店铺中，10月3日之前在售商品的售出时间在国庆头七天9月15日~10月3日的数据（拿出有用到的数据，完全没用的数据可以先剔除出去，之所以不提前筛选901店铺，原因在于只要当天有任何店铺的任何商品售出了，都要显示901店铺的滞销率）

2. 筛选出所有店铺中，在10月1~3日有售出商品的日期（解决上面【只要当天有任何店铺的任何商品售出了，都要显示901店铺的滞销率】，应该要显示的日期有哪些）

3. 首先对【表一main】和【表二tb_dt】进行笛卡尔积

4. 对得到的笛卡尔积，进行筛选，选出901店铺的 product_id商品，在dt的前七天【是否上架了，是否售出过】

5. 后面就简单了，查看dt这天，【dt-6，dt】售出的商品种类数和 dt当天上架的商品总种类数

6. 得到最终结果

发表于 2024-09-20 20:49:06 回复(0)

Mysql

游客_1004

解题排名第一的答案有一点小问题，答主默认每天的商品数都是一样的，但实际上要结合tb_product_info的release_time来判断，对原答案做了一点修改：

SELECT dt, ROUND(cnt / total_cnt, 3) AS sale_rate, ROUND(1 - cnt / total_cnt, 3) AS unsale_rate
FROM
(
	SELECT DISTINCT
		DATE(event_time) AS dt,
		(
			SELECT COUNT(DISTINCT (IF(shop_id != 901, null, product_id)))
			FROM tb_order_overall
			JOIN tb_order_detail USING (order_id)
			JOIN tb_product_info USING (product_id)
			WHERE TIMESTAMPDIFF(DAY, event_time, to1.event_time) BETWEEN 0 AND 6
		) AS cnt,
		(
			SELECT COUNT(DISTINCT product_id)
            FROM tb_product_info
			WHERE shop_id = 901 and release_time <= to1.event_time
		) AS total_cnt 
	FROM tb_order_overall as to1
	WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
) as t0
ORDER BY dt;

最大的难点是依赖外部查询的子查询，几个板块只有这一题有用这种写法。比如as cnt这个子查询，如果把这个子查询单独拿出来执行，只会得到一个值，但如果作为子查询，会对表tb_order_overall(to1)中每一行执行一次，这样每一天执行的结果都不一样

发表于 2024-09-09 21:40:40 回复(0)

爱刷美剧的柯基在许愿

select d,round(count(distinct if(timestampdiff(day,f,d) between 0 and 6,t.product_id,null))/count(distinct if(d>=date(release_time),u.product_id,null)),3) 
,1-round(count(distinct if(timestampdiff(day,f,d) between 0 and 6, t.product_id,null))/count(distinct if(d>=date(release_time),t.product_id,null)),3) 
from(select date(event_time) d from tb_order_overall having d between '2021-10-01' and '2021-10-03') y
,(select product_id,date(event_time) f from tb_order_overall  join tb_order_detail using(order_id) join tb_product_info using(product_id)where shop_id=901) t 
join tb_product_info u using(product_id)
group by 1

发表于 2024-08-03 14:51:41 回复(0)

牛客609440145号

这道题，为了曲折而曲折，毫无优美感。尽管有使用基础操作做出来的方法，依然被这狗屎般的设计膈应到了。。。答案如下

select dt,if(isnull(sale_rate),0.000,sale_rate) sale_rate,if(isnull(unsale_rate),1.000,unsale_rate) unsale_rate
from (select date_format(event_time,'%Y-%m-%d') dt
    from tb_order_overall
    where date_format(event_time,'%Y%m%d') between 20211001 and 20211003
    group by date_format(event_time,'%Y-%m-%d')
    )a
left join
    (
    select date_format(base_date,'%Y-%m-%d') date,round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id=901),3) sale_rate, round(1-count(distinct product_id)/(select count(product_id) from tb_product_info  where shop_id=901),3) unsale_rate
    from(
        select c.*,b.product_id, price 
        from tb_product_info a
        join tb_order_detail b on a.product_id=b.product_id 
        join (select b.*,date_format(a.event_time,'%Y%m%d') base_date
            from tb_order_overall a join tb_order_overall b
            on datediff(a.event_time,b.event_time)<=6 and datediff(a.event_time,b.event_time)>=0
            where date_format(a.event_time,'%Y%m%d') between 20211001 and 20211003
            )c 
        on b.order_id=c.order_id
        where shop_id=901  
        )a
    group by base_date 
    )b
on a.dt=b.date
order by dt

发表于 2024-04-22 22:34:33 回复(0)

店铺901国庆期间的7日动销率和滞销率

分析思路

求解代码

问题信息

热门推荐

相关试题