Nano Hash - криптовалюты, майнинг, программирование

Teradata перекрывает периоды с пробелами

Был бы очень признателен за помощь в попытке сгруппировать перекрывающиеся периоды. Это исходные данные, которые у меня есть в таблице 1:

Start_Date  End_Date    Status  Id    Main_Id
01.01.2020  03.05.2020  0       11    1
01.02.2020  14.04.2020  3       12    1
14.04.2020  15.05.2020  5       13    1
10.05.2020  20.05.2020  0       14    1
22.05.2020  25.05.2020  2       15    1

Желаемый результат:

Valid_Period        Decision      Main_Id
01.01.2020  01.02.2020  NOK       1
01.02.2020  03.05.2020  DOOMED    1
03.05.2020  10.05.2020  BAD       1
10.05.2020  15.05.2020  DOOMED    1
15.05.2020  20.05.2020  NOK       1
20.05.2020  22.05.2020  OK        1
22.05.2020  25.05.2020  BAD       1

В выходных данных может быть 4 разных решения, основанных на входном статусе и периоде:

  1. Статус 0 существует (и одновременно нет статуса ›0) - 'NOK'
  2. Статус ›0 существует (но в то же время нет статуса 0) - «ПЛОХО»
  3. Статус ›0 и 0 существуют вместе - 'ОБРЕЧЕН'
  4. Ни один из других статусов не существует - он предназначен для покрытия временных промежутков между периодами - «ОК».

Вывод, который я позже буду использовать для целей отчетности, должен иметь возможность получить правильный результат для каждого Main_Id в любой момент времени. До сих пор я пытался с помощью одного SELECT сгруппировать перекрывающиеся статусы 0 и ›0 и с помощью отдельного SELECT, чтобы покрыть временные промежутки, а затем UNIION ALL, чтобы собрать все вместе:

 select NORMALIZE ON MEETS OR OVERLAPS (
case 
        when table1.Status = 1 then 'NOK'  
        when table1.Status >1 then 'BAD' end) Decision,
period(table1.Start_Date, coalesce (table1.End_Date, cast('9999-01-01' as date))) valid_period,
table1.Main_Id
from table1

union all

select  'OK' Decision,
period (a.prev_end_date, a.Start_Date) valid_period,
a.Main_Id
from    (
    select  
    table1.Main_Id,
    LAG (table1.End_Date) over (
    partition by table1.Main_Id 
    order by table1.Start_Date, coalesce (table1.End_Date, cast('9999-01-01' as date))) prev_end_date,
    table1.Start_Date
    from table1
    qualify prev_end_date < table1.Start_Date)a 
;

Текущий вывод должен быть примерно таким:

01.01.2020  03.05.2020  NOK
01.02.2020  15.05.2020  BAD
10.05.2020  20.05.2020  NOK
22.05.2020  25.05.2020  BAD
20.05.2020  22.0.2020   OK

Это первый раз, когда я пытаюсь использовать тип данных Teradata PERIOD, поэтому все еще учусь. Также пытался с calendar_date получить одну строку для каждого дня в периоде, но он не покрывает пробелы, и катушка уже становится слишком большой:

select c.calendar_date,table1
MAX(CASE 
    WHEN table1.status = 1
    THEN 1
    ELSE 0
END) NOK,
MAX(CASE 
    WHEN table1.status > 1
    THEN 1
    ELSE 0
END) BAD
from table1
join table_calendar c
on  table1.start_date<=c.calendar_date and (
table1.end_date>c.calendar_date or c.calendar_date is null
)
group by c.calendar_date
;

Поэтому все еще изо всех сил пытаюсь получить желаемый результат с хорошей производительностью. Любая помощь будет оценена по достоинству. Заранее спасибо!

teradata gaps-and-islands teradatasql
17.07.2021

Ответы:


1

Это действительно сложно. Мой ответ основан на похожем вопросе, который можно упростить с помощью нового синтаксиса:

with all_ranges as
 ( -- create ranges based on all start/end dates
   select  
      dt, Main_Id,
      period(dt, lead(dt) over(partition by Main_Id order by dt)) as pd 
   from table1
    -- split rows into begin/end
   unpivot(dt for col in(Start_Date as '1',End_Date as '-1')) as p
   group by Main_Id, dt
   qualify pd is not null -- remove last row
 )
select normalize
   coalesce(ar.pd P_INTERSECT PERIOD(t1.Start_Date, t1.End_Date), ar.pd) AS ValidPeriod
  ,ar.Main_Id
  ,case max(case when Status = 0 then 1 else 0 end)
      + max(case when Status > 0 then 2 else 0 end)
     when 0 then 'OK'     -- no row, cover time gaps between periods
     when 1 then 'NOK'    -- Status 0 exists (and no status >0 at the same time)
     when 2 then 'BAD'    -- Status >0 exists (but no status 0 at the same time)
     when 3 then 'DOOMED' -- Status >0 and 0 exist together
   end
from all_ranges as ar 
left join table1 as t1
  on ar.Main_Id = t1.Main_Id
 -- match the ranges to existing rows
 and ar.pd overlaps PERIOD(t1.Start_Date, t1.End_Date)
group by ar.Main_Id, ValidPeriod
order by 1;
20.07.2021
Новые материалы
Кластеризация: более глубокий взгляд
Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
Как написать эффективное резюме
Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
Частный метод Python: улучшение инкапсуляции и безопасности
Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
Как я автоматизирую тестирование с помощью Jest
Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..