Historique de la question
Les moyennes pondérées dans le temps sont devenues un indicateur critique dans l'IoT industriel et l'analyse des séries temporelles financières. Les moyennes arithmétiques simples déforment la réalité car les valeurs des capteurs persistent jusqu'à la prochaine mesure. Avant la norme ANSI SQL:2003, le calcul de ces moyennes nécessitait des curseurs procéduraux ou des auto-jointures coûteuses. Ces méthodes exécutaient en complexité temporelle O(n²).
L'introduction des fonctions de fenêtre LEAD et LAG a révolutionné ce domaine. Elles ont permis des calculs d'intervalle en un seul passage, basés sur des ensembles, qui s'exécutent en O(n). Cela rend l'analyse en temps réel sur des milliards de lignes réalisable au sein de la couche de base de données.
Le problème
Étant donné une table readings avec les colonnes device_id, ts (timestamp) et value, l'objectif est de calculer une moyenne pondérée. Chaque ligne doit contribuer proportionnellement à la différence de temps jusqu'à la prochaine lecture. Mathématiquement, cela s'exprime par $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$.
La dernière ligne présente une condition aux limites. Elle n'a pas de timestamp suivant, donc son intervalle doit être défini soit comme zéro, soit extrapolé à un temps actuel, soit limité à un temps de fin connu. La solution doit éviter les curseurs, les fonctions définies par l'utilisateur ou les auto-jointures pour rester purement déclarative.
La solution
Utilisez la fonction de fenêtre LEAD pour projeter le prochain timestamp dans la ligne actuelle. Calculez la différence d'époque pour en tirer le poids. Ensuite, appliquez les formules de moyenne pondérée standard.
WITH weighted AS ( SELECT device_id, value, ts, COALESCE( EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY device_id ORDER BY ts) - ts)), 0 ) AS duration_seconds FROM readings ) SELECT device_id, SUM(value * duration_seconds) / NULLIF(SUM(duration_seconds), 0) AS time_weighted_avg FROM weighted GROUP BY device_id;
Cette approche utilise PARTITION BY pour s'assurer que la fenêtre se réinitialise par appareil. Cela prévient l'intercalation des timestamps de différents capteurs. Le COALESCE gère la ligne terminale en assignant un poids nul, l'excluant effectivement du dénominateur.
Une ligne de production pharmaceutique surveille 200 bioréacteurs. Chacun émet des données de température à des intervalles irréguliers—toutes les 10 secondes pendant les phases de chauffage, mais toutes les 30 minutes pendant les périodes d'attente. L'équipe qualité exigeait une moyenne pondérée quotidienne pour garantir la conformité. Une simple moyenne surévaluerait les échantillons de chauffage rapides et sous-évaluerait les périodes stables, masquant potentiellement des excursions de température dangereuses.
Une solution proposée consistait à extraire toutes les données dans un DataFrame Python pandas. Les ingénieurs calculeraient diff() sur les timestamps et computationneraient la moyenne pondérée. Bien que flexible, cette approche transférait des gigaoctets de données à travers le réseau. Elle a également provoqué le crash de la station de travail d'analyse lors du traitement des rapports de fin de trimestre s'étalant sur 90 jours de données à haute fréquence.
Une autre alternative utilisait une sous-requête corrélée pour trouver le MIN(ts) supérieur à la ligne actuelle pour chaque appareil. Cela s'exécutait correctement sur des ensembles de test de 1 000 lignes. Cependant, il a montré une dégradation quadratique, prenant 45 minutes pour l'historique complet d'un seul réacteur.
L'équipe a choisi l'approche des fonctions de fenêtre ANSI SQL. En gardant le calcul à l'intérieur du cluster PostgreSQL, la requête a tiré parti des scans séquentiels parallèles et a évité la surcharge réseau. L'implémentation finale a traité 50 millions de lignes sur tous les réacteurs en moins de 12 secondes. Cela a permis des mises à jour du tableau de bord en temps réel qui ont permis aux opérateurs de repérer les dérives thermiques en quelques minutes plutôt qu'en quelques heures.
Comment gérez-vous l'observation finale dans chaque partition où aucun timestamp suivant n'existe pour définir le poids de l'intervalle ?
Les candidats omettent souvent la condition aux limites. Cela provoque une évaluation de l'intervalle de la ligne terminale comme NULL, que les agrégats SQL ignorent. Par conséquent, la contribution de la lecture finale est supprimée, faussant la moyenne. L'approche correcte utilise COALESCE pour substituer soit zéro, soit EXTRACT(EPOCH FROM (boundary_time - ts)) si la moyenne doit s'étendre à un temps de fin connu comme CURRENT_TIMESTAMP.
Pourquoi la formule SUM(value * duration) / SUM(duration) représente-t-elle mathématiquement la moyenne pondérée dans le temps, et que se passe-t-il si vous utilisez AVG(value) à la place ?
Cela calcule la moyenne arithmétique pondérée où la durée sert de poids $w_i$. Les candidats confondent souvent cela avec une moyenne géométrique ou tentent d'utiliser AVG(value * duration), ce qui produit une somme de produits sans normalisation. L'utilisation de AVG(value) traite chaque ligne de manière égale, supposant des pas de temps uniformes, ce qui viole l'exigence selon laquelle des valeurs plus durables exercent une plus grande influence.
Comment les lacunes temporelles ou les timestamps dupliqués dans la même partition affectent-ils le comportement de la fonction LEAD, et pourquoi est-il essentiel d'utiliser PARTITION BY device_id ?
Les candidats oublient parfois que LEAD opère sur l'ordre physique des lignes dans la spécification de la fenêtre. Sans PARTITION BY device_id, la fonction calcule les intervalles entre différents capteurs, créant des durées négatives ou massives sans sens. De plus, si des timestamps dupliqués existent, LEAD renvoie la prochaine ligne distincte, quelle qu'elle soit, ce qui peut potentiellement créer des intervalles de zéro seconde. Les candidats doivent décider s'ils veulent d'abord dédupliquer en utilisant DISTINCT ou un filtrage ROW_NUMBER() pour éviter les erreurs de division par zéro.