获取snowflake中row_number()窗口函数的最后两行

Question

希望有人能帮助我…

我试图从row_number()窗口函数获得最后两个值。例如，假设我的结果包含的行号最多为6。如何可以获得行数是5和6的行吗?

请告诉我是否可以用其他窗口函数或其他方式来完成。

亲切的问候,

Answer 1

使用QUALIFY:

SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(ORDER BY ... DESC) <= 2;

这种方法可以进一步扩展为每个分区得到两行:

SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... DESC) <= 2;

Answer 2

您可以将top与order by desc一起使用，如:

select top 2 row_number() over([partition by] [order by]) as rn
from table
order by rn desc

Answer 3

我想说@Shmiel是正式和优雅的方式，以防万一，将与:

WITH CTE AS
(SELECT product,
user_id,
ROW_NUMBER() OVER (PARTITION BY user_id order by product desc)
as RN
FROM Mytable)
SELECT product, user_id
FROM CTE
WHERE RN < 3;

您将使用order by [order_condition]和&;desc&;。然后您将使用RN(行号)来选择您想要的任意多行