Объединить строки по промежутку времени в таблице SCD2

У меня есть следующая таблица, полученная из таблицы 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 20.12.2020 14:41

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

Elwood B. 21.12.2020 08:27
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
2
201
4
Перейти к ответу Данный вопрос помечен как решенный

Ответы 4

Это проблема острова и разрыва.

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

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. Они должны быть на одной линии.

Elwood B. 21.12.2020 08:49

Это действительно проблема пробелов и островов, где острова — это соседние записи, имеющие одинаковый элемент и цвет.

Здесь я бы рекомендовал использовать разницу между номерами строк для определения групп. Это включает только один уровень вложенности, а не два при использовании 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. Они должны быть на одной линии.

Elwood B. 21.12.2020 08:47
Ответ принят как подходящий

Ваши данные очень регулярны. Кажется, вы просто хотите объединить соседние, мозаичные записи, которые не имеют перекрытий или пробелов. Однако следующее обрабатывает пробелы и более общие перекрытия:

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<>, иллюстрирующий оба метода

Это, кажется, сделало свое дело. Большое спасибо, Гордон!

Elwood B. 21.12.2020 08:51

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

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, что будет неверным.

Elwood B. 21.12.2020 08:30

Другие вопросы по теме