Hive sql 面试题-统计每个品牌的总的营销天数

程序员面试笔记

已于 2024-02-26 11:09:25 修改

阅读量547

点赞数 11

分类专栏： Hive 文章标签：前端

于 2024-02-26 10:06:01 首次发布

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

版权

Hive 专栏收录该内容

74 篇文章 0 订阅

订阅专栏

本文详细描述了如何使用SQL查询统计华为、小米和苹果等品牌的营销活动总天数，包括计算连续天数和累计天数的过程。

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

统计每个品牌的总的营销天数

create table marking (brand string ,startdate string ,enddate string );

insert into marking VALUES('华为','2018-08-04','2018-08-05');
insert into marking VALUES('华为','2018-08-04','2020-12-25');
insert into marking VALUES('小米','2018-08-15','2018-08-20');
insert into marking VALUES('小米','2020-01-01','2020-01-05');
insert into marking VALUES('苹果','2018-09-01','2018-09-05');
insert into marking VALUES('苹果','2018-09-03','2018-09-06');
insert into marking VALUES('苹果','2018-09-09','2018-09-15');

t1:

select  brand, startdate, enddate ,min(startdate) over(partition by brand )  min_date , max(enddate) over(partition by brand)  max_date, max(enddate) over(partition by brand order by startdate asc rows between  unbounded preceding and 1 preceding  )  last_date from marking ;

t2:
select brand, startdate, enddate, datediff(max_date ,min_date) as all_date , if( datediff(startdate,last_date ) <=0 , 0 , datediff(startdate,last_date)-1  ) as differ_date
from t1

t3:
select brand,startdate,enddate,max(all_date) - sum(differ_date ) as tianshu from t2;

select brand,startdate,enddate,max(all_date) - sum(differ_date ) as tianshu from (
      select brand, 
           startdate, 
           enddate,
           min_date,
           max_date, 
           datediff(max_date ,min_date) +1  all_date , 
           if( datediff(startdate,last_date ) <=0 , 0 , datediff(startdate,last_date)-1  )  differ_date
    from (
      select  brand, startdate, enddate ,
      min(startdate) over(partition by brand )  min_date , 
      max(enddate) over(partition by brand)  max_date, 
      max(enddate) over(partition by brand order by startdate asc rows between  unbounded preceding and 1 preceding  )  last_date 
      from marking  
    ) t1
) t2 ;