Привет форумчане, помогите решить не простую задачку есть таблица tinux(Время в юникс тайме типа int) name_equip (наименование оборудования, active_enetgi (int) , reactive_enetgi (int) активная и реактивная мощность соответственно. Для упрощения допустим у нас три Генератора (Ген1, Ген2, Ген3) и каждый час снимаются показания и пишутся в базу (если какой-то не в работе записывается последнее показание,а если еще не ввели какойто генератор в работу ни разу то пишется 0, ну в общем не в этом суть)
Выглядит это примерно так
`tinux`_____ `name_equip` `active_enetgi` `reactive_enetgi`
1549584000 Ген1 1 2
1549584000 Ген2 3 4
1549584000 Ген3 0 0
1549587600 Ген3 0 0
1549587600 Ген1 11 14
1549587600 Ген2 18 15
1549591200 Ген1 22 25
1549591200 Ген2 26 22
1549591200 Ген3 2 3
.....
И надо посчитать РАЗНОСТЬ по активной и реактивной энергии для каждого генератора за сутки(на каждый час),месяц и год
в итоге должно получится что-то такое для часовок за сутки
_____________1___2___3__4__....__22__23__24 (можно еще колонку итого сделать)
gen1_act_e____1__10__11____...______________________22 (тоесть для ген1 1,11-1,22-11...)
gen1_react_e__2__12__ 11___..._______________________25
gen2_act_e____3__15__8_____...______________________26 и т.д.
gen2_react_e
gen3_act_e
gen3_react_e


либо в таком виде:
`tinux`_____ `gen1_act_e` `gen1_react_e` `gen2_act_e` `gen2_react_e` `gen3_act_e` `gen3_react_e`
1549584000 1 2 3
1549587600 10 12 15
1549591200 11 11 8

и тоже самое для месяца (это 30 дней) тоесть разность энергии между предыдущем днем и следующим тип 23числа в 00часов записалось значение 1122 а 24 в 00 часов записалось 1322 то в 24 дату запишем 200.
1_2_3_4...._28_29_30
и тоже самое для года (это 12 месяцев)
1_2_3_4...._11_12

вот как-то так! Буду очень признателен за помощь!!!!

Выглядит это примерно так
`tinux`_____ `name_equip` `active_enetgi` `reactive_enetgi`
1549584000_____ Ген1________ 1 _____________2
1549584000 _____Ген2________ 3 _____________4
1549584000 _____Ген3 ________0_____________ 0
1549587600 _____Ген3________ 0_____________ 0

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

либо в таком виде:
`tinux`_____ `gen1_act_e` `gen1_react_e` `gen2_act_e` `gen2_react_e` `gen3_act_e` `gen3_react_e`
1549584000______ 1___________ 2 __________3
1549587600 ______10_________ 12__________ 15
1549591200______ 11_________ 11__________ 8

вобщем сделать я сделал, но это такой говонокод просто жесть,вот для месяца для 4х генераторов

PROCEDURE BUR_test.sssddd(IN date_value int(12))
BEGIN
DROP TABLE IF EXISTS `v_statement`;
CREATE TEMPORARY TABLE `v_statement`
AS
SELECT DISTINCT

a.ts,

-- выводим часoвую разницу между энергиями из таблицы Statement_main
a.aen_m1,
a.raen_m1,
b.aen_m2,
b.raen_m2,
c.aen_m3,
c.raen_m3,
d.aen_m4,
d.raen_m4
FROM
-- определяем отрезок времени
-- день 1
((SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=date_value AND ts<=(date_value+86400) AND name_param='Генератор МКА01'
-- выбираем отдельно столбцы по активной и реактивной энергии для каждого агрегата
UNION
-- день 2
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+86400) AND ts<=(date_value+172800) AND name_param='Генератор МКА01')
UNION
-- день 3
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+172800) AND ts<=(date_value+259200) AND name_param='Генератор МКА01')
UNION
-- день 4
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+259200) AND ts<=(date_value+345600) AND name_param='Генератор МКА01')
UNION
-- день 5
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+345600) AND ts<=(date_value+432000) AND name_param='Генератор МКА01')
UNION
-- день 6
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+432000) AND ts<=(date_value+518400) AND name_param='Генератор МКА01')
UNION
-- день 7
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+518400) AND ts<=(date_value+604800) AND name_param='Генератор МКА01')
UNION
-- день 8
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+604800) AND ts<=(date_value+691200) AND name_param='Генератор МКА01')
UNION
-- день 9
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+691200) AND ts<=(date_value+777600) AND name_param='Генератор МКА01')
UNION
-- день 10
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+777600) AND ts<=(date_value+864000) AND name_param='Генератор МКА01')
UNION
-- день 11
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+864000) AND ts<=(date_value+950400) AND name_param='Генератор МКА01')
UNION
-- день 12
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+950400) AND ts<=(date_value+1036800) AND name_param='Генератор МКА01')
UNION
-- день 13
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1036800) AND ts<=(date_value+1123200) AND name_param='Генератор МКА01')
UNION
-- день 14
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1123200) AND ts<=(date_value+1209600) AND name_param='Генератор МКА01')
UNION
-- день 15
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1209600) AND ts<=(date_value+1296000) AND name_param='Генератор МКА01')
UNION
-- день 16
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1296000) AND ts<=(date_value+1382400) AND name_param='Генератор МКА01')
UNION
-- день 17
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1382400) AND ts<=(date_value+1468800) AND name_param='Генератор МКА01')
UNION
-- день 18
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1468800) AND ts<=(date_value+1555200) AND name_param='Генератор МКА01')
UNION
-- день 19
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1555200) AND ts<=(date_value+1641600) AND name_param='Генератор МКА01')
UNION
-- день 20
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1641600) AND ts<=(date_value+1728000) AND name_param='Генератор МКА01')
UNION
-- день 21
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1728000) AND ts<=(date_value+1814400) AND name_param='Генератор МКА01')
UNION
-- день 22
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1814400) AND ts<=(date_value+1900800) AND name_param='Генератор МКА01')
UNION
-- день 23
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1900800) AND ts<=(date_value+1987200) AND name_param='Генератор МКА01')
UNION
-- день 24
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+1987200) AND ts<=(date_value+2073600) AND name_param='Генератор МКА01')
UNION
-- день 25
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+2073600) AND ts<=(date_value+2160000) AND name_param='Генератор МКА01')
UNION
-- день 26
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+2160000) AND ts<=(date_value+2246400) AND name_param='Генератор МКА01')
UNION
-- день 27
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+2246400) AND ts<=(date_value+2332800) AND name_param='Генератор МКА01')
UNION
-- день 28
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+2332800) AND ts<=(date_value+2419200) AND name_param='Генератор МКА01')
UNION
-- день 29
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+2419200) AND ts<=(date_value+2505600) AND name_param='Генератор МКА01')
UNION
-- день 30
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+2505600) AND ts<=(date_value+2592000) AND name_param='Генератор МКА01')
UNION
-- день 31
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m1, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m1
FROM Statement_main AS a WHERE ts>=(date_value+2592000) AND ts<=(date_value+2678400) AND name_param='Генератор МКА01')
)AS a

LEFT OUTER JOIN
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=date_value AND ts<=(date_value+86400) AND name_param='Генератор МКА02'
-- выбираем отдельно столбцы по активной и реактивной энергии для каждого агрегата
UNION
-- день 2
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+86400) AND ts<=(date_value+172800) AND name_param='Генератор МКА02')
UNION
-- день 3
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+172800) AND ts<=(date_value+259200) AND name_param='Генератор МКА02')
UNION
-- день 4
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+259200) AND ts<=(date_value+345600) AND name_param='Генератор МКА02')
UNION
-- день 5
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+345600) AND ts<=(date_value+432000) AND name_param='Генератор МКА02')
UNION
-- день 6
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+432000) AND ts<=(date_value+518400) AND name_param='Генератор МКА02')
UNION
-- день 7
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+518400) AND ts<=(date_value+604800) AND name_param='Генератор МКА02')
UNION
-- день 8
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+604800) AND ts<=(date_value+691200) AND name_param='Генератор МКА02')
UNION
-- день 9
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+691200) AND ts<=(date_value+777600) AND name_param='Генератор МКА02')
UNION
-- день 10
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+777600) AND ts<=(date_value+864000) AND name_param='Генератор МКА02')
UNION
-- день 11
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+864000) AND ts<=(date_value+950400) AND name_param='Генератор МКА02')
UNION
-- день 12
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+950400) AND ts<=(date_value+1036800) AND name_param='Генератор МКА02')
UNION
-- день 13
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1036800) AND ts<=(date_value+1123200) AND name_param='Генератор МКА02')
UNION
-- день 14
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1123200) AND ts<=(date_value+1209600) AND name_param='Генератор МКА02')
UNION
-- день 15
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1209600) AND ts<=(date_value+1296000) AND name_param='Генератор МКА02')
UNION
-- день 16
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1296000) AND ts<=(date_value+1382400) AND name_param='Генератор МКА02')
UNION
-- день 17
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1382400) AND ts<=(date_value+1468800) AND name_param='Генератор МКА02')
UNION
-- день 18
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1468800) AND ts<=(date_value+1555200) AND name_param='Генератор МКА02')
UNION
-- день 19
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1555200) AND ts<=(date_value+1641600) AND name_param='Генератор МКА02')
UNION
-- день 20
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1641600) AND ts<=(date_value+1728000) AND name_param='Генератор МКА02')
UNION
-- день 21
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1728000) AND ts<=(date_value+1814400) AND name_param='Генератор МКА02')
UNION
-- день 22
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1814400) AND ts<=(date_value+1900800) AND name_param='Генератор МКА02')
UNION
-- день 23
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1900800) AND ts<=(date_value+1987200) AND name_param='Генератор МКА02')
UNION
-- день 24
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+1987200) AND ts<=(date_value+2073600) AND name_param='Генератор МКА02')
UNION
-- день 25
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+2073600) AND ts<=(date_value+2160000) AND name_param='Генератор МКА02')
UNION
-- день 26
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+2160000) AND ts<=(date_value+2246400) AND name_param='Генератор МКА02')
UNION
-- день 27
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+2246400) AND ts<=(date_value+2332800) AND name_param='Генератор МКА02')
UNION
-- день 28
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+2332800) AND ts<=(date_value+2419200) AND name_param='Генератор МКА02')
UNION
-- день 29
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+2419200) AND ts<=(date_value+2505600) AND name_param='Генератор МКА02')
UNION
-- день 30
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+2505600) AND ts<=(date_value+2592000) AND name_param='Генератор МКА02')
UNION
-- день 31
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m2, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m2
FROM Statement_main AS a WHERE ts>=(date_value+2592000) AND ts<=(date_value+2678400) AND name_param='Генератор МКА02')
) AS b ON a.ts=b.ts)
LEFT OUTER JOIN
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=date_value AND ts<=(date_value+86400) AND name_param='Генератор МКА03'
-- выбираем отдельно столбцы по активной и реактивной энергии для каждого агрегата
UNION
-- день 2
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+86400) AND ts<=(date_value+172800) AND name_param='Генератор МКА03')
UNION
-- день 3
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+172800) AND ts<=(date_value+259200) AND name_param='Генератор МКА03')
UNION
-- день 4
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+259200) AND ts<=(date_value+345600) AND name_param='Генератор МКА03')
UNION
-- день 5
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+345600) AND ts<=(date_value+432000) AND name_param='Генератор МКА03')
UNION
-- день 6
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+432000) AND ts<=(date_value+518400) AND name_param='Генератор МКА03')
UNION
-- день 7
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+518400) AND ts<=(date_value+604800) AND name_param='Генератор МКА03')
UNION
-- день 8
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+604800) AND ts<=(date_value+691200) AND name_param='Генератор МКА03')
UNION
-- день 9
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+691200) AND ts<=(date_value+777600) AND name_param='Генератор МКА03')
UNION
-- день 10
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+777600) AND ts<=(date_value+864000) AND name_param='Генератор МКА03')
UNION
-- день 11
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+864000) AND ts<=(date_value+950400) AND name_param='Генератор МКА03')
UNION
-- день 12
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+950400) AND ts<=(date_value+1036800) AND name_param='Генератор МКА03')
UNION
-- день 13
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1036800) AND ts<=(date_value+1123200) AND name_param='Генератор МКА03')
UNION
-- день 14
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1123200) AND ts<=(date_value+1209600) AND name_param='Генератор МКА03')
UNION
-- день 15
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1209600) AND ts<=(date_value+1296000) AND name_param='Генератор МКА03')
UNION
-- день 16
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1296000) AND ts<=(date_value+1382400) AND name_param='Генератор МКА03')
UNION
-- день 17
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1382400) AND ts<=(date_value+1468800) AND name_param='Генератор МКА03')
UNION
-- день 18
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1468800) AND ts<=(date_value+1555200) AND name_param='Генератор МКА03')
UNION
-- день 19
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1555200) AND ts<=(date_value+1641600) AND name_param='Генератор МКА03')
UNION
-- день 20
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1641600) AND ts<=(date_value+1728000) AND name_param='Генератор МКА03')
UNION
-- день 21
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1728000) AND ts<=(date_value+1814400) AND name_param='Генератор МКА03')
UNION
-- день 22
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1814400) AND ts<=(date_value+1900800) AND name_param='Генератор МКА03')
UNION
-- день 23
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1900800) AND ts<=(date_value+1987200) AND name_param='Генератор МКА03')
UNION
-- день 24
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+1987200) AND ts<=(date_value+2073600) AND name_param='Генератор МКА03')
UNION
-- день 25
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+2073600) AND ts<=(date_value+2160000) AND name_param='Генератор МКА03')
UNION
-- день 26
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+2160000) AND ts<=(date_value+2246400) AND name_param='Генератор МКА03')
UNION
-- день 27
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+2246400) AND ts<=(date_value+2332800) AND name_param='Генератор МКА03')
UNION
-- день 28
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+2332800) AND ts<=(date_value+2419200) AND name_param='Генератор МКА03')
UNION
-- день 29
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+2419200) AND ts<=(date_value+2505600) AND name_param='Генератор МКА03')
UNION
-- день 30
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+2505600) AND ts<=(date_value+2592000) AND name_param='Генератор МКА03')
UNION
-- день 31
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m3
FROM Statement_main AS a WHERE ts>=(date_value+2592000) AND ts<=(date_value+2678400) AND name_param='Генератор МКА03')
) AS c ON a.ts=c.ts
LEFT OUTER JOIN
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=date_value AND ts<=(date_value+86400) AND name_param='Генератор МКА04'
-- выбираем отдельно столбцы по активной и реактивной энергии для каждого агрегата
UNION
-- день 2
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+86400) AND ts<=(date_value+172800) AND name_param='Генератор МКА04')
UNION
-- день 3
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+172800) AND ts<=(date_value+259200) AND name_param='Генератор МКА04')
UNION
-- день 4
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+259200) AND ts<=(date_value+345600) AND name_param='Генератор МКА04')
UNION
-- день 5
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+345600) AND ts<=(date_value+432000) AND name_param='Генератор МКА04')
UNION
-- день 6
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+432000) AND ts<=(date_value+518400) AND name_param='Генератор МКА04')
UNION
-- день 7
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+518400) AND ts<=(date_value+604800) AND name_param='Генератор МКА04')
UNION
-- день 8
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+604800) AND ts<=(date_value+691200) AND name_param='Генератор МКА04')
UNION
-- день 9
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+691200) AND ts<=(date_value+777600) AND name_param='Генератор МКА04')
UNION
-- день 10
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+777600) AND ts<=(date_value+864000) AND name_param='Генератор МКА04')
UNION
-- день 11
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+864000) AND ts<=(date_value+950400) AND name_param='Генератор МКА04')
UNION
-- день 12
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+950400) AND ts<=(date_value+1036800) AND name_param='Генератор МКА04')
UNION
-- день 13
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m3, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1036800) AND ts<=(date_value+1123200) AND name_param='Генератор МКА04')
UNION
-- день 14
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1123200) AND ts<=(date_value+1209600) AND name_param='Генератор МКА04')
UNION
-- день 15
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1209600) AND ts<=(date_value+1296000) AND name_param='Генератор МКА04')
UNION
-- день 16
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1296000) AND ts<=(date_value+1382400) AND name_param='Генератор МКА04')
UNION
-- день 17
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1382400) AND ts<=(date_value+1468800) AND name_param='Генератор МКА04')
UNION
-- день 18
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1468800) AND ts<=(date_value+1555200) AND name_param='Генератор МКА04')
UNION
-- день 19
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1555200) AND ts<=(date_value+1641600) AND name_param='Генератор МКА04')
UNION
-- день 20
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1641600) AND ts<=(date_value+1728000) AND name_param='Генератор МКА04')
UNION
-- день 21
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1728000) AND ts<=(date_value+1814400) AND name_param='Генератор МКА04')
UNION
-- день 22
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1814400) AND ts<=(date_value+1900800) AND name_param='Генератор МКА04')
UNION
-- день 23
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1900800) AND ts<=(date_value+1987200) AND name_param='Генератор МКА04')
UNION
-- день 24
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+1987200) AND ts<=(date_value+2073600) AND name_param='Генератор МКА04')
UNION
-- день 25
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+2073600) AND ts<=(date_value+2160000) AND name_param='Генератор МКА04')
UNION
-- день 26
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+2160000) AND ts<=(date_value+2246400) AND name_param='Генератор МКА04')
UNION
-- день 27
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+2246400) AND ts<=(date_value+2332800) AND name_param='Генератор МКА04')
UNION
-- день 28
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+2332800) AND ts<=(date_value+2419200) AND name_param='Генератор МКА04')
UNION
-- день 29
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+2419200) AND ts<=(date_value+2505600) AND name_param='Генератор МКА04')
UNION
-- день 30
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+2505600) AND ts<=(date_value+2592000) AND name_param='Генератор МКА04')
UNION
-- день 31
(SELECT a.ts,MAX(a.active_energy)-MIN(a.active_energy) AS aen_m4, MAX(a.reactive_energy)-MIN(a.reactive_energy) AS raen_m4
FROM Statement_main AS a WHERE ts>=(date_value+2592000) AND ts<=(date_value+2678400) AND name_param='Генератор МКА04')
) AS d ON a.ts=d.ts
;
SELECT * FROM `v_statement`;
END

было

стало

Как видно, по факту генераторов аж 10, запрос просто гигантерский ,и хз как все это дело упростить. Помогите!!!

artas,

На сколько смог разобраться в коде
Тут сделано (в маем случае) для одного генератора допустим (i=3600...и т.д.) на 24 часа . И если делать для 10 генераторов то нужно все равно это както объединять и количество запросов возрастет.
Хотелось бы всетаки запихнуть все в процедуру и передовать только дату а возвращать целую таблицу (которую я потом через php в таблицу на web страничку вывожу.) В идеале сократить время генерации этой таблицы сейчас она около 7 секунд. Ваш метод я попробую одоптровать может будет занимать меньше времени :)

artas,

вот что получилось согласно вашему примеру

PROCEDURE BUR_test.mypro(IN date_value int(12))
BEGIN

DROP TABLE IF EXISTS `v_statement`;
CREATE TEMPORARY TABLE `v_statement`
AS
SELECT DISTINCT
d.name_param ,
d.ha1, d.hr1, d.ha2,d.hr2, d.ha3, d.hr3, d.ha4, d.hr4, d.ha5, d.hr5, d.ha6, d.hr6, d.ha7, d.hr7, d.ha8, d.hr8, d.ha9, d.hr9, d.ha10, d.hr10, d.ha11,d.hr11, d.ha12,d.hr12,
d.ha13, d.hr13, d.ha14, d.hr14, d.ha15,d.hr15, d.ha16, d.hr16,d.hr17,d.ha17, d.hr18, d.ha18, d.hr19, d.ha19, d.ha20, d.hr20, d.ha21, d.hr21, d.ha22, d.hr22, d.ha23, d.hr23, d.ha24, d.hr24
-- c.aen_m3,
-- c.raen_m3

FROM
-- определяем отрезок времени
-- час 1

(SELECT tt1.name_param,MAX((CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=1 THEN tt2.active_energy-tt1.active_energy ELSE 0 END)) AS ha1,MAX((CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=1 THEN tt2.reactive_energy-tt1.reactive_energy ELSE 0 END)) AS hr1,
MAX( (CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=2 THEN tt2.active_energy-tt1.active_energy ELSE 0 END)) AS ha2,MAX((CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=2 THEN tt2.reactive_energy-tt1.reactive_energy ELSE 0 END)) AS hr2,
................
MAX( (CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=23 THEN tt2.active_energy-tt1.active_energy ELSE 0 END)) AS ha23,MAX((CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=23 THEN tt2.reactive_energy-tt1.reactive_energy ELSE 0 END)) AS hr23,
MAX( (CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=24 THEN tt2.active_energy-tt1.active_energy ELSE 0 END)) AS ha24,MAX((CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=24 THEN tt2.reactive_energy-tt1.reactive_energy ELSE 0 END)) AS hr24
FROM Statement_main AS tt1 JOIN Statement_main AS tt2 ON tt1.name_param=tt2.name_param AND tt2.ts-tt1.ts=3600 WHERE tt2.ts>=date_value AND tt2.ts<=(date_value+86400) GROUP BY tt1.name_param ASC ) AS d ;
SELECT * FROM `v_statement` ;
END

artas,

Но вот незадача в конец на 24 часа записываются нули
а на час записываются данные уже со второго часа
как сдвинуть все это дело на час ?

А разобрался. Нужно вместо первого часа брать нулевой MAX((CASE WHEN EXTRACT(HOUR FROM FROM_UNIXTIME(tt1.ts))=0 THEN tt2.active_energy-tt1.active_energy ELSE 0 END)) AS ha1...

Теперь вопрос можно ли как то это дела привести к такому виду? и если да то как?

artas,

охрошо, я подумаю если будет время как это реализовать...
а вот такой вопрос допустим связь с сервером оборвалась и какоето время в базу ничего не пишется
и к примеру на 1час показания были 22 Ват а на 5 часов 122 Ват. как сделать так чтобы sql посчитал 122-22=100
разделил на количество пропущеных часов /5=20 и заполнил пропуски цифрой 20?

Время выполнения построения таблицы как в 3-ем посте и более поздний вариант сделаный по подсказке artas
для одного дня улучшился почти в два раза и составил 3,6 сек.
но вод для месяца наоборот многократно ухудшился, так для первого метода требуется 8 сек, а для второго 92 сек. это печально