5,838
edits
Category:Cumulus MX: Difference between revisions
m
→MySQL settings
m (→MySQL settings) |
|||
| Line 592: | Line 592: | ||
**# The SQL you want to run, what you type in this small text box should include INSERT IGNORE (or REPLACE) to insert a row, or include UPDATE to change columns in a row that already exists, like any SQL it must include the name of the table, the columns to be updated, and the values you want to insert into the columns are either expressed as web tags or as a sub-query reading the values from somewhere else. | **# The SQL you want to run, what you type in this small text box should include INSERT IGNORE (or REPLACE) to insert a row, or include UPDATE to change columns in a row that already exists, like any SQL it must include the name of the table, the columns to be updated, and the values you want to insert into the columns are either expressed as web tags or as a sub-query reading the values from somewhere else. | ||
**#* Here is an example of a suitable query that MX can process for you [note I have had to include some yesterday tags e.g. for primary key ('''<#metdateyesterday format=yyyy-MM-dd>''') and that I have added a second query with sub-query after the first to update a field for which there is no web tag, note the extra quotes and brackets required to define a value in this way]: | **#* Here is an example of a suitable query that MX can process for you [note I have had to include some yesterday tags e.g. for primary key ('''<#metdateyesterday format=yyyy-MM-dd>''') and that I have added a second query with sub-query after the first to update a field for which there is no web tag, note the extra quotes and brackets required to define a value in this way]: | ||
<pre>INSERT IGNORE INTO `test_daily_summary` (`MaxRainRate`, `TMaxRainRate`, `HighHourRain`, `THighHourRain`, `TotRainFall`, `SnowFalling`, `SnowLying`, `SnowDepth`, `CumChillHours`, `LogDate`, `RollOver`, `MinTemp`, `TMinTemp`, `HeatDegDays`, `AvgTemp`, `MaxTemp`, `TMaxTemp`, `CoolDegDays`, `LowDewPoint`, `TLowDewPoint`, `LowHum`, `TLowHum`, `HighHum`, `THighHum`, `HighDewPoint`, `THighDewPoint`, `GreatWindChill`, `TGreatWindChill`, `LowAppTemp`, `TLowAppTemp`, `HighAppTemp`, `THighAppTemp`, `HighHeatInd`, `THighHeatInd`, `MinPress`, `TMinPress`, `MaxPress`, `TMaxPress`, `HighAvgWSpeed`, `THighAvgWSpeed`, `StrongestWindGust`, `TStrongestWindGust`, `BearStrongestWindGust`, `BearStrongestWindGustSym`,`BearDomWind`, `BearDomWindSym`, `TotWindRun`) VALUES ('<#rrateTM>', '<#TrrateTM>', '<#hourlyrainTH>', '<#ThourlyrainTH>', '<#rfall> ', '<#snowfalling>', '<#snowlying>', '<#snowdepth>', '<#chillhours>', '<#metdateyesterday format=yyyy-MM-dd>', 'SUBSTRING(<#rollovertime>,0,2)', '<#tempYL>', '<#TtempYL> ', '<#heatdegdays> ', '<#avgtemp>', '<#tempTH>', '<#TtempTH> ', '<#cooldegdays> ', '<#dewpointTL>', '<#TdewpointTL>', '<#humTL>', '<#ThumTL>', '<#humTH>', '<#ThumTH>', '<#dewpointTH>', '<#TdewpointTH>', '<#wchillTL>', '<#TwchillTL>', '<#apptempTL>', '<#TapptempTL>', '<#apptempTH>', '<#TapptempTH>', '<#heatindexTH>', '<#TheatindexTH>', '<#pressTL>', '<#TpressTL>', '<#pressTH>', '<#TpressTH>', '<#windTM>', '<#TwindTM>', '<#wgustTM>', '<#TwgustTM>', '<#bearingTM>', '<#directionTM>', '<#domwindbearing>', '<#domwinddir>', '<#windrun>'); UPDATE `test_daily_summary` SET `DailyChillHours`= "(<#chillhours> - (SELECT `CumChillHours` FROM `test_daily_summary` WHERE `LogDate` = '(DATE_SUB(<#metdateyesterday format=yyyy-MM-dd>, INTERVAL 1 DAY))) | <pre>INSERT IGNORE INTO `test_daily_summary` (`MaxRainRate`, `TMaxRainRate`, `HighHourRain`, `THighHourRain`, `TotRainFall`, `SnowFalling`, `SnowLying`, `SnowDepth`, `CumChillHours`, `LogDate`, `RollOver`, `MinTemp`, `TMinTemp`, `HeatDegDays`, `AvgTemp`, `MaxTemp`, `TMaxTemp`, `CoolDegDays`, `LowDewPoint`, `TLowDewPoint`, `LowHum`, `TLowHum`, `HighHum`, `THighHum`, `HighDewPoint`, `THighDewPoint`, `GreatWindChill`, `TGreatWindChill`, `LowAppTemp`, `TLowAppTemp`, `HighAppTemp`, `THighAppTemp`, `HighHeatInd`, `THighHeatInd`, `MinPress`, `TMinPress`, `MaxPress`, `TMaxPress`, `HighAvgWSpeed`, `THighAvgWSpeed`, `StrongestWindGust`, `TStrongestWindGust`, `BearStrongestWindGust`, `BearStrongestWindGustSym`,`BearDomWind`, `BearDomWindSym`, `TotWindRun`) VALUES ('<#rrateTM>', '<#TrrateTM>', '<#hourlyrainTH>', '<#ThourlyrainTH>', '<#rfall> ', '<#snowfalling>', '<#snowlying>', '<#snowdepth>', '<#chillhours>', '<#metdateyesterday format=yyyy-MM-dd>', 'SUBSTRING(<#rollovertime>,0,2)', '<#tempYL>', '<#TtempYL> ', '<#heatdegdays> ', '<#avgtemp>', '<#tempTH>', '<#TtempTH> ', '<#cooldegdays> ', '<#dewpointTL>', '<#TdewpointTL>', '<#humTL>', '<#ThumTL>', '<#humTH>', '<#ThumTH>', '<#dewpointTH>', '<#TdewpointTH>', '<#wchillTL>', '<#TwchillTL>', '<#apptempTL>', '<#TapptempTL>', '<#apptempTH>', '<#TapptempTH>', '<#heatindexTH>', '<#TheatindexTH>', '<#pressTL>', '<#TpressTL>', '<#pressTH>', '<#TpressTH>', '<#windTM>', '<#TwindTM>', '<#wgustTM>', '<#TwgustTM>', '<#bearingTM>', '<#directionTM>', '<#domwindbearing>', '<#domwinddir>', '<#windrun>'); UPDATE `test_daily_summary` SET `DailyChillHours`= "(<#chillhours> - (SELECT `CumChillHours` FROM `test_daily_summary` WHERE `LogDate` = '(DATE_SUB(<#metdateyesterday format=yyyy-MM-dd>, INTERVAL 1 DAY))' WHERE `LogDate` = '<#metdateyesterday format=yyyy-MM-dd>')";</pre> | ||
** Again before you enable this option, there is a facility lower down this setting page (under the heading '''Create database table''') where you can type some SQL to be run immediately, that can create the table you want this option to update, (although it could even populate any table with historic data, it is only intended for a small query). I am using a table that already exists as I have used it for testing changes to my PHP scripts, so I did not need to create a table before I enabled the query shown above. | ** Again before you enable this option, there is a facility lower down this setting page (under the heading '''Create database table''') where you can type some SQL to be run immediately, that can create the table you want this option to update, (although it could even populate any table with historic data, it is only intended for a small query). I am using a table that already exists as I have used it for testing changes to my PHP scripts, so I did not need to create a table before I enabled the query shown above. | ||