MySQL 如何删除最小值和最大值并取一行中剩余值的平均值？

Question

我在 MySQL DB(版本 5.5.31)中有一个表，比如 table1 和描述:

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>

此表每五分钟更新一次实时数据。我想做的是通过检查 ts 来获取 calculated_percent 的平均值。 ts 应该在“当前时间”和“当前时间 - 1 小时”之间。将在一周的同一天检查过去 6 周的同一时间段。到目前为止，我可以使用以下查询获取数据:

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> 

现在我的问题是，有没有什么方法可以跳过 6 周 (avg1-avg6) 的最小值和最大值并计算剩余 4 周的平均值？

因为这些值在行而不是列中，所以我找不到在那里使用 MIN/MAX 函数的方法。我正在搜索创建临时表是否可以在这里以任何方式提供帮助。我也可以避免使用“GROUP BY type”并为每种类型(O、R 和 S)创建 3 个单独的查询，如果这可能有任何帮助的话。

另一个可以帮助我的选项是，如果我在单列而不是 6 个不同的列中获取 avg1-avg6 的值。有没有办法(使用 UNION 等)以这种格式获取数据:

+-------------------+---------+
| type              | average |
+-------------------+---------+
| O                 | 24.5833 |
| O                 | 18.0286 |
| O                 | 17.9843 |
| O                 | 17.6145 |
| O                 | 17.8780 |
| O                 | 17.7976 |
| R                 | 30.4167 |
| R                 | 23.0656 |
| R                 | 23.0620 |
| R                 | 22.5315 |
| R                 | 22.9573 |
| R                 | 22.7793 |
| S                 |  5.5833 |
| S                 |  2.4963 |
| S                 |  2.4958 |
| S                 |  2.4665 |
| S                 |  2.4893 |
| S                 |  2.5833 |
+-------------------+---------+

Answer 1

我想我明白了。我正在使用简单的 UNION。虽然这很难，而且查询又大又难看，但它使我能够跳过 6 周的最小值和最大值 (avg1-avg6) 并计算剩余 4 周的平均值，因为我可以在同一列中获取 6 周的值6 个不同的列。

SELECT type, (SUM(Average) - (MIN(Average) + MAX(Average)))/4 as FourWeekAverage FROM
(
SELECT
  type,
  sum1/count1 AS Average
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
  FROM table1
  WHERE ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 1 week)
  AND type = 'O'
) AS baseview
UNION
SELECT
  type,
  sum2/count2 AS Average
FROM (
  SELECT
    type,
    SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 2 week) AND ts <= DATE_SUB(NOW(), interval 2 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 2 week), 1, 0)) as count2
  FROM table1
  WHERE ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 2 week)
  AND type = 'O'
) AS baseview
UNION
SELECT
  type,
  sum3/count3 AS Average
FROM (
  SELECT
    type,
    SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 3 week) AND ts <= DATE_SUB(NOW(), interval 3 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 3 week), 1, 0)) as count3
  FROM table1
  WHERE ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 3 week)
  AND type = 'O'
) AS baseview
UNION
SELECT
  type,
  sum4/count4 AS Average
FROM (
  SELECT
    type,
    SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 4 week) AND ts <= DATE_SUB(NOW(), interval 4 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 4 week), 1, 0)) as count4
  FROM table1
  WHERE ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 4 week)
  AND type = 'O'
) AS baseview
UNION
SELECT
  type,
  sum5/count5 AS Average
FROM (
  SELECT
    type,
    SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 5 week) AND ts <= DATE_SUB(NOW(), interval 5 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 5 week), 1, 0)) as count5
  FROM table1
  WHERE ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 5 week)
  AND type = 'O'
) AS baseview
UNION
SELECT
  type,
  sum6/count6 AS Average
FROM (
  SELECT
    type,
    SUM(IF (ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 6 week) AND ts <= DATE_SUB(NOW(), interval 6 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 6 week), 1, 0)) as count6
  FROM table1
  WHERE ts >= DATE_SUB(DATE_SUB(NOW(), interval 1 hour), interval 6 week)
  AND type = 'O'
) AS baseview
) AS baseview;

输出是:

+---------+-----------------+
| type    | FourWeekAverage |
+---------+-----------------+
| O       |     37.43750000 |
+---------+-----------------+

还有一个问题是我在测试时发现的。如果 6 周中有两个或更多完全相同的值，我认为 MIN/MAX 函数无法正常工作并且 FourWeekAverage 值计算错误。我仍在寻找一种使查询不受这种情况影响的方法。