SQL-пересекающиеся вложенные запросы
Используя Oracle SQL, я пытаюсь написать утверждение, которое отвечает на следующий вопрос: команды НФЛ с большим количеством побед, чем их средний дивизион, которые также имеют больше штрафных ярдов, чем среднее по дивизиону.
У меня есть два отдельных утверждения, которые можно вернуть по отдельности (IE: таблица, в которой перечислены команды с большим количеством побед, чем в среднем по дивизиону, и отдельная таблица, в которой перечислены команды с большим количеством штрафных ярдов, чем в среднем)
Однако я сталкиваюсь с ошибками, когда пытаюсь пересечь два запроса. Ошибка "Отсутствует ключевое слово для выбора".
WITH divisionPenaltyYards AS
(
SELECT division
,avg(penaltyyards) AS AVGPenalty
FROM nfl.teams
GROUP BY division
)
WITH divisionWins AS
(
SELECT division
,avg(wins) AS AVGWins
FROM nfl.teams
GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionPenaltyYards
ON nfl.teams.division = divisionPenaltyYards.division
WHERE penaltyyards > AVGPenalty
INTERSECT
SELECT team
FROM nfl.teams
INNER JOIN divisionWins
ON nfl.teams.division = divisionWins.division
WHERE wins > AVGWins;
Обновлено: Дополнительная информация по запросу
NFL.Teams состоит из статистики для всех команд НФЛ. Команда, дивизион, победы, пенальти ...
Пример набора данных будет включать
Packers, NFC NORTH, 6, 984Steelers, AFC NORTH, 12, 817
и так далее.
Желаемый результат - таблица, в которой перечислены все команды, у которых больше побед, чем в среднем по дивизиону, но при этом у них больше штрафных ярдов, чем в среднем по дивизиону.
Не могли бы вы предоставить образцы данных из ваших таблиц?
Ответы 1
Второй CTE не должен начинаться с ключевого слова WITH. Просто , divisionWins AS
WITH divisionPenaltyYards AS
(
SELECT division
,avg(penaltyyards) AS AVGPenalty
FROM nfl.teams
GROUP BY division
)
, divisionWins AS
(
SELECT division
,avg(wins) AS AVGWins
FROM nfl.teams
GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionPenaltyYards
ON nfl.teams.division = divisionPenaltyYards.division
WHERE penaltyyards > AVGPenalty
INTERSECT
SELECT team
FROM nfl.teams
INNER JOIN divisionWins
ON nfl.teams.division = divisionWins.division
WHERE wins > AVGWins;
Здесь также нет необходимости использовать два CTE.
WITH divisionCTE AS
(
SELECT division,
avg(penaltyyards) as avgpenalty,
avg(wins) as avgwins
FROM nfl.teams
GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE penaltyyards > AVGPenalty
INTERSECT
SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE wins > AVGWins;
Кроме того, здесь работает INTERSECT, но UNION имеет больше смысла. Однако в конечном итоге ни то, ни другое не требуется:
WITH divisionCTE AS
(
SELECT division,
avg(penaltyyards) as avgpenalty,
avg(wins) as avgwins
FROM nfl.teams
GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE penaltyyards > AVGPenalty
AND wins > avgwins
Наконец, если вы хотите избежать JOIN, вы можете использовать оконные функции:
SELECT team
FROM
(
SELECT team, wins, penaltyyards,
avg(penaltyyards) OVER (PARTITION BY division) as avgpenalty,
avg(wins) OVER (PARTITION BY division) as avgwins
FROM nfl.teams
) averages
WHERE penaltyyards > AVGPenalty AND wins > avgwins
Отличный ответ, и я ценю ваш вклад. Все, что заставляет его работать с меньшим количеством кода, прекрасно. Одно небольшое замечание, вы забыли изменить имя DivisionWins во время оператора соединения на divisionCTE, когда вы переименовали CTE. В остальном это сработало безупречно.
Ах! Я, конечно, сделал. Я исправил это и добавил еще одну опцию с использованием оконных функций для вычисления средних значений, чтобы можно было избежать JOIN. Не уверен, что это будет быстрее, потому что держу пари, что ваши данные относительно малы, но это хороший лаконичный запрос.
Образцы данных и желаемые результаты действительно помогут другим людям понять, что вы пытаетесь сделать.