PostgreSQL: получите ТОП-сотрудников, приносящих доход, в каждом магазине
Итак, я использую dvdrental DB и хочу получить список сотрудников, приносящих наибольший доход в 2017 году, с указанием каждого магазина. Проблема в том, что я получаю информацию обо всех сотрудниках и их доходах, но мне нужен только 1 ТОП-сотрудник на каждый магазин. Я также ограничен в использовании оконных функций.
Вот мой запрос
SELECT staff.first_name,
staff.last_name,
address.address,
SUM (payment.amount) AS revenue_generated
FROM payment
INNER JOIN staff ON staff.staff_id = payment.staff_id
INNER JOIN store ON store.store_id = staff.store_id
INNER JOIN address ON address.address_id = store.address_id
WHERE EXTRACT(YEAR FROM payment.payment_date) = 2017
GROUP BY staff.staff_id, address.address_id
ORDER BY revenue_generated DESC;
И вот результат
Hanna Carry 28 MySQL Boulevard 79736.45
Hanna Rainbow 47 MySakila Drive 40537.94
Peter Lockyard 47 MySakila Drive 40077.97
Jon Stephens 28 MySQL Boulevard 33927.04
Mike Hillyer 47 MySakila Drive 33489.47
Итак, я хочу, чтобы все было так, без использования LIMIT:
Hanna Carry 28 MySQL Boulevard 79736.45
Hanna Rainbow 47 MySakila Drive 40537.94
MS Сервер и что? ROW_NUMBER()/RANK()/DENSE_RANK() предложил в ответе, что я также связал работу в postgres. Что касается агрегата, запрос в вопросе уже делает это...
Это более или менее работает, более или менее одинаково в большинстве реализаций SQL. Это не достаточная причина для перекрестных ссылок между разными СУБД как дубликатов друг друга - если только мы также не решим закрыть все темы, спрашивающие о sqrt как о дубликатах какого-то древнего языка, только потому, что это также более или менее работает более или менее одинаково. в большинстве языков и диалектов. Я согласен, что весьма вероятно, что эта тема является дубликатом, но я утверждаю, что это не лучший выбор для ссылки.
Вы можете возразить, что эта ветка, кажется, содержит уникальный элемент ОП, спрашивающий: «Как мне применить это здесь». В конечном счете, это вопрос о том, для кого полезно закрытие этой темы: ОП оставляет разочарование, будущие посетители перенаправляются от конкретной проблемы в том же образовательном образце базы данных PostgreSQL к смутно связанной теме T-SQL.
Интересно посмотреть, как нужно лгать, чтобы доказать свою точку зрения, хм? Написание большего или меньшего количества работ, более или менее таким же образом, выделенным курсивом, как вы это сделали, чтобы заставить людей поверить, что это моя цитата, хотя это не так (вы отвечали на неотредактированный автоматический комментарий, когда я пометил вопрос как дубликат) можно назвать только так: ложь. Кроме того, я бы сказал, что оконные функции являются лучшим решением для вашего ответа именно потому, что они работают точно так же (те же запросы будут пониматься и возвращать те же результаты) в других базах данных, вот что значит следовать такому стандарту, как SQL. для.
DISTINCT ON — это расширение языка, предназначенное только для Postgres. Кроме того, ваш ответ никак не влияет на связи между двумя или более сотрудниками, о чем говорится в связанном ответе. Это также не позволит иметь первые N записей для каждой группы (со связями или без них), все это очень легко решается с помощью оконных функций в моем комментарии. Вероятно, это не по теме для этого сайта, но в случае оценки персонала это может означать дискриминацию сотрудников, выплачивая более высокий бонус только лучшим работникам, хотя на самом деле несколько человек могут заслуживать одинаковую сумму.
Курсив или курсив в основном используются не в кавычках, а просто в акценте. Если бы это была цитата, я бы использовал кавычки с курсивом или без него. Я удивлен, как быстро вы перешли к такому запутанному и сверхразумному обвинению, вытекающему не из того, что я сказал, а из-за вашего непонимания или дикой чрезмерной интерпретации шрифта. Вы можете не любить distinct on и отдавать предпочтение оконным функциям, и я не только не буду с вами спорить, я думаю, что это вполне разумный выбор по умолчанию. Дело в том, что ОП указал: «Я также ограничен в использовании оконных функций».
@Atmo Если вы почувствовали, что на вас напали, бросили вызов или вами манипулировали посредством коварного использования курсива - это не было моей целью, и я приношу свои извинения. Я подчеркнул, что моя точка зрения заключалась в несоответствии СУБД, и Charlieface уже устранил эту проблему, добавив ветку PostgreSQL distinct on к ссылкам перед закрытием ветки. Комментарий об оконных функциях и distinct on был бы более подходящим для ответа, к которому вы обращаетесь, если вы хотите продолжить тему, но не похоже, что мы расходимся во мнениях по какому-либо важному вопросу. ОП получил ответ, тред дедуплицирован, вы получили согласие и извинения — надеюсь, на этом мы закончили.
Ответы 1
Вы можете обернуть это в подзапрос и добавить отдельный к , чтобы получить по одной записи для каждого store_id. Если имена ваших первичных ключей совпадают с именами внешних ключей, вы можете использовать более короткий вариант JOIN...USING().
Демо на db<>fiddle:
SELECT DISTINCT ON (store_id)
first_name, last_name, address, revenue_generated
FROM (
SELECT store_id,
staff.first_name,
staff.last_name,
address.address,
SUM (payment.amount) AS revenue_generated
FROM payment
INNER JOIN staff USING (staff_id)
INNER JOIN store USING (store_id)
INNER JOIN address USING (address_id)
WHERE EXTRACT(YEAR FROM payment.payment_date) = 2017
GROUP BY store.store_id, staff.staff_id, address.address_id) AS subq
ORDER BY store_id, revenue_generated DESC;
Ваша идея с LIMIT сработала бы, если бы вы использовали боковой подзапрос , позволяющий каждому магазину запускать собственное агрегирование для каждого из своих сотрудников в 2017 году, упорядочив его по убыванию, а затем limit 1. Ограничение будет применяться к подзапросу каждого магазина отдельно, в результате чего каждый из них получит только одну строку.
@Atmo Это ветка Microsoft SQL Server, и здесь речь идет об одной верхней строке для каждой группы, но верхняя строка определяется на основе агрегатной функции, а не чего-то в исходных данных.