Блог

Главная - Вопросы по программированию - Неправильные результаты с инструкцией IN в MariaDB?

Неправильные результаты с инструкцией IN в MariaDB?

#mysql #sql #count #mariadb #aggregate-functions

#mysql #sql #количество #mariadb #агрегатные функции

Вопрос:

Я столкнулся с довольно странной проблемой с моим экземпляром MariaDB при использовании инструкции IN.

Например, если мне нужна информация о некоторых совокупных объемах продуктов, которые продают торговые устройства, я выполняю запрос следующим образом:

 SELECT
SUM( l.volume_given ) AS volume,
COUNT( l.id ) AS sessions,
l.order AS container,
MIN( dev.NAME ) AS NAME,
MIN( dev.address ) AS address,
dev.id AS id
FROM
devices AS dev
JOIN my_logs AS l ON dev.id = l.device_id
WHERE
l.created_at >= '2020-08-31 21:00:00'
AND l.created_at <= '2020-10-21 21:00:00'
AND l.water_type = 0
AND l.device_id IN (*some 20 IDs here*)
GROUP BY container
ORDER BY sessions DESC;

Странно то, что, например, для ID = 114 я получаю только 4 строки для этого устройства из 232 полных строк. Однако, если я выполню тот же запрос, но с

 AND l.device_id = 114

Я получаю 44 строки для этого идентификатора, который является его реальным результатом.

То, что я пытался, было:

  1. пропустите материал JOIN и используйте только my_logs table;
  2. попробуйте разные интервалы дат;
  3. попробуйте аналогичные запросы для других таблиц.

Никакой разницы со всем этим.

У вас есть какие-либо идеи, что проверить? Большое вам спасибо!

Комментарии:

1. Вы агрегируете по container . И ваши group by столбцы несовместимы со select столбцами. Я удивлен, что запрос работает.

2. На самом деле это слегка измененный (путем ОБЪЕДИНЕНИЯ) запрос из серверного приложения Ruby, где он как-то работает.

Ответ №1:

Если я правильно вас понял, вы хотите id , чтобы в GROUP BY предложении:

 GROUP BY l.order, dev.id

В SQL существует золотое правило, согласно которому все неагрегированные столбцы в SELECT предложении должны повторяться в GROUP BY предложении (если только они функционально не зависят от другого столбца, который принадлежит этому предложению, но это уже другая история).

MySQL был небрежен в этом отношении в ранних версиях, вызывая какое-то нелогичное поведение, которое затем трудно отлаживать, если вы не учитываете это правило. Убедитесь, что всегда включен режим sql ONLY_FULL_GROUP_BY , поэтому такие проблемы рассматриваются как синтаксические ошибки.

Обратите внимание, что в этом измененном GROUP BY предложении используются фактические имена столбцов, а не их псевдонимы; это также стандартный SQL (в то время как MySQL допускает иное).

Ответ №2:

Выворачивание наизнанку может ускорить его:

 SELECT  l2.volume, l2.sessions, l2.container,
        dev.NAME, dev.address, dev.id
    FROM  
    (
        SELECT  l.device_id, l.order AS container,
                SUM( l.volume_given ) AS volume,
                COUNT(*) AS sessions
            FROM  logs AS l
            WHERE  l.created_at >= '2020-08-31 21:00:00'
              AND  l.created_at <= '2020-10-21 21:00:00'
              AND  l.water_type = 0
              AND  l.device_id IN (*some 20 IDs here*)
            GROUP BY  l.device_id, container 
    ) AS l2
    JOIN  devices AS dev  ON dev.id = l2.device_id
    ORDER BY  l2.sessions DESC

И это может исправить перерасчет count (*).

Я рекомендую

 INDEX(water_type, device_id)
INDEX(water_type, created_at)