Hive Sql之实际生产中几个复杂场景的

场景一:取当前数据的上一条和下一条，直到取到满足条件的为止.

场景二:要计算每天每个员工当月累计的销售额，但是某些员工在某几天是没有销售记录的，怎么处理?

场景三:使用HiveQL实现类似滑动窗口的效果.

场景四:计算满足一定条件的连续.

---

场景一：取当前数据的上一条和下一条，直到取到满足条件的为止

• 难度指数：★★★

• 需求描述：
  有一张入库成本表,一个商品每次入库就会产生一条数据,里面包含商品id,入库时间,以及入库采购的成本.但由于某些某些原因,导致表中某些数据的成本是有丢失的.现在的逻辑是,当成本丢失时,有两种取成本的方式，现在需要把两种成本都取出来，最后取2次成本的平均值作为本次入库的成本。

• 1.取同一个商品最近一次入库的有效成本（即存在成本时就为有效成本，无效成本为null）

• 2.取同一个商品紧接着一次入库的有效成本

• 效果展示：

• 测试数据：

---1.测试表存在则删除
drop table test.aaa001;
---2.创建测试表
create table test.aaa001
(
product_id string comment '商品id',
into_warehouse_time string comment '入库时间',
cost int comment '成本'
)
;
---3.插入测试数据
insert into test.aaa001 values
('1101','2021-01-01',120),
('1102','2021-01-01',150),
('1102','2021-01-02'),
('1102','2021-01-03'),
('1102','2021-01-04',200),
('1102','2021-01-05',210),
('1103','2021-01-06',300),
('1103','2021-01-07'),
('1103','2021-01-08',400),
('1104','2022-01-01',111111),
('1104','2022-01-02'),
('1104','2022-01-03'),
('1104','2022-01-04',22222),
('1104','2022-01-05')
;

• 4.sql答案参考：

---sql逻辑
select
product_id
,into_warehouse_time
,cost
,first_value(cost) over(partition by product_id,group_flag1 order by into_warehouse_time) as last_cost
,last_value(cost) over(partition by product_id,group_flag2 order by into_warehouse_time rows between current row and unbounded following) as next_cost
from
(
  SELECT 
  product_id
  ,into_warehouse_time
  ,cost
  ,sum(if(cost is null,0,1)) over(partition by product_id order by into_warehouse_time) as group_flag1
  ,sum(if(cost is null,0,1)) over(partition by product_id order by into_warehouse_time rows between current row and unbounded following) as group_flag2
  from test.aaa001
)tmp
order by product_id ,into_warehouse_time
;

---

场景二：要计算每天每个员工当月累计的销售额，但是某些员工在某几天是没有销售记录的，需要特殊处理。

• 难度指数：★★★

• 需求描述：
  现在有一张员工销售记录表.我们需要每天统计每一个员工的当月累计销售额.

• 效果展示：

• 测试数据：

---1.测试表存在则删除
drop table test.aaa001;
--2.创建测试表
create table test.aaa001
(
sale_date string,
emp_id int,
emp_name string,
sale_amount int
)
;
--3.插入测试数据
insert into table test.aaa001 values
('2021-10-02',101,'张三',100),
('2021-10-03',101,'张三',300),
('2021-10-05',101,'张三',400),
('2021-10-01',102,'李四',111),
('2021-10-03',102,'李四',222),
('2021-10-08',102,'李四',333)
;

• 4.sql答案参考：

--sql逻辑开发
select
t1.dt
,t1.emp_id
,t1.emp_name
,sum(if(t2.sale_amount is null,0,t2.sale_amount)) over(partition by t1.emp_id,substr(t1.dt,1,7) order by t1.dt) as total_sale_amount
from
(
    ---得到连续日期,而且是带emp_id和emp_name的
    select
    date_add(t.start_date,tab.pos) as dt
    ,t.emp_id
    ,t.emp_name
    from
    (
        select
        emp_id
        ,emp_name
        ,'2021-10-01' as start_date
        ,'2021-10-08' as end_date
        from test.aaa001
        group by emp_id,emp_name
    )t
    lateral view posexplode(split(repeat(',',datediff(end_date,start_date)),',')) tab as pos,val
)t1
left join
(
    select
    sale_date
    ,emp_id
    ,emp_name
    ,sale_amount
    from test.aaa001
)t2
on t1.dt=t2.sale_date and t1.emp_id=t2.emp_id and t1.emp_name=t2.emp_name
;

---

场景三：使用HiveQL实现类似滑动窗口的效果

• 难度指数：★★★

• 需求描述：
  有一张用户购买记录表.现在我们需要找出所有的特殊用户.特殊用户的定义如下:
  在当前购买时间的近7天内(含当天)购买次数超过3次(含),且近7天的购买总金额超过1000的用户即为特殊用户.

• 效果展示：

• 测试数据：

---1.测试表存在则删除
drop table test.aaa001;
--2.创建测试表
create table test.aaa001
(
user_id int,
buy_date string,
amount int
)
;
--3.插入测试数据(102和104为特殊用户)
insert into table test.aaa001 values
(101,'2021-01-01',1000),
(101,'2021-01-02',2000),
(102,'2021-10-01',10),
(102,'2021-10-02',700),
(102,'2021-10-07',200),
(103,'2021-11-07',500),
(103,'2021-11-08',500),
(103,'2021-11-20',500),
(104,'2021-03-01',10),
(104,'2021-03-05',200),
(104,'2021-03-09',800),
(104,'2021-03-09',800),
(105,'2021-05-01',1),
(105,'2021-05-10',2),
(106,'2021-10-01',10),
(106,'2021-10-02',20),
(106,'2021-10-03',30),
(106,'2021-10-04',945)
;

• sql答案参考：

select
*
from test.aaa001
order by user_id ,buy_date 
;


---sql逻辑
select
distinct user_id
from
(
  select
    user_id
    ,buy_date
    ,count(1) over(PARTITION by user_id order by datediff(buy_date,'2021-01-01') RANGE between 6 PRECEDING and CURRENT row) as cnt
    ,sum(amount) over(PARTITION by user_id order by datediff(buy_date,'2021-01-01') RANGE between 6 PRECEDING and CURRENT row) as amount
  from test.aaa001
)t1
where cnt>=3 and amount>1000
;

---

场景四：计算满足一定条件的连续

• 难度指数：★★

• 需求描述：
  已知用户ID，日期，消费金额，地址，需要找出连续3天及以上都有消费且每日的消费金额都大于20的所有用户的全部消费详情.

• 效果展示：

• 测试数据：

---1.删除测试表
drop table test.aaa0001 
;
---2.建表语句
create table test.aaa0001 
(
user_id string comment '用户id',
buy_date string comment '购买时间',
amount int comment '购买金额',
address string comment '购买地址'
)
;
---3.插入测试数据
insert into test.aaa0001 values
(101,'2022-01-01',100,'北京'),
(101,'2022-01-01',200,'上海'),
(101,'2022-01-02',300,'深圳'),
(101,'2022-01-04',400,'北京'),
(102,'2022-01-08',200,'深圳'),
(102,'2022-01-09',300,'上海'),
(102,'2022-01-10',10,'北京'),
(102,'2022-01-10',30,'深圳'),
(102,'2022-01-11',500,'上海'),
(102,'2022-01-15',600,'北京'),
(103,'2022-01-20',700,'广州'),
(103,'2022-01-21',10,'北京'),
(103,'2022-01-22',300,'深圳')
;

• sql答案参考：

select
t1.*
from test.aaa0001 t1
left semi join
(
  select
  user_id 
  from
  (
    select
    user_id 
    ,date_sub(buy_date,dense_rank() over(partition by user_id order by buy_date)) as date_diff   ---给排序.相同用户相同日期编号一样,如果日期连续,那么编号也是连续的,差值就相等
    from
    (
      select
      user_id 
      ,buy_date 
      from  test.aaa0001 
      group by user_id,buy_date 
      having sum(amount)>20
    )ttt1
  )tt1
  group by user_id,date_diff
  having count(1)>=3
)t2
on t1.user_id=t2.user_id
order by user_id ,buy_date 
;