Поиск острова дат

Question

Sql server 2017. Есть таблица с историчностью(Temporal tables). Стоит задача получить историю, но не по всем полям, а по определенным. Следующий запрос:

SELECT cd.id,
                        cd.fn_factory_number,
                        cd.outlet_id,
                        cd.valid_from,
                        cd.valid_to
              FROM dbo.cashdesk_data
                  FOR SYSTEM_TIME all AS cd
              WHERE cd.id = 1
              ORDER BY cd.valid_to

выдает

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

Как это сделать без построчного пребора и дополнительных вычислений представить не могу. По хорошему нужно вычислить какое то подобие ранга, что бы было как на скриншоте, что бы сгруппировать по этому полю и получить min max дат.

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

Answer 1

На вскидку, ввиду отсутствия примера данных в виде текста и вопросе, должно быть что то в этом роде:

select *, sum(grp) over(order by dt) grp
  from (
    select *,
           case when
              coalesce(lag(fn_factory_number) over(order by cd.valid_to), fn_factory_number) = fn_factory_number and
              coalesce(lag(outlet_id) over(order by cd.valid_to), outlet_id) = outlet_id
           then 0 else 1 end grp
      from cashdesk_data ...
) x

Функция lag() дает значение колонки из предыдущей строки в указанном порядке сортировки. Сравниваем значения c текущим, если отличается выставляем 1. Во внешнем запросе считаем сумму этих 1 оконной функцией в указанном порядке сортировки. Таким образом формируется номер группы к которой следует относить строку. Остается сгруппировать под данному полю (grp) и вычислить необходимые минимумы/максимумы

Answer 2

Публикую своё решение. Оконные функции используются для определения границы "островов".

SELECT 
     id
    ,fn_factory_number
    ,outlet_id
    ,ISNULL(LAG(next_window_valid_from) OVER (ORDER BY row), min_valid_to) AS valid_from
    ,valid_to
FROM (
    SELECT
         id
        ,fn_factory_number
        ,outlet_id
        ,valid_to
        ,min_valid_to
        ,row
        ,IIF(LEAD(row_part) OVER (ORDER BY row) - row_part = 1, 0, 1) AS window_end
        ,LEAD(valid_from) OVER (ORDER BY row) AS next_window_valid_from
    FROM (
        SELECT
             id
            ,fn_factory_number
            ,outlet_id
            ,valid_from
            ,valid_to
            ,MIN(valid_from) OVER (ORDER BY valid_to) AS min_valid_to
            ,ROW_NUMBER() OVER (ORDER BY valid_to) AS row
            ,ROW_NUMBER() OVER (PARTITION BY id, fn_factory_number, outlet_id ORDER BY valid_to) AS row_part
        FROM dbo.cashdesk_data
        FOR SYSTEM_TIME ALL
        WHERE id = 1
    ) parts
) windows
WHERE window_end = 1
ORDER BY row;

БЛОГ НА HUSL

Поиск острова дат

Ответы (2 шт):