Выберите последний набор записей в группе sql
У меня есть база данных Orable со следующей таблицей:
ID PROVIDER_ID FROM_CURRENCY TO_CURRENCY DATE AMOUNT ACTIVE
1 1 USD EUR 06-FEB-19 1.23 1
2 2 USD EUR 06-FEB-19 1.5 1
3 1 GBP EUR 06-FEB-19 1.23 1
4 1 CAD EUR 06-FEB-19 1.23 1
5 1 USD EUR 05-FEB-19 0.7 1
6 1 GBP EUR 05-FEB-19 0.7 1
Как видите, мы храним обменные курсы от разных провайдеров с их датой, и мне нужно получить идентификаторы последних доступных курсов для конкретного to_currency (например, EUR) и provider_id (например, 1) на определенную дату. . Итак, я должен получить что-то вроде следующего:
ID
1
3
4
Имейте в виду, что DATE не уникальна, поэтому значения могут дублироваться в разных строках, и я не могу использовать ее для соединения с подзапросом. Кроме того, учтите, что DATE и ID не коррелированы, поэтому более высокие даты не обязательно означают более высокие ID (я не могу просто использовать MAX(ID) в предложении select, чтобы получить ID целевой строки).
Я попытался выполнить следующий запрос, но получил ошибку ORA-00979: not a GROUP BY expression.
SELECT ID
FROM EXCHANGE_RATE
WHERE ACTIVE = 1 AND PROVIDER_ID = 1 AND TO_CURRENCY = 'EUR' AND DATE <= TO_DATE('2019-02-06', 'YYYY-MM-DD')
GROUP BY PROVIDER_ID, FROM_CURRENCY, TO_CURRENCY
HAVING DATE = MAX(DATE)
Я также пробовал следующий запрос с той же ошибкой:
SELECT ID, MAX(DATE)
FROM EXCHANGE_RATE
WHERE ACTIVE = 1 AND PROVIDER_ID = 1 AND TO_CURRENCY = 'EUR' AND DATE <= TO_DATE('2019-02-06', 'YYYY-MM-DD')
GROUP BY PROVIDER_ID, FROM_CURRENCY, TO_CURRENCY
Запрос, который извлекает нужные мне строки, следующий, но я не знаю, как получить поле ID для этих строк, поскольку ID не является частью предложения group by.
SELECT MAX(DATE)
FROM EXCHANGE_RATE
WHERE ACTIVE = 1 AND PROVIDER_ID = 1 AND TO_CURRENCY = 'EUR' AND DATE <= TO_DATE('2019-02-06', 'YYYY-MM-DD')
GROUP BY PROVIDER_ID, FROM_CURRENCY, TO_CURRENCY
Любая идея о том, как я могу достичь того, что мне нужно?
РЕДАКТИРОВАТЬ Я думаю, что нашел решение
SELECT ID
FROM EXCHANGE_RATE E JOIN
(SELECT PROVIDER_ID, FROM_CURRENCY, TO_CURRENCY, MAX(DATE) AS LATEST
FROM EXCHANGE_RATE
WHERE DATE <= TO_DATE('2019-02-06', 'YYYY-MM-DD')
GROUP BY PROVIDER_ID, FROM_CURRENCY, TO_CURRENCY) LATEST_EX
ON E.PROVIDER_ID = LATEST_EX.PROVIDER_ID
AND E.TO_CURRENCY = LATEST_EX.TO_CURRENCY
AND E.FROM_CURRENCY = LATEST_EX.FROM_CURRENCY
AND E.DATE = LATEST_EX.LATEST
WHERE E.ACTIVE = 1 AND E.PROVIDER_ID = 1 AND E.TO_CURRENCY = 'EUR'
Ответы 3
Самый простой способ написать хранимую процедуру для хранения строки данных, возвращаемой окончательным запросом, и получить идентификатор, извлекая указатель для этого в таблице.
SELECT MAX(DATE)
FROM EXCHANGE_RATE
WHERE ACTIVE = 1 AND PROVIDER_ID = 1 AND TO_CURRENCY = 'EUR' AND DATE <= TO_DATE('2019-02-06', 'YYYY-MM-DD')
GROUP BY PROVIDER_ID, FROM_CURRENCY, TO_CURRENCY
Помимо этого, единственный способ получить идентификатор, возвращаемый из одного запроса, - это использовать его в группе, но я не вижу, что это решит цель, подзапрос не будет работать так, как вы упомянули.
Надеюсь это поможет :)
Вы можете использовать подзапрос
SELECT ID
FROM EXCHANGE_RATE
WHERE ("DATE") IN
(
SELECT MAX("DATE")
FROM EXCHANGE_RATE
WHERE ACTIVE = 1
AND PROVIDER_ID = 1
AND TO_CURRENCY = 'EUR'
)
AND ACTIVE = 1
AND PROVIDER_ID = 1
AND TO_CURRENCY = 'EUR'
@ beni0888 подзапрос уже удовлетворяет всем необходимым условиям. Я добавил демо.
Он работает как раз из-за совпадения в наборе данных. Попробуйте изменить дату четвертой строки (CAD, EUR) на день до 2019-02-05, и вы увидите, что она не включена в результат, и это возможно. Но все равно спасибо за ваши усилия, предложившие решение с примером :)
@beni0888 добро пожаловать. В этом случае ID 1 и 3 возвращаются, хорошо, но разве вам не нужна последняя дата для 1,1,'EUR'? Без учета даты вернулись бы уже 1,3,4,5,6.
Барбарос, я не говорю, что вам не следует учитывать дату, просто указываю, что предлагаемое вами решение не соответствует тому, что я ищу. Мне нужно получить последнюю строку для каждого (provider_id, from_currency, to_currency) независимо от разницы в датах между ними.
пользовательская функция row_number() для ранжирования дат в порядке убывания для каждой валюты
SELECT id
FROM
(SELECT id, provider_id, from_Currency,
row_number() over
( partition by from_Currency order by "DATE" desc ) as rn
FROM EXCHANGE_RATE
)
WHERE rn=1
спасибо за правку ,,написал в спешке забыл() после row_number#
Я думаю, вы упускаете момент, мне нужно получить каждую последнюю комбинацию для (provider_id, from_currency, to_currency), но подзапрос в вашем ответе вернет только один результат, содержащий строку с максимальным значением между всеми теми, которые соответствуют где пункт.