Блог

Я написал скрипт для выполнения следующего

• создайте массив day_event , содержащий EventID для событий при условии 1, для которых требуется очистка методом 1
• создайте массив night_event для условия 2, для которого требуется очистка методом 2
• создайте массив cross_day_event для условия 3, для которого требуется метод очистки 3
• повторите цикл запроса day_event и очистите каждое событие, используя метод 1
• повторите цикл запроса night_event и очистите каждое событие, используя метод 2
• повторите цикл запроса cross_day_event и очистите каждое событие, используя метод 3
  — ПРИМЕЧАНИЕ: после очистки одна строка будет разбита на несколько строк
• запрашивайте исходные данные, но удаляйте исходную запись и объединяйте все очищенные данные

Я считаю, что сценарий мог бы быть намного чище, но я не знаю как. Есть предложения? Спасибо!

 declare day_event array<int64>;
declare night_event array<int64>;
declare cross_day_event array<int64>;
declare i int64 default 0;
declare j int64 default 0;
declare k int64 default 0;

-- query three arrays based on different conditions. 
-- each condition needs a slightly different cleaning method.
set day_event = array(
select eventId from
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj
from `event_raw_data`)
where condition_1
);
set night_event = array(
select eventId from
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj
from `event_raw_data`)
where condition_2
);
set cross_day_event = array(
select eventId from
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj
from `event_raw_data`)
where condition_3
);

-- array 1 and condition 1
create or replace temp table day_event_clean as
select cast(null as int64) as eventId, cast(null as timestamp) as startTime_adj, cast(null as timestamp) as endTime_adj;

loop 
set i = i 1;
if i > array_length(day_event) then leave;
end if;
create or replace temp table day_event_clean as
-- here is the cleaning I need to do
select t.eventId, greatest(startTime_adj, dt) as startTime_adj, least(timestamp_add(dt, interval 12 hour), endTime_adj) as endTime_adj from 
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj from `event_raw_data` where eventId = day_event[ordinal(i)]) t cross join
unnest(generate_timestamp_array(timestamp(date(startTime_adj)), timestamp_add(timestamp(date(endTime_adj)), interval 12 hour), interval 12 hour)) dt
union all select * from day_event_clean;
-- end of cleaning
end loop;

-- array 2 and condition 2
create or replace temp table night_event_clean as
select cast(null as int64) as eventId, cast(null as timestamp) as startTime_adj, cast(null as timestamp) as endTime_adj;

loop
set j = j 1;
if j > array_length(night_event) then leave;
end if;
create or replace temp table night_event_clean as
select t.eventId, greatest(startTime_adj, dt) as startTime_adj, least(timestamp_add(dt, interval 12 hour), endTime_adj) as endTime_adj from 
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj from `event_raw_data` where eventId = night_event[ordinal(j)]) t cross join
unnest(generate_timestamp_array(timestamp_add(timestamp(date(startTime_adj)), interval 12 hour), timestamp_add(timestamp(date(endTime_adj)), interval 12 hour), interval 12 hour)) dt
union all select * from night_event_clean;
end loop;

-- array 3 and condition 3
create or replace temp table cross_day_event_clean as
select cast(null as int64) as eventId, cast(null as timestamp) as startTime_adj, cast(null as timestamp) as endTime_adj;

loop
set k = k 1;
if k > array_length(cross_day_event) then leave;
end if;
create or replace temp table cross_day_event_clean as 
select t.eventId, greatest(startTime_adj, dt) as startTime_adj, least(timestamp_add(dt, interval 12 hour), endTime_adj) as endTime_adj from 
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj from `event_raw_data` where eventId = cross_day_event[ordinal(k)]) t cross join
unnest(generate_timestamp_array(timestamp_add(timestamp(date(startTime_adj)), interval 12 hour), timestamp(date(endTime_adj)), interval 12 hour)) dt
union all select * from cross_day_event_clean;
end loop;

--query the original data and union with all the cleaned data
select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj from `event_raw_data` where eventId not in
(condition_1 or condition_2 or conditon_3) union all
select * from day_event_clean where eventId is not null union all
select * from night_event_clean where eventId is not null union all
select * from cross_day_event_clean where eventId is not null
order by startTime_adj;
  

Быстрая рекомендация № 1 от быстрого просмотра верхней части вашего скрипта

Итак, приведенный ниже фрагмент слишком подробный и, что наиболее важно, запрашивает одну и ту же таблицу три раза — так что стоимость в 3 раза больше, чем должна быть!!!

 -- query three arrays based on different conditions. 
-- each condition needs a slightly different cleaning method.
set day_event = array(
select eventId from
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj
from `event_raw_data`)
where condition_1
);
set night_event = array(
select eventId from
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj
from `event_raw_data`)
where condition_2
);
set cross_day_event = array(
select eventId from
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj
from `event_raw_data`)
where condition_3
);    
  

вместо above вы должны использовать below simple и только с одним сканированием вашего скрипта таблицы

 set (day_event, night_event, cross_day_event) = (
  select as struct
    array_agg(if(condition_1, id, null) ignore nulls ) array1,
    array_agg(if(condition_2, id, null) ignore nulls ) array2,
    array_agg(if(condition_3, id, null) ignore nulls ) array3
  from (
    select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj
    from `event_raw_data` 
  )
);   
  

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

Еще один совет — использование циклов в вашем случае вообще не оправдано — вам не нужно выполнять цикл только для перебора элементов массивов — вы скорее делаете это заданным способом (sql-способом — в одном запросе), используя функцию unnest

Наконец, если вам все еще понадобится помощь — я рекомендую вам упростить пример вашей логики цикла и опубликовать его как отдельный вопрос — в противном случае он (текущий вопрос) слишком широк, чтобы ответить и рассмотреть все возможные улучшения (которых здесь довольно много)

Обновить

Есть несколько дополнительных минут, чтобы следить за вашими циклами.
Итак, давайте преобразуем ваш первый цикл
вместо приведенного ниже фрагмента вашего скрипта

 -- array 1 and condition 1
create or replace temp table day_event_clean as
select cast(null as int64) as eventId, cast(null as timestamp) as startTime_adj, cast(null as timestamp) as endTime_adj;

loop 
set i = i 1;
if i > array_length(day_event) then leave;
end if;
create or replace temp table day_event_clean as
-- here is the cleaning I need to do
select t.eventId, greatest(startTime_adj, dt) as startTime_adj, least(timestamp_add(dt, interval 12 hour), endTime_adj) as endTime_adj from 
(select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj from `event_raw_data` where eventId = day_event[ordinal(i)]) t cross join
unnest(generate_timestamp_array(timestamp(date(startTime_adj)), timestamp_add(timestamp(date(endTime_adj)), interval 12 hour), interval 12 hour)) dt
union all select * from day_event_clean;
-- end of cleaning
end loop;   
  

вы можете просто использовать только один простой запрос

 create or replace temp table day_event_clean as
select t.eventId, greatest(startTime_adj, dt) as startTime_adj, least(timestamp_add(dt, interval 12 hour), endTime_adj) as endTime_adj 
from (
  select eventId, timestamp_sub(startTime_CST, interval  6 hour) as startTime_adj, timestamp_sub(endTime_CST, interval  6 hour) as endTime_adj 
  from `event_raw_data` 
  where eventId in unnest(day_event)
) t 
cross join unnest(
  generate_timestamp_array(timestamp(date(startTime_adj)), timestamp_add(timestamp(date(endTime_adj)), interval 12 hour), interval 12 hour)
) dt     
  

это не только менее подробно и проще в обслуживании — это экономит вам $$$, потому что вместо сканирования event_raw_data таблицы столько раз, сколько элементов в соответствующем массиве, приведенный выше запрос выполняет только единицы!!!

тот же подход применим к остальным двум циклам

и, наконец, после того, как вы примените все вышесказанное и получите компактный и управляемый скрипт — я действительно надеюсь, что вы увидите, что весь ваш оригинальный [огромный] скрипт может быть реализован как один относительно простой запрос — всего один. как я упоминал ранее — если вы дойдете до этого момента и вам все еще понадобится помощь — отправьте новый вопрос, используя скрипт, к которому вы сможете добраться к этому моменту

удачи : o)