How to remove min and max and take average of remaining values from a row?
Hi all,
I have a table in MySQL DB (Version 5.5.31) say table1 with description:
mysql> desc table1;
+--------------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------------------+-------+
| ts | timestamp | NO | PRI | 0000-00-00 00:00:00 | |
| type | varchar(100) | NO | PRI | NULL | |
| calculated_percent | int(3) | NO | | 100 | |
| used | int(11) | NO | | 100 | |
| available | int(11) | NO | | 100 | |
+--------------------+--------------+------+-----+---------------------+-------+
5 rows in set (0.03 sec)
mysql>
This table gets updated every five minutes with real time data.
What I am trying to do is get average of calculated_percent, by checking ts. ts should be between "current time" and "current time - 1 hour". This same time slot will be checked for past 6 weeks for the same day of the week.
I can get data until this point using following query:
mysql> SELECT
-> type,
-> sum1/count1 AS avg1,
-> sum2/count2 AS avg2,
-> sum3/count3 AS avg3,
-> sum4/count4 AS avg4,
-> sum5/count5 AS avg5,
-> sum6/count6 AS avg6
-> FROM (
-> SELECT
-> type,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 1 week) AND ts <= DATE_SUB(NOW(), interval 1 week), calculated_percent, 0)) as sum1,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 1 week) AND ts <= DATE_SUB(NOW(), interval 1 week), 1, 0)) as count1,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 2 week) AND ts <= DATE_SUB(NOW(), interval 1 week), calculated_percent, 0)) as sum2,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 2 week) AND ts <= DATE_SUB(NOW(), interval 1 week), 1, 0)) as count2,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 3 week) AND ts <= DATE_SUB(NOW(), interval 1 week), calculated_percent, 0)) as sum3,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 3 week) AND ts <= DATE_SUB(NOW(), interval 1 week), 1, 0)) as count3,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 4 week) AND ts <= DATE_SUB(NOW(), interval 1 week), calculated_percent, 0)) as sum4,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 4 week) AND ts <= DATE_SUB(NOW(), interval 1 week), 1, 0)) as count4,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 5 week) AND ts <= DATE_SUB(NOW(), interval 1 week), calculated_percent, 0)) as sum5,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 5 week) AND ts <= DATE_SUB(NOW(), interval 1 week), 1, 0)) as count5,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 6 week) AND ts <= DATE_SUB(NOW(), interval 1 week), calculated_percent, 0)) as sum6,
-> SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 6 week) AND ts <= DATE_SUB(NOW(), interval 1 week), 1, 0)) as count6
-> FROM table1
-> WHERE ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 6 week)
-> GROUP BY type
-> ) AS baseview;
+-------------------+---------+---------+---------+---------+---------+---------+
| type | avg1 | avg2 | avg3 | avg4 | avg5 | avg6 |
+-------------------+---------+---------+---------+---------+---------+---------+
| O | 24.5833 | 18.0286 | 17.9843 | 17.6145 | 17.8780 | 17.7976 |
| R | 30.4167 | 23.0656 | 23.0620 | 22.5315 | 22.9573 | 22.7793 |
| S | 5.5833 | 2.4963 | 2.4958 | 2.4665 | 2.4893 | 2.5833 |
+-------------------+---------+---------+---------+---------+---------+---------+
3 rows in set (0.44 sec)
mysql>
Now my question is, is there any way I can skip min and max values from the 6 weeks(avg1-avg6) and calculate average of remaining 4 weeks? As these values are in row instead of column, I couldn't find a way to use MIN/MAX functions there. I am searching if creating temp table could help in any way here.
I can also avoid using "GROUP BY type" and create 3 separate queries for each type (O, R and S) if that might be of any help.