Блог

Главная - Вопросы по программированию - Как выполнить предварительный фильтр дат перед группировкой

Как выполнить предварительный фильтр дат перед группировкой

#mysql #group-by

Вопрос:

Я сгруппировал некоторые данные MySQL, чтобы выполнить некоторое объединение полей. Однако у меня есть даты, которые мне нужно найти раньше всего, прежде чем группировать, а затем отобразить самую раннюю дату начала и последнюю дату окончания в результате.

 SELECT *,
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR'   ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping

Мои табличные данные представлены в формате:

OrderNbr, Qty, InventoryID, StartDate, FinishDate, RequestedDate, OrderDate

После группировки мне нужен результат, чтобы показать:

InventoryID, Qty, EarliestStartDate, LatestFinishDate, OrderDate, RequestedDate, jobs

Но я не уверен, как найти и отобразить даты до и после группировки.

Я думаю примерно так

 SELECT MIN(startdate) AS EarlistStartDate,
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR'   ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping

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

1. Предоставьте примеры данных в виде СОЗДАТЬ ТАБЛИЦУ ВСТАВИТЬ В и желаемый вывод для этих данных. Также укажите точную версию MySQL.

2. Мне было поручено рассмотреть этот вопрос, поскольку он был написан новым автором. Вопрос, похоже, в порядке вещей. 1Up

Ответ №1:

Если вы хотите найти самую раннюю дату начала и самую последнюю дату окончания внутри группы, этого должно быть достаточно:

 SELECT MIN(startdate) AS EarliestStartDate, MAX(finishDate) AS LatestFinishDate
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR'   ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping

Однако, если вам нужно найти последнюю дату окончания до группировки и самую раннюю дату начала после группировки, вам нужно будет сделать что-то вроде этого:

 SELECT COALESCE(latestbefore.finishDate, jbgroup.EarliestStartDate),
       COALESCE(earliestafter.startDate, jbgroup.LatestFinishDate),
       jbgroup.jobs
FROM
(
SELECT MIN(startdate) AS EarliestStartDate,
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR'   ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping
) jbgroup
LEFT JOIN jobs latestbefore
ON latestbefore.finishDate <= jbgroup.EarliestStartDate
LEFT JOIN jobs afterlatestbefore
ON afterlatestbefore.finishDate > latestbefore.finishDate AND
   afterlatestbefore.finishdate <= jbgroup.EarliestStartDate
LEFT JOIN jobs earliestafter
ON earliestafter.startDate >= jbgroup.LatestFinishDate
LEFT JOIN jobs beforeearliestafter
ON beforeearliestafter.startDate < earliestafter.startDate AND
   beforeearliestafter.startDate >= jbgroup.LatestFinishDate
WHERE afterlatestbefore.finishDate IS NULL AND
      beforeearliestafter.finishDate IS NULL;

Объяснение:

  • мы используем наш первоначальный запрос в качестве подвыборки, получая группы
  • latestbefore является последней датой перед началом группы
  • мы гарантируем, что latestbefore это последняя дата до группы, гарантируя, что таковой afterlatestbefore не существует, то есть до группы, но после latestbefore
  • earliestafter является самой ранней датой после группы
  • мы гарантируем, что earliestafter это самая ранняя дата после создания группы, гарантируя, что такой beforeearliestafter даты не существует после создания группы, но до earliestafter
Метки: