hive 使用partion by获取分组后的首尾时间

最新推荐文章于 2024-06-28 17:42:12 发布

fengfengchen95

最新推荐文章于 2024-06-28 17:42:12 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： hive

本文链接：https://blog.youkuaiyun.com/fengfengchen95/article/details/81316000

该博客介绍了如何在Hive中利用窗口函数`first_value`和`last_value`，结合`partition by`和`order by`，来获取分组后的开始和结束时间。同时，讲解了`rank()`函数在分组中获取行号的应用，帮助读者理解如何从海量数据中提取特定时间段的信息。

insert overwrite table smes_trans.trans_reel_supplier
select
distinct
queue_tri.wo_code,
queue_tri.ws_id,
queue_tri.slot_no,
queue_tri.slot_side,
compare_results.mcno,
compare_results.bom_cpid,
queue_tri.slot_reel_id,
c.internal_parts_code,
queue_tri.inputtime,
queue_tri.outputtime,
c.supplier_date_code,
d.supplier_name
from
(select distinct wo_code from smes_source.T_PCB_WO_WS_QUEUE ) as wo
left join
(
select * from (
select
wo_code,
ws_id,
slot_no,
slot_side,
slot_reel_id,
first_value(date_time) over(partition by wo_code,ws_id,slot_no,slot_side,slot_reel_id order by date_time) inputtime,
last_value(date_time) over(partition by wo_code,ws_id,slot_no,slot_side,slot_reel_id order by date_time rows between unbounded preceding and unbounded following) outputtime,
rank()over(part