Предложение MySQL SQL EXISTS не работает в качестве обходного пути для ограничений использования LIMIT
Я тестировал приведенный ниже запрос, думая, что он поможет мне обойти ограничение «нельзя использовать LIMIT в подзапросах». Запрос выполняется, но среднее значение — это просто среднее всех записей WHERE MScores.MemberId=1193 AND MScores.Div="FSO"
Кажется, он полностью игнорирует предложение EXISTS.
Запрос должен возвращать скользящее среднее 5 (или менее) последних оценок для конкретного члена в определенном подразделении.
Есть идеи, почему предложение EXISTS не влияет на результаты?
SELECT MScores.MemberId, MScores.`Div`, AVG(MScores.Final) AS AvgOfFinal
FROM qryMatchScores MScores
WHERE MScores.MemberId=1193 AND MScores.`Div` = "FSO" AND
EXISTS (
SELECT MatchDate
FROM qryMatchScores MDates
WHERE
MDates.MemberId = MScores.MemberId AND
MDates.`Div` = MScores.`Div` AND
MDates.MatchDate = MScores.MatchDate
ORDER BY MatchDate DESC
LIMIT 5
);
Обновление =============================================== ===
Я оценил все ответы, но после того, как нашел решение, понял, что все ответы, кроме одного, были получены от людей, практически не имеющих опыта работы с MySQL (специфично для MariaDb).
Спасибо @joel-coehoorn (отмечен как ответ) за его решение, которое подтвердило то, к чему я в конечном итоге пришел... они почти одинаковы!
SELECT MemberId, `Div`, AVG(Final) AS RollingAVG
FROM (
SELECT
MemberId,
`Div`,
Final,
MatchDate,
ROW_NUMBER() OVER (PARTITION BY MemberId, `Div` ORDER BY MatchDate DESC) AS row_num
FROM qryMatchScores
ORDER BY MemberId, `Div`
) AS MScores
WHERE row_num <= 5
GROUP BY MemberId, `Div`;
... и спасибо всем, кто проголосовал против вопроса. Вы действительно умеете воодушевлять других!
Ваше существование выглядит подозрительно неправильно. Приведите полный минимальный воспроизводимый пример
Начните с включения ONLY_FULL_GROUP_BY. dev.mysql.com/doc/refman/8.0/en/…
КСТАТИ. EXISTS имеет значение true, если из подзапроса возвращается хотя бы одна строка.
Какова ваша настоящая цель здесь?
Запрос не должен был выполняться. Как можно суммировать данные без GROUP BY?
Эта тестовая версия не требует группировки. Поля MemberId и Div указаны. Как только я найду работающее решение, я удалю WHERE MScores.MemberId=1193 AND MScores.Div = "FSO" и добавлю предложение GROUP BY.
К вашему сведению... Агрегаты SQL не требуют предложения GROUP BY. Если этот параметр опущен, возвращается агрегат для всего набора данных.
Выборочные данные и ожидаемые результаты имеют важное значение. Кстати, ваш запрос не имеет смысла, потому что столбцы LIMIT (без смещения) и ORDER BY и SELECT не используются в проверке EXISTS, а только наличие строк в подзапросе. Вероятно, вам нужна оконная функция AVG OVER, но без дополнительной информации сложно сказать.
@MarkP, только когда MySQL работает с отключенным ONLY_FULL_GROUP_BY. Не рекомендуется.
Я думаю, что вам действительно нужно ПРИСОЕДИНИТЬСЯ к подзапросу, который использует ORDER BY и LIMIT, а не использовать EXISTS.
@jarlh Они выбирают только отдельные MemberId и Div, поэтому группировка по этим столбцам не изменит агрегацию.
Ответы 3
Вы пытаетесь сделать две отдельные и несовместимые вещи в своем подзапросе: выберите последние 5 дат и отфильтруйте их по дате внешней строки. Вы не можете иметь и то, и другое. Самое близкое к вашему подходу, что я могу придумать, это:
select AVG(MScores.Final) AvgOfFinal
from qryMatchScores MScores
where MScores.MemberId=1193 and MScores.`Div` = "FSO" and 5 > (
select count(distinct MDates.MatchDate)
from qryMatchScores MDates
where (MScores.MemberId,MScores.`Div`)=(MDates.MemberId,MDates.`Div`) and
MDates.MatchDate > MScores.MatchDate
)
Без некоторых примеров данных и соответствующего ожидаемого результата трудно сказать, что вы хотите сделать. Еще одна вещь, не до конца объясненная, это ваше утверждение:
Запрос должен возвращать скользящее среднее 5 (или менее) последних баллы для конкретного члена в определенном дивизионе
Насколько я понял, это можно решить, используя аналитическую функцию Avg() Over() с предложением окон и Row_Number() для обработки порядка (и области, если необходимо)...
Create Table
MatchScores ( MemberId Int, Division Text, Final Int, MatchDate Date );
--
-- S a m p l e D a t a :
Insert Into MatchScores ( MemberId, Division, Final, MatchDate )
VALUES
( 101, 'FSO', 100, '2024-07-01' ),
( 101, 'FSO', 110, '2024-07-02' ),
( 101, 'FSO', 120, '2024-07-03' ),
( 101, 'FSO', 120, '2024-07-04' ),
( 101, 'FSO', 100, '2024-07-05' ),
( 101, 'FSO', 130, '2024-07-06' ),
( 101, 'FSO', 100, '2024-07-07' ),
--
( 201, 'FSO', 10, '2024-07-01' ),
( 201, 'FSO', 16, '2024-07-02' ),
( 201, 'FSO', 10, '2024-07-03' ),
( 201, 'FSO', 14, '2024-07-04' ),
( 201, 'FSO', 12, '2024-07-05' ),
--
( 301, 'FSO', 1, '2024-07-01' ),
( 301, 'FSO', 3, '2024-07-02' );
... SQL получает скользящее (текущее) среднее значение в обоих случаях, упорядоченное по дате и среднее значение всех выбранных строк...
SELECT ms.MemberId, ms.Division, ms.Final, ms.MatchDate,
Avg(Final) Over( Partition By ms.MemberId
Order By ms.RN
Rows Between 4 Preceding And Current Row) as rolling_avg,
Avg(Final) Over( Partition By ms.MemberId ) as avg,
Avg(Final) Over( Partition By ms.MemberId
Order By ms.RN Desc
Rows Between 4 Preceding And Current Row) as reversed_rolling_avg,
ms.RN
FROM ( Select MemberId, Division, Final, MatchDate,
Row_Number()
Over( Partition By MemberId Order By MatchDate Desc) as RN
From MatchScores
) ms
ORDER BY ms.MemberId, ms.RN;
/* R e s u l t :
MemberId Division Final MatchDate rolling_avg avg reversed_rolling_avg RN
-------- -------- ---------- ---------- ----------- ---------- -------------------- --
101 FSO 100 2024-07-07 100.0000 111.4286 114.0000 1
101 FSO 130 2024-07-06 115.0000 111.4286 116.0000 2
101 FSO 100 2024-07-05 110.0000 111.4286 110.0000 3
101 FSO 120 2024-07-04 112.5000 111.4286 112.5000 4
101 FSO 120 2024-07-03 114.0000 111.4286 110.0000 5
101 FSO 110 2024-07-02 116.0000 111.4286 105.0000 6
101 FSO 100 2024-07-01 110.0000 111.4286 100.0000 7
201 FSO 12 2024-07-05 12.0000 12.4000 12.4000 1
201 FSO 14 2024-07-04 13.0000 12.4000 12.5000 2
201 FSO 10 2024-07-03 12.0000 12.4000 12.0000 3
201 FSO 16 2024-07-02 13.0000 12.4000 13.0000 4
201 FSO 10 2024-07-01 12.4000 12.4000 10.0000 5
301 FSO 3 2024-07-02 3.0000 2.0000 2.0000 1
301 FSO 1 2024-07-01 2.0000 2.0000 1.0000 2 */
... если вы хотите отфильтровать (ограничить) все до 5 последних строк для каждого идентификатора - просто добавьте предложение WHERE во внешний запрос...
SELECT ms.MemberId, ms.Division, ms.Final, ms.MatchDate,
Avg(Final) Over( Partition By ms.MemberId
Order By ms.RN
Rows Between 4 Preceding And Current Row) as rolling_avg,
Avg(Final) Over( Partition By ms.MemberId ) as avg,
Avg(Final) Over( Partition By ms.MemberId
Order By ms.RN Desc
Rows Between 4 Preceding And Current Row) as reversed_rolling_avg,
ms.RN
FROM ( Select MemberId, Division, Final, MatchDate,
Row_Number()
Over( Partition By MemberId Order By MatchDate Desc) as RN
From MatchScores
) ms
WHERE ms.RN <= 5 -- filtering to just 5 latest rows per id
ORDER BY ms.MemberId, ms.RN;
/* R e s u l t :
MemberId Division Final MatchDate rolling_avg avg reversed_rolling_avg RN
-------- -------- ---------- ---------- ----------- ---------- -------------------- --
101 FSO 100 2024-07-07 100.0000 114.0000 114.0000 1
101 FSO 130 2024-07-06 115.0000 114.0000 117.5000 2
101 FSO 100 2024-07-05 110.0000 114.0000 113.3333 3
101 FSO 120 2024-07-04 112.5000 114.0000 120.0000 4
101 FSO 120 2024-07-03 114.0000 114.0000 120.0000 5
201 FSO 12 2024-07-05 12.0000 12.4000 12.4000 1
201 FSO 14 2024-07-04 13.0000 12.4000 12.5000 2
201 FSO 10 2024-07-03 12.0000 12.4000 12.0000 3
201 FSO 16 2024-07-02 13.0000 12.4000 13.0000 4
201 FSO 10 2024-07-01 12.4000 12.4000 10.0000 5
301 FSO 3 2024-07-02 3.0000 2.0000 2.0000 1
301 FSO 1 2024-07-01 2.0000 2.0000 1.0000 2 */
См. скрипку здесь.
ПРИМЕЧАНИЕ:
В зависимости от ваших фактических данных вам необходимо добавить столбец Division в предложение Partition By (с MemberId) аналитических функций (в случае, если 1 MemberId может быть с 2 подразделениями) ИЛИ выполнить фильтрацию Division в предложении WHERE (если 1 MemberId всегда с 1 дивизией)...
ROW_NUMBER() и оконные функции - это определенно то, что вам нужно... никаких LIMIT не требуется и не требуется, что в любом случае является нестандартным sql.
Было бы полезно, если бы вы попробовали row_number()
Select MemberId, `Div`, AVG(Final) as AvgOfFinal
from
(
Select
MScores.MemberId,
MScores.`Div`,
MScores.Final,
ROW_NUMBER() OVER (PARTITION BY MScores.MemberId, MScores.`Div` order by MScores.MatchDate desc) AS rn
from qryMatchScores MScores
where MScores.MemberId = 1193 AND MScores.`Div` = 'FSO'
) as RankedScores
where rn <= 5
group by 1,2;
Можете ли вы создать репродуктор? dbfiddle.uk/3aMmxpPT можно использовать.