sql - 如何对三个表使用 MAX 函数？

Question

所以，我的 SQL 查询有问题。

这是关于获取德国城市的天气数据。我有 4 个表:staedte(主键 loc_id 的城市)，gehoert_zu(包含城市键和离该城市最近的气象站键(stations_id))，wettermessung(包含所有天气信息和气象站的键值)和 wetterstation(包含站键和位置)。我正在使用 PostgreSQL

表格如下:

wetterstation
s_id[PK]   standort    lon    lat    hoehe 
----------------------------------------
10224      Bremen      53.05  8.8    4


wettermessung
stations_id[PK]    datum[PK]     max_temp_2m   ......
----------------------------------------------------
10224              2013-3-24     -0.4


staedte
loc_id[PK]    name    lat    lon
-------------------------------
15            Asch    48.4   9.8


gehoert_zu
loc_id[PK]    stations_id[PK]
-----------------------------
15            10224

我想要做的是获取指定日期(可能是整月或一天)(例如)最高温度的城市的名称。由于天气数据是绑定(bind)到一个站点的，所以我实际上需要获取站点的ID，然后选择与该站点对应的城市之一。一个可能的问题是:“哪个城市 6 月 HitTest ？”并且，比如说，最高测量温度是在站号 10224 中。因此我想获得城市 Asch。到目前为止我得到的是这个

SELECT name, MAX (max_temp_2m)
FROM wettermessung, staedte, gehoert_zu 
WHERE wettermessung.stations_id = gehoert_zu.stations_id
    AND gehoert_zu.loc_id = staedte.loc_id 
    AND wettermessung.datum BETWEEN '2012-8-1' AND '2012-12-1' 
GROUP BY name
ORDER BY MAX (max_temp_2m) DESC 
LIMIT 1

结果有两个问题:1)它花费的时间太长了。表格不是那么大(城市有大约 70k 个条目)，但需要 1 到 7 分钟才能完成(取决于时间跨度)2) 它总是产生同一个城市，我很确定它也不是正确的城市。

我希望我能够足够清楚地解释我的问题，我很乐意提供任何帮助。提前致谢 ! :D

Answer 1

如果您想获得每个城市的最高温度，请使用以下语句:

SELECT * FROM (
   SELECT gz.loc_id, MAX(max_temp_2m) as temperature
      FROM wettermessung as wm
      INNER JOIN gehoert_zu as gz
         ON wm.stations_id = gz.stations_id
      WHERE wm.datum BETWEEN '2012-8-1' AND '2012-12-1' 
      GROUP BY gz.loc_id) as subselect
   INNER JOIN staedte as std
      ON std.loc_id = subselect.loc_id
      ORDER BY subselect.temperature DESC

用这条语句得到温度最高的城市(只有1个城市):

SELECT * FROM(
   SELECT name, MAX(max_temp_2m) as temp
   FROM wettermessung as wm
   INNER JOIN gehoert_zu as gz
      ON wm.stations_id = gz.stations_id
   INNER JOIN staedte as std
      ON gz.loc_id = std.loc_id
   WHERE wm.datum BETWEEN '2012-8-1' AND '2012-12-1' 
   GROUP BY name
   ORDER BY MAX(max_temp_2m) DESC 
   LIMIT 1) as subselect
ORDER BY temp desc
LIMIT 1

出于性能原因，始终使用显式连接作为 LEFT、RIGHT、INNER JOIN，并避免使用具有分隔表名的连接，因此您的 sql serevr 不必猜测您的表引用。