Kod: Markera allt
create view statistik as select a.time, b.name "Sensor", c.name "Egenskap", d.name "Typ", a.value, c.enhet from data a, sensors b, properties c, aggregates d where a.aggregate > 2 and a.sensorid = b.sensorid and a.property = c.propertyid and b.sensorid = c.sensorid and a.aggregate = d.aggregate;
Kod: Markera allt
mysql> desc aggregates;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| aggregate | smallint(6) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from aggregates;
+-----------+----------+
| aggregate | name |
+-----------+----------+
| 1 | Råvärde |
| 2 | Medel 1h |
| 3 | Medel 1d |
| 4 | Min 1d |
| 5 | Max 1d |
+-----------+----------+
Kod: Markera allt
mysql> select * from statistik where time > '2013-09-04';
+---------------------+----------------------+---------------+----------+---------+-------+
| time | Sensor | Egenskap | Typ | value | enhet |
+---------------------+----------------------+---------------+----------+---------+-------+
| 2013-09-05 00:00:00 | EDS0068 - korridor | Temperatur | Medel 1d | 23.1037 | degC |
| 2013-09-04 06:30:03 | EDS0068 - korridor | Temperatur | Min 1d | 22.3125 | degC |
| 2013-09-04 18:08:03 | EDS0068 - korridor | Temperatur | Max 1d | 24.375 | degC |
| 2013-09-05 00:00:00 | EDS0068 - korridor | Luftfuktighet | Medel 1d | 45.4384 | % |
| 2013-09-04 18:57:03 | EDS0068 - korridor | Luftfuktighet | Min 1d | 42 | % |
| 2013-09-04 06:48:04 | EDS0068 - korridor | Luftfuktighet | Max 1d | 49 | % |
| 2013-09-05 00:00:00 | EDS0068 - korridor | Ljusstyrka | Medel 1d | 28.8639 | LUX |
| 2013-09-04 01:43:04 | EDS0068 - korridor | Ljusstyrka | Min 1d | 0 | LUX |
| 2013-09-04 16:08:03 | EDS0068 - korridor | Ljusstyrka | Max 1d | 114 | LUX |
| 2013-09-05 00:00:00 | EDS0068 - korridor | Lufttryck | Medel 1d | 1015.64 | hPa |
| 2013-09-04 00:00:03 | EDS0068 - korridor | Lufttryck | Min 1d | 1014.66 | hPa |
| 2013-09-04 10:02:04 | EDS0068 - korridor | Lufttryck | Max 1d | 1016.69 | hPa |
| 2013-09-05 00:00:00 | Radiatortemp - ut | Temp | Medel 1d | 23.4916 | degC |
| 2013-09-04 23:24:03 | Radiatortemp - ut | Temp | Min 1d | 20.125 | degC |
| 2013-09-04 06:58:03 | Radiatortemp - ut | Temp | Max 1d | 28.75 | degC |
| 2013-09-05 00:00:00 | Fjärrrvärmetemp - in | Temp | Medel 1d | 46.5553 | degC |
| 2013-09-04 21:31:04 | Fjärrrvärmetemp - in | Temp | Min 1d | 29.5 | degC |
| 2013-09-04 06:20:03 | Fjärrrvärmetemp - in | Temp | Max 1d | 56.6875 | degC |
+---------------------+----------------------+---------------+----------+---------+-------+