Преобразование таблицы по дате в новую таблицу «от-до»

У нас есть система инфраструктуры на железных дорогах. Все компоненты имеют даты Valid_From и Valid_To. Для данного проекта нам нужно объединить до 8 таблиц, и каждый из слоев может иметь разные периоды. Таким образом, единственным быстрым способом получить правильную комбинацию каждого слоя было сделать это с таблицей дат. Работает нормально и правильно. Но сейчас у меня есть как пример такая таблица.

CREATE TABLE [ods_tr_amelie].[INES_DATA_PREP_INES_ROUTING_OK]
(
    [MAP_DATE] [DATE] NULL,
    [FROM_ID_PK] [BIGINT] NOT NULL,
    [FROM_INES_KPO] [INT] NULL,
    [TO_ID_PK] [BIGINT] NOT NULL,
    [ROUT_MAX_SPEED] [INT] NULL
)
WITH
(
    DISTRIBUTION = HASH ( [MAP_DATE] ),
    CLUSTERED COLUMNSTORE INDEX
)

Теперь я хотел бы преобразовать это обратно в таблицу VALID_FROM - VALID_TO. Но... Поскольку это работает в хранилище данных SQL Azure, я не могу использовать курсоры, это нужно делать в обычном выборе со ссылками на себя в необходимости.

Ключ, на котором должен создаваться период, это FROM_ID_PK, FROM_INES_KPO, TO_ID_PK и ROUT_MAX_SPEED.

MIN(MAP_DATE) и MAX(MAP_DATE) неверны, как показано в упрощенном примере данных ниже.

MAP_DATE   SYMBOLIC_KEY
01/01/2020 VALUE_X
01/02/2020 VALUE_Y
01/03/2020 VALUE_X
01/04/2020 VALUE_X

MIN_MAX создаст

SYMBOLIC_KEY VALID_FROM VALID_TO
VALUE_X      01/01/2020 01/04/2020
VALUE_Y      01/02/2020 01/02/2020

И желаемый результат будет

SYMBOLIC_KEY VALID_FROM VALID_TO
VALUE_X      01/01/2020 01/01/2020
VALUE_Y      01/02/2020 01/02/2020
VALUE_X      01/03/2020 01/04/2020

Любое предложение было бы здорово ..


sql azure-sql-data-warehouse azure-sqldw
person Harry Leboeuf    schedule 24.02.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Это проблема пробелов и островов. Вы можете использовать row_number() и агрегацию:

select symbolic_value, min(map_date), max(map_date)
from (select t.*,
             row_number() over (order by map_date) as seqnum,
             row_number() over (partition by symbolic_value order by map_date) as seqnum_1
      from INES_DATA_PREP_INES_ROUTING_OK t
     ) t
group by symbolic_value, (seqnum - seqnum_1);

Примечание. В ваших данных нет пробелов в датах. Эта версия игнорирует пробелы. Если вы хотите, чтобы они создавали отдельные группы, то:

select symbolic_value, min(map_date), max(map_date)
from (select t.*,
             row_number() over (order by map_date) as seqnum
      from INES_DATA_PREP_INES_ROUTING_OK t
     ) t
group by symbolic_value, datediff(day, - seqnum, map_date);
person Gordon Linoff    schedule 24.02.2020