Блог

Я объединяю две таблицы.

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

Первая таблица

 UniqueID  Items start_time
123       one   10:00 AM
456       two   11:00 AM
789       three 11:30 AM
  

Вторая таблица

 UniqueID Items time_hit  value
123      one   10:00 AM    x
123      one   10:05 AM    x
123      one   10:10 AM    x
123      one   10:30 AM    x
456      two   11:00 AM    x
456      two   11:15 AM    x
789      three 11:30 AM    x
  

Итак, при объединении двух таблиц у меня есть это:

 UniqueID Items start_time  time_hit   value 
123      one   10:00 AM    10:00 AM   x
123      null  null        10:05 AM   x
123      null  null        10:10 AM   x
123      null  null        10:30 AM   x
456      two   11:00 AM    11:00 AM   x
456      null  null        11:15 AM   x
789      three 11:30 AM    11:30 AM   x
  

Я хотел бы заменить эти null значения значениями из ненулевой предшествующей строки…

Таким образом, ожидаемый результат

 UniqueID Items start_time  time_hit   value 
123      one   10:00 AM    10:00 AM   x
123      one   10:00 AM    10:05 AM   x
123      one   10:00 AM    10:10 AM   x
123      one   10:00 AM    10:30 AM   x
456      two   11:00 AM    11:00 AM   x
456      two   11:00 AM    11:15 AM   x
789      three 11:30 AM    11:30 AM   x
  

Я безуспешно пытался создать свое соединение, используя следующую функцию:

   FIRST_VALUE(Items IGNORE NULLS) OVER (
    PARTITION BY time_hit ORDER BY time_hit
    ROWS BETWEEN CURRENT ROW AND
    UNBOUNDED FOLLOWING) AS test
  

Мой вопрос был немного не таким. Я обнаружил, что UniqueID были несовместимы, поэтому у меня были эти нулевые значения в моем выводе. Таким образом, проверенный ответ является хорошим вариантом для заполнения нулевых значений при объединении двух таблиц, и одна из ваших таблиц содержит больше уникальных строк, чем другая.

Вы могли бы использовать first_value (но last_value это также сработало бы и в этом сценарии). Часть импорта заключается в указании rows between unbounded preceding and current row для установки границ окна.

Ответ обновлен, чтобы отразить обновленный вопрос, и предпочтение для first_value

 select
first_value(t1.UniqueId ignore nulls) over (partition by t2.UniqueId
                                           order by t2.time_hit
                                           rows between unbounded preceding and current row) as UniqueId,
first_value(t1.items ignore nulls) over (partition by t2.UniqueId
                                        order by t2.time_hit
                                        rows between unbounded preceding and current row) as Items,
first_value(t1.start_time ignore nulls) over (partition by t2.UniqueId
                                        order by t2.time_hit
                                        rows between unbounded preceding and current row) as start_time,
t2.time_hit,
t2.item_value
from table2 t2
left join table1 t1 on t1.start_time = t2.time_hit
order by t2.time_hit;
  

Результат

 | UNIQUEID | ITEMS | START_TIME | TIME_HIT | ITEM_VALUE |
|----------|-------|------------|----------|------------|
|      123 |   one |   10:00:00 | 10:00:00 |          x |
|      123 |   one |   10:00:00 | 10:05:00 |          x |
|      123 |   one |   10:00:00 | 10:10:00 |          x |
|      123 |   one |   10:00:00 | 10:30:00 |          x |
|      456 |   two |   11:00:00 | 11:00:00 |          x |
|      456 |   two |   11:00:00 | 11:15:00 |          x |
|      789 | three |   11:30:00 | 11:30:00 |          x |
  

Пример скрипки SQL

Примечание: Мне пришлось использовать Oracle в SQL Fiddle (поэтому мне пришлось изменить типы данных и имя столбца). Но это должно работать для вашей базы данных.

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

 SELECT t1.items, t1.start_time, t2.time_hit, t2.value
FROM table1 t1
INNER JOIN table2 t2 
    ON  t1.items = t2.items
    AND t1.start_time <= t2.time_hit  
    AND NOT EXISTS (
        SELECT 1 FROM table1 t10
        WHERE 
            t10.items = t2.items 
            AND t10.start_time <= t2.time_hit
            AND t10.start_time > t1.start_time
    )
  

Демонстрация на скрипте DB:

 | items | start_time | time_hit | value |
| ----- | ---------- | -------- | ----- |
| one   | 10:00:00   | 10:00:00 | x     |
| one   | 10:00:00   | 10:05:00 | x     |
| one   | 10:00:00   | 10:10:00 | x     |
| one   | 10:00:00   | 10:30:00 | x     |
| two   | 11:00:00   | 11:00:00 | x     |
| two   | 11:00:00   | 11:15:00 | x     |
| three | 11:30:00   | 11:30:00 | x     |
  

Альтернативное решение, позволяющее избежать использования EXISTS при JOIN условии (не допускается в большом запросе): просто переместите это условие в WHERE предложение.

 SELECT t1.items, t1.start_time, t2.time_hit, t2.value
FROM table1 t1
INNER JOIN table2 t2 
    ON  t1.items = t2.items
    AND t1.start_time <= t2.time_hit  
WHERE NOT EXISTS (
    SELECT 1 FROM table1 t10
    WHERE 
        t10.items = t2.items 
        AND t10.start_time <= t2.time_hit
        AND t10.start_time > t1.start_time
)
  

Скрипта DB

Я предполагаю, что вы ожидаете вывода с использованием INNER JOIN. Но не уверен, почему вы использовали FIRST_VALUE.

 SELECT I.Item, I.Start_Time, ID.Time_hit,  ID.Value
FROM Items I
INNER JOIN ItemDetails ID
 ON I.Items = ID.Items
  

Пожалуйста, объясните, ищете ли вы какие-либо конкретные причины для рассмотрения этого подхода.