Hive综合案例练习（中级）第二十三题：销售额完成任务指标的商品

销售额完成任务指标的商品

题目需求

商家要求每个商品每个月需要售卖出一定的销售总额

假设1号商品销售总额大于21000，2号商品销售总额大于10000，其余商品没有要求

请写出SQL从订单详情表中（order_detail）查询连续两个月销售总额大于等于任务总额的商品

结果如下：

sku_id（商品id）
1

代码实现及步骤

• 求出1号商品和2号商品 每个月的购买总额 并过滤掉没有满足指标的商品

select sku_id,
       concat(substring(create_date, 0, 7), '-01') ymd,
       sum(price * sku_num)                        order_amount
from order_detail
where sku_id = '1'
   or sku_id = '2'
group by sku_id, substring(create_date, 0, 7)
having (sku_id = '1' and order_amount >= 21000)
    or (sku_id = '2' and order_amount >= 10000);

• 判断是否为连续两个月

select distinct sku_id
from (
         select sku_id,
                count(*) over (partition by sku_id, flag) cn
         from (
                  select sku_id,
                         ymd,
                         add_months(ymd, -row_number() over (partition by sku_id order by ymd)) flag
                  from (
                           select sku_id,
                                  concat(substring(create_date, 0, 7), '-01') ymd,
                                  sum(price * sku_num)                        order_amount
                           from order_detail
                           where sku_id = '1'
                              or sku_id = '2'
                           group by sku_id, substring(create_date, 0, 7)
                           having (sku_id = '1' and order_amount >= 21000)
                               or (sku_id = '2' and order_amount >= 10000)
                       ) t1
              ) t2
     ) t3
where cn >= 2;