MySql 查询列属性

Question

我的 table 是这样的。

+-----------------+----------+------+-----+---------+-------+
| Field           | Type     | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| start_date_time | datetime | NO   | PRI | NULL    |       |
| ch01            | int(11)  | YES  |     | NULL    |       |
| ch02            | int(11)  | YES  |     | NULL    |       |
| ch03            | int(11)  | YES  |     | NULL    |       |
| ch04            | int(11)  | YES  |     | NULL    |       |
| ch05            | int(11)  | YES  |     | NULL    |       |
| ch06            | int(11)  | YES  |     | NULL    |       |
| ch07            | int(11)  | YES  |     | NULL    |       |
| ch08            | int(11)  | YES  |     | NULL    |       |
| ch09            | int(11)  | YES  |     | NULL    |       |
| ch10            | int(11)  | YES  |     | NULL    |       |
| ch11            | int(11)  | YES  |     | NULL    |       |
| ch12            | int(11)  | YES  |     | NULL    |       |
| ch13            | int(11)  | YES  |     | NULL    |       |
| ch14            | int(11)  | YES  |     | NULL    |       |
| ch15            | int(11)  | YES  |     | NULL    |       |
| ch16            | int(11)  | YES  |     | NULL    |       |
| ch17            | int(11)  | YES  |     | NULL    |       |
| ch18            | int(11)  | YES  |     | NULL    |       |
+-----------------+----------+------+-----+---------+-------+

我有这个疑问。

select 
    @ch := 'ch01' as channel, 
    avg(ch01) as average, std(ch01) as std, 
    max(ch01) as max, min(ch01) as min 
from 
    (select ch01 
     from myTable/*This query is more complex, this is a simplified version just to keep things clear*/) as t1
union
select 
    @ch:='ch02' as channel, avg(ch02) as average, 
    std(ch02) as std, max(ch02) as max, min(ch02) as min 
from 
    (select ch02 
     from myTable/*Same here, this is a simplified version, same as the one above*/) as t2
union
....so on for the 18 channels i have.

输出如下所示。

+---------+----------+---------+------+------+
| channel | average  | std     | max  | min  |
+---------+----------+---------+------+------+
| ch01    | 244.9091 | 12.5514 |  265 |  223 |
| ch02    | 282.0909 | 22.1049 |  321 |  239 |
..............................................
+---------+----------+---------+------+------+

是否有更好的方法来实现相同的输出？

Answer 1

首先考虑更改您的数据模型!

CREATE TABLE MyTable (
    channel VARCHAR(8),
    start_date_time TIMESTAMP,
    channelValue INT
)

(添加约束并根据您的需要调整数据类型!)

如果您无法修改模型，那么这里有一个解决方案:

您尝试做的事情称为:UNPIVOT不幸的是MySQL没有像PIVOT和UNPIVOT这样的功能。解决方法是 UNION ALL 解决方案。

您可以通过创建子查询来简化复杂的部分，而不是对该子查询的结果使用聚合函数:

SELECT
    channel,
    AVG(channelValue)
    /* Add the additional aggregations here*/
FROM (
        SELECT 'ch01' as channel, start_date_time, ch01 AS channelValue FROM myTable
        UNION ALL
        SELECT 'ch02' as channel, start_date_time, ch02 FROM myTable
        UNION ALL
        SELECT 'ch03' as channel, start_date_time, ch03 FROM myTable
        UNION ALL
        /* ... */
        UNION ALL
        SELECT 'ch18' as channel, start_date_time, ch18 FROM myTable
    ) AS UPVT
GROUP BY
    channel

您可以基于子查询创建 View ，并可以在查询中使用它

CREATE VIEW MyTableUnpivot AS
SELECT 'ch01' as channel, start_date_time, ch01 AS channelValue FROM myTable
UNION ALL
SELECT 'ch02' as channel, start_date_time, ch02 FROM myTable
UNION ALL
SELECT 'ch03' as channel, start_date_time, ch03 FROM myTable
UNION ALL
/* ... */
UNION ALL
SELECT 'ch18' as channel, start_date_time, ch18 FROM myTable

然后您可以使用新 View :

SELECT
    channel,
    AVG(channelValue)
    /* Add the additional aggregations here*/
FROM MyTableUnpivot
GROUP BY
    channel