最近在分区上使用row_number（）的SQL [英] SQL most recent using row_number() over partition

问题描述

我正在处理一些网页点击数据，并且只是在访问user_id时（通过时间戳）查找最新的page_name。使用下面的代码重复user_id，并显示page_name，并按降序排序。但是，我只想要recent_click always = 1.查询完成后将用作较大查询中的子查询。

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.

这是我目前的代码：

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

推荐答案

您应该可以将查询移动到子查询并添加其中标准：

You should be able to move your query to a subquery and add where criteria:

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

这篇关于最近在分区上使用row_number（）的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！