小让の码场

外观

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

Static BadgeStatic Badge

题目描述 ​

商品信息表tb_product_info

idproduct_idshop_idtagin_pricequantityrelease_time
18001901日用6010002020-01-01 10:00:00
28002901零食1405002020-01-01 10:00:00
38003901零食1605002020-01-01 10:00:00

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

订单总表tb_order_overall

idorder_iduidevent_timetotal_amounttotal_cntstatus
13010041022021-09-30 10:00:0017011
23010051042021-10-01 10:00:0016011
33010031012021-10-02 10:00:0030021
43010021022021-10-03 11:00:0023521

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

订单明细表tb_order_detail

idorder_idproduct_idpricecnt
130100480021801
230100580021701
33010028001851
430100280031801
530100380021501
630100380031801

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

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

注：

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

输出示例：

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

dtsale_rateunsale_rate
2021-10-010.3330.667
2021-10-020.6670.333
2021-10-031.0000.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；

SQL Schema ​

sql
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);

答案 ​

sql
SELECT t1.dt,
       ROUND(COUNT(DISTINCT t2.product_id) /
             (SELECT COUNT(product_id) FROM tb_product_info WHERE shop_id = '901'), 3)     AS `sale_rate`,
       1 - ROUND(COUNT(DISTINCT t2.product_id) /
                 (SELECT COUNT(product_id) FROM tb_product_info WHERE shop_id = '901'), 3) AS `unsale_rate`
FROM (SELECT DISTINCT DATE(event_time) AS `dt`
      FROM tb_order_overall
      WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
        AND status = 1) t1
         LEFT JOIN (SELECT DATE(too.event_time) AS `dt`,
                           tod.product_id
                    FROM tb_order_overall too
                             INNER JOIN tb_order_detail tod on too.order_id = tod.order_id
                             INNER JOIN tb_product_info tpi on tod.product_id = tpi.product_id
                    WHERE tpi.shop_id = '901'
                      AND too.status = 1) t2
                   ON TIMESTAMPDIFF(DAY, t2.dt, t1.dt) BETWEEN 0 AND 6
GROUP BY t1.dt
ORDER BY t1.dt;