mysql query to join 3 queries in 1 table while averaging -


i have 3 large tables, values getting logged every minute, below extract of these tables.

i hourly averages period of 1 day of these tables , join them respect time, please note there couple of seconds gap between log time ph , temperature

table ph (extract only, table large more 130,000 values)

    id      time                ph      72176   2013-04-06 03:29:34 7.58     72177   2013-04-06 03:30:34 7.58     72178   2013-04-06 03:31:34 7.54     72179   2013-04-06 03:32:34 7.58     72180   2013-04-06 03:33:34 7.58     72181   2013-04-06 03:34:34 7.58     72182   2013-04-06 03:35:34 7.54     72183   2013-04-06 03:36:34 7.58     72184   2013-04-06 03:37:34 7.54     72185   2013-04-06 03:38:34 7.58     72186   2013-04-06 03:39:34 7.58

table temperature1 (extract only, table large more 130,000 values)

    id      time            temperature  133312  2013-04-06 03:29:36 25.37 133313  2013-04-06 03:30:36 25.37 133314  2013-04-06 03:31:36 25.37 133315  2013-04-06 03:32:36 25.31 133316  2013-04-06 03:33:36 25.31 133317  2013-04-06 03:34:36 25.31 133318  2013-04-06 03:35:36 25.37 133319  2013-04-06 03:36:36 25.31 133320  2013-04-06 03:37:36 25.31 133321  2013-04-06 03:38:36 25.31 133322  2013-04-06 03:39:36 25.37

table solids (extract only, table large more 130,000 values)

    id      time            solids  123791  2013-04-06 03:29:49 140 123792  2013-04-06 03:30:49 140 123793  2013-04-06 03:31:49 143 123794  2013-04-06 03:32:49 140 123795  2013-04-06 03:33:49 140 123796  2013-04-06 03:34:49 140 123797  2013-04-06 03:35:49 140 123798  2013-04-06 03:36:49 143 123799  2013-04-06 03:37:49 140 123800  2013-04-06 03:38:49 140 123801  2013-04-06 03:39:49 140

i getting hourly averages using query below

select date_format(x.time,'%y-%m-%d %h:00:00')      , avg(x.solids) avg_solids   solids x time >= now() - interval 1 day  group      date_format(x.time,'%y-%m-%d %h:00:00');

how can efficiently join (with respect time) results of query above each sensor (x3) displayed in 1 table

===============================

this query below gets hourly values, not sure how tweek averages per hour

select date_format(timetable.minutetime, '%y-%m-%d %k:%i') time, (ot2.temperature) temperature, (t2.temperature) temp, (s2.solids) solids, (p2.ph) ph   (     select minutetime.minutetime minutetime,     ( select max(time) outside_temperature time <= minutetime.minutetime , time >= now() - interval 1 day) otemptime,      ( select max(time) temperature1 time <= minutetime.minutetime , time >= now() - interval 1 day) temptime,      ( select max(time) ph time <= minutetime.minutetime , time >= now() - interval 1 day) phtime,       ( select max(time) solids time <= minutetime.minutetime , time >= now() - interval 1 day) solidstime            (         select date(time) + interval (hour(time) div 1 *1 ) hour minutetime         ph         time >= now() - interval 1 day , time <= now()         union select date(time) + interval (hour(time) div 1 *1) hour         solids         time >= now() - interval 1 day , time <= now()         union select date(time) + interval (hour(time) div 1 *1) hour         outside_temperature         time >= now() - interval 1 day , time <= now()         union select date(time) + interval (hour(time) div 1 *1) hour         temperature1         time >= now() - interval 1 day , time <= now()         group 1     ) minutetime ) timetable left join outside_temperature ot2 on ot2.time = timetable.otemptime left join temperature1 t2 on t2.time = timetable.temptime left join solids s2 on s2.time = timetable.solidstime left join ph p2 on p2.time = timetable.phtime   group date_format(timetable.minutetime, '%y-%m-%d %k:%i')  order minutetime asc

hour() seems useful function this, since looking @ single day. perhaps work you:

select *  (select hour(time) hour, avg(ph) avg_ph   ph    time >= now() - interval 1 day   group hour) p join   (select hour(time) hour, avg(temperature) avg_temp   temperature1    time >= now() - interval 1 day   group hour) t on t.hour = p.hour join   (select hour(time) hour, avg(solids) avg_solids   solids    time >= now() - interval 1 day   group hour) s on s.hour = p.hour;

being using inner joins, i'm making assumption there @ least 1 records in each table hour, seems reasonable assumption.


Comments

Post a Comment

Popular posts from this blog

monitor web browser programmatically in Android? -

i've got tricky question here. need users make payment bank (namely barclaycard) in uk. so, have https url , add parameters (such amount pay, order reference, etc) url, start http connection intent.actionview, redirect user browser can enter credit card details on bank's webpage , make payment our account successfully. far ? the code use below (i changed values privacy reasons) problem is, need app when user has completed/failed/cancelled payment. barclaycardautomatically redirects particular url when payment has succeeded, 1 if failed. there no way of knowing when barclaycard payment has succeeded go android app somehow ? button cardbutton = (button) findviewbyid(r.id.card_button); cardbutton.setonclicklistener(new view.onclicklistener() { @override public void onclick(view arg0) { string prehashstring = new string(); string prohashstring = new string(); string shapassphrase = new string(); shapassphrase = "gsvth£h70zkh...
Read more

Shrink a YouTube video to responsive width -

i have youtube video embedded on our website , when shrink screen tablet or phone sizes stops shrinking @ around 560px in width. standard youtube videos or there can add code make go smaller? you can make youtube videos responsive css. wrap iframe in div class of "videowrapper" , apply following styles: .videowrapper { float: none; clear: both; width: 100%; position: relative; padding-bottom: 56.25%; padding-top: 25px; height: 0; } .videowrapper iframe { position: absolute; top: 0; left: 0; width: 100%; height: 100%; } the .videowrapper div should inside responsive element. padding on .videowrapper necessary keep video collapsing. may have tweak numbers depending upon layout.
Read more

wpf - PdfWriter.GetInstance throws System.NullReferenceException -

i'm trying create pdf document using itextsharp 5.3.4 using following document document = new document(); filestream stm = new filestream(filename, filemode.create); pdfwriter writer = pdfwriter.getinstance(document, stm); i'm getting system.nullreferenceexception following stack trace: system.nullreferenceexception occurred hresult=-2147467261 message=la référence d'objet n'est pas définie à une instance d'un objet. source=itextsharp stacktrace: à itextsharp.text.version.getinstance() innerexception: i've verified neither document nor stm null, , if select "continue" in vs12 document created - exception thrown. updated itextsharp 5.4.0 , it's still occurring. can't find information on anywhere - got ideas? make sure not catching exceptions. nullreferenceexception can 1 caught , handled inside itextsharp, don't care it. fact can continue supports theory. change following setting verify: debug -> exceptions ...
Read more