在Postgres中选择每个用户的第N行 [英] Selecting every Nth row per user in Postgres

问题描述

我正在使用以下SQL语句：

I was using this SQL statement:

SELECT "dateId", "userId", "Salary" 
FROM (
   SELECT *, 
          (row_number() OVER (ORDER BY "userId", "dateId"))%2 AS rn 
   FROM user_table
 ) sa 
 WHERE sa.rn=1 
   AND "userId" = 789 
   AND "Salary" > 0;

但是每次表获得新行时，查询结果都是不同的。

我缺少什么吗？

But every time the table gets new rows the result of the query is different.
Am I missing something?

推荐答案

假设（ dateId， userId）是唯一的，新行总是具有更大的（以后） dateId 。

Assuming that ("dateId", "userId") is unique and new rows always have a bigger (later) dateId.

我认为需要做什么：

SELECT "dateId", "userId", "Salary"
FROM (
   SELECT "dateId", "userId", "Salary"
         ,(row_number() OVER (PARTITION BY "userId"   -- either this
                              ORDER BY "dateId")) % 2 AS rn
   FROM   user_table
   WHERE  "userId" = 789                              -- ... or that
   ) sub
WHERE  sub.rn = 1
AND    "Salary" > 0;

注意 部分B是 。这样，您就为每个 userId 每秒跳过一次 dateId ，并且其他（以后）行不会更改选择，因此

Notice the PARTITION BY. This way you skip every second dateId for each userId, and additional (later) rows don't change the selection so far.

此外，只要您为 单 userId选择行（ USERID = 789 ），将谓词拉入子查询，实现相同的效果（单个用户的稳定选择）。

Also, as long as you are selecting rows for a single userId (WHERE "userId" = 789), pull the predicate into the subquery, achieving the same effect (stable selection for a single user). You don't need both.

子查询中的 WHERE 子句仅适用于单个用户， PARTITION BY 在一个查询中可用于任意数量的用户。

The WHERE clause in the subquery only works for a single user, PARTITION BY works for any number of users in one query.

<sub>是吗？是吗？

为此，他们应该给我侦探徽章。

认真。</sub>

这篇关于在Postgres中选择每个用户的第N行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！