1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
#standardSQL
WITH calibration AS (
SELECT
RTRIM(LTRIM(REGEXP_REPLACE(filtered_test_name, r'(/\d+)|(bins/.+/)|(cmake/.+/.+/)', ''))) AS test_binary,
REGEXP_EXTRACT(test_name, r'GRPC_POLL_STRATEGY=(\w+)') AS poll_strategy,
job_name,
build_id
FROM (
SELECT
REGEXP_REPLACE(test_name, r'(/\d+)|(GRPC_POLL_STRATEGY=.+)', '') AS filtered_test_name,
test_name,
job_name,
build_id,
timestamp
FROM
`grpc-testing.jenkins_test_results.aggregate_results`
WHERE
timestamp > TIMESTAMP(DATETIME("{calibration_begin} 00:00:00", "America/Los_Angeles"))
AND timestamp <= TIMESTAMP(DATETIME("{calibration_end} 23:59:59", "America/Los_Angeles"))
AND NOT REGEXP_CONTAINS(job_name,
'portability')
AND result != 'PASSED'
AND result != 'SKIPPED' )),
reporting AS (
SELECT
RTRIM(LTRIM(REGEXP_REPLACE(filtered_test_name, r'(/\d+)|(bins/.+/)|(cmake/.+/.+/)', ''))) AS test_binary,
REGEXP_EXTRACT(test_name, r'GRPC_POLL_STRATEGY=(\w+)') AS poll_strategy,
job_name,
build_id,
timestamp
FROM (
SELECT
REGEXP_REPLACE(test_name, r'(/\d+)|(GRPC_POLL_STRATEGY=.+)', '') AS filtered_test_name,
test_name,
job_name,
build_id,
timestamp
FROM
`grpc-testing.jenkins_test_results.aggregate_results`
WHERE
timestamp > TIMESTAMP(DATETIME("{reporting_begin} 00:00:00", "America/Los_Angeles"))
AND timestamp <= TIMESTAMP(DATETIME("{reporting_end} 23:59:59", "America/Los_Angeles"))
AND NOT REGEXP_CONTAINS(job_name,
'portability')
AND result != 'PASSED'
AND result != 'SKIPPED' ))
SELECT
reporting.test_binary,
reporting.poll_strategy,
reporting.job_name,
reporting.build_id,
STRING(reporting.timestamp, "America/Los_Angeles") as timestamp_MTV
FROM
reporting
LEFT JOIN
calibration
ON
reporting.test_binary = calibration.test_binary
WHERE
calibration.test_binary IS NULL
ORDER BY
timestamp DESC;
|
