Блог

При использовании функции задержки во временных рядах в SQL Server я всегда борюсь с первым значением во временном ряду. Предположим, что этот тривиальный пример

 CREATE TABLE demo
    ([id] int, [time] date, [content] int)
;
INSERT INTO demo (id, time, content) VALUES
  (1, '2021-05-31', cast(rand()*1000 as int)),
  (2, '2021-06-01', cast(rand()*1000 as int)),
  (3, '2021-06-02',cast(rand()*1000 as int)),
  (4, '2021-06-03', cast(rand()*1000 as int)),
(5, '2021-06-04', cast(rand()*1000 as int)),
(6, '2021-06-05', cast(rand()*1000 as int)),
(7, '2021-06-06', cast(rand()*1000 as int)),
(8, '2021-06-07', cast(rand()*1000 as int)),
(9, '2021-06-08', cast(rand()*1000 as int));
 

Я хочу получить все значения и их предыдущее значение в июне, так что что-то вроде этого

 select content, lag(content, 1, null) over (order by time)
from demo
where time >= '2021-06-01'
 

пока все хорошо, однако первая запись приведет к нулю для предыдущего значения.

Конечно, есть много решений о том, как заполнить нулевое значение, например, выбрать больший диапазон и т. Д. Но Для очень больших таблиц я почему-то думаю, что для этого должно быть элегантное решение.

Иногда я делаю такие вещи, как это

 select content, lag(content, 1, 
(select content from demo d1 join 
(select max(time) maxtime from demo where time < '2021-06-01') d2 on d1.time = d2.maxtime
)) over (order by time)
from demo
where time >= '2021-06-01'
 

Есть ли что-то более эффективное? (примечание: конечно, для этого тривиального примера я не вижу разницы, но для таблиц с разделением и 500 000 000 записей следует найти наиболее эффективное решение)

Посмотри на скрипку

Ключевая идея состоит в том, чтобы использовать подзапрос:

 select t.*
from (select content, lag(content) over (order by time)
      from demo d
     ) d
where time >= '2021-06-01';
 

Это, вероятно, будет сканировать всю таблицу. Однако вы можете создать индекс demo(time, content) , чтобы помочь lag() .

Затем вы можете оптимизировать это, если у вас есть разумный период возврата. Например, если записи появляются каждый месяц, просто вернитесь на один месяц назад в подзапросе:

 select t.*
from (select content, lag(content) over (order by time)
      from demo d
      where time >= '2021-05-01'
     ) d
where time >= '2021-06-01';
 

Это также может быть очень важно, если ваши данные разбиты на разделы, как это обычно бывает с большими таблицами.

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

 WITH cte AS (
    SELECT time, content, LAG(content) OVER (ORDER BY time) lag_content
    FROM demo
)

SELECT content, lag_content
FROM cte
WHERE time >= '2021-06-01';
 

Какими бы вы хотели видеть нулевые значения? Я поставил их как 0 в приведенном ниже примере.

 SELECT
    content,
    coalesce(LAG(content, 1, NULL) OVER(
        ORDER BY
            time
    ), content-1) lag_content
FROM
    demo
WHERE
    time >= '2021-06-01'
 

Выход:

 content lag_content
-------------------
      2           1
      3           2
      4           3
      5           4
      6           5
      7           6
      8           7
      9           8
 

Попробуйте это здесь: dbfiddle