MS SQL Server
  1. MS SQL Server
  3. Comment calculer 90e percentile, écart-type, moyenne pour datatables dans SQL
Intereting Posts
Comment insert en bloc 20 files 100mb CSV dans SQL Server sql – left join – count Problème de réplication SQL Server 2008 R2 Mise en cache de SessionFactory et configuration pour plusieurs bases de données avec NHibernate Pour chaque catégorie, affichez sa sous-catégorie SQL: extraire la date de la string Comment puis-je modifier la clause ORDER BY en fonction des parameters transmis dans la procédure stockée MS TSQL pour la question du path xml concernant l'élément xpath En utilisant la requête FTS, pouvez-vous find toutes les inputs contient 'abc' Y at-il une meilleure façon de valider SqlXml dans CLR? Définir le résultat EXECUTE sp_executesql dans une variable en SQL SQL Server: supprimer tous les préfixes, split le prénom et le second nom – Améliorer la qualité du code Reformatez la table existante à partir des colonnes appariées en lignes Obtenir le timeout d'expiration expire erreur à l'aide du lecteur de données Résultat de requête incompréhensible recherchant des lignes dans une requête qui ne sont pas dans une autre requête

Comment calculer 90e percentile, écart-type, moyenne pour datatables dans SQL

Salut, j'ai une installation de table. Qui détient un score pour chaque jour (plusieurs scores peuvent être rapportés chaque jour et les deux seraient valides)

J'ai besoin de calculer le 90e percentile, SD, et Mean pour le score par mois.

Établissement: 

Id Month Date score 1 Jan 1 5 1 Jan 1 5 1 Jan 2 3 1 Jan 3 4 1 Jan 4 4 1 Jan 5 4 1 Feb 1 5 1 Feb 1 5 1 Feb 2 3 1 Feb 3 4 1 Feb 4 4 1 Feb 5 4

Y a-t-il un moyen?

Merci de votre aide.

Vous pouvez utiliser la nouvelle suite de fonctions analytiques introduite dans SQL Server 2012: 

 SELECT DISTINCT [Month], Mean = AVG(Score) OVER (PARTITION BY [Month]), StdDev = STDEV(Score) OVER (PARTITION BY [Month]), P90 = PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Score) OVER (PARTITION BY [Month]) FROM my_table

Il y a 2 fonctions de percentile: PERCENTILE_CONT pour la dissortingbution continue et PERCENTILE_DISC pour la dissortingbution discrète. Choisissez celui qui convient à vos besoins.

Voici la configuration … 

 CREATE TABLE Facility (Id INT NOT NULL, Month nvarchar(3) NOT NULL, Date INT NOT NULL, score INT NOT NULL) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 1, 5) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 1, 5) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 2, 3) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 3, 4) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 4, 4) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 5, 4) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 1, 5) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 1, 5) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 2, 3) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 3, 4) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 4, 4) INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 5, 4)

Maintenant, l'écart-type et la moyenne sont assez simples – il y a des fonctions agrégées pour eux … 

 SELECT [Month], AVG(CONVERT(real, score)) AS [Mean], STDEV(score) AS [Standard Deviation] FROM Facility GROUP BY [Month]

Pour votre 90e centile, vous devrez inventer une fonction … 

 CREATE FUNCTION NintythPercentile(@Month nvarchar(3)) RETURNS INT AS BEGIN DECLARE @ReturnValue INT SELECT @ReturnValue = MIN(DerivedTopTenPercent.score) --AS [90th Percentile] FROM ( SELECT TOP 10 PERCENT score FROM Facility WHERE [Month] = @Month ORDER BY score DESC ) DerivedTopTenPercent RETURN @ReturnValue END

Avec cette fonction en place, votre requête finale ressemblera à ceci … 

 SELECT [Month], AVG(CONVERT(real, score)) AS [Mean], STDEV(score) AS [Standard Deviation], dbo.NintythPercentile([Month]) AS [90th Percentile] FROM Facility GROUP BY [Month]