Рекурсивный / подмножественный запрос PostgreSQL

ProgramBox

Рекурсивный / подмножественный запрос PostgreSQL

Post author:admin
Запись опубликована:23 декабря, 2022
Post category:Вопросы по программированию

#postgresql

Вопрос:

У меня есть база данных результатов спортивных гонок, идентификатор гонки, который будет отображаться до 8 раз со строками для каждого имени спортсмена и данными о времени финиша.

Я пытаюсь извлечь 4-е время для каждой гонки.

Мой текущий (псевдо) код:

 races = "SELECT DISTINCT race_id, race_date FROM race_table ORDER BY race_date"

for race in races:
    fourth_place = "SELECT race_date, finish_time FROM race_table WHERE race_id={} ORDER BY finish_time LIMIT 1 OFFSET 3".format(race['race_id'])

В Python я выполняю отдельный запрос для каждой гонки, что требует времени.

Есть ли способ, которым я могу создать вложенный запрос PostgreSQL, который вернет этот набор данных из одной строки для каждой гонки, и эта строка является подзапросом смещения?

Мои первоначальные попытки вернули только одну строку. Если это невозможно сделать в SQL SELECT, должен ли я изучать хранимые процедуры?

Редактировать: Пример набора данных, одна таблица, несортированный:

 race_id, race_date, athlete, finish_time
1001, 2021-01-16, "Joe B", 12.54
1001, 2021-01-16, "Emma J", 12.03
1001, 2021-01-16, "Tim N", 12.66
1001, 2021-01-16, "Joe B", 13.54
1001, 2021-01-16, "Sarah J", 12.28
1001, 2021-01-16, "Tom N", 13.22
1001, 2021-01-16, "Jim Z", 12.37
1001, 2021-01-16, "Emma K", 11.94
1002, 2021-01-22, "John B", 13.01
1002, 2021-01-22, "Jane D", 13.22
1002, 2021-01-22, "Ron H", 14.01
1002, 2021-01-22, "Dan M", 15.22
1002, 2021-01-22, "Gill P", 14.27
1002, 2021-01-22, "Jo V", 13.88
1002, 2021-01-22, "Harry T", 13.21
1002, 2021-01-22, "Jane Q", 14.62

Запрос должен возвращать одну строку за гонку (и это должно быть выбрано 4-м размещенным (4-м наименьшим числом) временем:

 race_date, finish_time
2021-01-16, 12.37
2021-01-22, 13.88

1. where race_id in (1,2,3,) ?

2. Итак, вы хотите несколько гонок, и всегда четвертую в каждой? Здесь можно использовать обычные табличные выражения / оконные функции, вы можете упорядочить строки внутри рас и выбрать только ту, которую вы хотите

3. Схема таблицы, тестовые данные и ожидаемый результат ускорят ответ на ваш вопрос.

4. Я хотел бы, чтобы в каждой гонке была одна строка на гонку, и эта строка имеет 4-е самое быстрое время (СМЕЩЕНИЕ ПОРЯДКА 3). Я пойду и посмотрю на общие табличные выражения

Ответ №1:

Ваш пример:

 CREATE TABLE race_table(race_id integer, race_date date, athlete text, finish_time numeric (4,2));
INSERT INTO race_table VALUES
(1001, '2021-01-16', 'Joe B', '12.54'),
(1001, '2021-01-16', 'Emma J', '12.03'),
(1001, '2021-01-16', 'Tim N', '12.66'),
(1001, '2021-01-16', 'Joe B', '13.54'),
(1001, '2021-01-16', 'Sarah J', '12.03'),
(1001, '2021-01-16', 'Tom N', '13.22'),
(1001, '2021-01-16', 'Jim Z', '12.37'),
(1001, '2021-01-16', 'Emma K', '11.94'),
(1002, '2021-01-22', 'John B', '13.01'),
(1002, '2021-01-22', 'Jane D', '13.22'),
(1002, '2021-01-22', 'Ron H', '14.01'),
(1002, '2021-01-22', 'Dan M', '15.22'),
(1002, '2021-01-22', 'Gill P', '14.27'),
(1002, '2021-01-22', 'Jo V', '13.88'),
(1002, '2021-01-22', 'Harry T', '13.21'),
(1002, '2021-01-22', 'Jane Q', '14.62');

Я изменил ваши данные, чтобы они содержали повторяющееся значение для finish_time.

Вы начинаете с запроса, который добавляет столбец, содержащий ранг, к вашим данным:

 SELECT race_id,race_date,athlete,finish_time,
 rank() OVER(PARTITION BY race_id ORDER BY finish_time)AS row_nr
FROM race_table

Я думаю rank() , это то, что вам нужно. В зависимости от ваших требований вы можете использовать row_number() или dense_rank() вместо этого. Проверьте это — это влияет на то, как обрабатывается повторяющееся значение.

Затем используйте этот запрос и фильтруйте по row_nr :

 WITH race_ranked AS 
(SELECT race_id,race_date,athlete,finish_time,
 rank() OVER(PARTITION BY race_id ORDER BY finish_time)AS row_nr
FROM race_table )
SELECT race_id,race_date,athlete,finish_time 
FROM race_ranked 
WHERE row_nr = 4;

Вывод:

  race_id | race_date  | athlete | finish_time 
--------- ------------ --------- -------------
    1001 | 2021-01-16 | Jim Z   |       12.37
    1002 | 2021-01-22 | Jo V    |       13.88