HQL题目24：根据商品销售情况进行商品分类

24 根据商品销售情况进行商品分类
题目需求：从订单详情表中(order_detail)对销售件数对商品进行分类，0-5000为冷门商品，5001-19999位一般商品， 20000往上为热门商品，并求出不同类别商品的数量。
表数据：order_detail

方法一：使用case when来进行条件判断
①首先查询每个商品的销售数量，并根据销售数量为商品进行分类

select
    sku_id,
    case when  sum(sku_num) between 0 and 500 then '冷门商品'
         when  sum(sku_num) between 5001 and 19999 then '一般商品'
         else '热门商品'
        end as level
from order_detail
group by sku_id;

②根据分类好的商品，统计每类商品的数量

with t1 as (
    select
        sku_id,
        case when  sum(sku_num) between 0 and 500 then '冷门商品'
             when  sum(sku_num) between 5001 and 19999 then '一般商品'
             else '热门商品'
        end as level
    from order_detail
    group by sku_id
)
select
    level,
    count(sku_id)
from t1
group by t1.level;

方法二：使用if来进行条件判断

with t1 as (
    select
        sku_id,
        sum(sku_num) num
    from order_detail
    group by sku_id
),t2 as (
    select
        sku_id,
        if((num between 0 and 5000),'冷门商品',if((num between 5000 and 1999),'一般商品','热门商品')) level
        from t1
)
select
       level,
       count(sku_id)
from t2
group by level

总结：这个题目的难点在于是否熟悉case when和if条件判断的使用语法