MS SQL Server
  1. MS SQL Server
  3. Aujourd'hui contre le même jour les 3 dernières semaines
Intereting Posts
Fusionner les lignes de la table T-SQL – Stuff to CSV pour seulement certains groupes Insérer une requête en utilisant jdbctemplate donne l'exception UncategorizedSQLException MS SQL Server 2005 Copiez datatables d'une table à une autre Rediriger la sortie de l'installation de SQL Server silencieuse vers l'erreur standard en C #? Comment sélectionner les 5 premières lignes avec l'identifiant spécifié et la dernière date? SQL Server: INSERT multiple avec MERGE Suivi du stream de noeuds XML dans SQL Server SqlServer et nvarchar (max) Quelle est la meilleure façon de stocker des données historiques dans SQL Server 2005/2008? Version 64 bits de SQL Server Report Builder Quels critères sont requirejs pour que les champs soient assimilés? Recherche de tables et de colonnes nommées comme mots-keys Relier deux colonnes de key étrangère dans le tableau A à une colonne de key primaire dans le tableau B Créer un file "dbf" dans SQL Server ou C #

Aujourd'hui contre le même jour les 3 dernières semaines

J'ai des données comme dans l'exemple ci-dessous, chaque cellule est le nombre d'events. 

| 29/09/2016 | 06/10/2016 | 13/10/2016 | Today | Hour | |------------|------------|------------|-------|------| | 470 | 649 | 652 | 140 | 0 | | 159 | 322 | 354 | 925 | 1 | | 874 | 399 | 412 | 168 | 2 | | 699 | 312 | 69 | 51 | 3 | | 911 | 908 | 780 | 322 | 4 | | 756 | 805 | 579 | 161 | 5 | | 653 | 295 | 900 | 885 | 6 | | 637 | 699 | 221 | 310 | 7 | | 227 | 508 | 382 | 655 | 8 | | 823 | 782 | 437 | 278 | 9 | | 389 | 34 | 95 | 754 | 10 | | 229 | 309 | 29 | 945 | 11 | | 163 | 16 | 593 | 968 | 12 | | 833 | 953 | 767 | 438 | 13 | | 153 | 356 | 619 | 741 | 14 | | 335 | 493 | 511 | 877 | 15 | | 469 | 353 | 643 | 431 | 16 | | 610 | 137 | 121 | 647 | 17 | | 411 | 951 | 674 | 57 | 18 | | 593 | 854 | 500 | 380 | 19 | | 16 | 608 | 445 | 889 | 20 | | 764 | 143 | 734 | 178 | 21 | | 709 | 845 | 297 | 292 | 22 | | 894 | 355 | 309 | 98 | 23 |

J'ai besoin de comparer, par heure, le nombre d'events d'aujourd'hui vs moy 3 derniers jours même jour – même heure vs std vs Avg + std vs Avg-std. 

 | Today-Std | Today+Std | Std | Average | Today | Hour | |-----------|-----------|-----|---------|-------|------| | -127 | 407 | 267 | 590 | 140 | 0 | | 654 | 1196 | 271 | 278 | 925 | 1 | | -105 | 441 | 273 | 562 | 168 | 2 | | -224 | 326 | 275 | 360 | 51 | 3 | | 49 | 595 | 273 | 866 | 322 | 4 | | -106 | 428 | 267 | 713 | 161 | 5 | | 617 | 1153 | 268 | 616 | 885 | 6 | | 43 | 577 | 267 | 519 | 310 | 7 | | 385 | 925 | 270 | 372 | 655 | 8 | | 2 | 554 | 276 | 681 | 278 | 9 | | 478 | 1030 | 276 | 173 | 754 | 10 | | 674 | 1216 | 271 | 189 | 945 | 11 | | 702 | 1234 | 266 | 257 | 968 | 12 | | 182 | 694 | 256 | 851 | 438 | 13 | | 496 | 986 | 245 | 376 | 741 | 14 | | 630 | 1124 | 247 | 446 | 877 | 15 | | 172 | 690 | 259 | 488 | 431 | 16 | | 374 | 920 | 273 | 289 | 647 | 17 | | -204 | 318 | 261 | 679 | 57 | 18 | | 118 | 642 | 262 | 649 | 380 | 19 | | 613 | 1165 | 276 | 356 | 889 | 20 | | -88 | 444 | 266 | 547 | 178 | 21 | | 37 | 547 | 255 | 617 | 292 | 22 | | -168 | 364 | 266 | 519 | 98 | 23 |

Enfin, j'ai besoin de créer un graphique Aujourd'hui vs Avg vs Avg + STD vs Avg-Std.

Besoin d'aide pour faire cela dans Google Bigquery ou SQL Server 2012

Merci

Ci-dessous est pour BigQuery Legacy SQL.

C'est trop simple je pense, donc je ne suis pas sûr que ce soit ce que vous cherchez: 

 SELECT Today - Std AS Std_Today, Today + Std AS Today_Std, Std, Average, Today, HOUR FROM ( SELECT INTEGER(ROUND(SQRT(((d1-Average)*(d1-Average) + (d2-Average)*(d2-Average) + (d3-Average)*(d3-Average))/2))) AS Std, Average , Today, HOUR FROM ( SELECT d1, d2, d3, Today, HOUR, INTEGER(ROUND((d1+d2+d3)/3)) AS Average FROM ( SELECT d1, d2, d3, Today, HOUR FROM (SELECT 470 AS d1, 649 AS d2, 652 AS d3, 140 AS Today, 0 AS HOUR),(SELECT 159 AS d1, 322 AS d2, 354 AS d3, 925 AS Today, 1 AS HOUR), (SELECT 874 AS d1, 399 AS d2, 412 AS d3, 168 AS Today, 2 AS HOUR),(SELECT 699 AS d1, 312 AS d2, 69 AS d3, 51 AS Today, 3 AS HOUR), (SELECT 911 AS d1, 908 AS d2, 780 AS d3, 322 AS Today, 4 AS HOUR),(SELECT 756 AS d1, 805 AS d2, 579 AS d3, 161 AS Today, 5 AS HOUR), (SELECT 653 AS d1, 295 AS d2, 900 AS d3, 885 AS Today, 6 AS HOUR),(SELECT 637 AS d1, 699 AS d2, 221 AS d3, 310 AS Today, 7 AS HOUR), (SELECT 227 AS d1, 508 AS d2, 382 AS d3, 655 AS Today, 8 AS HOUR),(SELECT 823 AS d1, 782 AS d2, 437 AS d3, 278 AS Today, 9 AS HOUR), (SELECT 389 AS d1, 34 AS d2, 95 AS d3, 754 AS Today, 10 AS HOUR),(SELECT 229 AS d1, 309 AS d2, 29 AS d3, 945 AS Today, 11 AS HOUR), (SELECT 163 AS d1, 16 AS d2, 593 AS d3, 968 AS Today, 12 AS HOUR),(SELECT 833 AS d1, 953 AS d2, 767 AS d3, 438 AS Today, 13 AS HOUR), (SELECT 153 AS d1, 356 AS d2, 619 AS d3, 741 AS Today, 14 AS HOUR),(SELECT 335 AS d1, 493 AS d2, 511 AS d3, 877 AS Today, 15 AS HOUR), (SELECT 469 AS d1, 353 AS d2, 643 AS d3, 431 AS Today, 16 AS HOUR),(SELECT 610 AS d1, 137 AS d2, 121 AS d3, 647 AS Today, 17 AS HOUR), (SELECT 411 AS d1, 951 AS d2, 674 AS d3, 57 AS Today, 18 AS HOUR),(SELECT 593 AS d1, 854 AS d2, 500 AS d3, 380 AS Today, 19 AS HOUR), (SELECT 16 AS d1, 608 AS d2, 445 AS d3, 889 AS Today, 20 AS HOUR),(SELECT 764 AS d1, 143 AS d2, 734 AS d3, 178 AS Today, 21 AS HOUR), (SELECT 709 AS d1, 845 AS d2, 297 AS d3, 292 AS Today, 22 AS HOUR),(SELECT 894 AS d1, 355 AS d2, 309 AS d3, 98 AS Today, 23 AS HOUR) ) as YourTable ) )

Peut être facilement modifié (si nécessaire) en SQL standard BigQuery (voir Activation de SQL standard et migration à partir de SQL hérité )

D'autre part …

vous pouvez utiliser du code plus élégant dans BigQuery Standard SQL 

 WITH YourTable AS ( SELECT 470 AS d1, 649 AS d2, 652 AS d3, 140 AS Today, 0 AS Hour UNION ALL SELECT 159 AS d1, 322 AS d2, 354 AS d3, 925 AS Today, 1 AS Hour UNION ALL SELECT 874 AS d1, 399 AS d2, 412 AS d3, 168 AS Today, 2 AS Hour UNION ALL SELECT 699 AS d1, 312 AS d2, 69 AS d3, 51 AS Today, 3 AS Hour UNION ALL SELECT 911 AS d1, 908 AS d2, 780 AS d3, 322 AS Today, 4 AS Hour UNION ALL SELECT 756 AS d1, 805 AS d2, 579 AS d3, 161 AS Today, 5 AS Hour UNION ALL SELECT 653 AS d1, 295 AS d2, 900 AS d3, 885 AS Today, 6 AS Hour UNION ALL SELECT 637 AS d1, 699 AS d2, 221 AS d3, 310 AS Today, 7 AS Hour UNION ALL SELECT 227 AS d1, 508 AS d2, 382 AS d3, 655 AS Today, 8 AS Hour UNION ALL SELECT 823 AS d1, 782 AS d2, 437 AS d3, 278 AS Today, 9 AS Hour UNION ALL SELECT 389 AS d1, 34 AS d2, 95 AS d3, 754 AS Today, 10 AS Hour UNION ALL SELECT 229 AS d1, 309 AS d2, 29 AS d3, 945 AS Today, 11 AS Hour UNION ALL SELECT 163 AS d1, 16 AS d2, 593 AS d3, 968 AS Today, 12 AS Hour UNION ALL SELECT 833 AS d1, 953 AS d2, 767 AS d3, 438 AS Today, 13 AS Hour UNION ALL SELECT 153 AS d1, 356 AS d2, 619 AS d3, 741 AS Today, 14 AS Hour UNION ALL SELECT 335 AS d1, 493 AS d2, 511 AS d3, 877 AS Today, 15 AS Hour UNION ALL SELECT 469 AS d1, 353 AS d2, 643 AS d3, 431 AS Today, 16 AS Hour UNION ALL SELECT 610 AS d1, 137 AS d2, 121 AS d3, 647 AS Today, 17 AS Hour UNION ALL SELECT 411 AS d1, 951 AS d2, 674 AS d3, 57 AS Today, 18 AS Hour UNION ALL SELECT 593 AS d1, 854 AS d2, 500 AS d3, 380 AS Today, 19 AS Hour UNION ALL SELECT 16 AS d1, 608 AS d2, 445 AS d3, 889 AS Today, 20 AS Hour UNION ALL SELECT 764 AS d1, 143 AS d2, 734 AS d3, 178 AS Today, 21 AS Hour UNION ALL SELECT 709 AS d1, 845 AS d2, 297 AS d3, 292 AS Today, 22 AS Hour UNION ALL SELECT 894 AS d1, 355 AS d2, 309 AS d3, 98 AS Today, 23 AS Hour ), tempTable AS ( SELECT (SELECT CAST(STDdev(d) AS INT64) FROM UNNEST([d1, d2, d3]) AS d) AS Std, (SELECT CAST(AVG(d) AS INT64) FROM UNNEST([d1, d2, d3]) AS d) AS Average , Today, Hour FROM YourTable ) SELECT Today - Std AS Std_Today, Today + Std AS Today_Std, Std, Average, Today, Hour FROM tempTable ORDER BY Hour

Lequel (assez marrant) …

me ramène à la version SQL Legacy (mais similaire à celle ci-dessus pour Standard) 

 SELECT Today - Std AS Std_Today, Today + Std AS Today_Std, Std, Average, Today, HOUR FROM ( SELECT INTEGER(STDDEV(d)) AS Std, INTEGER(AVG(d)) AS Average, Today, HOUR FROM ( SELECT Today, HOUR, SPLIT(CONCAT(STRING(d1), ",", STRING(d2), ",", STRING(d3))) AS d FROM ( SELECT d1, d2, d3, Today, HOUR FROM (SELECT 470 AS d1, 649 AS d2, 652 AS d3, 140 AS Today, 0 AS HOUR),(SELECT 159 AS d1, 322 AS d2, 354 AS d3, 925 AS Today, 1 AS HOUR), (SELECT 874 AS d1, 399 AS d2, 412 AS d3, 168 AS Today, 2 AS HOUR),(SELECT 699 AS d1, 312 AS d2, 69 AS d3, 51 AS Today, 3 AS HOUR), (SELECT 911 AS d1, 908 AS d2, 780 AS d3, 322 AS Today, 4 AS HOUR),(SELECT 756 AS d1, 805 AS d2, 579 AS d3, 161 AS Today, 5 AS HOUR), (SELECT 653 AS d1, 295 AS d2, 900 AS d3, 885 AS Today, 6 AS HOUR),(SELECT 637 AS d1, 699 AS d2, 221 AS d3, 310 AS Today, 7 AS HOUR), (SELECT 227 AS d1, 508 AS d2, 382 AS d3, 655 AS Today, 8 AS HOUR),(SELECT 823 AS d1, 782 AS d2, 437 AS d3, 278 AS Today, 9 AS HOUR), (SELECT 389 AS d1, 34 AS d2, 95 AS d3, 754 AS Today, 10 AS HOUR),(SELECT 229 AS d1, 309 AS d2, 29 AS d3, 945 AS Today, 11 AS HOUR), (SELECT 163 AS d1, 16 AS d2, 593 AS d3, 968 AS Today, 12 AS HOUR),(SELECT 833 AS d1, 953 AS d2, 767 AS d3, 438 AS Today, 13 AS HOUR), (SELECT 153 AS d1, 356 AS d2, 619 AS d3, 741 AS Today, 14 AS HOUR),(SELECT 335 AS d1, 493 AS d2, 511 AS d3, 877 AS Today, 15 AS HOUR), (SELECT 469 AS d1, 353 AS d2, 643 AS d3, 431 AS Today, 16 AS HOUR),(SELECT 610 AS d1, 137 AS d2, 121 AS d3, 647 AS Today, 17 AS HOUR), (SELECT 411 AS d1, 951 AS d2, 674 AS d3, 57 AS Today, 18 AS HOUR),(SELECT 593 AS d1, 854 AS d2, 500 AS d3, 380 AS Today, 19 AS HOUR), (SELECT 16 AS d1, 608 AS d2, 445 AS d3, 889 AS Today, 20 AS HOUR),(SELECT 764 AS d1, 143 AS d2, 734 AS d3, 178 AS Today, 21 AS HOUR), (SELECT 709 AS d1, 845 AS d2, 297 AS d3, 292 AS Today, 22 AS HOUR),(SELECT 894 AS d1, 355 AS d2, 309 AS d3, 98 AS Today, 23 AS HOUR) ) AS YourTable ) GROUP BY Today, HOUR )

ci-dessous est d'adresser des commentaires sur ma réponse originale – il est arrivé que le schéma de données dans la question initiale était incorrect, mais plutôt est comme ci-dessous: 

 events hour date 649 0 06/10/2016 652 0 13/10/2016 140 0 20/10/2016 470 0 29/09/2016 322 1 06/10/2016 354 1 13/10/2016 925 1 20/10/2016 159 1 29/09/2016 399 2 06/10/2016

Donc, en supposant que les dates sont stockées en tant que strings avec le format "jj / mm / aaaa" – ci-dessous est la requête pour BigQuery SQL standard (voir Activation de SQL standard et migration à partir de SQL hérité ) 

 SELECT Date, Hour, Events, Average, Std, events - Std AS Std_Events, events + Std AS Events_Std FROM ( SELECT x.DATE, x.hour, x.events, CAST(STDDEV(z.events) AS INT64) AS Std, CAST(AVG(z.events) AS INT64) Average FROM yourTable AS x JOIN (SELECT '20/10/2016' AS xDay) AS y -- use this line to set specific day to treat as today --JOIN (SELECT FORMAT_DATE('%d/%m/%Y', DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)) AS xDay) AS y -- use this line to do analysis for yesterday ON x.date = xDay JOIN yourTable as z ON MOD(DATE_DIFF(PARSE_DATE('%d/%m/%Y', xDay), PARSE_DATE('%d/%m/%Y', z.Date), DAY), 7) = 0 AND DIV(DATE_DIFF(PARSE_DATE('%d/%m/%Y', xDay), PARSE_DATE('%d/%m/%Y', z.Date), DAY), 7) BETWEEN 1 AND 3 AND z.hour = x.hour GROUP BY 1, 2, 3 ) ORDER BY hour

Vous pouvez transformer 

  JOIN (SELECT '20/10/2016' AS xDay) AS y

avoir quelques dates que vous devez parsingr en une fois – par exemple 

  JOIN (SELECT '19/10/2016' AS xDay UNION ALL SELECT '20/10/2016' AS xDay) AS y