最近在分区上使用 row_number() 的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27402850/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL most recent using row_number() over partition
提问by jKraut
I'm working with some web clicks data, and am just looking for the most recent page_name with the user_id visited (by a timestamp). Using the below code, the user_id is repeated and page_name with shown, with sorted descending. However, I would just like recent_click always = 1. The query when complete will be used as a subquery in a larger query.
我正在处理一些网络点击数据，我只是在寻找带有访问过的 user_id（通过时间戳）的最新 page_name。使用下面的代码，user_id 重复，page_name 显示，按降序排序。但是，我只希望最近点击始终 = 1。完成后的查询将用作更大查询中的子查询。
Here is my current code:
这是我当前的代码：
SELECT user_id,
page_name,
row_number() over(partition by session_id order by ts desc) as recent_click
from clicks_data;
user_id | page_name | recent_click
--------+-------------+--------------
0001 | login | 1
0001 | login | 2
0002 | home | 1
回答by sgeddes
You should be able to move your query to a subquery and add wherecriteria:
您应该能够将查询移动到子查询并添加where条件：
SELECT user_id, page_name, recent_click
FROM (
SELECT user_id,
page_name,
row_number() over (partition by session_id order by ts desc) as recent_click
from clicks_data
) T
WHERE recent_click = 1
回答by AeroX
You should move the row_number()function into a subquery and then filter it in the outer query.
Something like this:
您应该将该row_number()函数移动到子查询中，然后在外部查询中对其进行过滤。
像这样的东西：
SELECT * FROM (
SELECT
[user_id]
,[page_name]
,ROW_NUMBER() OVER (PARTITION BY [session_id]
ORDER BY [ts] DESC) AS [recent_click]
FROM [clicks_data]
)x
WHERE [recent_click] = 1
