语法格式:
row_number() over(partition by 分组列 order by 排序列 desc)
场景
在使用impala group by 查询示,语法只支持回显group by后的字段,无法满足需求
例子
SELECT
time,
page,
userid,
action
FROM
(
SELECT
Row_Number ( ) OVER ( PARTITION BY page, userid ORDER BY time DESC ) rank,
page,
userid,
time,
action
FROM
logdb.origin_log
WHERE
time > '2020-06-06 00:00:00'
) temp
WHERE
rank = 1
ORDER BY
time DESC
LIMIT 2 OFFSET 1
结论
row_number() over(partition by 分组列 order by 排序列 desc)函数可以把排序后的生成一个行号,
这里我取别名rank,我需取最新数据,只需根据时间排序,然后取rank=1的数据即可