Hristo Ivanov Hristo Ivanov - 1 month ago 8
SQL Question

MySql querying columns properties

My table looks like this.

+-----------------+----------+------+-----+---------+-------+
| 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 | |
+-----------------+----------+------+-----+---------+-------+


I have this query.

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.


The output looks like this.

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


Is there a better approach to achieve the same output?

This text is just thrash so i can publish the question, I think its clear enough.

I'm having trouble posting my question:

It looks like your post is mostly code; please add some more details.

Answer

First of all consider to change your data model!

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

(Add constraints and adjust the datatypes to your needs!)

If you can not modify your model, then here is a solution:

The thing, you are trying to do is called: UNPIVOT Unfortunetly MySQL have no function like PIVOT and UNPIVOT. The workaround is the UNION ALL solution.

You can simplify your complex part by creating a subquery, than you can use aggregate function on the result of that subquery:

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

You can create a view based on the subquery and you can use it in your query

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

Then you can use your new view:

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