unix—两列中两个连续行的两个日期之间的差异

ff29svar  于 2021-06-28  发布在  Hive
关注(0)|答案(1)|浏览(441)

在此处输入图像描述我有一个包含数百万条记录的配置单元表。输入类型如下:输入:

rowid     |starttime              |endtime                  |line   |status
--- 1     2007-07-19 00:05:00    2007-07-19 00:23:00        l1     s1 
--- 2     2007-07-20 00:00:10    2007-07-20 00:22:00        l1     s2
--- 3     2007-07-19 00:00:00    2007-07-19 00:11:00        l2     s2

我要做的是当第一次按开始时间按行分组排序表时。然后找出连续两行endtime和starttime之间的差异。如果差异超过5分钟,则在新表中添加一个状态为misstime的新行。
在第1行和第2行中,时间差为1小时10分钟,因此第一个我将为第19个日期创建一行,并用缺少的时间完成该日期,然后为第20个日期再添加一行,如下所示。
输出:

rowid |starttime               |endtime              |line   |status
--- 1 |2007-07-19 00:05:00     |2007-07-19 00:23:00  |l1     |s1 
--- 2 |2007-07-19 00:23:01     |2007-07-19 00:00:00  |l1     |misstime
--- 3 |2007-07-20 00:00:01     |2007-07-20 00:00:09  |l1     |misstime
--- 4 |2007-07-20 00:00:10     |2007-07-20 00:22:00  |l1     |s2
--- 3 |2007-07-19 00:00:00     |2007-07-19 00:11:00  |l2     |s2

有人能帮我直接在色调Hive里实现吗?unix脚本也可以。
提前谢谢。

nkoocmlb

nkoocmlb1#

解决方案模板为:
使用lag()函数获取前一行的开始时间或结束时间。
对于每一行,计算当前时间和前一时间之间的差值
筛选差异超过5分钟的行。
将数据集转换为所需的输出。
例子:

insert into yourtable
    select 
    s.rowid, 
    s.starttime ,
    s.endtime,
    --calculate your status here, etc, etc
from    
    (
    select rowid starttime endtime, 
           lag(endtime) over(partition by rowid order by starttime) prev_endtime
    from yourtable ) s
    where (unix_timestamp(endtime) - unix_timestamp(prev_endtime))/60 > 5 --latency>5 min

相关问题