MySQL Forums
Forum List  »  Newbie

How to remove min and max and take average of remaining values from a row?
Posted by: Pramod Patki
Date: November 18, 2015 09:52AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.