mysql - Hive 中的 SQL 查询获取 2 列中的前 2 个值

Question

我有一个像 access(url, access_time) 这样的表，每个 url 可能有很多访问时间。

我有另一个表是 asset(url, foo)

我想做一个查询，将其转换为 join_data(url, first_access_time, secondary_access_time)

如果没有访问时间，则first_access_time为NULL，如果没有第二访问时间，则second_access_time为NULL。

我怎样才能在 hive 中做到这一点？

Answer 1

您可以使用row_number来执行此操作。

with twotimes as (select ast.url, a.access_time,
                  row_number() over(partition by a.url order by a.access_time) as rn
                  from asset ast 
                  left join access a on a.url = ast.url )
select url, max(first_access_time), max(second_access_time)
from (
select url, access_time as first_access_time, null as second_access_time
from twotimes where rn = 1
union all
select url, null as first_access_time, access_time as second_access_time
from twotimes where rn = 2
) t
group by url