Hive sql 常见面试题-国庆期间的7日动销率和滞销率

最新推荐文章于 2024-11-14 16:48:54 发布

程序员面试笔记

最新推荐文章于 2024-11-14 16:48:54 发布

阅读量428

点赞数 9

分类专栏： Hive 文章标签： hive sql hadoop

本文链接：https://blog.csdn.net/weixin_42435657/article/details/136496483

版权

Hive 专栏收录该内容

74 篇文章 0 订阅

订阅专栏

1. 题目需求

动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。
滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。
只要当天任一店铺有任何商品的销量就输出该天的结果
从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率

结果如下（截取部分）：
在这里插入图片描述

2. 需要用到的表

订单明细表：order_detail
在这里插入图片描述

商品信息表：sku_info

在这里插入图片描述

3. 查询sql

WITH tmp as(

SELECT

	category_id,

        sku_id,

	count(1) over(partition by category_id) category_count

from sku_info

where from_date < '2021-10-01'

)

SELECT

	category_id,

    CAST(sum(IF(create_date='2021-10-01',sku_count,0)) / max(category_count) as decimal(16,2))  first_sale_rate,

    1.00 - CAST(sum(IF(create_date='2021-10-01',sku_count,0)) / max(category_count) as decimal(16,2))  first_unsale_rate,

    CAST(sum(IF(create_date='2021-10-02',sku_count,0)) / max(category_count) as decimal(16,2))  second_sale_rate,

    1.00 - CAST(sum(IF(create_date='2021-10-02',sku_count,0)) / max(category_count) as decimal(16,2))  second_unsale_rate,

	CAST(sum(IF(create_date='2021-10-03',sku_count,0)) / max(category_count) as decimal(16,2))  third_sale_rate,

    1.00 - CAST(sum(IF(create_date='2021-10-03',sku_count,0)) / max(category_count) as decimal(16,2))  third_unsale_rate,

	CAST(sum(IF(create_date='2021-10-04',sku_count,0)) / max(category_count) as decimal(16,2))  fourth_sale_rate,

    1.00 - CAST(sum(IF(create_date='2021-10-04',sku_count,0)) / max(category_count) as decimal(16,2))  fourth_unsale_rate,

	CAST(sum(IF(create_date='2021-10-05',sku_count,0)) / max(category_count) as decimal(16,2))  fifth_sale_rate,

    1.00 - CAST(sum(IF(create_date='2021-10-05',sku_count,0)) / max(category_count) as decimal(16,2))  fifth_unsale_rate,

    CAST(sum(IF(create_date='2021-10-06',sku_count,0)) / max(category_count) as decimal(16,2))  sixth_sale_rate,

    1.00 - CAST(sum(IF(create_date='2021-10-06',sku_count,0)) / max(category_count) as decimal(16,2))  sixth_unsale_rate,

	CAST(sum(IF(create_date='2021-10-07',sku_count,0)) / max(category_count) as decimal(16,2))  seventh_sale_rate,

    1.00 - CAST(sum(IF(create_date='2021-10-07',sku_count,0)) / max(category_count) as decimal(16,2))  seventh_unsale_rate

from(

    SELECT

        category_id,create_date,

        count(DISTINCT sku_id) sku_count,

        max(category_count) category_count

    from(

        SELECT

            category_id,

            od.sku_id,

            create_date,

            category_count

        from order_detail od 

        LEFT JOIN tmp 

        on od.sku_id = tmp.sku_id

        where create_date BETWEEN '2021-10-01' and '2021-10-07'

    )t1 GROUP by category_id,create_date

)t2 group by category_id

ORDER by category_id DESC