PostgreSQL：SELECT DISTINCT ON 表达式必须与初始 ORDER BY 表达式匹配

假设我有以下 PostgreSQL 表，名为products:

CREATE TABLE IF NOT EXISTS mytable (
    id serial NOT NULL PRIMARY KEY,
    label VARCHAR(50) NOT NULL,
    info jsonb NOT NULL,
    created_at timestamp NOT NULL DEFAULT now()
);

这是一些测试数据。请注意，我的实际表有数百万条记录。

INSERT INTO products (label, info) VALUES ('a', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('a', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('c', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('c', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('b', '[1, 2, 3]');

我想编写一个查询来获取不同的标签并按以下顺序对记录进行排序created_at场地。我的第一反应是编写以下查询：

SELECT DISTINCT ON (label) * FROM products ORDER BY created_at DESC;

但是，此操作失败并出现以下错误：

错误：SELECT DISTINCT ON 表达式必须与初始 ORDER BY 表达式匹配

看起来我可以使用 SQL 子查询来解决这个问题：

SELECT * FROM (
    SELECT DISTINCT ON (label) * FROM products
) AS subquery ORDER BY created_at DESC;

生成以下预期结果：

 id | label |   info    |         created_at
----+-------+-----------+----------------------------
  5 | b     | [1, 2, 3] | 2022-11-14 03:32:23.245669
  3 | c     | [1, 2, 3] | 2022-11-14 03:32:23.242813
  1 | a     | [1, 2, 3] | 2022-11-14 03:32:23.239791

这是解决此问题的最佳方法吗？或者有没有更快的方法来查询这些数据？请注意，我在上面提到了我的实际表如何拥有数百万条记录，因此我想提出尽可能最佳的查询。

Leading ORDER BY表达式必须匹配DISTINCT ON表达式（反之亦然）：

SELECT DISTINCT ON (label) * FROM products ORDER BY label, created_at DESC;  

See:

• 选择每个 GROUP BY 组中的第一行？ https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564

不清楚你是否想要created_at ASC or created_at DESC：您显示了后者，但您称为“预期”的结果与前者匹配 - 因为您的“修复”并没有像您认为的那样进行。

对结果（不同）行进行排序created_at DESC，你必须运行一个外部SELECT使用不同的排序顺序：

SELECT *
FROM  (
   SELECT DISTINCT ON (label) *
   FROM   products
   ORDER  BY label, created_at DESC
   ) sub
ORDER  BY created_at DESC;

See:

• PostgreSQL DISTINCT ON 具有不同的 ORDER BY https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104

对于大表，一定要有索引(label, created_at) or (label, created_at DESC)， 分别。

我的实际表有数百万条记录，所以我想提出尽可能最好的查询。

根据未公开的细节，可能会有（更快）更快的解决方案。最重要的是：

• 总共有多少行有多少个不同的“标签”？
• 是否有一个单独的表，每个（相关）不同标签一行？
• 你真的需要吗SELECT *, or is SELECT label, created_at一切你需要的？