sql - 如何使用标准查询或使用 UDF 标记一行并将其特定值存储在 Hive 中？

Question

我需要在 Hive 中编写查询或定义需要执行以下操作的函数:

数据集:

Student || Time    || ComuputerPool
-------------------------------------
  A     ||  9:15AM ||  Pool1.Machine2
-------------------------------------
  A     ||  9:45AM ||  Pool1.Machine7
-------------------------------------
  A     ||  10:15AM||  Pool1.Machine9
-------------------------------------
  A     ||  11:00AM||  Pool2.Machine2
-------------------------------------
  A     ||  12:05  ||  Pool2.Machine3
-------------------------------------
  A     ||  12:40  ||  Pool3.Machine5
------------------------------------- 
  A     ||  13:10  ||  Pool1.Machine3
-------------------------------------
  A     ||  13:50  ||  Pool1.Machine10
-------------------------------------
  B   ..........................

所以现在查询应该通过计算他第一次使用池中的机器和他第一次开始使用另一个池中的机器的时间差来找出特定学生在特定计算机池中花费了多长时间。所以这个例子他花的时间是: 上午 11:00 - 上午 9:15 = 1 小时 45 分钟

我的问题是如何在一个存储时间值中标记第一次使用，并在以后找到下一个池数据时使用它。

Answer 1

Hive 支持 row_number()，因此您可以使用一种技巧对顺序值进行分组。两个行号之间的差异定义了一个组，然后可以将其用于聚合。结果查询如下所示:

select student, grp, min(time), max(time)
from (select t.*,
             (row_number() over (partition by student order by time) -
              row_number() over (partition by student, computerpool order by time)
             ) as grp
      from dataset t
     ) t
group by student, grp, computerpool;

编辑:

您的问题是关于池，而不是机器。因此，大概您只关心期间之前的部分。 (顺便说一下，您应该将此信息存储在单独的列中)。

select student, substr(computerpool, 1, 6), min(time), max(time)
from (select t.*,
             (row_number() over (partition by student order by time) -
              row_number() over (partition by student, substr(computerpool, 1, 6) order by time)
             ) as grp
      from dataset t
     ) t
group by student, grp, substr(computerpool, 1, 6);