Moyenne pondérée en T-SQL (comme SUMPRODUCT d'Excel)

Je suis à la recherche d'un moyen de calculer une moyenne pondérée de deux lignes de données avec le même nombre de colonnes, où la moyenne s'établit comme suit (emprunt Excel notation):

(A1*B1)+(A2*B2)+...+(An*Bn)/SUM(A1:An)

La première partie reflète les mêmes fonctionnalités que d'Excel SUMPRODUCT() fonction.

Mon hic, c'est que j'ai besoin de manière dynamique spécifier la ligne qui obtient une moyenne avec des poids, et la ligne qui les pondérations viennent, et une plage de dates.

EDIT: C'est plus facile que je pensais, parce que Excel me faisait penser-je une sorte de pivot. Ma solution à ce jour est donc:

select sum(baseSeries.Actual * weightSeries.Actual) / sum(weightSeries.Actual)
from (
    select RecordDate , Actual 
    from CalcProductionRecords 
    where KPI = 'Weighty'
) baseSeries inner join (       
    select RecordDate , Actual 
    from CalcProductionRecords 
    where KPI = 'Tons Milled'   
) weightSeries on baseSeries.RecordDate = weightSeries.RecordDate

source d'informationauteur ProfK

16

Quassnoi réponse montre comment faire le SumProduct, et à l'aide d'une clause where vous permettrait de restreindre par un champ de Date...
```
SELECT
   SUM([tbl].data * [tbl].weight) / SUM([tbl].weight)
FROM
   [tbl]
WHERE
   [tbl].date >= '2009 Jan 01'
   AND [tbl].date < '2010 Jan 01'
```
La partie la plus complexe est l'endroit où vous voulez "dynamique préciser" ce champ est [données] et ce champ est [poids]. La réponse courte est que, de façon réaliste, vous auriez à utiliser du SQL Dynamique. Quelque chose le long des lignes de:

- Créer un modèle de chaîne

- Remplacer toutes les occurrences de [tbl].de données avec les données appropriées champ

- Remplacer toutes les occurrences de [tbl].poids avec le poids approprié champ

- Exécution de la chaîne

SQL dynamique, cependant, porte ses propres généraux. Est les requêtes sont relativement peu fréquents , ou le temps d'exécution de la requête elle-même est relativement long, ce n'est pas trop grave. Si elles sont communes et court, cependant, vous pouvez remarquer que l'utilisation de sql dynamique introduit une malformation des frais généraux. (Pour ne pas mentionner faisant attention à des attaques par injection SQL, etc.)

EDIT:

Dans votre dernier exemple, vous pouvez mettre en évidence trois domaines:
- RecordDate
- KPI
- Réelle
Lorsque le [KPI] est de Poids "Y", puis [Réels] le Facteur de Pondération à utiliser.

Lorsque le [KPI] est "Tonnes extraites", puis [Réels] est les Données que vous voulez agréger.

Quelques questions que j'ai sont:
- Existe-il des autres domaines?
- Est-il qu'UN seul réel par date par KPI?
La raison pour laquelle je demande d'être qui vous voulez assurer la JOINDRE à vous faire est toujours de 1:1. (Vous ne voulez pas 5 chiffres Réels de rejoindre avec 5 Poids, donnant 25 resultsing records)

Indépendamment, une légère simplification de votre requête est certainement possible...
```
SELECT
   SUM([baseSeries].Actual * [weightSeries].Actual) / SUM([weightSeries].Actual)
FROM
   CalcProductionRecords AS [baseSeries]
INNER JOIN
   CalcProductionRecords AS [weightSeries]
      ON [weightSeries].RecordDate = [baseSeries].RecordDate
--    AND [weightSeries].someOtherID = [baseSeries].someOtherID
WHERE
   [baseSeries].KPI = 'Tons Milled'
   AND [weightSeries].KPI = 'Weighty'
```
Le commentaire sur la ligne uniquement nécessaire si vous avez besoin de plus de prédicats d'assurer une relation 1:1 entre vos données et le poids.

Si vous ne pouvez pas guarnatee juste Une valeur par jour, et de ne pas avoir d'autres champs pour participer, vous pouvez modifier votre sub_query version légèrement...
```
SELECT
   SUM([baseSeries].Actual * [weightSeries].Actual) / SUM([weightSeries].Actual)
FROM
(
    SELECT
        RecordDate,
        SUM(Actual)
    FROM
        CalcProductionRecords
    WHERE
        KPI = 'Tons Milled'
    GROUP BY
        RecordDate
)
   AS [baseSeries]
INNER JOIN
(
    SELECT
        RecordDate,
        AVG(Actual)
    FROM
        CalcProductionRecords
    WHERE
        KPI = 'Weighty'
    GROUP BY
        RecordDate
)
   AS [weightSeries]
      ON [weightSeries].RecordDate = [baseSeries].RecordDate
```
Ce qui suppose le AVG de poids est valable que si il y a plusieurs poids pour le même jour.

EDIT : Quelqu'un a voté pour cela j'ai donc pensé à améliorer la réponse finale 🙂
```
SELECT
   SUM(Actual * Weight) / SUM(Weight)
FROM
(
    SELECT
        RecordDate,
        SUM(CASE WHEN KPI = 'Tons Milled' THEN Actual ELSE NULL END)   AS Actual,
        AVG(CASE WHEN KPI = 'Weighty'     THEN Actual ELSE NULL END)   AS Weight
    FROM
        CalcProductionRecords
    WHERE
        KPI IN ('Tons Milled', 'Weighty')
    GROUP BY
        RecordDate
)
   AS pivotAggregate
```
Cela évite de le REJOINDRE et analyse la table une fois.

Il s'appuie sur le fait que NULL valeurs sont ignorées lors du calcul de la AVG().

SELECT  SUM(A * B) / SUM(A)
FROM    mytable

Si je comprends le problème, alors essayez cette

SET DATEFORMAT dmy
    declare @tbl table(A int, B int,recorddate datetime,KPI varchar(50))
    insert into @tbl 
        select 1,10 ,'21/01/2009', 'Weighty'union all 
        select 2,20,'10/01/2009', 'Tons Milled' union all
        select 3,30 ,'03/02/2009', 'xyz'union all 
        select 4,40 ,'10/01/2009', 'Weighty'union all
        select 5,50 ,'05/01/2009', 'Tons Milled'union all 
        select 6,60,'04/01/2009', 'abc' union all
        select 7,70 ,'05/01/2009', 'Weighty'union all 
        select 8,80,'09/01/2009', 'xyz' union all
        select 9,90 ,'05/01/2009', 'kws'    union all 
        select 10,100,'05/01/2009', 'Tons Milled'

    select SUM(t1.A*t2.A)/SUM(t2.A)Result  from  
                   (select RecordDate,A,B,KPI from @tbl)t1 
        inner join(select RecordDate,A,B,KPI from @tbl t)t2
        on t1.RecordDate = t2.RecordDate
        and t1.KPI = t2.KPI

Vous devez vous connecter pour publier un commentaire.