mysql 对列、聚合和转换结果执行GROUP BY

mwkjh3gx 于 2023-02-21 发布在 Mysql

关注(0)|答案(1)|浏览(119)

我有以下表格：

date       | data_type | data_value
01-01-2023 |  max      |    4
02-01-2023 |  min      |    7
03-01-2023 |  avg      |    54
04-01-2023 |  max      |    8
05-01-2023 |  min      |    98
06-01-2023 |  avg      |    23
01-02-2023 |  max      |    65
02-02-2023 |  min      |    2
03-02-2023 |  avg      |    45
04-02-2023 |  max      |    22
05-02-2023 |  min      |    56
06-02-2023 |  avg      |    65
01-03-2023 |  max      |    7
02-03-2023 |  min      |    5
03-03-2023 |  avg      |    23
04-03-2023 |  max      |    65
05-03-2023 |  min      |    51
06-03-2023 |  avg      |    33

在下表中，我需要按当前月份分组，并从中取最大值 data_type为“max”，类型为“min”的数据中较小的一个，最后是类型为“avg”的数据的平均值，如下所示：

mounth | max | min |  avg
1      |  8  |  7  |  38,5
2      |  65 |  2  |  55
3      |  65 |  5  |  28

因此，我想问一下，为了解决请求，哪一个SQL查询的性能最好。
谢谢大家。

mysql

来源：https://stackoverflow.com/questions/75500401/group-by-on-column-aggregation-and-transform-results

1条答案

按热度按时间

mpgws1up1#

按如下方式使用条件聚集：

select year(date_) as 'yaer',
       month(date_) as 'month',
       min(case when data_type = 'min' then data_value end) as 'min',
       max(case when data_type = 'max' then data_value end) as 'max',
       avg(case when data_type = 'avg' then data_value end) as 'avg'
from tbl
group by year(date_), month(date_)

或者，如果要聚集特定年份：

select month(date_) as 'month',
       min(case when data_type = 'min' then data_value end) as 'min',
       max(case when data_type = 'max' then data_value end) as 'max',
       avg(case when data_type = 'avg' then data_value end) as 'avg'
from tbl
where year(date_) = 2023
group by month(date_)

See demo.

赞(0）回复(0）举报 2023-02-21

我来回答

mysql 对列、聚合和转换结果执行GROUP BY

1条答案

相关问题

热门标签

最新问答