Выбор данных из последних 3 дат посещения (последнее, предыдущее, до) — производительность запроса

Я пытаюсь вернуть GPS-координаты за последние 3 посещения магазина. Текущий визит Предыдущий визит Посетите до этого.

Проблема в том, что существует более 15 000 магазинов, каждый из которых можно посетить в разные дни (в течение года).

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

Запрос, который у меня есть, возвращает правильный набор данных, однако его эффективность сильно недостаточна, поскольку его выполнение занимает очень много времени (несколько часов).

SELECT 
    MAX(ActionDate)     'VisitDate'
,   Store           'Store'
,   Route           'Route'
,   GPS         'GPS'
FROM 
    sys_data.mod_visit AA
WHERE 
    ActionDate  = (SELECT 
               MAX(ActionDate) FROM sys_data.mod_visit MX
               WHERE 
              ActionDate < (SELECT MAX(ActionDate) FROM sys_data.mod_visit WHERE Store = MX.Store)
               AND MX.Store = AA.Store 
                       GROUP BY 
             Store)
AND ActionDate < CURDATE()
AND YEAR(ActionDate) = YEAR(CURDATE())

Столбцы store и actiondate индексируются.

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

Какая у вас версия MySQL?

Paul Spiegel 22.05.2019 10:54

@PaulSpiegel Сервер MySQL 8.0

Leon Claassen 22.05.2019 10:58

Насколько я понимаю, ваш запрос возвращает только «Предыдущее» посещение магазина. Разве вам не нужны все три (Последний, Предыдущий, Перед)?

Paul Spiegel 22.05.2019 11:10
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
1
3
46
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Дата действия, если = (подзапрос), не нужна AND ActionDate < CURDATE() (уже используется в подзапросе)

и вместо подзапроса where вы можете попробовать внутреннее соединение

    SELECT 
        MAX(ActionDate)     'VisitDate'
    ,   Store           'Store'
    ,   Route           'Route'
    ,   GPS         'GPS'
    FROM  sys_data.mod_visit AA
    INNER JOIN   (
      SELECT Store, MAX(ActionDate) max_date 
      FROM sys_data.mod_visit 
      WHERE  ActionDate < CURDATE()
      GROUP BY Store
    ) MX on MX.max_date  = AA.ActionDate 
          AND MX.store  = AA.store 
  WHERE YEAR(ActionDate) = YEAR(CURDATE())

в любом случае убедитесь, что у вас есть правильный составной индекс на

  table  sys_data.mod_visit  columns  (store , ActionDate)

Спасибо, попробуем эти предложения, посмотрим, каковы результаты, и предоставим отзыв.

Leon Claassen 22.05.2019 12:31

В MySQL 8 вы можете использовать оконную функцию ROW_NUMBER() для перечисления строк на Store в порядке ActionDate. Затем вы просто заглядываете в первые три (для каждого магазина):

SELECT *
FROM (
    SELECT 
        ActionDate     'VisitDate'
    ,   Store           'Store'
    ,   Route           'Route'
    ,   GPS         'GPS'
    ,   ROW_NUMBER() OVER (PARTITION BY Store ORDER BY ActionDate DESC) rn
    FROM 
        sys_data.mod_visit AA
    WHERE ActionDate < CURDATE()
      AND YEAR(ActionDate) = YEAR(CURDATE())
) x
WHERE rn <= 3

У вас должен быть составной индекс на (Store, ActionDate). Но трудно сказать, как оптимизатор использует индексы для оконных функций.

Я бы тоже переписал

AND YEAR(ActionDate) = YEAR(CURDATE())

к

AND ActionDate >= DATE_FORMAT(CURDATE(), '%Y-01-01')

Спасибо @PaulSpiegel, ваше предложение сработало, возвращая 3 строки для каждой торговой точки, единственная проблема заключается в том, что оно возвращает 3 идентичных строки для каждого магазина, а не 3 разные даты посещения для каждого магазина. Да, мне нужны все три, но строил поэтапно, и только на втором этапе это уже заняло целую вечность.

Leon Claassen 23.05.2019 11:05

@LeonClaassen Некоторые образцы данных действительно помогут. Лучше всего, если вы создадите db-рабочий пример - сохраните и сделайте репост.

Paul Spiegel 23.05.2019 11:27

Другие вопросы по теме