Hive综合案例练习（中级）第二十七题：找出销售额连续3天超过30000的商品

最新推荐文章于 2023-04-10 21:14:17 发布

yiluohan0307

最新推荐文章于 2023-04-10 21:14:17 发布

阅读量159

点赞数

分类专栏：大数据框架之Hive 文章标签： hive sql 数据库

本文链接：https://blog.csdn.net/yiluohan0307/article/details/129938763

版权

大数据框架之Hive 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

文章提供了一个SQL查询来解决从订单详情表(order_detail)中提取销售额连续三天超过30000的商品ID的问题。通过分组计算每日销售额，然后使用窗口函数找出连续日期序列，最终筛选出满足条件的商品。

摘要由CSDN通过智能技术生成

找出销售额连续3天超过30000的商品

题目需求

从订单详情表（order_detail）中找出销售额连续3天超过30000的商品

结果如下：

Sku_id（商品id）
1
10
…

代码实现

求每个商品每天的销售总额

select sku_id,
       create_date,
       sum(price * sku_num) order_amount
from order_detail
group by sku_id, create_date
having order_amount >= 30000;

求销售额连续3天以上超过30000的商品

select distinct sku_id
from
(
    select sku_id,
           count(*) over (partition by sku_id, date_rk) cn
    from (
             select sku_id,
                    create_date,
                    date_sub(create_date, rank() over (partition by sku_id order by create_date)) date_rk
             from (
                      select sku_id,
                             create_date,
                             sum(price * sku_num) order_amount
                      from order_detail
                      group by sku_id, create_date
                      having order_amount >= 30000
                  ) t1
         ) t2
    )t3
where cn >= 3;

select sku_id
from (
         select sku_id,
                create_date,
                date_sub(create_date, row_number() over (partition by sku_id order by create_date)) diff
         from (
                  select sku_id,
                         create_date,
                         sum(price * sku_num) order_amount
                  from order_detail od
                  group by sku_id, create_date
                  having sum(price * sku_num) >= 30000
              ) t1
     ) t2
group by sku_id, diff
having count(*) >= 3;