У меня есть таблица журналов со столбцами id
, name
, type
и date
. И я хочу создать новый столбец, который представляет следующее значение id
из второго type
. Запрос должен быть сгруппирован по имени, и только значения типа 1 заполняются на основе следующего значения типа 2.
таблица журналов:
желаемый результат:
Я смог сделать это, используя соединения и функцию 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
Рассмотрим ниже подход
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)
если применяется к выборочным данным в вашем вопросе - вывод