Как проверить текущую строку следующей строкой и обновить текущую строку, не используя цикл while и курсор

#sql #tsql #sql-server-2016

Вопрос:

Есть ли какой-либо другой способ, кроме использования курсора или цикла while, чтобы проверить текущую строку следующей строкой, обновить текущую строку и сделать это через все строки в наборе данных?

Например, вот таблица, которую мы имеем:

 id  date                      value
1   2020-09-01 00:00:00.000   0.00
2   2020-09-01 00:15:00.000   0.30
3   2020-09-01 00:30:00.000   0.00
4   2020-09-01 00:45:00.000   0.15
5   2020-09-01 01:00:00.000   0.20
6   2020-09-01 01:15:00.000   0.10
7   2020-09-01 01:30:00.000   0.10
8   2020-09-01 01:45:00.000   0.00
9   2020-09-01 02:00:00.000   0.00



declare @i int = 1, @new_value money = 0.3

while @i <= 10
begin

        select @new_value = iif(value> (@new_value * 0.2) and value < @new_value,
                                       value,
                                       @new_value)
        from table
        where value >= 0.1
        and id = @i   1;

    set @i = @i   1
end

select @new_value -- Output will be 0.10

Этот цикл while проходит через каждую строку и обновляет @new_value на основе некоторых условий, а затем проверяет следующую строку.

Я хотел бы посмотреть, есть ли какой-либо способ не использовать цикл или курсор и получить тот же результат.

1. какова роль we have new value = 3 , когда вы сказали check the current row with the next row ? И в таблице вы никогда ничего не упоминаете о следующей строке

2. Обновил вопрос с более подробной информацией @Squirrel . Спасибо.

3. Вы можете использовать рекурсивный cte.

Ответ №1:

Вот он в рекурсивном cte

 declare @tbl table
(
    id  int,
    [value] decimal(5,2)
)

insert into @tbl values
(1, 0.00),
(2, 0.30),
(3, 0.00),
(4, 0.15),
(5, 0.20),
(6, 0.10),
(7, 0.10),
(8, 0.00),
(9, 0.00);

declare @new_value decimal(5,2) = 0.3;

with rcte as
(
    select  id, value, 
            new_value = iif(value > (@new_value * 0.2) 
                        and value < @new_value,
                        value,
                        new_value)
    from    @tbl
    where   id  = 1

    union all

    select  t.id, t.value, 
            new_value = iif(t.value > (r.new_value * 0.2) 
                        and t.value < r.new_value,
                        t.value,
                        r.new_value)
    from    rcte r
            inner join @tbl t   on  r.id    = t.id - 1
)
select  *
from    rcte

1. Спасибо! хотя производительность все еще остается проблемой, но лучше, чем использование цикла. мой набор данных содержит около 100 тысяч строк

2. с помощью такого рода вычислений вы не сможете избежать какой-либо формы цикла

3. @Squirrel Вы уверены, что для этого требуется рекурсивный цикл как таковой, похоже, оконные функции должны сделать свое дело

4. @Charlieface было бы здорово, если бы это можно было сделать с помощью функции окна

5. Просто подумайте об этом: хотя я получаю те же результаты в скрипке, это не совсем та же семантика. Если значение > 0,2 * предыдущее значение, но не >> 0,2 * (предыдущее значение, которое >>> 0,2 из >>> его предыдущего), то вы и ОП исключаете его, а я включаю его.

Ответ №2:

Для этого вы можете использовать функцию LAG окна, а также MIN агрегацию

 SELECT MIN(t.value)
FROM (
    SELECT *,
        LAG(t.value, 1, 0.3) OVER (ORDER BY t.id) AS prevValue
    FROM [table] AS t
) AS t
WHERE t.value > (t.prevValue * 0.2) AND value < prevValue;

Скрипка SQL

Я настоятельно рекомендую вам не использовать money тип данных, вместо этого используйте decimal

1. Спасибо! хотя вывод один и тот же, и задержка-лучший вариант, но значение PREV всегда меняется строка за строкой в моем случае. В своем ответе вы получаете все значения один раз, а затем выполняете предложение where. В моем случае предложение where должно проверяться по каждой строке каждый раз и обновляться.