Блог

ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ: я приношу свои извинения, если выражение этой проблемы слишком сложно. Я старался изо всех сил. У меня было действительно приблизительное представление о том, как это решить, поэтому я добавляю код ниже, хотя это не способствует решению моей проблемы. Спасибо.

У меня есть такая таблица:

 WITH table1 AS (

  SELECT  '2020-12-01 00:00:06 UTC' Datetime,   NULL Column1,  'A0' Column2, 'x' Column3 UNION ALL
  SELECT  '2020-12-01 00:00:16 UTC',           'A1',           'A0',         'x'  UNION ALL
  SELECT  '2020-12-01 00:00:26 UTC',           'A1',           'A1',         'y'  UNION ALL
  SELECT  '2020-12-01 00:00:36 UTC',           'A1',           'A1',         'y'  UNION ALL
  SELECT  '2020-12-01 00:00:46 UTC',           'A1',           'A2',         'z'  UNION ALL
  SELECT  '2020-12-01 00:00:56 UTC',           'A1',           'A2',         'z'  UNION ALL
  SELECT  '2020-12-01 00:00:66 UTC',           NULL,           'A2',         'z'  UNION ALL
  SELECT  '2020-12-01 00:01:06 UTC',           NULL,           'A2',         'z'   
)  

select * from table1
 

Я хочу создать новый столбец и добавить каждое значение из Column3 на всю длину Column1, где NULLS являются границами, если условие (Column1 = Column2) было выполнено хотя бы один раз.

Подробное объяснение: я хочу сначала проверить условие Column1 = Column2; когда это верно, я хочу проверить значение в Column3. В моем случае это «y». Когда условие выполнено, я хочу выполнить ЗАДЕРЖКУ и ОПЕРЕЖЕНИЕ до НУЛЯ в столбце 1, а затем применить ‘y’ для всей длины A1 в столбце 1 к новому столбцу Column4. Если условие Column1 = Column2 не будет выполнено в первую очередь, в Column4 должно быть NULL .

Тогда выходная таблица будет:

Я пробовал использовать приведенный ниже код, но, к сожалению, единственное, что он делает, это беспорядок.

 select *,
case when lag(Column1) over (partition by Column1 = Column2 order by DateTime) is null
             then Column3
     when lead(Column1) over (partition by Column1 = Column2 order by DateTime) is null
             then Column3
             else NULL
        end as Column4
from XX.YY.ZZ 
order by datetime
 

Вы можете решить это с помощью нескольких оконных функций.

Нужно сделать 2 вещи:
1- Получить значение из столбца 3, если столбец 1 = столбец 2 (простая часть :))
2- Найти группы столбцов 1 (острова)

Для первого довольно ясно, что я делаю:
IF(Column1 = Column2, Column3, NULL) Col4_temp,

Для второй проблемы, 1- я пытаюсь найти изменения в column1. Если значение Column1 отличается от предыдущей строки, я отмечаю его 1, в противном случае 0.

Чтобы иметь возможность это сделать, сначала я привожу значение из предыдущей строки с LAG(Column1) помощью . Если оно равно значению Column1, я возвращаю 0, в противном случае 1.

Если предыдущая строка равна NULL, это сравнение также возвращает NULL, поэтому я заполняю его с помощью COALESCE и также помечаю его как 1.

Весь код отслеживания изменений находится здесь:
COALESCE(IF(Lag(Column1) over wd = Column1, 0, 1), 1) as Col1_changes

На следующем шаге я суммирую общее количество изменений. Если изменений нет, это означает, что они находятся в одной группе.
sum(Col1_changes) over (order by Datetime) as Col1_Group

В Col1_Group вы можете видеть, что я сгруппировал Column1 на основе изменений. Наконец, я получаю максимальное значение внутри каждого Col1_Group и присваиваю его Column4.

Надеюсь, теперь все ясно 🙂 Если нет, я бы рекомендовал вам узнать больше о функциях окна.

Весь код здесь:

 WITH t1 AS (

  SELECT  '2020-12-01 00:00:06 UTC' Datetime,   NULL Column1,  'A0' Column2, 'x' Column3 UNION ALL
  SELECT  '2020-12-01 00:00:16 UTC',           'A1',           'A0',         'x'  UNION ALL
  SELECT  '2020-12-01 00:00:26 UTC',           'A1',           'A1',         'y'  UNION ALL
  SELECT  '2020-12-01 00:00:36 UTC',           'A1',           'A1',         'y'  UNION ALL
  SELECT  '2020-12-01 00:00:46 UTC',           'A1',           'A2',         'z'  UNION ALL
  SELECT  '2020-12-01 00:00:56 UTC',           'A1',           'A2',         'z'  UNION ALL
  SELECT  '2020-12-01 00:00:66 UTC',           NULL,           'A2',         'z'  UNION ALL
  SELECT  '2020-12-01 00:01:06 UTC',           NULL,           'A2',         'z'   
),
t2 AS
(
    select *, 
        IF(Column1 = Column2, Column3, NULL) Col4_temp,
        COALESCE(IF(Lag(Column1) over wd = Column1, 0, 1), 1) as Col1_changes
    from t1
    window wd as (order by Datetime)
),
t3 as
(
    select *,
        sum(Col1_changes) over (order by Datetime) as Col1_Group
    from t2
)
select *,
    MAX(Col4_temp) over (partition by Col1_Group) as Column4
from t3