mysql> create temporary table per select ahour, mod(ahour, 24) as dhour, node_mac, node_ip, IF(hwtype = "bcm", 1 - (rx_packets / (rx_packets + rx_fifo + rx_drop + rx_error + rx_frame)), 1 - (rx_packets / (rx_packets + madwifi_rx_crc + madwifi_rx_phy))) AS per from reports left join hwtype_mac using (node_mac);

mysql> select dhour, avg(per), stddev(per) from per group by dhour;
+-------+------------+-------------+
| dhour | avg(per)   | stddev(per) |
+-------+------------+-------------+
|     0 | 0.60410407 |  0.32723744 | 
|     1 | 0.59419658 |  0.32702417 | 
|     2 | 0.58523665 |  0.32223518 | 
|     3 | 0.57839165 |  0.31816341 | 
|     4 | 0.57873525 |  0.31933000 | 
|     5 | 0.58109202 |  0.32230213 | 
|     6 | 0.59332395 |  0.32958796 | 
|     7 | 0.60650298 |  0.33197818 | 
|     8 | 0.62259688 |  0.33549653 | 
|     9 | 0.63527306 |  0.33717191 | 
|    10 | 0.64165550 |  0.34036079 | 
|    11 | 0.64519399 |  0.34096922 | 
|    12 | 0.64632158 |  0.34041509 | 
|    13 | 0.64301584 |  0.34177339 | 
|    14 | 0.64167971 |  0.34285715 | 
|    15 | 0.64346954 |  0.34158954 | 
|    16 | 0.64514582 |  0.34138036 | 
|    17 | 0.64670218 |  0.34149904 | 
|    18 | 0.65467186 |  0.33995837 | 
|    19 | 0.65365221 |  0.34183400 | 
|    20 | 0.64760085 |  0.34304200 | 
|    21 | 0.64282513 |  0.34170944 | 
|    22 | 0.63090914 |  0.33709339 | 
|    23 | 0.61736931 |  0.33407688 | 
+-------+------------+-------------+
24 rows in set (0.94 sec)

# bereinigt um Gleichanteil
mysql> create temporary table per_node select node_mac, avg(per) from per group by node_mac;
mysql> select dhour, avg(per-`avg(per)`), stddev(per-`avg(per)`) from per left join per_node using (node_mac) group by dhour;
mysql> select dhour, avg(per-`avg(per)`), stddev(per-`avg(per)`) from per left join per_node using (node_mac) group by dhour;
+-------+---------------------+------------------------+
| dhour | avg(per-`avg(per)`) | stddev(per-`avg(per)`) |
+-------+---------------------+------------------------+
|     0 |     -0.020020043636 |         0.097746941307 | 
|     1 |     -0.029446567029 |         0.099328001997 | 
|     2 |     -0.038780654669 |         0.096447827182 | 
|     3 |     -0.045109413669 |         0.094461228000 | 
|     4 |     -0.045044061878 |         0.098908106160 | 
|     5 |     -0.042529964343 |         0.101600505921 | 
|     6 |     -0.030196081005 |         0.104519644128 | 
|     7 |     -0.017271341774 |         0.105626689165 | 
|     8 |     -0.001760994426 |         0.107262251740 | 
|     9 |      0.010551990324 |         0.110170546301 | 
|    10 |      0.016758889075 |         0.108929357466 | 
|    11 |      0.019166177701 |         0.108204405907 | 
|    12 |      0.021635425729 |         0.108224465432 | 
|    13 |      0.018356387954 |         0.107904247918 | 
|    14 |      0.016287516360 |         0.105311278542 | 
|    15 |      0.019611250575 |         0.105885015561 | 
|    16 |      0.021543265111 |         0.104488577179 | 
|    17 |      0.022790694820 |         0.108590964316 | 
|    18 |      0.030694622950 |         0.109975928282 | 
|    19 |      0.029260871401 |         0.106582891157 | 
|    20 |      0.023600150528 |         0.104195919588 | 
|    21 |      0.018972519400 |         0.101131958512 | 
|    22 |      0.007113781537 |         0.102769425619 | 
|    23 |     -0.005717247093 |         0.101951919620 | 
+-------+---------------------+------------------------+
24 rows in set (22.19 sec)