Блог

У меня есть TEST_RESULT таблица, в которой есть один результат теста в виде строки.

 version| test_id | test_result
---------------------------------------
v1     |   53    |     fail
v2     |   53    |     fail
v3     |   53    |     success
---------------------------------------
v1     |   60    |     unprocessable
v2     |   60    |     null
v3     |   60    |     null
 

Тест 53 не удался на v1, v2, но на v3 нем был исправлен и, таким образом, удался.
Тест 60 был unprocessable выполнен v1 , и он не был выполнен в более поздних версиях v2 и v3 .

Финальный стол, из которого я хочу выбраться, выглядит так:

 test_id | final_result
---------------------------------------
53      |   success
60      |   unprocessable
 

final_result будет отображаться test_result значение из самой высокой версии, если значение не равно нулю. Если значение равно нулю, то будет отображаться следующая по старшинству версия test_result и так далее. (Обратите внимание, что в нем всегда будет строка версии, независимо от того, есть ли у версии результат теста или нет).

Я смог упорядочить строки с одинаковым test_id использованием ROW_NUMBER() , но немного потерял, как мы можем реализовать часть «ненулевое test_result значение из версии, которая как можно выше». Лучше ли такую логику реализовывать в бэкэнд-коде? Я не могу придумать подходящего оператора SQL для этого.

 SELECT
 test_id,
 ROW_NUMBER() OVER (PARTITION BY test_Id ORDER BY version DESC) AS row_num
FROM TEST_RESULT
GROUP BY test_id
 

Вы можете использовать оконные функции. Ключ состоит в том, чтобы поместить результаты теста со значениями перед NULL значениями:

 SELECT tr.*
FROM (SELECT tr.*,
             ROW_NUMBER() OVER (PARTITION BY test_Id ORDER BY (case when test_result IS NOT NULL then 1 else 2 end), version DESC) AS seqnum
      FROM TEST_RESULT tr
     ) tr
WHERE seqnum = 1;
 

Обратите внимание, что это возвращает все test_Id s в таблице, даже те, где указаны все результаты NULL .

Возможно, это не лучшее решение, но оно работает 🙂

 WITH T as (
    SELECT test_id,
           max(version) OVER (PARTITION BY test_id ORDER BY version DESC) as version
    FROM test_truc
    WHERE test_result IS NOT NULL 
        OR test_result='fail'
    GROUP BY test_id,
             version)
SELECT DISTINCT T.*,
       test.test_result
FROM T
JOIN test_truc test ON test.version = T.version 
    AND test.test_id = T.test_id
 

Не можете ли вы просто исключить записи с WHERE test_result is NOT NULL помощью .

 SELECT test_id, test_result
FROM (
    SELECT 
        test_id,
        test_result,
        ROW_NUMBER() OVER (PARTITION BY test_id ORDER BY version DESC) AS row_num
    FROM TEST_RESULT
    WHERE test_result is NOT NULL
)   
WHERE row_num = 1