Доброго времени суток!
Имеется 3 таблицы:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE TABLE IF NOT EXISTS `salestrk` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`obj` int(3) NOT NULL,
`datetime` datetime NOT NULL,
`np_code` int(10) NOT NULL,
`np_article` int(10) NOT NULL,
`trk` int(2) NOT NULL,
`crane` int(1) NOT NULL,
`count` decimal(8,3) NOT NULL,
`sum` decimal(7,2) NOT NULL,
`typecoin` text COLLATE utf8_unicode_ci NOT NULL,
`checknum` text COLLATE utf8_unicode_ci NOT NULL,
`checkdry` text COLLATE utf8_unicode_ci NOT NULL,
`ordernum` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14419 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE IF NOT EXISTS `dpu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`obj` int(11) NOT NULL,
`datetime` datetime NOT NULL,
`th_doc` text COLLATE utf8_unicode_ci NOT NULL,
`docType` text COLLATE utf8_unicode_ci NOT NULL,
`np_code` int(10) NOT NULL,
`tank_code` int(10) NOT NULL,
`lvl` decimal(5,1) NOT NULL,
`value` decimal(8,3) NOT NULL,
`temp` decimal(3,1) NOT NULL,
`density` decimal(5,4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25486 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE IF NOT EXISTS `trk2tank` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetimeFrom` datetime NOT NULL,
`obj` int(2) NOT NULL,
`trk` int(2) NOT NULL,
`crane` int(1) NOT NULL,
`np_article` int(10) NOT NULL,
`np_code` int(10) NOT NULL,
`tank_number` int(2) NOT NULL,
`tank_code` int(10) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=874 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Необходимо для каждой строки из
salestrk (s) получить наибольшее по trk2tank.`datetimeFrom` значения
trk2tank.`tank_number` и
trk2tank.`tank_code` из таблицы
trk2tank (t2) по условию, т.е.:
1.
t2.`obj`=s.`obj` AND t2.`crane`=s.`crane` AND t2.`trk`=s.`trk` AND t2.`datetimeFrom`<=s.`datetime` LIMIT 1
Затем для каждой строки из этой же таблицы (s) получить среднее значение между предыдущим и следующим по времени значениями `density` из таблицы
dpu (d) по условиям (пример предыдущего, т.к. как вывести среднее двух не разобрался):
1.
WHERE 2.`obj`=s.`obj` AND 2.`tank_code`=t.`tank_code` AND 2.`datetime`<=s.`datetime` ORDER BY 2.`datetime` DESC LIMIT 1
В данный момент имею следующий запрос
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT s.`obj`,s.`datetime`,s.`np_code`,s.`trk`,s.`crane`, s.`count`,
t.`tank_number` AS 'T_tank_number', t.`tank_code` AS 'T_tank_code', t.`np_code` AS 'T_np_code',
d.`density` AS 'D_density'
FROM `salestrk` s
LEFT JOIN `trk2tank` t
ON t.`id`= (SELECT t2.`id`
FROM `trk2tank` t2
WHERE t2.`obj`=s.`obj` AND t2.`crane`=s.`crane` AND t2.`trk`=s.`trk` AND t2.`datetimeFrom`<=s.`datetime` LIMIT 1)
LEFT JOIN `dpu` d
ON d.`id`= (SELECT d2.`id`
FROM `dpu` d2
WHERE d2.`obj`=s.`obj` AND d2.`tank_code`=t.`tank_code` AND d2.`datetime`<=s.`datetime`
ORDER BY d2.`datetime` DESC LIMIT 1)
WHERE s.`obj`=53
И исключая того что из dpu выводится только ближайшее предыдущее значение он работает. (Пробовал сделать еще один JOIN к этой же таблице, запрос выполняется наверное до истечения таймаута)
НО составлял я его по отрывкам из гугла, поэтому уверен что его нужно оптимизировать.
И требуется чтобы вместо ближайшего предыдущего, выводилось среднее между ближайшим предыдущим и ближайшим следующим.
В таблице salestrk содержится ~100 000 строк
В таблице dpu содержится ~1 000 000 строк
В таблице trk2tank содержится ~1 000 строк
Соответственно в таком виде запрос выполняется довольно долго.
Изменение структуры таблиц при необходимости допускается
Буду рад любой помощи.
в SQL не силен, прошу сильно не пинать)
