Блог

Итак, я использую данные Google Analytics в BigQuery и хочу иметь возможность отслеживать разницу между обращениями, сгруппированными по имеющемуся у меня пользовательскому измерению, которое может изменяться в течение сеанса. Если у меня есть данные, которые выглядят как

 __________________________
| customDimension1 | Time |
|__________________|______|
| abc              |  t1  |
| abc              |  t2  |
| def              |  t3  |
| def              |  t4  |
| def              |  t5  |
| abc              |  t6  |
| abc              |  t7  |
|__________________|______|
  

Я хочу иметь возможность получить что-то вроде

 _______________________________________
| customDimension1 | Time | Difference |
|__________________|______|____________|
| abc              |  t1  |  t2 - t1   |
| abc              |  t2  |  t3 - t2   |
| def              |  t3  |  t4 - t3   |
| def              |  t4  |  t5 - t4   |
| def              |  t5  |  t6 - t5   |
| abc              |  t6  |  t7 - t6   |
| abc              |  t7  |      0     |
|__________________|______|____________|
  

Есть хорошие идеи, как это сделать, не проходя через Dataflow / Dataproc для преобразований?

Ниже приведен для BigQuery Стандартный SQL (и предполагается, что столбец Time имеет тип данных TIMESTAMP)

 #standardSQL
SELECT *, 
  TIMESTAMP_DIFF(IFNULL(LEAD(Time) OVER(ORDER BY Time), Time), Time, SECOND) AS Difference 
FROM `project.dataset.table`
  

Вы можете протестировать, поиграть с приведенными выше фиктивными данными, как в примере ниже

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 'abc' customDimension1, TIMESTAMP '2020-08-26 23:03:21.938228 UTC' Time UNION ALL
  SELECT 'abc', '2020-08-26 23:03:23.938228 UTC' UNION ALL
  SELECT 'def', '2020-08-26 23:03:26.938228 UTC' UNION ALL
  SELECT 'def', '2020-08-26 23:03:28.938228 UTC' UNION ALL
  SELECT 'def', '2020-08-26 23:03:41.938228 UTC' UNION ALL
  SELECT 'abc', '2020-08-26 23:03:51.938228 UTC' UNION ALL
  SELECT 'abc', '2020-08-26 23:03:55.938228 UTC' 
)
SELECT *, 
  TIMESTAMP_DIFF(IFNULL(LEAD(Time) OVER(ORDER BY Time), Time), Time, SECOND) AS Difference 
FROM `project.dataset.table`   
  

с выводом

 Row customDimension1    Time                            Difference   
1   abc                 2020-08-26 23:03:21.938228 UTC  2    
2   abc                 2020-08-26 23:03:23.938228 UTC  3    
3   def                 2020-08-26 23:03:26.938228 UTC  2    
4   def                 2020-08-26 23:03:28.938228 UTC  13   
5   def                 2020-08-26 23:03:41.938228 UTC  10   
6   abc                 2020-08-26 23:03:51.938228 UTC  4    
7   abc                 2020-08-26 23:03:55.938228 UTC  0