запрос для поиска топ-3 из подзапроса

ProgramBox

запрос для поиска топ-3 из подзапроса

Post author:admin
Запись опубликована:28 августа, 2022
Post category:Вопросы по программированию

#sql-server

Вопрос:

Я создал запрос

 SELECT  Years 
        ,Months
        ,VulName
        ,Vulcount
        ,SUM(VulCount) OVER(PARTITION BY  VulId) AS  Totals 
FROM
( 
    SELECT  YEAR(outertblissues.OpenDt) AS Years 
            ,MONTH(outertblissues.OpenDt) AS Months
            ,outertblvulnerability.VulId
            ,outertblvulnerability.VulName
            ,COUNT(outertblvulnerability.VulId) Vulcount
    FROM    tbl_apptestdetails AS outertblapptestdetails 
            INNER JOIN tbl_applicationlist AS outertblapplicationlist 
                ON outertblapptestdetails.appid=outertblapplicationlist.appid 
            INNER JOIN tbl_bu AS outertblbu 
                ON outertblbu.buid=outertblapplicationlist.buid 
            INNER JOIN tbl_Issues AS outertblissues 
                ON outertblapptestdetails.testdetailid=outertblissues.testdetailid 
                   AND outertblissues.Status NOT IN('1','4')  
            INNER JOIN tbl_vulnerability AS outertblvulnerability 
                ON outertblissues.VulId=outertblvulnerability.VulId
    GROUP   BY YEAR(outertblissues.OpenDt)
               ,MONTH(outertblissues.OpenDt)
               ,outertblvulnerability.VulId
               ,outertblvulnerability.VulName
) a
ORDER BY Totals DESC

Что дает результат

Я просто хочу выбрать все те записи, которые входят в самые большие верхние (3) итоговые значения, и следует иметь в виду, что если итоговые значения более одного VulName одинаковы, то результат должен содержать только отдельные верхние 3 VulName, итоговые значения — мой последний столбец

Ответ №1:

Это может сработать для вас, обернув ваш первый запрос в cte (удалив предложение order by ), затем используйте другой cte для определения трех интересующих вас первых строк, которые затем фильтруют ваш окончательный выбор по внутреннему соединению;

 WITH cte_Select AS
(
    SELECT  Years 
            ,Months
            ,VulName
            ,Vulcount
            ,SUM(VulCount) OVER(PARTITION BY  VulId) AS  Totals 
    FROM
    ( 
        SELECT  YEAR(outertblissues.OpenDt) AS Years 
                ,MONTH(outertblissues.OpenDt) AS Months
                ,outertblvulnerability.VulId
                ,outertblvulnerability.VulName
                ,COUNT(outertblvulnerability.VulId) Vulcount
        FROM    tbl_apptestdetails AS outertblapptestdetails 
                INNER JOIN tbl_applicationlist AS outertblapplicationlist 
                    ON outertblapptestdetails.appid=outertblapplicationlist.appid 
                INNER JOIN tbl_bu AS outertblbu 
                    ON outertblbu.buid=outertblapplicationlist.buid 
                INNER JOIN tbl_Issues AS outertblissues 
                    ON outertblapptestdetails.testdetailid=outertblissues.testdetailid 
                       AND outertblissues.Status NOT IN('1','4')  
                INNER JOIN tbl_vulnerability AS outertblvulnerability 
                    ON outertblissues.VulId=outertblvulnerability.VulId
        GROUP   BY YEAR(outertblissues.OpenDt)
                   ,MONTH(outertblissues.OpenDt)
                   ,outertblvulnerability.VulId
                   ,outertblvulnerability.VulName
    ) a            
)

, cte_Top3_Vulcount as
(

    SELECT TOP (3)
        Totals
    FROM cte_Select
    GROUP BY Totals
    ORDER BY Totals DESC
)

SELECT  s.Years 
        ,s.Months
        ,s.VulName
        ,s.Vulcount
        ,s.Totals 
FROM cte_Select s
INNER JOIN cte_Top3_Vulcount t3
    on t3.[Totals] = s.[Totals]

На самом деле, час назад было уточнено требование быть;

Первые три уникальных Vulname упорядоченных по Totals

На этом основании замените содержимое cte_Top3_Vulcount на следующее;

     SELECT TOP (3)
        VulName
    FROM cte_Select
    GROUP BY VulName
    ORDER BY max(Totals) DESC

Весь запрос станет;

 WITH cte_Select AS
(
    SELECT  Years 
            ,Months
            ,VulName
            ,Vulcount
            ,SUM(VulCount) OVER(PARTITION BY  VulId) AS  Totals 
    FROM
    ( 
        SELECT  YEAR(outertblissues.OpenDt) AS Years 
                ,MONTH(outertblissues.OpenDt) AS Months
                ,outertblvulnerability.VulId
                ,outertblvulnerability.VulName
                ,COUNT(outertblvulnerability.VulId) Vulcount
        FROM    tbl_apptestdetails AS outertblapptestdetails 
                INNER JOIN tbl_applicationlist AS outertblapplicationlist 
                    ON outertblapptestdetails.appid=outertblapplicationlist.appid 
                INNER JOIN tbl_bu AS outertblbu 
                    ON outertblbu.buid=outertblapplicationlist.buid 
                INNER JOIN tbl_Issues AS outertblissues 
                    ON outertblapptestdetails.testdetailid=outertblissues.testdetailid 
                       AND outertblissues.Status NOT IN('1','4')  
                INNER JOIN tbl_vulnerability AS outertblvulnerability 
                    ON outertblissues.VulId=outertblvulnerability.VulId
        GROUP   BY YEAR(outertblissues.OpenDt)
                   ,MONTH(outertblissues.OpenDt)
                   ,outertblvulnerability.VulId
                   ,outertblvulnerability.VulName
    ) a            
)

, cte_Top3_VulName as
(
    SELECT TOP (3)
        VulName
    FROM cte_Select
    GROUP BY VulName
    ORDER BY max(Totals) DESC
)

SELECT  s.Years 
        ,s.Months
        ,s.VulName
        ,s.Vulcount
        ,s.Totals 
FROM cte_Select s
INNER JOIN cte_Top3_VulName t3
    on t3.VulName = s.VulName

1. Пожалуйста, удалите ‘, ‘ после » МЕСЯЦА(outertblissues. OpenDt) В месяцах » и, пожалуйста, поставьте «, » перед COUNT (outertblvulnerability. VulId) Vulcount. просто внесите эти изменения, и это ответ, большое вам спасибо 🙂

2. @Ashu исправлено, как вы отметили. Возможно, было бы неплохо исправить ваш исходный Sql и в вашем вопросе 😉 Рад помочь 🙂

Ответ №2:

вот один из вариантов:

 WITH    CTE
          AS (
              SELECT
                c.Years
              , c.Months
              , c.VulName
              , c.Vulcount
              , SUM(c.Vulcount) OVER (PARTITION BY VulId) AS Totals
              FROM
                (
                 SELECT
                    YEAR(i.OpenDt) AS Years
                  , MONTH(i.OpenDt) AS Months
                  , v.VulId
                  , v.VulName
                  , COUNT(v.VulId) Vulcount
                 FROM
                    tbl_apptestdetails AS atd
                 INNER JOIN tbl_applicationlist AS al
                    ON atd.appid = al.appid
                 INNER JOIN tbl_bu AS bu
                    ON bu.buid = al.buid
                 INNER JOIN tbl_Issues AS i
                    ON atd.testdetailid = i.testdetailid
                       AND i.Status NOT IN ('1', '4')
                 INNER JOIN tbl_vulnerability AS v
                    ON i.VulId = v.VulId
                 GROUP BY
                    YEAR(i.OpenDt)
                  , MONTH(i.OpenDt)
                  , v.VulId
                  , v.VulName
                ) c
             )
    SELECT
        *
    FROM
        CTE c
    WHERE
        c.Totals IN (SELECT DISTINCT TOP 3
                        Totals
                     FROM
                        CTE
                     ORDER BY
                        Totals DESC);

Отредактировано: переместил порядок на, чтобы исправить запрос.

1. Не могли бы вы привести пример ожидаемого результата, чтобы я мог пересмотреть свой запрос. На самом деле я только что указал свою ошибку. Я разместил заказ вне подзапроса. Как дамп…

2. Теперь это должно сработать, когда я переместил оператор Order By туда, где он принадлежит. Извините за это.

3. из вашего cte вы выбираете top 3 Totals OrderBy Totals, но если итоговые значения для более чем одного Vulname одинаковы, то ваш запрос приводит к более чем 3 типам Vulname, мне нужны только верхние 3 типа VulName

4. тогда мой запрос не отвечает на ваш вопрос. Затем я пытаюсь поделиться другим ответом

Ответ №3:

На основе новой информации о требовании я предлагаю следующий запрос:

 WITH    CTE
          AS (
                 SELECT
                    YEAR(i.OpenDt) AS Years
                  , MONTH(i.OpenDt) AS Months
                  , v.VulId
                  , v.VulName
                  , COUNT(v.VulId) AS Vulcount
                  , SUM(COUNT(v.VulId)) OVER (PARTITION BY VulId) AS Totals
                 FROM
                    tbl_apptestdetails AS atd
                 INNER JOIN tbl_applicationlist AS al
                    ON atd.appid = al.appid
                 INNER JOIN tbl_bu AS bu
                    ON bu.buid = al.buid
                 INNER JOIN tbl_Issues AS i
                    ON atd.testdetailid = i.testdetailid
                       AND i.Status NOT IN ('1', '4')
                 INNER JOIN tbl_vulnerability AS v
                    ON i.VulId = v.VulId
                 GROUP BY
                    YEAR(i.OpenDt)
                  , MONTH(i.OpenDt)
                  , v.VulId
                  , v.VulName
             ) ,
        cte2
          AS (
              SELECT
                c.Years
              , c.Months
              , c.Vulcount
              , c.VulName
              , c.Totals
              , ROW_NUMBER() OVER (PARTITION BY c.VulId ORDER BY C.Totals DESC) AS RowNum
              FROM
                CTE c
             )
    SELECT
        c.Years
      , c.Months
      , c.VulName
      , c.Vulcount
      , c.Totals
    FROM
        cte2 c
    WHERE
        c.RowNum <=3
    ORDER BY
        c.Totals;

1. В первом cte будет раздел v.VulId, который выдает неверный вывод, запрос дает много ошибок

2. Это даст вам общее количество вхождений для каждого vulId. первый cte является точным эквивалентом 1-го cte в моем предыдущем ответе. просто упрощено. но поскольку у меня нет ни набора данных, ни ожидаемого результата, я могу это проверить.

3. Итак, сначала Cte выдает общее количество вхождений для каждого vulid, теперь упорядочите их в Totals Desc, после этого выберите только Top 3 различных Vulid, но order by должен оставаться в итогах

4. Я добавил оператор Order By .

5. Нет, Кирлен, этот запрос не помогает

Метки: запрос для поиска топ-3 из подзапроса