You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
63 lines
1.9 KiB
63 lines
1.9 KiB
#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;
|