#sql #oracle #select #max #aggregate-functions
#sql #Oracle #выберите #макс #агрегатные функции
Вопрос:
Я пытаюсь выполнить следующий запрос в базе данных Oracle express edition.
SELECT SIGHTING_ID, MAX(DISTANCE)
FROM
(
SELECT SIGHTING_ID,
SQRT(POWER(LATITUDE 28, 2) POWER(LONGITUDE - 151, 2)) AS DISTANCE
FROM SIGHTINGS
)
GROUP BY SIGHTING_ID;
Цель запроса — вернуть идентификатор объекта НАБЛЮДЕНИЯ и РАССТОЯНИЕ для объекта наблюдения с максимальным расстоянием от координат (-28, 151)
Описание для ПРОСМОТРА таблицы выглядит следующим образом
desc SIGHTINGS
Name Null? Type
----------------------------------------- -------- ----------------------------
SIGHTING_ID NOT NULL NUMBER
SPOTTER_ID NUMBER
BIRD_ID NUMBER
LATITUDE NUMBER
LONGITUDE NUMBER
SIGHTING_DATE DATE
DESCRIPTION VARCHAR2(255)
Когда я запускаю запрос вместо выбора максимального расстояния, он выбирает и показывает весь список наблюдений и их соответствующие расстояния. SIGHTINGS содержит большое количество кортежей, но ниже приведен небольшой список текущих выходных данных
SIGHTING_ID MAX(DISTANCE)
----------- -------------
264172 2.01556444
264174 2.34029913
264180 2.87647354
264198 .637887137
264205 2.08568933
264211 .232594067
264215 2.34104677
264221 .75
264224 .148660687
264235 .684470598
Мой вопрос в том, как мне сделать так, чтобы агрегатная функция MAX работала так, чтобы она возвращала только максимальное расстояние и относительный SIGHTING_ID?
Ответ №1:
Используйте Oracle rownum :
SELECT * FROM
(SELECT SIGHTING_ID, SQRT(POWER(LATITUDE 28, 2) POWER(LONGITUDE - 151, 2)) AS DISTANCE
FROM SIGHTINGS
ORDER BY distance DESC)
WHERE ROWNUM = 1
Причина, по которой вы получаете запись для каждого SIGHTING_ID , заключается в том, что вы группируете по ней. Предложение group by означает -> дайте мне 1 запись для каждой комбинации столбцов, упомянутых внутри предложения .
Комментарии:
1. Если есть две записи с максимальным расстоянием, то в этом случае одна из записей будет пропущена.
2. И откуда ты знаешь, что это не то, чего он хочет? @hemalp108
3. Я не сомневаюсь в вашем ответе, пусть он сам решает, чего он хочет. Я просто спрашиваю об этом из любопытства.
4. Большое вам спасибо, после удаления дополнительной скобки все прошло как по маслу
5. @FrankSchmitt — Вы снова правы. Еще недостаточно кофе!
Ответ №2:
В Oracle 12c можно использовать стандартную функциональность ANSI:
SELECT SIGHTING_ID,
SQRT(POWER(LATITUDE 28, 2) POWER(LONGITUDE - 151, 2)) AS DISTANCE
FROM SIGHTINGS
ORDER BY distance DESC
FETCH FIRST 1 ROW ONLY;
В более ранних версиях решение @sagi, вероятно, является лучшим способом.
Комментарии:
1. Правильным решением для такого рода проблем является первая / последняя агрегатная функция (как я демонстрирую в своем ответе). docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm
2. @mathguy Ваша формулировка вводит в заблуждение. Ваш подход является одним из нескольких правильных решений — Гордон Линофф — еще одно, а саги — еще одно.
3. @FrankSchmitt — Вы правы, я должен был сказать «лучший», а не «правильный». Для Oracle 12c решение Гордона одинаково хорошо; я не согласился с его оценкой для более ранних версий. Я бы отредактировал свои комментарии, но дальше это невозможно.
4. @mathguy . . . Я предпочитаю решение Саги, потому что оно легче обобщается на несколько столбцов (или все столбцы в строке).
Ответ №3:
select min(sighting_id) keep (dense_rank first order by distance) as sighting_id,
min(distance) as distance
from sightings;
Это позволит найти строку или строки с самым коротким distance , и из этих строк (и только из них), если есть связи (самый короткий distance достигается двумя или более sighting id ‘s), он выбирает строку с наименьшим sighting_id . Если вместо этого вы хотите, чтобы значение было наибольшим latitude (скажем), по-прежнему из тех, у которых наименьшее distance , измените order by distance на order by distance, latitude desc . Другие комбинации обрабатываются таким же образом.