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

id	product_id	shop_id	tag	int_	quantity	release_time
1	8001	901	日用	60	1000	2020-01-01 10:00:00
2	8002	901	零食	140	500	2020-01-01 10:00:00
3	8003	901	零食	160	500	2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id	order_id	uid	event_time	total_amount	total_cnt	status
1	301004	102	2021-09-30 10:00:00	170	1	1
2	301005	104	2021-10-01 10:00:00	160	1	1
3	301003	101	2021-10-02 10:00:00	300	2	1
4	301002	102	2021-10-03 11:00:00	235	2	1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id	order_id	product_id	price	cnt
1	301004	8002	180	1
2	301005	8002	170	1
3	301002	8001	85	1
4	301002	8003	180	1
5	301003	8002	150	1
6	301003	8003	180	1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

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

注：

动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

输出示例：

示例数据的输出结果如下：

dt	sale_rate	unsale_rate
2021-10-01	0.333	0.667
2021-10-02	0.667	0.333
2021-10-03	1.000	0.000

解释：

10月1日的近7日（9月25日---10月1日）店铺901有销量的商品有8002，截止当天在售商品数为3，动销率为0.333，滞销率为0.667；

10月2日的近7日（9月26日---10月2日）店铺901有销量的商品有8002、8003，截止当天在售商品数为3，动销率为0.667，滞销率为0.333；

10月3日的近7日（9月27日---10月3日）店铺901有销量的商品有8002、8003、8001，截止当天店铺901在售商品数为3，动销率为1.000，

滞销率为0.000；

示例1

输入:

DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '日用', 60, 1000, '2020-01-01 10:00:00'),
  (8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
  (8003, 901, '零食', 160, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301004, 102, '2021-09-30 10:00:00', 170, 1, 1),
  (301005, 104, '2021-10-01 10:00:00', 160, 1, 1),
  (301003, 101, '2021-10-02 10:00:00', 300, 2, 1),
  (301002, 102, '2021-10-03 11:00:00', 235, 2, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301004, 8002, 180, 1),
  (301005, 8002, 170, 1),
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8002, 150, 1),
  (301003, 8003, 180, 1);

输出:

2021-10-01|0.333|0.667
2021-10-02|0.667|0.333
2021-10-03|1.000|0.000

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2021-12-19

with t as
(
    select 
    tod.order_id
    ,tod.product_id
    ,date(event_time) dt
    ,shop_id
    from tb_order_detail tod
    left join tb_order_overall too
    on tod.order_id = too.order_id
    left join tb_product_info tpi
    on tod.product_id = tpi.product_id
    where shop_id = 901
)
select dt,sale_rate,unsale_rate
from(
select
'2021-10-01' dt
,round(sum(inde_1)/count(*),3) sale_rate
,round(1-sum(inde_1)/count(*),3) unsale_rate
from
(
    select max(if(dt between '2021-09-25' and '2021-10-01',1,0)) inde_1
    from t
    group by product_id
) t1
union all
select
'2021-10-02' dt
,round(sum(inde_2)/count(*),3) sale_rate
,round(1-sum(inde_2)/count(*),3) unsale_rate
from
(
    select max(if(dt between '2021-09-26' and '2021-10-02',1,0)) inde_2
    from t
    group by product_id
) t2
union all
select
'2021-10-03' dt
,round(sum(inde_3)/count(*),3) sale_rate
,round(1-sum(inde_3)/count(*),3) unsale_rate
from
(
    select max(if(dt between '2021-09-27' and '2021-10-03',1,0)) inde_3
    from t
    group by product_id
) t3
) tt
where tt.dt in (select distinct date(event_time) from tb_order_overall)

Mysql 解法, 执行用时: 37ms, 内存消耗: 6492KB, 提交时间: 2022-01-22

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

Mysql 解法, 执行用时: 37ms, 内存消耗: 6504KB, 提交时间: 2022-01-25

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

t1 as (
    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 (
    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 1
    order by 1

Mysql 解法, 执行用时: 37ms, 内存消耗: 6512KB, 提交时间: 2021-12-18

SELECT dt, sale_rate, 1 - sale_rate as unsale_rate
FROM (
    SELECT dt, ROUND(MIN(sale_pid_cnt) / COUNT(all_pid), 3) as sale_rate
    FROM (


SELECT dt, COUNT(DISTINCT IF(shop_id!=901, NULL, product_id)) as sale_pid_cnt
        FROM (
            SELECT DISTINCT DATE(event_time) as dt
            FROM tb_order_overall
            WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
        ) as t_dates
        LEFT JOIN (
            SELECT DISTINCT DATE(event_time) as event_dt, product_id
            FROM tb_order_overall
            JOIN tb_order_detail USING(order_id)
        ) as t_dt_pid ON DATEDIFF(dt,event_dt) BETWEEN 0 AND 6
        LEFT JOIN tb_product_info USING(product_id)
        group by dt) as t_dt_901_pid_cnt
    LEFT JOIN (
        -- 店铺901每个商品上架日期
        SELECT DATE(release_time) as release_dt, product_id as all_pid
        FROM tb_product_info
        WHERE shop_id=901
    ) as t_release_dt ON dt >= release_dt # 当天店铺901已上架在售的商品
    GROUP BY dt
) as t_dt_sr

Mysql 解法, 执行用时: 37ms, 内存消耗: 6548KB, 提交时间: 2021-12-17

select dt,
round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = 901),3),
round(1-count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = 901),3)
from
(select date(event_time) dt from tb_order_overall
where date(event_time) between '2021-10-01' and '2021-10-03') a
left join
(select date(c.event_time) fdt,b.product_id product_id
from tb_order_detail b left join tb_order_overall c using(order_id)
left join tb_product_info d using(product_id)
where d.shop_id = 901) f 
on datediff(a.dt,f.fdt) between 0 and 6
group by dt
order by dt asc;

上一题

下一题

详情