У меня есть следующая таблица, полученная из таблицы SCD2. Из этой исходной таблицы я выбрал только несколько столбцов, в результате чего несколько строк выглядят совершенно одинаково. Я хочу удалить ненужные строки, те, которые содержат одни и те же данные, и иметь столбец ValidFrom, показывающий первое значение, и столбец ValidTo, показывающий последнее значение в «группе временных интервалов».
Источник данных:
| Item | Color | ValidFrom | ValidTo |
| -------- | ---------- | ------------- | ---------- |
| Ball | Red | 2020-01-01 | 2020-03-24 |
| Ball | Blue | 2020-03-25 | 2020-04-12 |
| Ball | Blue | 2020-04-13 | 2020-05-07 |
| Ball | Blue | 2020-05-08 | 2020-11-14 |
| Ball | Red | 2020-11-15 | 9999-12-31 |
| Doll | Yellow | 2020-01-01 | 2020-03-24 |
| Doll | Green | 2020-03-25 | 2020-04-12 |
| Doll | Green | 2020-04-13 | 2020-05-07 |
| Doll | Green | 2020-05-08 | 2020-11-14 |
| Doll | Pink | 2020-11-15 | 9999-12-31 |
Чего я хочу добиться, так это:
| Item | Color | ValidFrom | ValidTo |
| -------- | ---------- | ------------- | ---------- |
| Ball | Red | 2020-01-01 | 2020-03-24 |
| Ball | Blue | 2020-03-25 | 2020-11-14 |
| Ball | Red | 2020-11-15 | 9999-12-31 |
| Doll | Yellow | 2020-01-01 | 2020-03-24 |
| Doll | Green | 2020-03-25 | 2020-11-14 |
| Doll | Pink | 2020-11-15 | 9999-12-31 |
Обратите внимание, что шар предметов сначала имеет красный цвет, затем синий, а затем снова становится красным. Это немного усложняет ситуацию, исходя из того, что я узнал.
Спасибо за вашу помощь.
@SMor Прошу прощения за то, что сбиваю с толку. Поскольку английский не является моим родным языком, я иногда ошибаюсь в некоторых словах.
Это проблема острова и разрыва.
Вы можете использовать аналитическую функцию следующим образом:
Select item, color,
min(validfrom) as validfrom,
Max(validto) as validto
From
(Select t.*,
Sum(case when lged between validfrom and validto then 0 else 1 end)
over (partition by item, color order by validfrom) as sm
From
(Select t.*,
Lag(validto) over (partition by item, color order by validfrom) as lged
From t) t) t
Group by item, color, sm
Запуск этого скрипта приводит к появлению двух строк для Ball — Blue с значениями ValidTo и ValidFrom 2020-03-25 — 2020-05-07 и 2020-05-08 — 2020-11-14. Они должны быть на одной линии.
Это действительно проблема пробелов и островов, где острова — это соседние записи, имеющие одинаковый элемент и цвет.
Здесь я бы рекомендовал использовать разницу между номерами строк для определения групп. Это включает только один уровень вложенности, а не два при использовании lag()
, поэтому это должен быть самый эффективный вариант:
select item, color, min(validfrom) as validfrom, max(validto) as validto
from (
select t.*,
row_number() over(order by validfrom) as rn1,
row_number() over(partition by item, color order by validfrom) as rn2
from mytable t
) t
group by item, color, rn1 - rn2
Запуск этого скрипта приводит к появлению двух строк для Ball — Blue с значениями ValidTo и ValidFrom 2020-03-25 — 2020-05-07 и 2020-05-08 — 2020-11-14. Они должны быть на одной линии.
Ваши данные очень регулярны. Кажется, вы просто хотите объединить соседние, мозаичные записи, которые не имеют перекрытий или пробелов. Однако следующее обрабатывает пробелы и более общие перекрытия:
select item, color, min(validfrom), max(validto)
from (select t.*,
sum(case when prev_validto >= dateadd(day, -1, validfrom)
then 0 else 1
end) over (partition by item order by validfrom) as grp
from (select t.*,
lag(validto) over (partition by item, color order by validfrom) as prev_validto
from t
) t
) t
group by item, color, grp;
Вы ищете островки строк в исходных данных, где «острова» имеют одинаковый элемент, цвет и смежные даты. Это определяет, где начинаются острова, просматривая предыдущий ряд для того же предмета и цвета. Если такой строки нет или строка заканчивается раньше, чем начинается текущая строка, то текущая строка является началом острова.
Тогда grp
представляет собой совокупную сумму «начал острова», и ее можно использовать для агрегирования и получения окончательных результатов.
Ваши конкретные данные довольно ограничены - идеально выложены плиткой, где одна строка заканчивается за день до начала следующей. Вы можете сделать что-то очень похожее, используя left join
:
select item, color, min(validfrom), max(validto)
from (select t.*,
sum(case when tprev.color is null then 1 else 0
end) over (partition by t.item order by t.validfrom) as grp
from t left join
t tprev
on tprev.item = t.item and
tprev.color = t.color and
tprev.validto = dateadd(day, -1, t.validfrom)
) t
group by item, color, grp
order by item, min(validfrom);
Здесь скрипт db<>, иллюстрирующий оба метода
Это, кажется, сделало свое дело. Большое спасибо, Гордон!
Поскольку между строками нет пробелов или перекрытий, возможно, этого запроса достаточно.
select item, color,
min(validfrom) as ValidFrom,
max(validto) as ValidTo
from tTable
group by item, color
order by item, ValidFrom;
Боюсь, это мне не поможет. Строки 1 и 5 будут сгруппированы вместе с указанием значений ValidFrom и ValidTo от 01.01.2020 до 31.12.9999, что будет неверным.
"точно так же" - это оксюморон. Эти строки похожи только на два одинаковых значения. Не усложняйте задачу, используя запутанные термины.