2
votes

SQL LIMIT dynamique N

Je veux obtenir la température moyenne de l'air (dernière ligne) de toutes les stations qui ont le numéro de comté spécifié.

Pour cette raison, ma solution serait quelque chose comme

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| station_id    | char(20)    | NO   | PRI | NULL    |       |
| county_number | int(10)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

Clairement , cela ne donne pas la ligne correcte car il renvoie la température de l'air moyenne basée sur toute la température de l'air jamais enregistrée d'une station.

De retour au problème, je veux obtenir la température moyenne de l'air sur la dernière ligne insérée de chaque station ayant le numéro de comté spécifié.

Table weather

+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| station_id       | char(20)    | YES  | MUL | NULL    |                |
| timestamp        | timestamp   | YES  |     | NULL    |                |
| air_temperature  | float       | YES  |     | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

Stations de table

SELECT AVG(air_temperature) 
  FROM weather 
 WHERE station_id IN (
       SELECT station_id 
         FROM stations 
        WHERE county_number = 25
       )
 ORDER 
    BY id DESC 
 LIMIT 1;

Les tables sont réduites

sql aggregate-functions mysql group-by

0 commentaires

3 Réponses :

0
votes

Je recommanderais de le faire avec une jointure et un peu de filtrage:

select avg(w.air_temperature)
from weather w join
     stations s
     on w.station_id = s.station_id
where s.county_number = 25 and
      w.timestamp = (select max(w2.timestamp) from weather w2 where w2.station_id = w.station_id)

0 commentaires

0
votes

Vous pouvez obtenir la dernière ligne insérée en vérifiant le max(timestamp):

SELECT 
  AVG(w.air_temperature) 
FROM weather w 
INNER JOIN (
  SELECT station_id, max(timestamp) maxtimestamp FROM weather GROUP BY station_id        
) t
ON w.station_id = t.station_id AND w.timestamp = t.maxtimestamp
WHERE 
  w.station_id IN (SELECT station_id FROM stations WHERE county_number = 25)

0 commentaires

0
votes

MISE À JOUR: Je viens de remarquer que votre colonne timestamp est nullable et que vous parlez de la "dernière ligne insérée". C'est celui qui a le plus grand ID. Par conséquent:

Depuis MySQL 8, vous pouvez utiliser les fonctions de fenêtre afin de ne lire le tableau qu'une seule fois:

select avg(air_temperature) 
from weather 
where (station_id, id) in
(
  select station_id, max(id)
  from weather 
  where station_id in (select station_id from stations where county_number = 25)
  group by station_id
);

Dans les anciennes versions, vous devez lire le tableau deux fois: p>

select avg(air_temperature) 
from
(
  select air_temperature, id, max(id) over (partition by station_id) as max_id
  from weather 
  where station_id in (select station_id from stations where county_number = 25)
) analyzed
where id = max_id;

0 commentaires