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

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

http://www.nowcoder.com/practice/e7837f66e8fb4b45b694d24ea61f0dc9

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

明确题意:

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

动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例,同理滞销率就是没有销量的商品比例。


问题分解:

  • 计算每天的动销率(有销量产品的比例):
    • 计算国庆期间店铺901截止每天的近7天有销量的商品数(生成子表t_dt_901_pid_cnt):
      • 统计每个有销量的日期(生成子表t_dates):
        • 筛选目标时间窗:WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
        • 取去重后日期:DISTINCT DATE(event_time) as dt
      • 统计每天有销量的商品(生成子表t_dt_pid):
        • 用订单号关联订单总表和明细表:tb_order_overall JOIN tb_order_detail USING(order_id)
        • 统计每天售出的商品:DISTINCT DATE(event_time) as event_dt, product_id
      • 左连接两张表,得到每个有销售的日期近7天售出的商品:t_dates LEFT JOIN t_dt_pid ON DATEDIFF(dt,event_dt) BETWEEN 0 AND 6
      • 继续左连接商品信息,得到店铺id:LEFT JOIN tb_product_info USING(product_id)
      • 按日期分组:GROUP BY dt
      • 统计每天店铺901近7天有销量的商品数:COUNT(DISTINCT IF(shop_id!=901, NULL, product_id)) as sale_pid_cnt
    • 统计店铺901每个商品上架日期(生成子表t_release_dt):
      • 筛选店铺:WHERE shop_id=901
      • 生成日期:SELECT DATE(release_time) as release_dt, product_id as all_pid
    • 左连接两个表,得到每天已上架在售的商品:t_dt_901_pid_cnt LEFT JOIN t_release_dt ON dt >= release_dt
    • 按日期分组:GROUP BY dt
    • 计算动销率(有销量商品数已计算好,非聚合列,加MIN避免语法错误):MIN(sale_pid_cnt) / COUNT(all_pid) as sale_rate
    • 保留3位小数:ROUND(x, 3)
  • 基于上述结果计算滞销率:1 - sale_rate as unsale_rate

细节问题:

  • 表头重命名:as

完整代码:

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 (
        -- 国庆期间店铺901截止每天的近7天有销量的商品数
        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;
SQL大厂真题 文章被收录于专栏

大厂真题手把手教你怎么解~

全部评论
大佬,请问第16行2表连接on的条件是datediff是什么意思呢?如果分别从业务和表连接逻辑来看的话
1
送花
回复 分享
发布于 2022-02-10 17:25
请问大佬,第20行为什么是left join ,我的理解是用right join以右表为基准根据release_dt来限制dt。这样的处理可以单独判断每个商品在当天是否已发布吗?
点赞
送花
回复 分享
发布于 2022-04-09 11:24
京东
校招火热招聘中
官网直投
25行的表关联 ON dt >= release_dt 还可以这么写吗 还能大于等于?
点赞
送花
回复 分享
发布于 2022-09-12 15:57 北京
不对吧,如果10-1号没有销售是不是就不对了
点赞
送花
回复 分享
发布于 2022-10-11 10:44 山东

相关推荐

55 7 评论
分享
牛客网
牛客企业服务