如何在*标准* SQL中*有效地获得每组行的最佳评级行?

Question

问题

我有两个SQL表albums和photos。相册可以分层地嵌套为树，并使用嵌套集范式。照片是相册的子元素。

我需要一个SQL查询，它返回每个专辑ID的封面照片的ID。封面照片应是相册中所有递归儿童照片中评分最高的一张。

解决方案必须与PostgreSQL, MySQL和SQLite一起工作;也就是说，它应该基本上只使用标准的SQL功能，最多只使用所有DBMS提供的非标准功能。

我知道类似的问题已经被问过了(例如在"Select first row in each GROUP BY GROUP ?")，但我只能找到使用特定DBMS的SQL扩展的答案。

我已经有了一种方法，但是它太慢了。

albums表(简化):

CREATE TABLE albums (
id INTEGER PRIMARY KEY,
parent_id INTEGER,
_lft INTEGER NOT NULL,
_rgt INTEGER NOT NULL,
)

photos表(简化)

CREATE TABLE photos (
id INTEGER PRIMARY KEY,
created_at DATETIME NOT NULL,
album_id INTEGER,
mime_type VARCHAR NOT NULL
width INTEGER NOT NULL,
height INTEGER NOT NULL,
is_starred BOOLEAN NOT NULL DEFAULT false
)

评分最高的照片被定义为最好是打了星的照片，然后是最近的照片。

第一种方法:正确，但速度太慢

SELECT
best_child_photo.album_id AS album_id,
best_child_photo.cover_id AS cover_id,
photos.mime_type AS cover_mime_type,
photos.width AS cover_width,
photos.height AS cover_height
FROM (
SELECT
covered_albums.id AS album_id,
(
SELECT p.id
FROM photos AS p
LEFT JOIN albums AS direct_parents ON (direct_parents.id = p.album_id)
WHERE direct_parents._lft >= covered_albums._lft AND direct_parents._rgt <= covered_albums._rgt
ORDER BY p.is_starred DESC, p.created_at DESC
LIMIT 1
) AS cover_id
FROM albums AS covered_albums
) AS best_child_photo
LEFT JOIN photos ON (photos.id = best_child_photo.cover_id);

较慢的部分是内部的单值查询，它为每个相册ID查找最佳儿童照片的ID。

第二种方法:快速但不完整

一个错误的，但更快的查询是

SELECT
albums.id AS album_id,
photos.id AS cover_id,
photos.mime_type AS cover_mime_type,
photos.width AS cover_width,
photos.height AS cover_height
FROM albums
LEFT JOIN (
photos
LEFT JOIN albums AS direct_parents
ON (direct_parents.id = photos.album_id)
)
ON (direct_parents._lft >= albums._lft AND direct_parents._rgt <= albums._rgt);
ORDER BY album_id ASC, photos.is_starred DESC, photos.created_at DESC;

是错误的，因为它没有为每个相册返回单行，而是将每个相册映射到它的所有递归子照片。尽管它返回很多很多行，但它比第一个查询快两个数量级。对于第二种方法，查询规划器可以使用其索引树来执行左连接。

讨论作为一个经验法则，我们可以说:"先排序，限制为1，最后加入";比"先联接(所有的)，最后排序"慢。如您所见，第二种方法忽略了"对1的限制"。一步。

所以我想知道是否有可能使用基于第二种方法的东西，然后用相同的album_id过滤每个分区中的第一行。不幸的是，像

这样的东西

SELECT
albums.id AS album_id,
FIRST(photos.id) AS cover_id,
FIRST(photos.type) AS cover_type
FROM ...
...
ORDER BY album_id ASC, photos.is_starred DESC, photos.created_at DESC
GROUP BY album_id

无效，因为没有聚合函数FIRST。

任何想法?

最后备注:我知道MySQL允许SELECT-子句中的列既不是分组函数也不是聚合函数。在这种情况下，MySQL使用第一行的值，这正是我需要的，但它是特定于MySQL的。对于PostgreSQL，我可以使用DISTINCT ON (album_id)，这也会给我想要的结果，但DISTINCT ON只被PostgreSQL支持。

Answer 1

这只是使用不相关行为的标准SQL来确定每个相册的封面照片的一个建议。

WITH cte AS (
SELECT covered_albums.*
, p.id AS photo_id
, p.album_id AS photo_album
, ROW_NUMBER() OVER (PARTITION BY covered_albums.id ORDER BY is_starred DESC, created_at DESC) AS rn
FROM photos AS p
JOIN albums AS direct_parents ON (direct_parents.id = p.album_id)
JOIN albums AS covered_albums
ON direct_parents._lft >= covered_albums._lft
AND direct_parents._rgt <= covered_albums._rgt
)
SELECT *
FROM cte
WHERE rn = 1
;

MySQL 8.0

结果(给出一些测试数据)。

注意:对于这个测试，创建了一个简单的相册层次结构(11个相册)，每个相册都有一张照片(巧合的是，具有相同的id)，只是为了展示一个简单的情况。

每张照片的created_at值初始化为不同，以避免绑定问题。

+----+-----------+------+------+----------+-------------+----+
| id | parent_id | _lft | _rgt | photo_id | photo_album | rn |
+----+-----------+------+------+----------+-------------+----+
|  1 |      NULL |    1 |   22 |       10 |          10 |  1 |
|  2 |      NULL |    2 |    9 |        6 |           6 |  1 |
|  3 |      NULL |   10 |   21 |       10 |          10 |  1 |
|  4 |      NULL |    3 |    8 |        6 |           6 |  1 |
|  5 |      NULL |    4 |    5 |        5 |           5 |  1 |
|  6 |      NULL |    6 |    7 |        6 |           6 |  1 |
|  7 |      NULL |   11 |   16 |       10 |          10 |  1 |
|  8 |      NULL |   17 |   18 |        8 |           8 |  1 |
|  9 |      NULL |   19 |   20 |        9 |           9 |  1 |
| 10 |      NULL |   12 |   13 |       10 |          10 |  1 |
| 11 |      NULL |   14 |   15 |       11 |          11 |  1 |
+----+-----------+------+------+----------+-------------+----+