MS SQL Server
  1. MS SQL Server
  3. Requête SQL pour calculer des calculs à partir de différentes lignes de la même table dans le server SQL
Intereting Posts
Centre ou centre de masse pour les points de geometry dans SQL Server Créer une requête arborescente à partir d'une table de mappage numérique dans SQL (format spécifique) Des extraits de code SQL SERVER 2014 manquant Convertir Binary 010101 en Lat OU Long en utilisant MSSQL Comment déboguer à distance une procédure stockée dans le même domaine? Joignez le résultat d'une colonne Temp à une table SQL Server Erreur SQL Server XQuery: un noeud ou un set de noeuds est requirejs pour number () Comment puis-je montrer tout le time entre 2 parameters de time différents Requête SQL pour find le nombre d'occurrences multiples des valeurs de colonne d'une table? Comment déclarer un espace de noms XML en SQL? Comparaison des résultats de requête SQL CAS ou ET / OU dans la condition WHERE. Quelle est la meilleure pratique? SQL trouve une mauvaise combinaison SQL Server Management Studio: import discrètement en ignorant 99,9% des données Aide pour l'utilisation (images exernales) du site Web avec SSRS

Requête SQL pour calculer des calculs à partir de différentes lignes de la même table dans le server SQL

Je veux une requête SQL pour ce qui suit. Je suis un novice en SQL. Et ci-dessous tableau est juste un exemple du type de données que j'ai. J'ai de très grosses données d'environ 30 millions de lignes et j'aimerais écrire une requête pour get la table de sortie ci-dessous. 

Id type data time ----------------------------------------------------------- 1 30 3.9 15:50:10.660555 1 30 4.0 15:50:10.660777 1 70 11.5 15:50:10.797966 1 30 4.1 15:50:10.834444 1 70 12.6 15:50:10.853114 1 70 16.7 15:50:10.955086 1 30 5 15:50:10.99 11 30 3.8 15:50:11.660555 11 30 4.1 15:50:11.660777 11 70 12.5 15:50:11.797966 11 30 4.7 15:50:11.834444 11 70 12.68 15:50:11.853114 11 70 16.76 15:50:11.955086 11 30 5.1 15:50:11.99

J'ai une table comme ci-dessus. Pour chaque type 70, j'ai besoin de calculer quelque chose avec le dernier type connu 30. Par exemple, pour Id = 1, pour le premier type = 70 données à 15: 50: 10.797966, je dois get type = 30 data à 15: 50: 10.660777 afin que je puisse calculer le résultat = 11.5 / 4.0. De même, pour le type = 70 à 15: 50: 10.853114, je veux des données de type = 30 à 15: 50: 10.834444, donc mon résultat = 12.6 / 4.1.

Je veux que la sortie ressemble à ceci: 

 Id type result time ------------------------------------------------------ 1 70 11.5/4.0 15:50:10.797966 1 70 12.6/4.1 15:50:10.853114 1 70 16.7/4.1 15:50:10.955086 11 70 12.5/4.1 15:50:11.797966 11 70 12.68/4.7 15:50:11.853114 11 70 16.76/4.7 15:50:11.955086

Je voudrais pouvoir exécuter ces requêtes SQL en python en utilisant pyodbc.

Toute aide serait grandement appréciée! Merci d'avance!!

En supposant qu'il y ait au less un type = 30 rangée type précédent = 70 par id, vous pouvez le faire en utilisant outer apply , obtenant le time max pour type = 30 avant chaque type = 70 row et en utilisant cette valeur pour la division. 

 SELECT x.id, x.type, x.time, x.data*1.0/t.data as result FROM (SELECT t.*,t1.maxtime_before FROM t OUTER APPLY (SELECT max(time) AS maxtime_before FROM t t1 WHERE t1.id=t.id AND t1.type=30 AND t1.time<t.time) t1 WHERE type = 70 ) x JOIN t ON t.id=x.id AND t.time=x.maxtime_before

S'il n'y a pas de ligne avec le type = 30 précédant une ligne de type = 70, vous pouvez afficher une valeur null pour cette heure dans la colonne de résultat en utilisant 

 WITH x AS (SELECT t.*, t1.maxtime_before FROM t OUTER APPLY (SELECT max(time) AS maxtime_before FROM t t1 WHERE t1.id=t.id AND t1.type=30 AND t1.time<t.time) t1 WHERE type = 70 ) SELECT x.id, x.type, x.time, x.data*1.0/t.data as resullt FROM t JOIN x ON t.id=x.id AND t.time=x.maxtime_before UNION ALL SELECT id, type, time, NULL FROM x WHERE maxtime_before IS NULL

Sample Demo

Une autre façon de le faire est d'utiliser la fonction max window pour garder trace de l'heure max de type = 30 row par id. 

 WITH x AS (SELECT t.*, MAX(CASE WHEN type=30 THEN time END) OVER(PARTITION BY id ORDER BY time) AS running_max FROM t ) SELECT x.id, x.type, x.time, x.data*1.0/t.data as result FROM x JOIN t ON t.id=x.id AND t.time=x.running_max WHERE x.type=70 UNION ALL SELECT id, type, time, NULL FROM x WHERE running_max IS NULL

Il y a un moyen de le faire avec seulement des fonctions de window.

Récupérez le type et la valeur précédents pour chaque ligne. En outre, énumérer les années 70 de telle sorte que vous pouvez les identifier en tant que groupe (vous pouvez le faire avec une sum cumulative).

Dans l'étape suivante, utilisez un max partitionné pour get le type et enfin faire votre calcul. 

 select t.*, data / data_30 as result from (select t.*, max(case when prev_type = 30 then prev_data end) over (partition by id, grp) as data_30 from (select t.*, sum(case when type <> 70 then 1 else 0 end) over (partition by id order by time) as grp, lag(type) over (partition by id order by time) as prev_type, lag(data) over (partition by id order by time) as prev_data from t where type in (30, 70) ) t ) t;

Un aspect intéressant de ceci. En limitant les types seulement à 30 et 70, nous garantissons que chaque groupe de 70 est directement précédé d'un 30.