mysql - UNION/GROUP BY - 防止 NULL 值

Question

我有三个包含天气观测的 MySQL 表。现在我正在尝试从所有这些按小时分组的表中查询数据。表格看起来像:

TABLE 1
--------------------------------------------------------------------
station     time        temperature     pressure        humidity
--------------------------------------------------------------------
10637       1520512345  22.9            NULL            NULL

TABLE 2
-------------------------------------------------------------------
station     time        temperature     pressure        humidity
-------------------------------------------------------------------
10637       1520512345  NULL            1016            NULL

TABLE 3
-------------------------------------------------------------------
station     time        temperature     pressure        humidity
-------------------------------------------------------------------
10637       1520512345  NULL            NULL            76

目前，我使用 UNION 和 GROUP BY 来查询每小时聚合:

SELECT *
FROM ((SELECT `time`,`temperature`,`pressure`,`humidity`
       FROM `table_1`
       WHERE `station` = 10637
      ) UNION
      (SELECT `time`,`temperature`,`pressure`,`humidity`
       FROM `table_2`
       WHERE `station` = 10637
      ) UNION
      (SELECT `time`,`temperature`,`pressure`,`humidity`
       FROM `table_3`
       WHERE `station` = 10637
      )
     ) AS `hourly`
GROUP BY DATE_FORMAT(FROM_UNIXTIME(`time`), '%Y %m %d %H')
ORDER BY `time`

查询有效。每小时需要一个可用行。但如示例所示，某些表包含其他表中不可用的数据。这会导致输出中出现许多不必要的 NULL 值。我需要如何更改查询，以便输出包含所有 NOT NULL 数据的聚合？

Answer 1

不要将 select * 与 group by 一起使用。它是一种反模式，在大多数情况下不受其他数据库的支持。接受挑战。弄清楚如何组合结果。

在这种情况下，简单的聚合函数起作用:

SELECT DATE_FORMAT(FROM_UNIXTIME(`time`), '%Y %m %d %H') as yyyymmddhh,
       AVG(temperature) as temperature,
       AVG(pressure) as pressure,
       AVG(humidity) as humidity
FROM ((SELECT time, temperature, pressure, humidity
       FROM `table_1`
       WHERE `station` = 10637
      ) UNION ALL
      (SELECT time, temperature, pressure, humidity
       FROM `table_2`
       WHERE `station` = 10637
      ) UNION ALL
      (SELECT time, temperature, pressure, humidity
       FROM `table_3`
       WHERE `station` = 10637
      )
     ) hourly
GROUP BY DATE_FORMAT(FROM_UNIXTIME(`time`), '%Y %m %d %H')
ORDER BY yyyymmddhh