用於限制查詢的 row_id 的不同結果

有2張桌子：

CREATE TABLE sample1 (
 id int,
 name varchar,
 score int,
 mode int
)

CREATE TABLE sample2 (
 id int,
 mode int
 ...
)

我需要name用MIN(score)和選擇不同的ROW_NUMBER()。必須排序ROW_NUMBER() ASC。mode必須存在於表中的sample2一行sample1，所以有INNER JOIN。

ROW_NUMBER()順序 LIMIT 查詢需要，因此我可以批量選擇數據。我不關心 LIMIT+ROW_NUMBER() 技巧的一致性。

我的查詢：

WITH sample1 AS (
   WITH sample1 AS (
       SELECT a.name, MIN(a.score) s
       FROM sample1 a
           JOIN sample2 USING (mode)
           GROUP BY a.name
   )
   SELECT *, ROW_NUMBER() OVER (ORDER BY s ASC) AS n
   FROM sample1
) SELECT * FROM sample1 WHERE n > $1 LIMIT $2

我擔心該選擇的效率，實際上它是 3 級深度子查詢。也許有更少醜陋和更有效的方法來實現相同的結果？

此表中的行數大約為 1,000,000，不同集預計為 100,000。查詢頻繁，其實是select的熱表，只有score列會分批更新，和limit select大小一樣。

看起來您不需要任何 CTE、子查詢甚至根本不需要JOIN。只是一個EXISTS半連接和一個附加OFFSET：

SELECT name
    , MIN(score) AS s
    , ROW_NUMBER() OVER (ORDER BY MIN(score)) AS n  -- still needed?
FROM   sample1 a
WHERE  EXISTS (SELECT FROM sample2 s2 WHERE s2.mode = a.mode)
GROUP  BY name
ORDER  BY s
OFFSET $1
LIMIT  $2;

大OFFSET的總是代價高昂，因為必須首先在排序中考慮所有前導的、丟棄的行。

如果它是只讀表，您可以MATERIALIZED VIEW在行號上添加帶有索引的索引並使用SELECT * FROM mv WHERE n > $1 AND n <= $1 + $2. 對大大小小

的都有幫助。$1``$2

引用自：https://dba.stackexchange.com/questions/255633