go.chromium.org/luci@v0.0.0-20240309015107-7cdc2e660f33/analysis/bigquery/test_variant_changepoints.sql (about)

     1  -- Copyright 2024 The LUCI Authors.
     2  --
     3  -- Licensed under the Apache License, Version 2.0 (the "License");
     4  -- you may not use this file except in compliance with the License.
     5  -- You may obtain a copy of the License at
     6  --
     7  --      http:--www.apache.org/licenses/LICENSE-2.0
     8  --
     9  -- Unless required by applicable law or agreed to in writing, software
    10  -- distributed under the License is distributed on an "AS IS" BASIS,
    11  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    12  -- See the License for the specific language governing permissions and
    13  -- limitations under the License.
    14  
    15  -- This materialized view caches changepoints and attributes used for grouping.
    16  -- This is used by the changepoints RPC service.
    17  -- If this needs to be updated, please do it manually through pantheon.
    18  CREATE OR REPLACE MATERIALIZED VIEW `internal.test_variant_changepoints`
    19  OPTIONS (
    20  	enable_refresh = true,
    21  	refresh_interval_minutes = 5,
    22  	max_staleness = INTERVAL "0:30:0" HOUR TO SECOND,
    23  	allow_non_incremental_definition = true
    24  )
    25  AS
    26  WITH
    27  merged_table AS (
    28  	SELECT *
    29  	FROM internal.test_variant_segment_updates
    30  	WHERE  has_recent_unexpected_results = 1
    31  	UNION ALL
    32  	SELECT *
    33  	FROM internal.test_variant_segments
    34  	WHERE has_recent_unexpected_results = 1
    35  ),
    36  merged_table_grouped AS (
    37  	SELECT
    38  		project, test_id, variant_hash, ref_hash,
    39  		ARRAY_AGG(m ORDER BY version DESC LIMIT 1)[OFFSET(0)] AS row
    40  	FROM merged_table m
    41  	GROUP BY project, test_id, variant_hash, ref_hash
    42  ),
    43  segments_with_failure_rate AS (
    44  	SELECT
    45  		project,
    46  		test_id,
    47  		variant_hash,
    48  		ref_hash,
    49  		row.variant,
    50  		row.ref,
    51  		segment,
    52  		idx,
    53  		SAFE_DIVIDE(segment.counts.unexpected_verdicts, segment.counts.total_verdicts) AS unexpected_verdict_rate,
    54  		SAFE_DIVIDE(tv.row.segments[0].counts.unexpected_verdicts, tv.row.segments[0].counts.total_verdicts) AS latest_unexpected_verdict_rate,
    55  		SAFE_DIVIDE(tv.row.segments[idx+1].counts.unexpected_verdicts, tv.row.segments[idx+1].counts.total_verdicts) AS previous_unexpected_verdict_rate,
    56  		tv.row.segments[idx+1].end_position AS previous_nominal_end_position
    57  	FROM merged_table_grouped tv, UNNEST(row.segments) segment WITH OFFSET idx
    58  	-- TODO: Filter out test variant branches with more than 10 segments is a bit hacky, but it filter out oscillate test variant branches.
    59  	-- It would be good to find a more elegant solution, maybe explicitly expressing this as a filter on the RPC.
    60  	WHERE ARRAY_LENGTH(row.segments) >= 2 AND ARRAY_LENGTH(row.segments) <= 10
    61  	AND idx + 1 < ARRAY_LENGTH(tv.row.segments)
    62  ),
    63  -- Obtain the alphabetical ranking for each test ID in each LUCI project.
    64  test_id_ranking AS (
    65  	SELECT project, test_id, ROW_NUMBER() OVER (PARTITION BY project ORDER BY test_id) AS row_num
    66  	FROM internal.test_variant_segments
    67  	GROUP BY project, test_id
    68  )
    69  SELECT
    70  	segment.* EXCEPT (segment, idx),
    71  	segment.segment.start_hour,
    72  	segment.segment.start_position_lower_bound_99th,
    73  	segment.segment.start_position,
    74  	segment.segment.start_position_upper_bound_99th,
    75  	ranking.row_num AS test_id_num
    76  FROM segments_with_failure_rate segment
    77  LEFT JOIN test_id_ranking ranking
    78  ON ranking.project = segment.project and ranking.test_id = segment.test_id
    79  -- Only keep regressions. A regression is a special changepoint when the later segment has a higher unexpected verdict rate than the earlier segment.
    80  -- In the future, we might want to return all changepoints to show fixes in the UI.
    81  WHERE segment.unexpected_verdict_rate - segment.previous_unexpected_verdict_rate > 0