J'ai une requête de travail du rapport utilisant la fonction de fenêtre min et max, mais je dois extraire les références de min et max. Voici la requête principale:

SELECT 
    company, 
    countryname,
    dept,
    unit,
    score,
    MAX(score) OVER(PARTITION BY company) AS max_comp_score,
    --need reference countryname, dept, unit for this max score

    MIN(score) OVER(PARTITION BY company) AS min_comp_score,
    --need reference countryname, dept, unit for this min score

    AVG(score) OVER(PARTITION BY company) AS avg_comp_score

FROM 

tt.vw_Score_Report

La vue est assez grande (5 millions de lignes), donc si je fais des sous-requêtes sur min et max pour extraire leurs références à partir de là, il faut des années pour les récupérer.

Quelles sont les options pour obtenir ces attributs dans la requête? Je pensais qu'il pourrait y avoir un moyen élégant de résoudre ce problème.

0
RandyMcKay 16 sept. 2020 à 16:45

2 réponses

Meilleure réponse

Vous pouvez utiliser un CROSS APPLY pour obtenir les lignes min et max:

SELECT 
    company, 
    countryname,
    dept,
    unit,
    score,
    srmin.countryname,
    srmin.dept,
     ...
    srmax.countryname,
    srmax.dept,
    srmax.score,
    .....
    AVG(score) OVER(PARTITION BY company) AS avg_comp_score

FROM tt.vw_Score_Report sr
CROSS APPLY (   SELECT TOP 1 countryname, dept, unit ,score
                FROM tt.vw_Score_Report sr2
                WHERE sr2.company = sr.company
                ORDER BY score DESC
    ) srmax
CROSS APPLY (   SELECT TOP 1 countryname, dept, unit ,score
                FROM tt.vw_Score_Report sr3
                WHERE sr3.company = sr.company
                ORDER BY score ASC
    ) srmin
2
Wouter 16 sept. 2020 à 14:04

J'essaierais quelque chose comme ça (vous voudrez alias les colonnes - ce n'est qu'un croquis)

;With cte_Co_Max
As
(
    Select 
        company, 
        dept,
        unit,
        score,
        Row_Number() Over (Partition By company Order By score Desc) As rn
    From tt.vw_Score_Report
), cte_Co_Min
As
(
    Select 
        company, 
        dept,
        unit,
        score,
        Row_Number() Over (Partition By company Order By score) As rn
    From tt.vw_Score_Report
)

Select 
    vsr.company, 
    vsr.countryname,
    vsr.dept,
    vsr.unit,
    vsr.score,

    cc_Max.company, 
    cc_Max.countryname,
    cc_Max.dept,
    cc_Max.unit,
    cc_Max.score,

    cc_Min.company, 
    cc_Min.countryname,
    cc_Min.dept,
    cc_Min.unit,
    cc_Min.score,

    AVG(score) OVER(PARTITION BY company) AS avg_comp_score

FROM 

tt.vw_Score_Report As vsr
    Inner Join cte_Co_Max As cc_Max
        On cc_Max.company = vsr.company
        and cc_Max.rn = 1
    Inner Join cte_Co_Min As cc_Min
        On cc_Min.company = vsr.company
        and cc_Min.rn = 1

Comme je n'avais pas d'échantillons de données, je ne pouvais pas les tester, bien sûr.

1
Brian 16 sept. 2020 à 14:09