Выбор последнего элемента над РАЗДЕЛОМ SQL

Примечание. Я пытаюсь изучить оконные функции, поэтому, хотя я мог бы сделать это с помощью GROUP BY, я явно намереваюсь использовать оконные функции.

У меня есть следующая таблица результатов теста

| Id | TargetId | TestId | ResultId | TestedOn                 |
+----+----------+--------+----------+--------------------------+
| 1  | 1        | 1      | 5        | 9/1/2017 6:28:32.220 PM  |
| 2  | 1        | 2      | 5        | 9/1/2017 6:28:32.220 PM  |
| 3  | 1        | 3      | 5        | 9/1/2017 6:28:32.220 PM  |
| 4  | 1        | 1      | 4        | 9/10/2017 6:28:32.220 PM |
| 5  | 1        | 2      | 4        | 9/10/2017 6:28:32.220 PM |
| 6  | 1        | 3      | 5        | 9/10/2017 6:28:32.220 PM |

Я хочу выбрать последний результат для каждого идентификатора теста, поэтому у меня есть следующее:

SELECT DISTINCT
    TargetId,
    TestId,
    FIRST_VALUE(tr.ResultId) OVER (PARTITION BY TestId ORDER BY TestedOn DESC) LatestResultId
FROM 
    TestResult tr

Я получаю ожидаемый результат

| TargetId | TestId | LatestResultId |
+----------+--------+----------------+
| 1        | 1      | 4              |
| 1        | 2      | 4              |
| 1        | 3      | 5              |

Чего я не понимаю, так это почему этот запрос, в котором вместо использования FIRST_VALUE я использую LAST_VALUE и соответствующим образом сортирую, все же дает другой результат.

SELECT DISTINCT
    TargetId, 
    TestId,
    LAST_VALUE(tr.ResultId) OVER (PARTITION BY TestId ORDER BY TestedOn) LatestResultId
FROM  
    TestResult tr


| TargetId | TestId | LatestResultId |
+----------+--------+----------------+
| 1        | 1      | 4              |
| 1        | 1      | 5              |
| 1        | 2      | 4              |
| 1        | 2      | 5              |
| 1        | 3      | 5              |
| 1        | 3      | 5              |

Для меня эти запросы должны давать один и тот же набор результатов.


sql sql-server sql-server-2012 window-functions
person Matt    schedule 21.09.2017    source источник
comment
Что такое TargetId? Я не вижу этого в вашей таблице результатов анализов?   -  person Andrew O'Brien    schedule 21.09.2017
comment
Упс, его нет в таблице результатов - все 1 в каждой строке. Исправим. Спасибо!   -  person Matt    schedule 21.09.2017

Ответы (2)


arrow_upward
5
arrow_downward

Попробуйте добавить:

OVER (PARTITION BY TestId ORDER BY TestedOn
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Я считаю, что по умолчанию для функций восходящего окна:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

person Aducci    schedule 21.09.2017
comment
Моя версия SQL, похоже, не любит И - просмотрит документы - person Matt; 21.09.2017
comment
должно быть ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - person Vamsi Prabhala; 21.09.2017
comment
Я так понимаю, по умолчанию оконная функция рассматривает только предыдущие строки, если не указано иное. Спасибо! - person Matt; 21.09.2017

arrow_upward
0
arrow_downward

Вы можете сделать это, используя предложение with , что-то вроде

WITH N(TESTID,LATEST_DATE) AS

(SELECT DISTINCT TestId, Max(TestedOn ) OVER(PARTITION BY TestId) FROM TestResult)

Select TestId, ResultId from FROM TestResult TR join N ON TR.TestId = N.TESTID AND TR.TestedOn =N.LATEST_DATE

Таблица «N» — это просто временная таблица со списком TestID и LATEST_DATE.

person developer developer    schedule 21.09.2017