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

题目：

• 请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序
• 只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0

难点：

• “只要当天任一店铺有任何商品的销量就输出该天的结果” ：意思就是，即使某一天，店铺901并没有销售出任何商品，而别的店铺存在销售记录，则同样要输出901的动销率（0）、滞销率（1）
• 店铺901在2021年10月1日、2日、3日的“近7日的交易记录”：这里可以直接使用DATEDIFF函数来获取交易记录与1日、2日、3日的间隔天数，从而确认是否在近7日内

解题思路

步骤 1：先找出2021年10月1日、2日、3日，在过去7天中存在的交易记录

• 这一步，可对订单总表tb_order_overall使用自联结

  /* dt为国庆前三天，t2.order_id为对应的、在过去7天内交易订单 */
  SELECT DATE(t1.event_time) AS dt, t2.order_id
  FROM tb_order_overall AS t1 INNER JOIN tb_order_overall AS t2
  /* 联结条件为t1的交易日期 大于等于 t2的交易日期，以获取t1每一个日期对应的、过往的交易记录 */
  ON DATE(t1.event_time) >= DATE(t2.event_time)
  /* 先从t1中筛选出国庆前3天，再滤掉此三天中，超过7天的历史交易记录 */
  WHERE (DATE(t1.event_time) BETWEEN "2021-10-01" AND "2021-10-03") 
  AND (DATEDIFF(DATE(t1.event_time), DATE(t2.event_time)) <= 6) AND t2.status = 1

步骤 2：找出每一次交易所涉及的商品id、及其所属的店铺id，并据此计算，国庆前三天，每一天所售卖的商品个数（去重）

• 这里直接使用外联结，需要重点看联结条件
• 左联结商品信息表tb_product_info的目的在于，找出所有交易中，是属于店铺901的记录；当某一天的交易，在produc_id或是shop_id上都与店铺901无关时，COUNT(DISTINCT pi.product_id)则为0 —— 注意，这里必须对表pi的product_id进行计数，只有该字段能反应店铺901的交易情况

  SELECT oo.dt, COUNT(DISTINCT pi.product_id) /* 由于pi.product_id可能存在重复，因此需要DISTINCT */
  FROM
      (SELECT DATE(t1.event_time) AS dt, t2.order_id
       FROM tb_order_overall AS t1 INNER JOIN tb_order_overall AS t2 ON DATE(t1.event_time) >= DATE(t2.event_time)
       WHERE (DATE(t1.event_time) BETWEEN "2021-10-01" AND "2021-10-03") 
       AND (DATEDIFF(DATE(t1.event_time), DATE(t2.event_time)) <= 6) AND t2.status = 1) AS oo
  /* 以订单明细表tb_order_detail为桥梁，获取每次交易所包含的商品id */
  LEFT OUTER JOIN tb_order_detail AS od ON oo.order_id = od.order_id
  /* 此左链接的目的在于，找出所有交易中，是属于店铺901的；
     由于是左联结、且联结条件为product_id与shop_id，因此，当shop_id不为901时，
     所返还的，表pi的所有字段都为NULL */
  LEFT OUTER JOIN tb_product_info AS pi ON od.product_id = pi.product_id AND pi.shop_id = 901
  GROUP BY oo.dt
  ORDER BY oo.dt ASC

步骤 3：找出店铺901在国庆前三天中，每一天的“截止当天的商品数”

• 这里直接使用了“子查询作为计算字段”的方法来达到目的，这个方法较少出现，但又非常好用，与联结查询有异曲同工之妙，建议同学们mark一下
• 注意子查询中的WHERE语句，使用了特殊条件“DATE(release_time) <= oo.dt”，以获取“截止当天的商品数”

  SELECT oo.dt, COUNT(DISTINCT pi.product_id),
        /* 获取国庆前三天，每一天的“截止当天的商品数” */
        (SELECT COUNT(*) FROM tb_product_info 
         WHERE shop_id = 901 AND DATE(release_time) <= oo.dt) AS products_901
  FROM
      (SELECT DATE(t1.event_time) AS dt, t2.order_id
       FROM tb_order_overall AS t1 INNER JOIN tb_order_overall AS t2 ON DATE(t1.event_time) >= DATE(t2.event_time)
       WHERE (DATE(t1.event_time) BETWEEN "2021-10-01" AND "2021-10-03") 
       AND (DATEDIFF(DATE(t1.event_time), DATE(t2.event_time)) <= 6) AND t2.status = 1) AS oo
  LEFT OUTER JOIN tb_order_detail AS od ON oo.order_id = od.order_id
  LEFT OUTER JOIN tb_product_info AS pi ON od.product_id = pi.product_id AND pi.shop_id = 901
  GROUP BY oo.dt
  ORDER BY oo.dt ASC

步骤 4：结合步骤2和3，可直接得到，在存在交易的每一天中，店铺901的动销率和滞销率

• 不多做解释了

  SELECT oo.dt, 
        ROUND(COUNT(DISTINCT pi.product_id) / (SELECT COUNT(*) FROM tb_product_info WHERE shop_id = 901 AND DATE(release_time) <= oo.dt), 3) AS sale_rate,
        ROUND(1 - COUNT(DISTINCT pi.product_id) / (SELECT COUNT(*) FROM tb_product_info WHERE shop_id = 901 AND DATE(release_time) <= oo.dt), 3) AS unsale_rate
  FROM
     (SELECT DATE(t1.event_time) AS dt, t2.order_id
      FROM tb_order_overall AS t1 INNER JOIN tb_order_overall AS t2 ON DATE(t1.event_time) >= DATE(t2.event_time)
      WHERE (DATE(t1.event_time) BETWEEN "2021-10-01" AND "2021-10-03") AND (DATEDIFF(DATE(t1.event_time), DATE(t2.event_time)) <= 6) AND t2.status = 1) AS oo
  LEFT OUTER JOIN tb_order_detail AS od ON oo.order_id = od.order_id
  LEFT OUTER JOIN tb_product_info AS pi ON od.product_id = pi.product_id AND pi.shop_id = 901
  GROUP BY oo.dt
  ORDER BY oo.dt ASC;