11
votes

Dérivé discret dans SQL

J'ai des données de capteur dans une table sous la forme:

Starttime Endtime   Change
10        20        100
20        36        130
36        46        110

postgresql sql time-series

2 commentaires

Ferait une excellente question d'entrevue: trivial de formuler mais non trivial à réparer.

+1 Pour le fait que vous soyez assez intelligent pour ne pas simplement le faire comme vous le savez déjà et posez la question.

5 Réponses :

0
votes

Tout d'abord, j'ajouterais une colonne d'identité à la table pour que vous ayez quelque chose qui augmente de manière prévisible de la ligne à la ligne.

Alors, je voudrais essayer la requête suivante: P>

SELECT t1.Time AS 'Starttime', t2.Time AS 'Endtime',
    (t2.Value - t1.Value) AS 'Change'
FROM SensorData t1
INNER JOIN SensorData t2 ON (t2.id - 1) = t1.id
ORDER BY t1.Time ASC

2 commentaires

Cela ne fonctionnera que si des lignes sont insérées dans l'ordre (par Starttime), et aucune ligne ne sera jamais supprimée; Supprimer une ligne entraînera une lacune pour que T2.Id - T1.Id sera> 1

En effet, vous avez raison, mais étant donné la nature des données (c.-à-d d'un capteur), je m'attendrais à ce que les lignes soient correctives correctement et qu'une ligne supprimée serait improbable (je suppose) car elle serait effectivement une lacune dans le Les données.

4
votes

Select a.Time as StartTime, b.time as EndTime, b.time-a.time as TimeChange, b.value-a.value as ValueChange
FROM YourTable a, YourTable b
WHERE b.time = (Select MIN(c.time) FROM YourTable c WHERE c.time>a.time)

2 commentaires

Testé cela sur ma copie de la table et cela fonctionne également. J'aime l'ajout du changement de temps ainsi que le changement de valeur :)

Voir aussi mon autre réponse, cela évite la sous-requête.

3
votes

Select a.Time as StartTime
     , b.time as EndTime
     , b.time-a.time as TimeChange
     , b.value-a.value as ValueChange
FROM YourTable a 
Left outer Join YourTable b ON b.time>a.time
Left outer Join YourTable c ON c.time<b.time AND c.time > a.time
Where c.time is null
Order By a.time

1 commentaires

Cela me semble un peu inefficace. Est-ce que ça a du sens à faire 2 jointures quand une jointure serait suffisante? Voir cette réponse Stackoverflow.com/Questtions/6299950/sql-Difference-between -r ows

0
votes

Est-ce que cela fonctionne?

WITH T AS
(
SELECT [Time]
    ,  Value
    ,  RN1 =  ROW_NUMBER() OVER (ORDER BY [Time])
    ,  RN2 =  ROW_NUMBER() OVER (ORDER BY [Time]) - 1   
FROM  SensorData
)
SELECT  
   StartTime = ISNULL(t1.[time], t2.[time])
 , EndTime = ISNULL(t2.[time], 0)
 , Change = t2.value - t1.value

FROM    T t1 
    LEFT OUTER JOIN 
        T t2

ON t1.RN1 = t2.RN2

1 commentaires

Travaillerait pour SQL Server, mais je réalise maintenant que ce n'est pas étiqueté.

1
votes

Vous pouvez utiliser une fonction de fenêtre SQL, ci-dessous est un exemple basé sur la syntaxe de bigquery.

SELECT 
  LAG(time, 1) OVER (BY time) AS start_time,
  time AS end_time, 
  (value - LAG(value, 1) OVER (BY time))/value AS Change
from data

1 commentaires

sur (commande par heure) pour postgreSQL.


            
                
		    
                    Articles qui pourrait vous intéresser :
                                            
                            Laravel whereRaw - comment échapper à un '?'                        
                        
                                            
                            Pgadmin ne se charge pas                        
                        
                                            
                            Interroger les données de PostgreSQL à partir de deux tables                        
                        
                                            
                            Erreur: impossible d'insérer plusieurs commandes dans une instruction préparée