PostgreSQL：如何跳过指定ID前的行（该ID非排序列）

我正在使用PostgreSQL 14.13，现有一个为学生智能排序卡片的查询：

select setseed(-0.07687704123439676);
SELECT cards.id,
       dc.deck_id,
       cards.question,
       cards.answer,
       uc.practice_session_sr_level
FROM cards
         LEFT JOIN decks_cards dc ON cards.id = dc.card_id
         LEFT JOIN user_card uc ON cards.id = uc.card_id

WHERE dc.deck_id = 2907
GROUP BY cards.id,
         uc.practice_session_sr_level,
         dc.deck_id
ORDER BY
    --   cards.id = 98486 desc,
         practice_session_sr_level = 'NOT_LEARNED' DESC,
         practice_session_sr_level = 'AGAIN' DESC,
         practice_session_sr_level = 'HARD' DESC,
         practice_session_sr_level = 'EASY' DESC,
         practice_session_sr_level = 'GOOD' DESC,
         RANDOM()
LIMIT 10 OFFSET 0;

该查询返回结果如下：

98512,2907,How do you say 'family'?,rodzina,NOT_LEARNED
98498,2907,How do you say 'teacher' in Polish?,nauczyciel,NOT_LEARNED
98580,2907,What is the Polish word for 'forest'?,las,NOT_LEARNED
98486,2907,How do you say 'friend' in Polish?,przyjaciel,NOT_LEARNED
98525,2907,What is the Polish word for 'heart'?,serce,NOT_LEARNED
98528,2907,How do you say 'solution' in Polish?,rozwiązanie,NOT_LEARNED
98497,2907,What is the Polish word for 'school'?,szkoła,NOT_LEARNED
98540,2907,How do you say 'drink' in Polish?,napój,NOT_LEARNED
...

我希望实现用户点击某张卡片后，从该卡片开始往后展示内容。例如，当用户点击ID为98486的卡片时，需要跳过该卡片之前的所有行。

我尝试取消注释cards.id = 98486 desc,，得到如下当前结果：
当前结果

98486,2907,How do you say 'friend' in Polish?,przyjaciel,NOT_LEARNED
98512,2907,How do you say 'family'?,rodzina,NOT_LEARNED
98498,2907,How do you say 'teacher' in Polish?,nauczyciel,NOT_LEARNED
98580,2907,What is the Polish word for 'forest'?,las,NOT_LEARNED
98525,2907,What is the Polish word for 'heart'?,serce,NOT_LEARNED
98528,2907,How do you say 'solution' in Polish?,rozwiązanie,NOT_LEARNED
98497,2907,What is the Polish word for 'school'?,szkoła,NOT_LEARNED
98540,2907,How do you say 'drink' in Polish?,napój,NOT_LEARNED
...

期望结果

98486,2907,How do you say 'friend' in Polish?,przyjaciel,NOT_LEARNED
98525,2907,What is the Polish word for 'heart'?,serce,NOT_LEARNED
98528,2907,How do you say 'solution' in Polish?,rozwiązanie,NOT_LEARNED
98497,2907,What is the Polish word for 'school'?,szkoła,NOT_LEARNED
98540,2907,How do you say 'drink' in Polish?,napój,NOT_LEARNED
...

请问如何修改该查询，使PostgreSQL能够跳过指定ID之前的行？

要实现跳过指定卡片之前的行，核心是先确定原排序规则下目标卡片的位置，再筛选出位置在它之后（包括自身）的记录。可以用窗口函数ROW_NUMBER()给原排序结果编号，再基于编号过滤：

WITH ranked_cards AS (
    select setseed(-0.07687704123439676);
    SELECT 
        cards.id,
        dc.deck_id,
        cards.question,
        cards.answer,
        uc.practice_session_sr_level,
        ROW_NUMBER() OVER (
            ORDER BY
                practice_session_sr_level = 'NOT_LEARNED' DESC,
                practice_session_sr_level = 'AGAIN' DESC,
                practice_session_sr_level = 'HARD' DESC,
                practice_session_sr_level = 'EASY' DESC,
                practice_session_sr_level = 'GOOD' DESC,
                RANDOM()
        ) AS rn
    FROM cards
    LEFT JOIN decks_cards dc ON cards.id = dc.card_id
    LEFT JOIN user_card uc ON cards.id = uc.card_id
    WHERE dc.deck_id = 2907
    GROUP BY cards.id, uc.practice_session_sr_level, dc.deck_id
)
SELECT id, deck_id, question, answer, practice_session_sr_level
FROM ranked_cards
WHERE rn >= (SELECT rn FROM ranked_cards WHERE id = 98486)
LIMIT 10;

说明： #

1. CTE子查询ranked_cards：按照你原本的排序规则给每条记录分配一个连续的行号rn，行号完全对应原查询的排序顺序。
2. 过滤逻辑：通过子查询找到目标卡片（ID=98486）对应的行号，然后只保留行号大于等于该值的记录，这样就自动跳过了目标卡片之前的所有行。
3. 保持随机性一致性：保留setseed()确保每次执行的随机排序结果一致，避免用户点击后排序混乱。

如果需要支持动态传入目标卡片ID，可以把98486替换成应用端参数或者PostgreSQL变量。

内容的提问来源于stack exchange，提问作者Kevin Amiranoff