Использование функции Lead в BigQuery SQL

Как получить структуру в ряду данных с помощью функции опережения

Функция LEAD — это функция навигации. Навигационные функции являются подмножеством оконных функций. Функция LEAD может быть очень полезна для подготовки данных в Data Engineering или для присвоения ранга во время анализа данных.

Он возвращает значение Value_expression в следующей строке. Изменение offsetvalue возвращает другую последующую строку. Значение по умолчанию — 1. Это указывает на следующую строку в рамке окна. Если offset равно NUllили отрицательному значению, возникает ошибка[1]. Чтобы привести конкретный пример, функцию ранга можно использовать для упорядочивания результатов гонки и во время финиша, чтобы показать, за каким бегуном следует бегун позади.

WITH sequence AS (
  SELECT 'Anna' as runner, TIMESTAMP '2016–10–18 2:51:45' as finish_time
  UNION ALL SELECT 'Lisa', TIMESTAMP '2016–10–18 3:01:17'
  UNION ALL SELECT 'Tim', TIMESTAMP '2016–10–18 2:59:01'
  UNION ALL SELECT 'Mark', TIMESTAMP '2016–10–18 3:01:17'
  UNION ALL SELECT 'Ray', TIMESTAMP '2016–10–18 3:05:42')
SELECT 
  runner,
  finish_time, 
    LEAD(runner) OVER (ORDER BY finish_time ASC) AS followed_by 
FROM Sequence;

Функция LEAD используется здесь для вычисления следующего runner из результатов оконной функции (OVER), которая сортирует с ORDER BY бегунов по их finish_time и записывает этот бегун в новый столбец ( followed_by).

В приведенном выше примере оконная функция использовалась только для упорядочения ряда данных, но ее также можно использовать с разделами. Давайте добавим новый атрибут sex, если у нас есть значения 'M' и 'F'.

WITH sequence AS
(
  SELECT ‘Anna’ as runner, TIMESTAMP ‘2016–10–18 2:51:45’ as finish_time,’F’ as sex
  UNION ALL SELECT ‘Lisa’, TIMESTAMP ‘2016–10–18 2:54:11’, ‘F’
  UNION ALL SELECT ‘Tim’, TIMESTAMP ‘2016–10–18 2:59:01’, ‘M’
  UNION ALL SELECT ‘Mark’, TIMESTAMP ‘2016–10–18 3:01:17’, ‘M’
  UNION ALL SELECT ‘Ray’, TIMESTAMP ‘2016–10–18 3:05:42’, ‘M’)
SELECT 
  runner,
  finish_time,
  LEAD(runner) OVER (PARTITION BY sex ORDER BY finish_time ASC) AS followed_by
FROM Sequence;

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

Таким образом, функция, которая может быть весьма полезна для обогащения данных либо в процессах обработки данных информацией, либо непосредственно в анализе данных. Другие интересные функции, которые вы можете использовать при работе с BigQuery, можно найти здесь:

Источники и дополнительная литература

[1] Google, ЛИД (2022)