Pretty new to big query and hitting timeouts on a 100M point dataset. I'm trying to find the points at which we reach a consistent series of values around 0 (stops) and points at which we consistently get above 0 (starts).
I saved the subquery that's being joined that determines the start file times to it's own dataset, but that didn't help. (seconds increments through multiple "files."
The part causing the problem is the initial aggregation of previous pts and next pts.
WITH test AS
(SELECT 'A' as ACM, CAST('2017-01-01' AS DATE) as file_date, CAST('10:10:10' AS TIME) as file_time , 0.0 as value, 0.1 as seconds
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', 0, 0.2 #start
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', 2000, 0.3
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', 1000, 0.4
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', 0, 0.5
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', -1000, 0.6
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', -2000, 0.7
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', 0, 0.8 #stop
UNION ALL SELECT 'A', '2017-01-01', '10:10:10', 0, 0.9
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 0, 1.0 #start
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 1000, 1.1
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 1000, 1.2
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 2000, 1.3
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 0, 1.4
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 0, 1.5
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', -1000, 1.6
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', -2000, 1.7
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 0, 1.8
UNION ALL SELECT 'A', '2017-01-01', '10:10:11', 1000, 1.9
UNION ALL SELECT 'A', '2017-01-01', '10:10:12', 2000, 2.0
UNION ALL SELECT 'A', '2017-01-01', '10:10:12', 1000, 2.1
UNION ALL SELECT 'A', '2017-01-01', '10:10:12', 0, 2.2 #stop
UNION ALL SELECT 'A', '2017-01-01', '10:10:12', 20, 2.3
UNION ALL SELECT 'A', '2017-01-01', '10:10:12', 0, 2.4
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', 0, 0.1
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', 0, 0.2 #start
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', 2000, 0.3
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', 1000, 0.4
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', 0, 0.5
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', -1000, 0.6
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', -2000, 0.7
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', 0, 0.8 #stop
UNION ALL SELECT 'B', '2017-01-01', '10:10:10', 0, 0.9
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 0, 1.0 #start
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 1000, 1.1
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 1000, 1.2
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 2000, 1.3
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 0, 1.4
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 0, 1.5
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', -1000, 1.6
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', -2000, 1.7
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 0, 1.8
UNION ALL SELECT 'B', '2017-01-01', '10:10:11', 1000, 1.9
UNION ALL SELECT 'B', '2017-01-01', '10:10:12', 2000, 2.0
UNION ALL SELECT 'B', '2017-01-01', '10:10:12', 1000, 2.1
UNION ALL SELECT 'B', '2017-01-01', '10:10:12', 0, 2.2 #stop
UNION ALL SELECT 'B', '2017-01-01', '10:10:12', 20, 2.3
UNION ALL SELECT 'B', '2017-01-01', '10:10:12', 0, 2.4 )
SELECT
acm,
file_date,
start_file_time,
file_times,
agg_sec as start_stop
FROM (
SELECT
acm,
file_date,
start_file_time,
file_times,
ARRAY_AGG(kind) OVER w AS agg_kind,
ARRAY_AGG(seconds) OVER w AS agg_sec
FROM (
SELECT
acm,
file_date,
start_file_time,
ARRAY(SELECT DISTINCT x FROM UNNEST(file_times) as x) AS file_times,
seconds,
CASE
WHEN (ABS(prev_val) < 50 and ABS(next_val) >= 50 and next_avg >= 50 and prev_avg < 50 ) THEN 'start'
WHEN (ABS(next_val) < 50 and ABS(prev_val) >= 50 and prev_avg >= 50 and next_avg < 50 ) THEN 'stop'
END as kind,
prev_val, next_val, prev_avg, next_avg
FROM (
SELECT
s.acm as acm,
s.file_date as file_date,
s.start_file_time as start_file_time,
seconds,
value,
ARRAY_AGG(s.file_time) OVER (PARTITION BY s.acm, s.file_date, s.start_file_time) as file_times,
AVG(ABS(value)) OVER prev as prev_avg,
NTH_VALUE(value, 2) OVER prev as prev_val,
AVG(ABS(value)) OVER next as next_avg,
NTH_VALUE(value, 2) OVER next as next_val
FROM test v
JOIN (
SELECT
acm,
file_date,
file_time,
TIME_SUB(file_time, INTERVAL CAST(FLOOR(MIN(seconds)) AS INT64) SECOND) as start_file_time
FROM test
GROUP BY acm, file_date, file_time
) s ON s.acm = v.acm AND s.file_date = v.file_date AND s.file_time = v.file_time
WINDOW prev AS (PARTITION BY s.acm, s.file_date, s.start_file_time ORDER BY seconds ROWS 2 PRECEDING), next AS (PARTITION BY s.acm, s.file_date, s.start_file_time ORDER BY seconds ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
)
WHERE value = 0)
WHERE kind IN ('start', 'stop')
WINDOW w AS (PARTITION BY acm, file_date, start_file_time ORDER BY seconds ROWS 1 PRECEDING))
WHERE ARRAY_LENGTH(agg_kind) = 2 AND agg_kind[ORDINAL(1)] = 'start' AND agg_kind[ORDINAL(2)] = 'stop'
;