SQL Server: вычисляем предыдущее и следующее значение с условием
Содержание:
1. Решение с фильтром TOP (Вы читаете данный раздел);
2. Решение с оконными функциями.
Начнем с того, что создадим таблицы и заполним их малым набором тестовых данных, используя программный код, представленный ниже.
DDL и малый набор тестовых данных
Допустим, перед нами стоит задача по вычислению двух значений для каждой строки, находящихся в таблице Precipitation:
1. Количество дней, отсчет которых был начат после последнего дня, когда значение осадков превысило 24 мм (не считая сегодняшнего дня). Назовем столбец результатов diffprev.
2. Количество дней до того дня, когда значение осадков вновь превысит 24 миллиметра (не считая сегодняшнего дня). Назовем столбец результатов diffnext.
Желаемый результат
В таблице выше приведен желаемый результат для малого набора тестовых данных.
Попробуйте найти самое эффективное решение для этой задачи. Используйте малый набор тестовых данных из кода, представленного выше, для проверки корректности вашего решения. Чтобы протестировать производительность решения, требуется гораздо больше данных. Для этой цели используйте программный код, представленный ниже, чтобы создать вспомогательную функцию с именем Get N urns, которая формирует последовательность целочисленных значений в запрошенном диапазоне. Воспользуйтесь приведенным в коде, представленном ниже, программным кодом для заполнения таблиц данными для 10 тыс. местоположений по результатам ежедневных измерений для каждого (всего около 10 млн измерений).
Вспомогательная функция GetNums
Большой набор тестовых данных
Вероятно, самое очевидное решение — использовать вложенный запрос с фильтром ТОР, чтобы получить нужное предыдущее или следующее значение. Например, чтобы получить предшествующую дату, для которой значение осадков больше 24 (назовем ее prevdt), следует применить следующий вложенный запрос (предполагается, что внешнему экземпляру назначен псевдоним Precipitation P1):
Аналогично, чтобы получить следующую дату, для которой значение осадков больше 24 (назовем ее it nextdt), следует применить такой вложенный запрос:
1.
2. Решение с оконными функциями.
Начнем с того, что создадим таблицы и заполним их малым набором тестовых данных, используя программный код, представленный ниже.
DDL и малый набор тестовых данных
Допустим, перед нами стоит задача по вычислению двух значений для каждой строки, находящихся в таблице Precipitation:
1. Количество дней, отсчет которых был начат после последнего дня, когда значение осадков превысило 24 мм (не считая сегодняшнего дня). Назовем столбец результатов diffprev.
2. Количество дней до того дня, когда значение осадков вновь превысит 24 миллиметра (не считая сегодняшнего дня). Назовем столбец результатов diffnext.
Желаемый результат
В таблице выше приведен желаемый результат для малого набора тестовых данных.
Попробуйте найти самое эффективное решение для этой задачи. Используйте малый набор тестовых данных из кода, представленного выше, для проверки корректности вашего решения. Чтобы протестировать производительность решения, требуется гораздо больше данных. Для этой цели используйте программный код, представленный ниже, чтобы создать вспомогательную функцию с именем Get N urns, которая формирует последовательность целочисленных значений в запрошенном диапазоне. Воспользуйтесь приведенным в коде, представленном ниже, программным кодом для заполнения таблиц данными для 10 тыс. местоположений по результатам ежедневных измерений для каждого (всего около 10 млн измерений).
Вспомогательная функция GetNums
Большой набор тестовых данных
Решение с фильтром TOP
Вероятно, самое очевидное решение — использовать вложенный запрос с фильтром ТОР, чтобы получить нужное предыдущее или следующее значение. Например, чтобы получить предшествующую дату, для которой значение осадков больше 24 (назовем ее prevdt), следует применить следующий вложенный запрос (предполагается, что внешнему экземпляру назначен псевдоним Precipitation P1):
( SELECT TOP (1) dt
FROM dbo.Precipitation AS P2
WHERE P2.locid = P1.locid
AND P2.dt < P1.dt
AND val > 24
ORDER BY P2.dt DESC ) AS prevdtАналогично, чтобы получить следующую дату, для которой значение осадков больше 24 (назовем ее it nextdt), следует применить такой вложенный запрос:
( SELECT TOP (1) dt
FROM dbo.Precipitation AS P2
WHERE P2.locid = P1.locid
AND P2.dt > P1.dt
AND val > 24
ORDER BY P2.dt ) AS nextdt
