时间戳

首页 > 解决方案 > 选择由不同事物排序的 DISTINCT 行

sql - 选择由不同事物排序的 DISTINCT 行

问题描述

我有这张桌子performances，那里有其他桌子上的戏剧表演：

id | play_id |    when    | other uninteresting meta data
1  |    3    | 2020-04-01 |             ...
2  |    4    | 2020-03-03 |             
3  |    3    | 2020-01-02 |             
4  |    1    | 2020-06-03 |             
5  |    4    | 2020-10-13 |

我想为每个不同的戏剧选择最早的表演（所以每个戏剧都由一个最早的表演代表），从最早到最晚排序。

所以从提供的数据中我想得到这个：

id | play_id |    when    | other uninteresting meta data
3  |    3    | 2020-01-02 |             ...
2  |    4    | 2020-03-03 |             
4  |    1    | 2020-06-03 |

到目前为止，在这里和这里研究了一些答案后，我想出了这个查询


SELECT * FROM
(
  SELECT DISTINCT ON (play_id) *
  FROM performances
  WHERE performances.deleted_at is null
  ORDER BY performances.play_id ASC, performances.when ASC
) distinct_plays
order by distinct_plays.when ASC

但是，我根本不相信它，因为在链接的线程中，有人争吵并告诉对方的答案是错误的；虽然我看到该线程中的答案存在一些问题，但我还没有在该答案中看到问题。

这是对我的任务的一个很好的查询吗？它不会选择重复的行还是非常无效？

标签： sqlpostgresqldatetimesubquerygreatest-n-per-group

解决方案

你的查询做你想做的事。distinct on通常是 Postgres 中解决此类最大 n-每组问题的正确工具……唉，它在结果集中的行顺序上没有提供灵活性。

看来您希望结果中有不同的排序distinct on- 因此您需要另一个级别的嵌套。虽然您的代码可以执行您想要的操作，但我建议您row_number()改用（这也具有在许多数据库中受支持的优势，而不是特定于供应商的distinct on）：

SELECT *
FROM (
    SELECT p.*, ROW_NUMBER() OVER(PARTITION BY play_id ORDER BY p.when asc) rn
    FROM performances p
    WHERE p.deleted_at is null
) p
WHERE rn = 1
ORDER BY p.when asc

您可能还想尝试相关子查询：

SELECT p.*
FROM performances p
WHERE p.deleted_at IS NULL AND p.when = (
    SELECT MIN(p1.when) FROM performances p1 WHERE p1.play_id = p.play_id
)
ORDER BY p.when

对于相关子查询的性能，请考虑在(play_id, when).

推荐阅读