Эффективный способ обратного заполнения групповых значений по типу

У меня есть таблица журналов со столбцами id, name, type и date. И я хочу создать новый столбец, который представляет следующее значение id из второго type. Запрос должен быть сгруппирован по имени, и только значения типа 1 заполняются на основе следующего значения типа 2.

таблица журналов:

идентификатор имя тип дата 1 имя1 Тип 1 2022-01-01 2 имя1 Тип 1 2022-01-02 3 имя1 тип2 2022-01-03 4 имя1 Тип 1 2022-01-03 5 имя1 тип2 2022-01-04 6 имя1 Тип 1 2022-01-05 7 имя2 Тип 1 2022-01-03 8 имя2 тип2 2022-01-08

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

идентификатор имя тип дата type2_id 1 имя1 Тип 1 2022-01-01 3 2 имя1 Тип 1 2022-01-02 3 4 имя1 Тип 1 2022-01-03 5 6 имя1 Тип 1 2022-01-05 7 имя2 Тип 1 2022-01-03 8

Я смог сделать это, используя соединения и функцию LAG. Однако, может быть, кто-то может помочь мне сделать его более эффективным?

WITH logs AS (
  SELECT 1 AS id, 'name1' AS name, 'type1' AS type, '2022-01-01' AS date,
  UNION ALL
  SELECT 2 AS id, 'name1' AS name, 'type1' AS type, '2022-01-02' AS date,
  UNION ALL
  SELECT 3 AS id, 'name1' AS name, 'type2' AS type, '2022-01-03' AS date,
  UNION ALL
  SELECT 4 AS id, 'name1' AS name, 'type1' AS type, '2022-01-03' AS date,
  UNION ALL
  SELECT 5 AS id, 'name1' AS name, 'type2' AS type, '2022-01-04' AS date,
  UNION ALL
  SELECT 6 AS id, 'name1' AS name, 'type1' AS type, '2022-01-05' AS date,
  UNION ALL
  SELECT 7 AS id, 'name2' AS name, 'type1' AS type, '2022-01-03' AS date,
  UNION ALL
  SELECT 8 AS id, 'name2' AS name, 'type2' AS type, '2022-01-08' AS date,
)


SELECT
  t1.id,
  t1.name,
  t1.type,
  t1.date,
  t2.id AS type2_id,

FROM (
  SELECT *,
  FROM logs
  WHERE logs.type = 'type1'
) AS t1

LEFT JOIN (
  SELECT *,
    IFNULL(LAG(logs.date) OVER(PARTITION BY logs.name, logs.type ORDER BY logs.date), '2000-01-1') AS date_prev,
  FROM logs
  WHERE type = 'type2'
) AS t2
  ON t2.name = t1.name
    AND t2.date > t1.date
    AND t2.date_prev <= t1.date

ORDER BY t1.name, t1.date
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
50
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Рассмотрим ниже подход

select *, first_value(if (type='type2', id, null) ignore nulls) over win as type2_id
from logs
qualify type = 'type1'
window win as (partition by name order by date rows between 1 following and unbounded following)           

если применяется к выборочным данным в вашем вопросе - вывод

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