MYSQL一次查询中最常见的列值和AVG

Question

我有一张表，其中的列包含有关风的数据(风、阵风和方向)

我想要获取所选时间段内风和阵风的平均值以及最常见的方向值

我可以设法在两个查询中完成它，但不知道如何合并它们

SELECT
    avg(wind) as wind,
    avg(gust) as gust
FROM
    station
WHERE
    id >='2018-10-20 00:00:00' AND id <'2018-10-20 04:00:00'

SELECT
    direction,
    count(direction) as sm
FROM
    station
WHERE
    id >='2018-10-20 00:00:00' AND id <'2018-10-20 04:00:00'
GROUP BY
    direction
ORDER BY
    sm DESC LIMIT 1

我试过了

SELECT
    avg(wind) as wind,
    avg(gust) as gust,
    direction,
    count(direction) as sm
FROM
    station
WHERE
    id >='2018-10-20 00:00:00' AND id <'2018-10-20 04:00:00'
GROUP BY
    direction
ORDER BY
    sm DESC LIMIT 1

但这将计算数据的平均值，但仅在方向是最常见值的情况下计算。感谢帮助

Answer 1

你可以试试这个。

SELECT direction,
       t1.wind,
       t1.gust,
       count(direction) as sm
FROM station CROSS JOIN (
    SELECT avg(wind) as wind,
           avg(gust) as gust 
    FROM station 
    WHERE id >='2018-10-20 00:00:00' AND id <'2018-10-20 04:00:00' 
) t1
WHERE id >='2018-10-20 00:00:00' AND id <'2018-10-20 04:00:00' 
GROUP BY direction 
ORDER BY sm DESC 
LIMIT 1

或者您可以在select中使用子查询

SELECT direction,
       (select avg(wind) FROM station) wind,
       (select avg(gust) FROM station) gust,
       count(direction) as sm
FROM station 
WHERE id >='2018-10-20 00:00:00' AND id <'2018-10-20 04:00:00' 
GROUP BY direction 
ORDER BY sm DESC 
LIMIT 1