Filip Kraus
Reputation: 2784
Speed up repeating MySQL query
What is the best way to improve, simplify and speed up these parts of my sql?
-- do pivoting
drop temporary table if exists inverted;
create temporary table inverted as
select cast(s.name as char(30)) as name,
s.ename,
max(if(s.ts='00:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '0:30_it1',
max(if(s.ts='00:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '0:30_it2',
'#ffffff' as '0:30_color',
max(if(s.ts='01:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '1:30_it1',
max(if(s.ts='01:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '1:30_it2',
'#ffffff' as '1:30_color',
max(if(s.ts='02:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '2:30_it1',
max(if(s.ts='02:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '2:30_it2',
'#ffffff' as '2:30_color',
max(if(s.ts='03:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '3:30_it1',
max(if(s.ts='03:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '3:30_it2',
'#ffffff' as '3:30_color',
max(if(s.ts='04:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '4:30_it1',
max(if(s.ts='04:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '4:30_it2',
'#ffffff' as '4:30_color',
max(if(s.ts='05:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '5:30_it1',
max(if(s.ts='05:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '5:30_it2',
'#ffffff' as '5:30_color',
max(if(s.ts='06:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '6:30_it1',
max(if(s.ts='06:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '6:30_it2',
'#ffffff' as '6:30_color',
max(if(s.ts='07:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '7:30_it1',
max(if(s.ts='07:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '7:30_it2',
'#ffffff' as '7:30_color',
max(if(s.ts='08:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '8:30_it1',
max(if(s.ts='08:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '8:30_it2',
'#ffffff' as '8:30_color',
max(if(s.ts='09:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '9:30_it1',
max(if(s.ts='09:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '9:30_it2',
'#ffffff' as '9:30_color',
max(if(s.ts='10:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '10:30_it1',
max(if(s.ts='10:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '10:30_it2',
'#ffffff' as '10:30_color',
max(if(s.ts='11:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '11:30_it1',
max(if(s.ts='11:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '11:30_it2',
'#ffffff' as '11:30_color',
max(if(s.ts='12:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '12:30_it1',
max(if(s.ts='12:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '12:30_it2',
'#ffffff' as '12:30_color',
max(if(s.ts='13:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '13:30_it1',
max(if(s.ts='13:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '13:30_it2',
'#ffffff' as '13:30_color',
max(if(s.ts='14:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '14:30_it1',
max(if(s.ts='14:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '14:30_it2',
'#ffffff' as '14:30_color',
max(if(s.ts='15:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '15:30_it1',
max(if(s.ts='15:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '15:30_it2',
'#ffffff' as '15:30_color',
max(if(s.ts='16:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '16:30_it1',
max(if(s.ts='16:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '16:30_it2',
'#ffffff' as '16:30_color',
max(if(s.ts='17:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '17:30_it1',
max(if(s.ts='17:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '17:30_it2',
'#ffffff' as '17:30_color',
max(if(s.ts='18:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '18:30_it1',
max(if(s.ts='18:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '18:30_it2',
'#ffffff' as '18:30_color',
max(if(s.ts='19:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '19:30_it1',
max(if(s.ts='19:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '19:30_it2',
'#ffffff' as '19:30_color',
max(if(s.ts='20:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '20:30_it1',
max(if(s.ts='20:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '20:30_it2',
'#ffffff' as '20:30_color',
max(if(s.ts='21:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '21:30_it1',
max(if(s.ts='21:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '21:30_it2',
'#ffffff' as '21:30_color',
max(if(s.ts='22:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '22:30_it1',
max(if(s.ts='22:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '22:30_it2',
'#ffffff' as '22:30_color',
max(if(s.ts='23:30:00', cast(s.value_t1 as decimal(9,2)), null)) as '23:30_it1',
max(if(s.ts='23:30:00', cast(s.value_t2 as decimal(9,2)), null)) as '23:30_it2',
'#ffffff' as '23:30_color',
cast(null as time) as hour,
cast(null as decimal(9,2)) as value,
cast(null as char(17)) as series,
table_chart
from splitted_table s
group by s.table_chart, s.name, s.ename;
UPDATE inverted SET `0:30_color` = fn_rep_get_color_for_field(`0:30_it1`, `0:30_it2`, p_min, p_max), `1:30_color` = fn_rep_get_color_for_field(`1:30_it1`, `1:30_it2`, p_min, p_max),
`2:30_color` = fn_rep_get_color_for_field(`2:30_it1`, `2:30_it2`, p_min, p_max), `3:30_color` = fn_rep_get_color_for_field(`3:30_it1`, `3:30_it2`, p_min, p_max),
`4:30_color` = fn_rep_get_color_for_field(`4:30_it1`, `4:30_it2`, p_min, p_max), `5:30_color` = fn_rep_get_color_for_field(`5:30_it1`, `5:30_it2`, p_min, p_max),
`6:30_color` = fn_rep_get_color_for_field(`6:30_it1`, `6:30_it2`, p_min, p_max), `7:30_color` = fn_rep_get_color_for_field(`7:30_it1`, `7:30_it2`, p_min, p_max),
`8:30_color` = fn_rep_get_color_for_field(`8:30_it1`, `8:30_it2`, p_min, p_max), `9:30_color` = fn_rep_get_color_for_field(`9:30_it1`, `9:30_it2`, p_min, p_max),
`10:30_color` = fn_rep_get_color_for_field(`10:30_it1`, `10:30_it2`, p_min, p_max), `11:30_color` = fn_rep_get_color_for_field(`11:30_it1`, `11:30_it2`, p_min, p_max),
`12:30_color` = fn_rep_get_color_for_field(`12:30_it1`, `12:30_it2`, p_min, p_max), `13:30_color` = fn_rep_get_color_for_field(`13:30_it1`, `13:30_it2`, p_min, p_max),
`14:30_color` = fn_rep_get_color_for_field(`14:30_it1`, `14:30_it2`, p_min, p_max), `15:30_color` = fn_rep_get_color_for_field(`15:30_it1`, `15:30_it2`, p_min, p_max),
`16:30_color` = fn_rep_get_color_for_field(`16:30_it1`, `16:30_it2`, p_min, p_max), `17:30_color` = fn_rep_get_color_for_field(`17:30_it1`, `17:30_it2`, p_min, p_max),
`18:30_color` = fn_rep_get_color_for_field(`18:30_it1`, `18:30_it2`, p_min, p_max), `19:30_color` = fn_rep_get_color_for_field(`19:30_it1`, `19:30_it2`, p_min, p_max),
`20:30_color` = fn_rep_get_color_for_field(`20:30_it1`, `20:30_it2`, p_min, p_max), `21:30_color` = fn_rep_get_color_for_field(`21:30_it1`, `21:30_it2`, p_min, p_max),
`22:30_color` = fn_rep_get_color_for_field(`22:30_it1`, `22:30_it2`, p_min, p_max), `23:30_color` = fn_rep_get_color_for_field(`23:30_it1`, `23:30_it2`, p_min, p_max)
where SUBSTRING(table_chart, 2, 1) = 'p';
UPDATE inverted SET `0:30_color` = fn_rep_get_color_for_field(`0:30_it1`, `0:30_it2`, q_min, q_max), `1:30_color` = fn_rep_get_color_for_field(`1:30_it1`, `1:30_it2`, q_min, q_max),
`2:30_color` = fn_rep_get_color_for_field(`2:30_it1`, `2:30_it2`, q_min, q_max), `3:30_color` = fn_rep_get_color_for_field(`3:30_it1`, `3:30_it2`, q_min, q_max),
`4:30_color` = fn_rep_get_color_for_field(`4:30_it1`, `4:30_it2`, q_min, q_max), `5:30_color` = fn_rep_get_color_for_field(`5:30_it1`, `5:30_it2`, q_min, q_max),
`6:30_color` = fn_rep_get_color_for_field(`6:30_it1`, `6:30_it2`, q_min, q_max), `7:30_color` = fn_rep_get_color_for_field(`7:30_it1`, `7:30_it2`, q_min, q_max),
`8:30_color` = fn_rep_get_color_for_field(`8:30_it1`, `8:30_it2`, q_min, q_max), `9:30_color` = fn_rep_get_color_for_field(`9:30_it1`, `9:30_it2`, q_min, q_max),
`10:30_color` = fn_rep_get_color_for_field(`10:30_it1`, `10:30_it2`, q_min, q_max), `11:30_color` = fn_rep_get_color_for_field(`11:30_it1`, `11:30_it2`, q_min, q_max),
`12:30_color` = fn_rep_get_color_for_field(`12:30_it1`, `12:30_it2`, q_min, q_max), `13:30_color` = fn_rep_get_color_for_field(`13:30_it1`, `13:30_it2`, q_min, q_max),
`14:30_color` = fn_rep_get_color_for_field(`14:30_it1`, `14:30_it2`, q_min, q_max), `15:30_color` = fn_rep_get_color_for_field(`15:30_it1`, `15:30_it2`, q_min, q_max),
`16:30_color` = fn_rep_get_color_for_field(`16:30_it1`, `16:30_it2`, q_min, q_max), `17:30_color` = fn_rep_get_color_for_field(`17:30_it1`, `17:30_it2`, q_min, q_max),
`18:30_color` = fn_rep_get_color_for_field(`18:30_it1`, `18:30_it2`, q_min, q_max), `19:30_color` = fn_rep_get_color_for_field(`19:30_it1`, `19:30_it2`, q_min, q_max),
`20:30_color` = fn_rep_get_color_for_field(`20:30_it1`, `20:30_it2`, q_min, q_max), `21:30_color` = fn_rep_get_color_for_field(`21:30_it1`, `21:30_it2`, q_min, q_max),
`22:30_color` = fn_rep_get_color_for_field(`22:30_it1`, `22:30_it2`, q_min, q_max), `23:30_color` = fn_rep_get_color_for_field(`23:30_it1`, `23:30_it2`, q_min, q_max)
where SUBSTRING(table_chart, 2, 1) = 'q';
UPDATE inverted SET `0:30_color` = fn_rep_get_color_for_field(`0:30_it1`, `0:30_it2`, u_min, u_max), `1:30_color` = fn_rep_get_color_for_field(`1:30_it1`, `1:30_it2`, u_min, u_max),
`2:30_color` = fn_rep_get_color_for_field(`2:30_it1`, `2:30_it2`, u_min, u_max), `3:30_color` = fn_rep_get_color_for_field(`3:30_it1`, `3:30_it2`, u_min, u_max),
`4:30_color` = fn_rep_get_color_for_field(`4:30_it1`, `4:30_it2`, u_min, u_max), `5:30_color` = fn_rep_get_color_for_field(`5:30_it1`, `5:30_it2`, u_min, u_max),
`6:30_color` = fn_rep_get_color_for_field(`6:30_it1`, `6:30_it2`, u_min, u_max), `7:30_color` = fn_rep_get_color_for_field(`7:30_it1`, `7:30_it2`, u_min, u_max),
`8:30_color` = fn_rep_get_color_for_field(`8:30_it1`, `8:30_it2`, u_min, u_max), `9:30_color` = fn_rep_get_color_for_field(`9:30_it1`, `9:30_it2`, u_min, u_max),
`10:30_color` = fn_rep_get_color_for_field(`10:30_it1`, `10:30_it2`, u_min, u_max), `11:30_color` = fn_rep_get_color_for_field(`11:30_it1`, `11:30_it2`, u_min, u_max),
`12:30_color` = fn_rep_get_color_for_field(`12:30_it1`, `12:30_it2`, u_min, u_max), `13:30_color` = fn_rep_get_color_for_field(`13:30_it1`, `13:30_it2`, u_min, u_max),
`14:30_color` = fn_rep_get_color_for_field(`14:30_it1`, `14:30_it2`, u_min, u_max), `15:30_color` = fn_rep_get_color_for_field(`15:30_it1`, `15:30_it2`, u_min, u_max),
`16:30_color` = fn_rep_get_color_for_field(`16:30_it1`, `16:30_it2`, u_min, u_max), `17:30_color` = fn_rep_get_color_for_field(`17:30_it1`, `17:30_it2`, u_min, u_max),
`18:30_color` = fn_rep_get_color_for_field(`18:30_it1`, `18:30_it2`, u_min, u_max), `19:30_color` = fn_rep_get_color_for_field(`19:30_it1`, `19:30_it2`, u_min, u_max),
`20:30_color` = fn_rep_get_color_for_field(`20:30_it1`, `20:30_it2`, u_min, u_max), `21:30_color` = fn_rep_get_color_for_field(`21:30_it1`, `21:30_it2`, u_min, u_max),
`22:30_color` = fn_rep_get_color_for_field(`22:30_it1`, `22:30_it2`, u_min, u_max), `23:30_color` = fn_rep_get_color_for_field(`23:30_it1`, `23:30_it2`, u_min, u_max)
where SUBSTRING(table_chart, 2, 1) = 'u';
DROP TEMPORARY TABLE IF EXISTS deviation;
CREATE TEMPORARY TABLE deviation LIKE inverted;
insert into export_data.deviation (`name`,`0:30_it1`,`1:30_it1`,`2:30_it1`,`3:30_it1`,`4:30_it1`,`5:30_it1`,`6:30_it1`,`7:30_it1`,`8:30_it1`,`9:30_it1`,`10:30_it1`,`11:30_it1`,`12:30_it1`,`13:30_it1`,`14:30_it1`,`15:30_it1`,`16:30_it1`,`17:30_it1`,`18:30_it1`,`19:30_it1`,`20:30_it1`,`21:30_it1`,`22:30_it1`,`23:30_it1`,
`0:30_it2`,`1:30_it2`,`2:30_it2`,`3:30_it2`,`4:30_it2`,`5:30_it2`,`6:30_it2`,`7:30_it2`,`8:30_it2`,`9:30_it2`,`10:30_it2`,`11:30_it2`,`12:30_it2`,`13:30_it2`,`14:30_it2`,`15:30_it2`,`16:30_it2`,`17:30_it2`,`18:30_it2`,`19:30_it2`,`20:30_it2`,`21:30_it2`,`22:30_it2`,`23:30_it2`,`table_chart`)
SELECT
'table footer' as name,
SUM(abs(`0:30_it1`-`0:30_it2`)) as `0:30_it1`,
SUM(abs(`1:30_it1`-`1:30_it2`)) as `1:30_it1`,
SUM(abs(`2:30_it1`-`2:30_it2`)) as `2:30_it1`,
SUM(abs(`3:30_it1`-`3:30_it2`)) as `3:30_it1`,
SUM(abs(`4:30_it1`-`4:30_it2`)) as `4:30_it1`,
SUM(abs(`5:30_it1`-`5:30_it2`)) as `5:30_it1`,
SUM(abs(`6:30_it1`-`6:30_it2`)) as `6:30_it1`,
SUM(abs(`7:30_it1`-`7:30_it2`)) as `7:30_it1`,
SUM(abs(`8:30_it1`-`8:30_it2`)) as `8:30_it1`,
SUM(abs(`9:30_it1`-`9:30_it2`)) as `9:30_it1`,
SUM(abs(`10:30_it1`-`10:30_it2`)) as `10:30_it1`,
SUM(abs(`11:30_it1`-`11:30_it2`)) as `11:30_it1`,
SUM(abs(`12:30_it1`-`12:30_it2`)) as `12:30_it1`,
SUM(abs(`13:30_it1`-`13:30_it2`)) as `13:30_it1`,
SUM(abs(`14:30_it1`-`14:30_it2`)) as `14:30_it1`,
SUM(abs(`15:30_it1`-`15:30_it2`)) as `15:30_it1`,
SUM(abs(`16:30_it1`-`16:30_it2`)) as `16:30_it1`,
SUM(abs(`17:30_it1`-`17:30_it2`)) as `17:30_it1`,
SUM(abs(`18:30_it1`-`18:30_it2`)) as `18:30_it1`,
SUM(abs(`19:30_it1`-`19:30_it2`)) as `19:30_it1`,
SUM(abs(`20:30_it1`-`20:30_it2`)) as `20:30_it1`,
SUM(abs(`21:30_it1`-`21:30_it2`)) as `21:30_it1`,
SUM(abs(`22:30_it1`-`22:30_it2`)) as `22:30_it1`,
SUM(abs(`23:30_it1`-`23:30_it2`)) as `23:30_it1`,
MAX(abs(`0:30_it1`-`0:30_it2`)) as `0:30_it2`,
MAX(abs(`1:30_it1`-`1:30_it2`)) as `1:30_it2`,
MAX(abs(`2:30_it1`-`2:30_it2`)) as `2:30_it2`,
MAX(abs(`3:30_it1`-`3:30_it2`)) as `3:30_it2`,
MAX(abs(`4:30_it1`-`4:30_it2`)) as `4:30_it2`,
MAX(abs(`5:30_it1`-`5:30_it2`)) as `5:30_it2`,
MAX(abs(`6:30_it1`-`6:30_it2`)) as `6:30_it2`,
MAX(abs(`7:30_it1`-`7:30_it2`)) as `7:30_it2`,
MAX(abs(`8:30_it1`-`8:30_it2`)) as `8:30_it2`,
MAX(abs(`9:30_it1`-`9:30_it2`)) as `9:30_it2`,
MAX(abs(`10:30_it1`-`10:30_it2`)) as `10:30_it2`,
MAX(abs(`11:30_it1`-`11:30_it2`)) as `11:30_it2`,
MAX(abs(`12:30_it1`-`12:30_it2`)) as `12:30_it2`,
MAX(abs(`13:30_it1`-`13:30_it2`)) as `13:30_it2`,
MAX(abs(`14:30_it1`-`14:30_it2`)) as `14:30_it2`,
MAX(abs(`15:30_it1`-`15:30_it2`)) as `15:30_it2`,
MAX(abs(`16:30_it1`-`16:30_it2`)) as `16:30_it2`,
MAX(abs(`17:30_it1`-`17:30_it2`)) as `17:30_it2`,
MAX(abs(`18:30_it1`-`18:30_it2`)) as `18:30_it2`,
MAX(abs(`19:30_it1`-`19:30_it2`)) as `19:30_it2`,
MAX(abs(`20:30_it1`-`20:30_it2`)) as `20:30_it2`,
MAX(abs(`21:30_it1`-`21:30_it2`)) as `21:30_it2`,
MAX(abs(`22:30_it1`-`22:30_it2`)) as `22:30_it2`,
MAX(abs(`23:30_it1`-`23:30_it2`)) as `23:30_it2`,
CONCAT('s',SUBSTRING(table_chart,2)) as table_chart
from export_data.inverted
group by table_chart;
Currently it takes too much time and I think there might be some easier way than to repeat the code again and again, but I am not sure how.
Upvotes: 0
Views: 56
Answers (1)
DRapp
Reputation: 48169
Very strange query, but looks like you are doing a pivot-table style query for your T1 and T2 values based on half-hour increments. I would hold off on pivot until after unique rows pre-aggregated. Since your "ts" column appears to represent a "Time-Slot" and there is no variation on the half-hour increment, I would start with your first query being simplified down to.
drop temporary table if exists inverted;
create temporary table inverted as
select
s.table_chart,
cast(s.name as char(30)) as name,
s.ename,
s.ts,
MAX( CAST( s.value_t1 as decimal(9,2)) as t1Val,
MAX( CAST( s.value_t2 as decimal(9,2)) as t2Val,
'#ffffff' as tsColor
from
splitted_table s
group by
s.table_chart,
s.name,
s.ename,
s.ts
So now, you may have 24 time-slot rows per chart, name, ename, but already pre-aggregated in their own rows. Your next UPDATE can be simplified against the generic rows. I removed your (hour, value, series) columns as they are not used until the final insert, so I tacked them on there instead.
UPDATE inverted
SET tsColor = fn_rep_get_color_for_field( t1Val, t2Val, p_min, p_max)
where SUBSTRING(table_chart, 2, 1) = 'p';
UPDATE inverted
SET tsColor = fn_rep_get_color_for_field(t1Val, t2Val, q_min, q_max)
where SUBSTRING(table_chart, 2, 1) = 'q';
UPDATE inverted
SET tsColor = fn_rep_get_color_for_field( t1Val, t2Val, u_min, u_max)
where SUBSTRING(table_chart, 2, 1) = 'u';
NOW, you can pivot. Since your first query was a group by table chart and ename, getting the MAX() value per respective T1 or T2 value, there would only be one record per time slot / table chart / eName.
DROP TEMPORARY TABLE IF EXISTS deviation;
CREATE TEMPORARY TABLE deviation as
select
'table footer' as name,
SUM( if( i.ts='00:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '0:30_it1',
SUM( if( i.ts='01:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '1:30_it1',
SUM( if( i.ts='02:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '2:30_it1',
SUM( if( i.ts='03:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '3:30_it1',
SUM( if( i.ts='04:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '4:30_it1',
SUM( if( i.ts='05:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '5:30_it1',
SUM( if( i.ts='06:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '6:30_it1',
SUM( if( i.ts='07:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '7:30_it1',
SUM( if( i.ts='08:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '8:30_it1',
SUM( if( i.ts='09:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '9:30_it1',
SUM( if( i.ts='10:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '10:30_it1',
SUM( if( i.ts='11:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '11:30_it1',
SUM( if( i.ts='12:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '12:30_it1',
SUM( if( i.ts='13:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '13:30_it1',
SUM( if( i.ts='14:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '14:30_it1',
SUM( if( i.ts='15:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '15:30_it1',
SUM( if( i.ts='16:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '16:30_it1',
SUM( if( i.ts='17:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '17:30_it1',
SUM( if( i.ts='18:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '18:30_it1',
SUM( if( i.ts='19:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '19:30_it1',
SUM( if( i.ts='20:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '20:30_it1',
SUM( if( i.ts='21:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '21:30_it1',
SUM( if( i.ts='22:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '22:30_it1',
SUM( if( i.ts='23:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '23:30_it1',
MAX( if( i.ts='00:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '0:30_it2',
MAX( if( i.ts='01:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '1:30_it2',
MAX( if( i.ts='02:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '2:30_it2',
MAX( if( i.ts='03:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '3:30_it2',
MAX( if( i.ts='04:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '4:30_it2',
MAX( if( i.ts='05:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '5:30_it2',
MAX( if( i.ts='06:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '6:30_it2',
MAX( if( i.ts='07:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '7:30_it2',
MAX( if( i.ts='08:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '8:30_it2',
MAX( if( i.ts='09:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '9:30_it2',
MAX( if( i.ts='10:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '10:30_it2',
MAX( if( i.ts='11:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '11:30_it2',
MAX( if( i.ts='12:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '12:30_it2',
MAX( if( i.ts='13:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '13:30_it2',
MAX( if( i.ts='14:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '14:30_it2',
MAX( if( i.ts='15:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '15:30_it2',
MAX( if( i.ts='16:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '16:30_it2',
MAX( if( i.ts='17:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '17:30_it2',
MAX( if( i.ts='18:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '18:30_it2',
MAX( if( i.ts='19:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '19:30_it2',
MAX( if( i.ts='20:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '20:30_it2',
MAX( if( i.ts='21:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '21:30_it2',
MAX( if( i.ts='22:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '22:30_it2',
MAX( if( i.ts='23:30:00', ABS( i.t1Val - i.t2Val ), 0 )) as '23:30_it2',
cast(null as time) as hour,
cast(null as decimal(9,2)) as value,
cast(null as char(17)) as series,
CONCAT('s',SUBSTRING(i.table_chart,2)) as table_chart
from
export_data.inverted
group by
table_chart;
Now, for your COLORs. Since you are never querying those, they would not even be part of you result set, and there could be different values based on the "p", "q" or "u" values, which do you want to take precidence over the other... MIN, MAX, or one specifically based on "u" wins? or maybe "p"? You would have to add IF() or case/when for that something like...
Your table structure would have the columns based on your original pivot query structure, and CREATE LIKE condition, but since you were never inserting to those columns, they would have always been blank. So to for your extra for hours, values, series columns.
MAX( if( i.ts='00:30:00',
case when SUBSTR( i.table_chart, 2, 1 ) = 'p' then i.tsColor
when SUBSTR( i.table_chart, 2, 1 ) = 'q' then i.tsColor
ELSE i.tsColor end,
null )) as '0:30:00_Color',
etc for rest of colors.
Hopefully this helps you see a possible solution from another angle.
Upvotes: 2