sql - 获得 10 个不同的项目以及相关任务的最新更新

Question

我在 PostgreSQL 9.5 数据库中有两个表:

project
  - id
  - name

task
  - id
  - project_id
  - name
  - updated_at

有〜1000 个项目(很少更新)和〜1000 万个任务(经常更新)。

我想列出具有最新任务​​更新的那 10 个不同的项目。

一个基本的查询是:

SELECT * FROM task ORDER BY updated_at DESC LIMIT 10;

但是，每个项目可以有许多更新的任务。所以我不会得到 10 个独特的项目。

如果我尝试在查询中的某处添加 DISTINCT(project_id)，则会出现错误:

for SELECT DISTINCT, ORDER BY expressions must appear in select list

问题是，我无法(主要)按 project_id 排序，因为我需要按时间对任务进行排序。按 updated_at DESC，project_id ASC 排序也不起作用，因为同一项目的多个任务可能是最新的。

我无法下载所有记录，因为有数百万条记录。

作为解决方法，我下载了 10 倍所需的行(没有不同的)范围，并在后端过滤它们。这适用于大多数情况，但显然不可靠:有时我没有得到 10 个独特的项目。

在 Postgres 9.5 中可以有效解决这个问题吗？

例子

 id |   name    
----+-----------
  1 | Project 1
  2 | Project 2
  3 | Project 3

 id | project_id |  name  |   updated_at    
----+------------+--------+-----------------
  1 |          1 | Task 1 | 13:12:43.361387
  2 |          1 | Task 2 | 13:12:46.369279
  3 |          2 | Task 3 | 13:12:54.680891
  4 |          3 | Task 4 | 13:13:00.472579
  5 |          3 | Task 5 | 13:13:04.384477

如果我查询:

SELECT project_id, updated_at FROM task ORDER BY updated_at DESC LIMIT 2

我得到:

 project_id |   updated_at    
------------+-----------------
          3 | 13:13:04.384477
          3 | 13:13:00.472579

但我想获得 2 个distinct 项目，它们各自具有最新的 task.update_at，如下所示:

 project_id |   updated_at    
------------+-----------------
          3 | 13:13:04.384477
          2 | 13:12:54.680891  -- from Task 3

Answer 1

简单(逻辑上正确)的解决方案是聚合任务以获得每个项目的最新更新，然后选择最新的 10 个，like @Nemeros提供。

但是，这会导致对任务 进行顺序扫描，这对于大 表来说是不可取的(昂贵的) .

如果您的项目相对较少(每个项目有很多任务条目)，则可以使用(位图)索引扫描进行更快的替代。

SELECT *
FROM   project p
     , LATERAL (
   SELECT updated_at AS last_updated_at
   FROM   task
   WHERE  project_id = p.id
   ORDER  BY updated_at DESC
   LIMIT  1
   ) t
ORDER  BY t.last_updated_at
LIMIT  10;

性能的关键是匹配的多列索引:

CREATE INDEX task_project_id_updated_at ON task (project_id, updated_at DESC);

具有 1000 个项目和 1000 万个任务的设置(如您评论的那样)是一个完美的选择。

背景:

• Optimize GROUP BY query to retrieve latest record per user
• Select first row in each GROUP BY group?

NULL 和“无行”

上述解决方案假设 updated_at 被定义为 NOT NULL。否则使用 ORDER BY updated_at DESCNULLS LAST 并理想地使索引匹配。

没有任何任务的项目通过隐式 CROSS JOIN 从结果中消除。 NULL 值不能以这种方式蔓延。这与 @Nemeros added to his answer 等相关子查询略有不同。 :那些返回 NULL 值的“无行”(项目根本没有相关任务)。除非另有说明，否则外部降序排序顺序会在顶部列出 NULL。很可能不是您想要的。

相关:

• PostgreSQL sort by datetime asc, null first?
• What is the difference between LATERAL and a subquery in PostgreSQL?