Как заставить SQL row_number() начинать с 1, игнорируя условия WHERE

Question

Я пытаюсь реализовать разбиение на страницы на стороне сервера в retool, используя Athena в качестве источника данных, и кажется, что для того, чтобы заставить его работать, мне нужно каким-то образом заставить row_number() начинаться с 1.
Запрос имеет дополнительные условия в WHERE, и он запрашивает результаты на основе динамической даты начала и даты окончания, собранных с помощью средства выбора диапазона дат и времени. Это означает, что номер строки может начинаться с любого значения в зависимости от выбранных дат.

Это мой запрос (query1), вдохновленный поддержкой AWS Athena (Presto) OFFSET:

      SELECT * 
FROM (SELECT row_number() over() AS rn, * FROM database.my_table)
WHERE rn
    BETWEEN {{table.paginationOffset}} AND {{table.paginationOffset + 10}}
    AND date > CAST('{{dateRangeModule.start_date}}' AS DATE)
    AND date < CAST('{{dateRangeModule.end_date}}' AS DATE)

В таблице есть количество страниц, и он показывает 10 строк на странице, что означает, что условие может привести к следующим случаям:
страница 1: МЕЖДУ 0 И 10
страница 2: МЕЖДУ 10 И 20
страница 3: МЕЖДУ 20 И 30
и так далее.

Выполнение запроса без условия, описанного выше:

      SELECT * 
FROM (SELECT row_number() over() AS rn, * FROM database.my_table)
WHERE rn
    date > CAST('{{dateRangeModule.start_date}}' AS DATE)
    AND date < CAST('{{dateRangeModule.end_date}}' AS DATE)

возвращает значения, которые не начинаются с 1 в столбец.

Это означает, что query1 будет возвращать результаты только начиная со страницы 3, где он покажет только те две записи, которые имеют rn =29 и rn =30, потому что:
строки с rn <29 не находятся на странице 1, странице 2 и странице 3.
строки с 29 <= rn <= 30 находятся на странице 3.
строки с 31 <= rn <= 40 находятся на странице 4.
и так далее.

Следуя приведенному выше описанию, как я могу заставить row_number начинаться с 1? Любое решение, подходящее для моего случая, но без использования row_number(), тоже подойдет.

---

Редактировать для решения, предоставленного Гордоном Линоффом:
попробовал выполнить следующий запрос, представленный в решении, но rn по-прежнему отличается от 1:

      SELECT t.*,
       ROW_NUMBER() OVER () as page_rn
FROM (SELECT row_number() over() AS rn, t.*
      FROM database.my_table t
     ) t
WHERE rn BETWEEN {{table.paginationOffset}} AND {{table.paginationOffset + 10}} AND
      date > CAST('{{dateRangeModule.start_date}}' AS DATE) AND
      date < CAST('{{dateRangeModule.end_date}}' AS DATE);

Answer 1

Добавьте новый номер строки для страницы:

      SELECT t.*,
       ROW_NUMBER() OVER () as page_rn
FROM (SELECT row_number() over() AS rn, t.*
      FROM database.my_table t
     ) t
WHERE rn BETWEEN {{table.paginationOffset}} AND {{table.paginationOffset + 10}} AND
      date > CAST('{{dateRangeModule.start_date}}' AS DATE) AND
      date < CAST('{{dateRangeModule.end_date}}' AS DATE);

Обратите внимание, что без - как для номера строки, так и для запроса - результаты неопределенны. Позвольте предположить, что у вас действительно есть ORDER BYs и просто упростили запрос вопроса.