#sql #postgresql #count #aggregate-functions #greatest-n-per-group
#sql #postgresql #подсчитать #агрегатные функции #наибольшее число пользователей на группу
Вопрос:
У меня есть таблица, в которой есть три столбца: валюта, биржа, которая торгует этой валютой, дата
Currency Exchange Date
USD NewYork 01/12/20
USD NewYork 01/11/20
USD NewYork 01/10/20
USD Montreal 01/10/20
CAD Montreal 01/07/20
CAD Montreal 01/06/20
CAD Beijing 01/06/20
Мне нужно ответить на вопрос, какая биржа является лидером для этой конкретной валюты.
Это означает, что для данной валюты вычислите, сколько записей для обмена, и верните только максимум Другими словами, результат запроса должен быть примерно таким
Currency Exchange Frequency
USD NewYork 3
CAD Montreal 2
Комментарии:
1. И откуда берутся
3
и7
?2. Гордон Линофф они исходят из количества раз, когда они появляются в таблице
3. @OlliePugh . , , Они не соответствуют вашим образцам данных.
4. Пожалуйста, укажите в своем вопросе базу данных, которую вы используете: mysql, oracle, postgresql …?
5. @GordonLinoff Это не мой образец данных, лол
Ответ №1:
Вы могли бы использовать оконные функции:
select *
from (
select currency, exchange, count(*) frequency,
rank() over(partition by currency order by count(*) desc) rn
from mytable
group by currency, exchange
) t
where rn = 1
Ответ №2:
Вы можете использовать distinct on
в сценарии. Просто считайте, группируя currency
exchange
и упорядочивая по currency
count
убыванию. Итак, запрос будет выглядеть следующим образом:
select
distinct on (currency)
currency,
exchange,
count(*)
from table1
group by 1,2
order by 1,3 desc
Ответ №3:
Если вам нужны наиболее распространенные строки в запросе агрегации, используйте оконные функции:
select ce.*
from (select currency, exchange, count(*) as cnt,
rank() over (partition by currency order by count(*) desc) as seqnum
from t
group by currency, exchange
) ce
where seqnum = 1;
Примечание: В случае связей это возвращает все максимальные значения. Если вы хотите только один, то используйте row_number()
вместо rank()
.
Редактировать:
В Postgres (который был добавлен после того, как я ответил) вы можете использовать distinct on
:
select distinct on (currency) exchange, count(*) as cnt
from t
group by currency, exchange
order by currency, count(*) desc;
Обратите внимание, что это не возвращает дубликаты, если есть связи.
Ответ №4:
С first_value()
помощью и max()
оконных функций:
select distinct currency,
first_value(exchange) over (partition by currency order by count(*) desc) exchange,
max(count(*)) over (partition by currency) frequency
from tablename
group by currency, exchange
Посмотрите демонстрацию.
Результаты:
> currency | exchange | frequency
> :------- | :---------- | --------:
> CAD | Montreal | 2
> USD | NewYork | 3