PostgreSQL:在 ORDER BY 之后返回 DISTINCT 行

Question

我的数据库:

SELECT * FROM odds ORDER BY created_at DESC;

 runner_id | value |         created_at         
-----------+-------+----------------------------
    365910 |     5 | 2017-03-13 14:29:45.404927
    365911 |   2.9 | 2017-03-13 14:29:45.404927
    365912 |     7 | 2017-03-13 14:29:45.404927
    365910 |     5 | 2017-03-13 14:29:02.654532
    365911 |     3 | 2017-03-13 14:29:02.654532
    365912 |     7 | 2017-03-13 14:29:02.654532
    365910 |   5.5 | 2017-03-13 14:28:22.733787
    365911 |     3 | 2017-03-13 14:28:22.733787
    365912 |     7 | 2017-03-13 14:28:22.733787
    365910 |   5.5 | 2017-03-13 14:25:42.885042
    365911 |     3 | 2017-03-13 14:25:42.885042
    365912 |   7.5 | 2017-03-13 14:25:42.885042
    365913 |    10 | 2017-03-13 14:25:42.885042
    365910 |     5 | 2017-03-13 14:25:21.842275
    365911 |     3 | 2017-03-13 14:25:21.842275
    365912 |   7.5 | 2017-03-13 14:25:21.842275
    365913 |    10 | 2017-03-13 14:25:21.842275

我正在寻找具有最高 created_at 的 DISTINCT runner_id 行。这是我想要的结果:

 runner_id | value |         created_at         
-----------+-------+----------------------------
    365910 |     5 | 2017-03-13 14:29:45.404927
    365911 |   2.9 | 2017-03-13 14:29:45.404927
    365912 |     7 | 2017-03-13 14:29:45.404927
    365913 |    10 | 2017-03-13 14:25:42.885042

这看起来很简单，但我无法理解。我开始认为这是不可能的，因为 ORDER BY 是最后发生的事情，我有点需要 SQL 来对行进行排序，然后在 runner_id 上选择 DISTINCT。

Answer 1

您的查询很适合使用 Postgres 的分析函数 ROW_NUMBER()。您希望保留每组 runner_id 记录中的最新记录。下面的 CTE 根据此逻辑计算行号，查询仅保留最新记录。

WITH cte AS (
    SELECT runner_id, value, created_at,
           ROW_NUMBER() OVER (PARTITION BY runner_id ORDER BY created_at DESC) rn
    FROM odds
)
SELECT
    t.runner_id,
    t.value,
    t.created_at
FROM cte t
WHERE t.rn = 1

我们也可以在这里尝试使用DISTINCT ON:

SELECT DISTINCT ON (runner_id)
     runner_id, value, created_at
FROM odds
ORDER BY runner_id, created_at DESC