Блог

Я изо всех сил пытаюсь понять, как написать запрос, который будет правильно агрегировать приведенные ниже примеры данных. Эти примерные данные представляют собой выходные данные сводного запроса, которые мне затем нужно отфильтровать дальше. Я не уверен на 100% в этом, но я думаю, что мне нужно провести сеанс с этими данными , где сеанс начинается с первой строки resource_name , которая предназначена null для complete , и заканчивается, когда имя ресурса true указано в столбце complete , с задержкой ~ 30 минут.

К сожалению, у меня нет возможности изменять данные, и все приведенные ниже выходные данные взяты из одной таблицы с запросом, который агрегирует поля как таковые.

Примеры Данных:

 |resource_name|operation_type|initiate|complete|timestamp             |
|-------------|--------------|--------|--------|----------------------|
|foo          |full          |true    |null    |2021-11-01-5:51:46 UTC|
|foo          |full          |null    |true    |2021-11-01-5:51:49 UTC|
|foo          |incomplete    |null    |null    |2021-11-01-7:02:22 UTC|  <--- foo begins
|foo          |incomplete    |null    |null    |2021-11-01-7:02:37 UTC|
|foo          |incomplete    |null    |null    |2021-11-01-7:03:19 UTC|
|baz          |incomplete    |null    |null    |2021-11-01-7:03:25 UTC|
|baz          |incomplete    |null    |null    |2021-11-01-7:03:29 UTC|
|foo          |full          |true    |null    |2021-11-01-7:03:31 UTC|
|foo          |full          |null    |true    |2021-11-01-7:12:55 UTC|  <--- foo ends
|bar          |incomplete    |null    |null    |2021-11-01-7:39:22 UTC|  <--- bar starts
|bar          |incomplete    |null    |null    |2021-11-01-7:40:37 UTC|
|baz          |incomplete    |null    |null    |2021-11-01-7:41:37 UTC|
|baz          |incomplete    |null    |null    |2021-11-01-7:41:39 UTC|
|baz          |incomplete    |null    |null    |2021-11-01-7:41:45 UTC|
|bar          |incomplete    |null    |null    |2021-11-01-7:44:19 UTC|
|bar          |incomplete    |null    |null    |2021-11-01-7:44:58 UTC|
|bar          |full          |true    |null    |2021-11-01-7:45:31 UTC|
|bar          |full          |null    |true    |2021-11-01-7:47:55 UTC|  <--- bar ends
|bar          |incomplete    |null    |null    |2021-11-01-9:38:22 UTC|  <--- bar starts again 
|bar          |incomplete    |null    |null    |2021-11-01-9:40:37 UTC|
|bar          |full          |true    |null    |2021-11-01-9:45:31 UTC|
|bar          |full          |null    |true    |2021-11-01-9:51:55 UTC|  <--- bar ends again
 

Что я пытаюсь сделать , так это найти различия во временных метках для каждого resource_name из них между первым incomplete operation_type и следующим full operation_type местом complete = true , для каждого имени ресурса.

Итак, в этом случае я бы вернул одно значение для foo и два значения для bar . foo имеет один инициал incomplete operation_type и один full operation_type с complete = true , и bar имеет два экземпляра одного и того же.

Мои результаты должны быть (продолжительность не вычисляется, чтобы вы могли видеть временные метки, которые должны быть подобраны, отсортированы по времени):

 |resource_name|duration                                                        |
|-------------|----------------------------------------------------------------|
|bar          | timestamp_diff(2021-11-01-9:51:55 - 2021-11-01-9:38:22, SECOND)|
|foo          | timestamp_diff(2021-11-01-7:12:55 - 2021-11-01-7:02:22, SECOND)|
|bar          | timestamp_diff(2021-11-01-7:47:55 - 2021-11-01-7:39:22, SECOND)|
 

Основываясь на предоставленной вами информации, я пришел с этим решением:

Я следую этим шагам, чтобы воссоздать его на своей стороне:

1. создайте таблицу .csv на основе данных, которые вы предоставляете в своем сообщении.
2. создал набор данных и таблицу
3. заполнил таблицу файлом .csv
4. выполните следующий запрос.

Таким образом, подход должен заключаться в том, чтобы сгруппировать ваши успешные пробеги. С этого момента вам просто нужно убедиться, что полученные вами неполные значения времени необходимо учитывать при расчете дифференциации.

CSV

 foo,full,true,,2021-11-01 05:51:46
foo,full,,true,2021-11-01 05:51:49
foo,incomplete,,,2021-11-01 07:02:22
foo,incomplete,,,2021-11-01 07:02:37
foo,incomplete,,,2021-11-01 07:03:19
baz,incomplete,,,2021-11-01 07:03:25
baz,incomplete,,,2021-11-01 07:03:29
foo,full,true,,2021-11-01 07:03:31
foo,full,,true,2021-11-01 07:12:55
bar,incomplete,,,2021-11-01 07:39:22
bar,incomplete,,,2021-11-01 07:40:37
baz,incomplete,,,2021-11-01 07:41:37
baz,incomplete,,,2021-11-01 07:41:39
baz,incomplete,,,2021-11-01 07:41:45
bar,incomplete,,,2021-11-01 07:44:19
bar,incomplete,,,2021-11-01 07:44:58
bar,full,true,,2021-11-01 07:45:31
bar,full,,true,2021-11-01 07:47:55
bar,incomplete,,,2021-11-01 09:38:22
bar,incomplete,,,2021-11-01 09:40:37
bar,full,true,,2021-11-01 09:45:31
bar,full,,true,2021-11-01 09:51:55
 

Код Bigquery

 with operations_success as (
    select resource_name,
    operation_type,
    timestamp,
    row_number() over(partition by resource_name) as occurrence
    from <MY BIGQUERY TABLE>  
    where operation_type = 'full' and complete = True
)
select resource_name,duration
from (
    select s.resource_name
    ,t.occurrence 
    ,t.timestamp as success_time
    ,date_diff(t.timestamp,case when t.occurrence = 1 then MIN(s.timestamp) 
                                    else (select MIN(x.timestamp) from <MY BIGQUERY TABLE> x 
                                    left join operations_success xs on x.resource_name = xs.resource_name and occurrence = t.occurrence-1
                                    where x.resource_name = s.resource_name and x.timestamp > xs.timestamp
                                    and x.operation_type = 'incomplete') end
    , SECOND) as duration
    from <MY BIGQUERY TABLE> s 
    inner join operations_success t on s.resource_name = t.resource_name
    where s.operation_type = 'incomplete'
    group by s.resource_name,t.occurrence,t.timestamp
) as resource_log
where duration > 0
order by success_time 
 

Обновление 05/11/2021: Я обновил код и процесс проверки, чтобы отразить последний комментарий.

выход будет:

 1| foo | 633
2| bar | 513
3| bar | 813
 

Пожалуйста, дайте мне знать, если этот код поможет вам в вашем процессе.

С уважением,