Головоломка запроса MySQL - поиск того, что БЫЛО бы самой последней датой

Я просмотрел все и еще не нашел разумного способа справиться с этим, хотя я уверен, что это возможно:

В одной таблице исторических данных есть квартальная информация:

CREATE TABLE Quarterly (
unique_ID INT UNSIGNED NOT NULL,
date_posted DATE NOT NULL,
datasource TINYINT UNSIGNED NOT NULL,
data FLOAT NOT NULL,
PRIMARY KEY (unique_ID));

Другая таблица исторических данных (очень большая) содержит ежедневную информацию:

CREATE TABLE Daily (
unique_ID INT UNSIGNED NOT NULL,
date_posted DATE NOT NULL,
datasource TINYINT UNSIGNED NOT NULL,
data FLOAT NOT NULL,
qtr_ID INT UNSIGNED,
PRIMARY KEY (unique_ID));

Поле qtr_ID не является частью потока ежедневных данных, заполняющих базу данных. Вместо этого мне нужно задним числом заполнить поле qtr_ID в таблице Daily идентификатором строки Quarterly.unique_ID, используя самые последние квартальные данные за что Daily.date_posted для этого источника данных.

Например, если квартальные данные

101 2009-03-31 1 4.5
102 2009-06-30 1 4.4
103 2009-03-31 2 7.6
104 2009-06-30 2 7.7
105 2009-09-30 1 4.7

и ежедневные данные

1001 14.07.2009 1 3.5 ??
1002 15.07.2009 1 3.4 &&
1003 14.07.2009 2 2.3 ^^

тогда мы хотели бы ?? Полю qtr_ID будет присвоено значение «102» в качестве самого последнего квартала для этого источника данных на эту дату, и && также будет равно «102», а ^^ будет равно «104».

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

Я пробовал различные объединения, используя datediff (где проблема заключается в том, чтобы найти минимальное значение datediff больше нуля) и другие попытки, но у меня ничего не работает - обычно мой синтаксис где-то ломается. Любые идеи приветствуются - я выполню любые основные идеи или концепции и отчитаюсь.


mysql join max min datediff
person Hank    schedule 14.05.2010    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Просто выполните подзапрос для идентификатора квартала, используя что-то вроде:

(
 SELECT unique_ID 
 FROM Quarterly 
 WHERE 
     datasource = ? 
     AND date_posted >= ? 
 ORDER BY
     unique_ID ASC
 LIMIT 1
)

Конечно, это, вероятно, не даст вам наилучшей производительности, и предполагается, что даты добавляются в Quarterly последовательно (иначе order by date_posted). Тем не менее, это должно решить вашу проблему.

Вы должны использовать этот подзапрос в ваших операторах INSERT или UPDATE в качестве значения вашего поля qtr_ID для вашей таблицы Daily.

person Kenaniah    schedule 14.05.2010
comment
К сожалению, это не совсем то, что нужно — он извлекает правильный источник данных, но ранжирует его на основе времени, прошедшего с момента (и затем предоставляет первую запись) самых ранних квартальных данных, а не того, что было бы наиболее важным. недавний. Итак, в приведенном выше примере он возвращает «101» для ВСЕХ данных из источника данных = «1» и «103» для ВСЕХ данных из источника данных = «2». Вот что я запустил: UPDATE Daily SET qtr_ID = ( SELECT unique_ID FROM Quarterly WHERE Daily.datasource = Quarterly.datasource AND Daily.date_posted ›= Quarterly.date_posted ORDER BY date_posted ASC LIMIT 1 ) - person Hank; 18.05.2010
comment
Кстати, это предложение НАМНОГО быстрее, чем мой успешный запрос ниже. - person Hank; 18.05.2010
comment
Большая разница в скорости заставила меня работать над предложением выше, что привело к открытию того, что нужно было сделать всего одно небольшое изменение (сортировка по дате_отправления в другом направлении), так что это код, который работает и работает быстро: UPDATE Daily SET qtr_ID = (ВЫБЕРИТЕ уникальный_ID ИЗ Ежеквартально, ГДЕ Daily.datasource = Ежеквартально.источник данных И Daily.date_posted ›= Quarterly.date_posted ORDER BY date_posted DESC LIMIT 1 ); Большое спасибо, Кенания - person Hank; 19.05.2010
comment
Хэнк, причина, по которой это быстрее, вероятно, связана с тем фактом, что unique_ID является индексированным полем. Это предполагает, что date_posted не является. - person Kenaniah; 24.05.2010

arrow_upward
0
arrow_downward

Следующее работает точно так, как задумано, но оно, безусловно, уродливо (с тремя вызовами одного и того же DATEDIFF!!), возможно, увидев рабочий запрос, кто-то сможет еще больше уменьшить или улучшить его:

UPDATE Daily SET qtr_ID = (select unique_ID from Quarterly
WHERE Quarterly.datasource = Daily.datasource AND
DATEDIFF(Daily.date_posted, Quarterly.date_posted) = 
(SELECT MIN(DATEDIFF(Daily.date_posted, Quarterly.date_posted)) from Quarterly
WHERE Quarterly.datasource = Daily.datasource AND
DATEDIFF(Daily.date_posted, Quarterly.date_posted) > 0));
person Hank    schedule 17.05.2010
comment
См. приведенный выше ответ для НАМНОГО более быстрого решения, чем это. - person Hank; 19.05.2010

arrow_upward
0
arrow_downward

После дополнительной работы над этим запросом я получил огромное улучшение производительности по сравнению с исходной концепцией. Самым важным улучшением было создание индексов как в ежедневных, так и в ежеквартальных таблицах — в ежедневных я создал индексы для (источник данных, дата_сообщения) и (дата_сообщения, источник данных), ИСПОЛЬЗУЯ BTREE, и для (источника данных), ИСПОЛЬЗУЯ ХЭШ, а в ежеквартально я сделал то же самое. вещь. Это излишество, но оно удостоверилось, что у меня есть опция, которую может использовать механизм запросов. Это сократило время запроса до менее чем 1% от того, что было раньше. (!!)

Затем я узнал, что с учетом моих конкретных обстоятельств я мог бы использовать MAX() вместо ORDER BY и LIMIT, поэтому я использую вызов MAX(), чтобы получить соответствующий уникальный_ID. Это сократило время запроса примерно на 20%.

Наконец, я узнал, что с помощью механизма хранения InnoDB я могу сегментировать часть таблицы Daily, которую я обновлял, с помощью любого запроса, что позволяло мне выполнять многопоточные запросы с небольшим усилием и написанием сценариев. Параллельная обработка работала хорошо, и каждый поток линейно сокращал время запроса.

Итак, базовый запрос, который работает буквально в 1000 раз лучше, чем моя первая попытка:

UPDATE Daily
SET qtr_ID =
(
  SELECT MAX(unique_ID)
  FROM Quarterly
  WHERE Daily.datasource = Quarterly.datasource AND
        Daily.date_posted > Quarterly.dateposted
)
WHERE unique_ID > ScriptVarLowerBound AND
      unique_ID <= ScriptVarHigherBound
;
person Hank    schedule 31.05.2010