BigQuery: проблема с аналитикой окна

Если я запускаю следующий запрос

WITH series AS(
  SELECT 'abcd' AS id, 'first' AS value, DATE('2020-07-27') AS date
  UNION ALL
  SELECT 'abcd' AS id, 'second' AS value, DATE('2020-07-28') AS date
  UNION ALL
  SELECT 'efgh' AS id, 'first' AS value, DATE('2020-07-29') AS date
)

SELECT DISTINCT id, LAST_VALUE(value) OVER (PARTITION BY id ORDER BY date ASC) AS value
FROM series

Я ожидаю получить следующие две записи: одну для abcd/second, а другую для efgh/first, но у меня получилось 3 строки:

введите здесь описание изображения

Любые идеи по этому вопросу? Это работало на прошлой неделе.

Спасибо,

Филипп


google-cloud-platform google-bigquery
person Philippe B    schedule 27.07.2020    source источник
comment
проверено с first_value и работает.   -  person Philippe B    schedule 27.07.2020
comment
Если вы нашли решение, пожалуйста, рассмотрите его публикацию. Если решение было найдено в вашем уже опубликованном ответе, рассмотрите возможность его принятия.   -  person Kevin Quinzel    schedule 28.07.2020

Ответы (2)


arrow_upward
1
arrow_downward

Ниже приведен стандартный SQL BigQuery.

#standardSQL
SELECT AS VALUE ARRAY_AGG(s ORDER BY date DESC LIMIT 1)[OFFSET(0)]
FROM series s
GROUP BY id

если применить к выборке данных из вашего вопроса - вывод

Row id      value   date     
1   abcd    second  2020-07-28   
2   efgh    first   2020-07-29
person Mikhail Berlyant    schedule 27.07.2020

arrow_upward
1
arrow_downward

Ответ от Гугла:

Это ожидается в стандарте SQL. Когда в аналитической функции есть предложение ORDER BY для FIRST_VALUE и LAST_VALUE, если диапазон окна явно не указан пользователем, по умолчанию это означает RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Таким образом, приведенный выше запрос эквивалентен следующему:

WITH series AS( SELECT 'abcd' AS id, 'первое' значение AS, DATE('2020-07-27') AS date UNION ALL SELECT 'abcd' AS id, 'второе' значение AS, DATE('2020-07 -28') AS date UNION ALL SELECT 'efgh' AS id, 'first' AS value, DATE('2020-07-29') AS date ) SELECT DISTINCT id, LAST_VALUE(value) OVER (PARTITION BY id ORDER BY date ASC ДИАПАЗОН МЕЖДУ НЕОГРАНИЧЕННОЙ ПРЕДЫДУЩЕЙ И ТЕКУЩЕЙ СТРОКАМИ) КАК значение ИЗ серии;

При использовании LAST_VALUE для первой строки (id='abcd' & value = 'first'), поскольку диапазон окна НЕ ОГРАНИЧЕН ПРЕДЫДУЩЕЙ И ТЕКУЩЕЙ СТРОКАМИ, в этом окне есть только одна строка, поэтому LAST_VALUE возвращает 'first'. Для второй строки (id='abcd' & value = 'second') LAST_VALUE возвращает 'second'. В результате первая и вторая строки не могут быть сгруппированы вместе с помощью DISTINCT, поскольку LAST_VALUE различаются. Вот почему в результате получается 3 строки. При использовании FIRST_VALUE первая и вторая строки возвращают один и тот же результат FIRST_VALUE «сначала», затем они дедуплицируются с помощью DISTINCT, так что остается только 2 строки.

Вот рабочее решение:

SELECT DISTINCT id, LAST_VALUE(value) OVER (PARTITION BY id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS value
FROM series
person Philippe B    schedule 27.07.2020