MS SQL Server
  1. MS SQL Server
  3. Calcul de la médiane avec PERCENTILE_CONT et regroupement
Intereting Posts
Quel est le problème avec la requête de sorting de date suivante Pourquoi ne puis-je pas insert un logging dans ma database SQL Compact 3.5? Déclencheur MS SQL Server pour mettre à jour le classment des éléments et le nombre de votes Comment puis-je interroger la culture de database (SQL Server 2005)? Comment convertir des valeurs en colonnes et y append des numbers? Brouiller datatables SEULEMENT dans une colonne particulière, les autres colonnes inchangées Meilleure list à utiliser lors de la command Modifier la colonne Type de données Php SELECT-query returnnant null sur les champs qui ne sont pas null La règle ou le triggersur provoque une erreur de dépassement inattendue SQL Server: remplacez la séquence des mêmes caractères dans le champ de text (TSQL uniquement) Relation récursive plusieurs-à-plusieurs sans entité principale Les caractères avec des accents ne sont pas interprétés correctement lors de la mise à jour sur le server sql Recherche DNS dans SQL Server Trouver le nom de la database actuelle

Calcul de la médiane avec PERCENTILE_CONT et regroupement

J'ai besoin de calculer le nombre médian de jours entre la date oppo_opened et la date oppo_offered sur une table appelée Opportunity , regroupant les résultats par une Company table jointe. Faire la moyenne est très facile: 

 ----Average Opened to Offered last 12 months Select Comp_Name,AVG(DATEDIFF(day,Oppo_Opened,oppo_offerDate)) as averageDTO from company join Opportunity on oppo_lender = Comp_CompanyId where DATEADD(Year,-1,GETDATE()) > Oppo_Opened AND oppo_offerDate is not null group by Comp_Name order by averageDTO desc

Ce qui précède renvoie une ligne par entreprise, car nous sums en Comp_Name regrouper par Comp_Name .

Ce n'est pas si facile quand on fait ça pour une médiane. J'ai localisé le post suivant:

Fonction pour calculer la médiane dans SQL Server

Et cela m'a amené à ici: 

 SELECT Comp_Name, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF(day,Oppo_Opened,oppo_offerDate)) OVER (PARTITION BY Comp_Name) AS MedianCont from company inner join Opportunity on oppo_lender = Comp_CompanyId where DATEADD(Year,-1,GETDATE()) > Oppo_Opened AND oppo_offerDate is not null order by MedianCont desc

Toutefois, cela renvoie une ligne par logging d' Opportunity et non une ligne par logging de Company . Je ne peux pas faire un simple group by Comp_Name sur ce soit comme nous obtenons alors:

Msg 8120, niveau 16, état 1, ligne 3

La colonne "Opportunité.Oppo_Opened" n'est pas valide dans la list de sélection car elle n'est contenue ni dans une fonction d'agrégat ni dans la clause GROUP BY.

Msg 8120, niveau 16, état 1, ligne 3

La colonne "Opportunity.oppo_offerDate" n'est pas valide dans la list de sélection car elle n'est contenue ni dans une fonction d'agrégat ni dans la clause GROUP BY.

Et mettre une méthode agrégée sur l'un ou l'autre de ce qui précède suppose que je vais bousiller mon calcul médian.

Ma question: Comment puis-je regrouper par la partition PERCENTILE_CONT pour get une ligne par Company (je devrais get 209 lignes) par opposition à une ligne par Opportunity (au nord de 30k lignes)?

J'ai adopté une approche différente, ce qui me permettra de mettre en évidence et d'expliquer comment la médiane est calculée.

Je devrais dire que la question précédente que vous avez examinée contient une meilleure discussion sur la performance et les mérites relatifs des différents calculs médians. Cette méthode n'est probablement pas la plus efficace. Cependant j'espère que ce sera plus facile à suivre et à modifier selon vos besoins.

Mon exemple utilise datatables définies ci-dessous. Il y a deux entresockets A et B. A a une médiane de 4 et B a une médiane de 5.

Exemple de données 

 /* Using a table variable to create shared * sample data. */ DECLARE @Sample TABLE ( Company VARCHAR(1), [Value] INT ) ; INSERT INTO @Sample ( Company, [Value] ) VALUES ('A', 2), -- Median value for Company A: {2, 4, 6} = 4. ('A', 4), ('A', 6), ('B', 2), -- Median records for Company B: {2, 4, 6, 8} = {4, 6}. ('B', 4), -- Median value = {4, 6} / 2 = 5. ('B', 6), ('B', 8) ;

J'ai basé mon calcul médian sur ce Wikipédia . Pour tout set avec un nombre impair d'loggings, j'ai pris l'input du milieu, lorsqu'il est sortingé par valeur. Pour les sets avec un nombre pair d'loggings, j'ai pris les deux loggings du milieu, à nouveau sortingés par valeur, et fait la moyenne du résultat.

Exemples

Impair 

 {3, 1, 2} = {1, 2, 3} Sort. {1, 2, 3} = 2 Find middle entry. 2 Median.

Même 

 {4, 3, 1, 2} = {1, 2, 3, 4} Sort. {1, 2, 3, 4} = {2, 3} Find middle values. {2, 3} / 2 = 2.5 Average. 2.5 Median.

Question 

 SELECT r.Company, AVG(r.[Value]) FROM ( /* You cannot use windowed functions directly in a WHERE clause. * This subquery makes those fields available to all clauses in the * outer query. */ SELECT ROW_NUMBER() OVER (PARTITION BY s.Company ORDER BY s.[Value]) AS RecordNumber, COUNT(*) OVER (PARTITION BY s.Company) AS CompanyRecordCount, CAST((COUNT(*) OVER (PARTITION BY s.Company)) AS DECIMAL(9, 1)) / 2.0 AS MedianPoint, COUNT(*) OVER (PARTITION BY s.Company) % 2 AS IsOdd, s.Company, s.[Value] FROM @Sample AS s ) AS r WHERE ( -- When company has odd number of records median is the middle record. r.IsOdd = 1 AND r.RecordNumber = CEILING(MedianPoint) ) OR ( -- When company has even number of records median is avg of two middle records. r.IsOdd = 0 AND r.RecordNumber IN (MedianPoint, MedianPoint + 1) ) GROUP BY r.Company ;

J'ai utilisé ROW_NUMBER pour numéroter chaque logging, dans chaque société, sortingé par valeur (RecordNumber).

J'ai combiné COUNT avec la clause OVER pour renvoyer le nombre d'loggings pour chaque société (CompanyRecordCount).

J'ai divisé par deux le nombre d'loggings pour chaque entreprise, pour find le point médian (MedianPoint). Nous allons utiliser cela pour filterr les loggings médians plus tard. J'ai également calculé le rest (IsOdd). Ceci sera utilisé pour déterminer quel calcul appliquer.

La clause WHERE filter les loggings impairs pour le CEILING du MedianPoint. Le plafond arrondit à l'entier le plus proche, et dans le cas de nombres impairs, il s'agit toujours de l'logging dont vous avez besoin. Si vous avez 3 loggings, le point médian est 1.5 et vous voulez save l'logging # 2.

Pour les loggings pairs, la clause WHERE renvoie l'logging médian et le suivant dans la séquence. 4 loggings a un point médian de 2. Nous voulons les loggings 2 et 3.

Enfin, la requête externe fait la moyenne du résultat. Dans le cas d'loggings impairs, il n'y a qu'une valeur moyenne, donc elle rest inchangée. Pour evens il y en a deux et la médiane est calculée en divisant l'un par l'autre.