如何修改SQL查询新增统计QID对应唯一Scan_Run_Date_Week数量的列

修改后完整SQL #

WITH QID_Week_Stats AS (
    -- 预先统计每个QID对应的唯一周数，即Time_On_Track
    SELECT 
        QID,
        COUNT(DISTINCT Scan_Run_Date_Week) AS Time_On_Track
    FROM dbo.RMO_Qualys_PROD
    WHERE Severity = '5' -- 若需要统计该QID所有严重等级下的在榜周数，可删除此条件
    GROUP BY QID
)
Select 
       QID_Count,
       QID,
       Scan_Run_Date_Week,
       Title,
       Severity,
       qs.Time_On_Track -- 新增的在榜时长字段
From
        (
        SELECT count(QID) as QID_Count, 
               QID,
               Scan_Run_Date_Week, 
               Title,
               row_number() over(partition by Scan_Run_Date_Week order by count(QID) desc) as Ranking,
               Severity

        FROM dbo.RMO_Qualys_PROD 

        Where Severity = '5' and Scan_Run_Date_Week = '2021-06-06 00:00:00.000'

        Group By Scan_Run_Date_Week, Title, QID, Severity
        ) as Ranking
-- 关联预先统计好的周数数据
LEFT JOIN QID_Week_Stats qs ON Ranking.QID = qs.QID
Where Ranking <=5

调整说明 #

• 新增QID_Week_Stats公共表表达式，提前在全表范围统计每个QID对应的去重周数，避免原有查询过滤了指定周后统计范围不准的问题
• 原有取指定周Top5 QID的逻辑完全保留，最后通过QID关联拿到对应的Time_On_Track字段即可
• 若使用的数据库不支持CTE语法，把QID_Week_Stats的逻辑改为子查询放在FROM后关联即可，逻辑完全一致

内容的提问来源于stack exchange，提问作者runatyr