hiveSQL面试题21__时间序列--取最新完成状态的前一个状态

最新推荐文章于 2024-03-08 11:37:11 发布

KG大数据

最新推荐文章于 2024-03-08 11:37:11 发布

阅读量301

点赞数

分类专栏： hiveSQL面试1000道文章标签： hive sql 面试

本文链接：https://blog.csdn.net/clktoy/article/details/122199899

版权

hiveSQL面试1000道专栏收录该内容

28 篇文章 28 订阅

订阅专栏

本文通过两个实例展示了如何使用SQL窗口函数处理时间序列数据。问题一讲解了如何获取最新完成状态之前的记录，输出了最新状态前的日期；问题二探讨了合并完成状态过程的方法，给出了状态合并后的序列。内容涉及到SQL的lead()和row_number()等窗口函数的高级用法。

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

0- 描述

描述：时间序列–取最新完成状态的前一个状态
表名：t21
表字段及内容：

date_id   a    b
2014     1    A
2015     1    B
2016     1    A
2017     1    B
2013     2    A
2014     2    B
2015     2    A
2014     3    A
2015     3    A
2016     3    B
2017     3    A

上表中B为完成状态。

1- 问题一

描述：取最新完成状态的前一个状态

输出结果如下所示：

date_id  a    b
2016     1    A
2013     2    A
2015     3    A

参考答案：

select
  next_date_id as date_id
  ,a
  ,next_b as b
from(
  select
    *,min(nk) over(partition by a,b) as minb
  from(
    select
      *,row_number() over(partition by a order by date_id desc) nk
      ,lead(date_id) over(partition by a order by date_id desc) next_date_id
      ,lead(b) over(partition by a order by date_id desc) next_b
    from(
      select * from t21
    ) t
  ) t
) t
where minb = nk and b = 'B';

2- 问题二

描述：如何将完成状态的过程合并
输出结果如下所示:

a   b_merge
1   A、B、A、B
2   A、B
3   A、A、B

参考答案：

select
  a
  ,collect_list(b) as b
from(
  select
    *
    ,min(if(b = 'B',nk,null)) over(partition by a) as minb
  from(
    select
      *,row_number() over(partition by a order by date_id desc) nk
    from(
      select * from t21
    ) t
  ) t
) t
where nk >= minb
group by a;