У меня есть таблица, которая выглядит так:
Используя функцию LAG в SQL, я хотел бы выполнить LAG только для значений, где star_date = end_date, и получить прошлую предыдущую запись start_date, где start_date = end_date. В моей конечной таблице будет дополнительный столбец вроде этого: 
Надеюсь, мой вопрос ясен, любая помощь приветствуется.
Вы можете назначить группу этим значениям и использовать это:
select t.*,
(case when start_date = end_date
then lag(start_date) over (partition by (case when start_date = end_date then 1 else 0 end) order by start_date)
end) as prev_eq_start_date
from t;
Or:
select t.*,
(case when start_date = end_date
then lag(start_date) over (partition by start_date = end_date order by start_date)
end) as prev_eq_start_date
from t;
Обратите внимание, что если у вас большие данные и у большинства строк разные даты, у вас может быть проблема с ресурсами. В этом случае может помочь дополнительный неиспользованный partition by ключ:
select t.*,
(case when start_date = end_date
then lag(start_date) over (partition by (case when start_date = end_date then 1 else 2 end), (case when start_date <> end_date then start_date end) order by start_date)
end) as prev_eq_start_date
from t;
Это не влияет на результат, но позволяет избежать ошибки ресурсов, вызванной слишком большим количеством строк с разными значениями.
partition by (StartDate = EndDate) даст синтаксическую ошибку!
- person Vignesh Kumar A; 29.11.2019
countif(), что забыл, что BQ не обрабатывает логические значения как другие типы.
- person Gordon Linoff; 29.11.2019
partition by (StartDate = EndDate) отлично сработает! вы проверили и подтвердили, что это не так ?!
- person Mikhail Berlyant; 29.11.2019
partition by (start_date = end_date)), и он отлично работает для меня (BigQuery Standard SQL) - я думаю, что он достаточно элегантен, чтобы отказаться от него: o (подумал, очевидно, что мне больше нравится мой ответ: o)
- person Mikhail Berlyant; 29.11.2019
Ниже приведен стандартный SQL BigQuery.
#standardSQL
SELECT *, NULL AS lag_result
FROM `project.dataset.table` WHERE start_date != end_date
UNION ALL
SELECT *, LAG(start_date) OVER(ORDER BY start_date)
FROM `project.dataset.table` WHERE start_date = end_date
Если обратиться к образцу данных в вашем вопросе - результат будет
Row user_id start_date end_date lag_result
1 1 2019-01-01 2019-02-28 null
2 3 2019-02-27 2019-02-28 null
3 4 2019-08-04 2019-09-01 null
4 2 2019-02-01 2019-02-01 null
5 5 2019-08-07 2019-08-07 2019-02-01
6 6 2019-08-27 2019-08-27 2019-08-07
Кстати, в случае, если ваши start_date и end_date имеют тип данных STRING ('27/02/2019') по сравнению с типом DATE ('2019-02-27', как предполагалось в приведенном выше запросе), вы должны использовать ниже один
#standardSQL
SELECT *, NULL AS lag_result
FROM `project.dataset.table` WHERE start_date != end_date
UNION ALL
SELECT *, LAG(start_date) OVER(ORDER BY PARSE_DATE('%d/%m/%Y', start_date))
FROM `project.dataset.table` WHERE start_date = end_date
с результатом
Row user_id start_date end_date lag_result
1 1 01/01/2019 28/02/2019 null
2 3 27/02/2019 28/02/2019 null
3 4 04/08/2019 01/09/2019 null
4 2 01/02/2019 01/02/2019 null
5 5 07/08/2019 07/08/2019 01/02/2019
6 6 27/08/2019 27/08/2019 07/08/2019
Используйте 1_
SELECT T.*,T1.LAG_Result
FROM TABLE T LEFT JOIN
(
SELECT User_Id,LAG(start_date) OVER(ORDER BY start_date) LAG_Result
FROM TABLE S
WHERE start_date = end_date
) T1 ON T.User_Id = T1.User_Id
