go.fuchsia.dev/infra@v0.0.0-20240507153436-9b593402251b/cmd/update_test_durations/query.sql (about)

     1  -- Copyright 2022 The Fuchsia Authors. All rights reserved.
     2  -- Use of this source code is governed by a BSD-style license that can be
     3  -- found in the LICENSE file.
     4  
     5  -- Computes the median duration of each Fuchsia test, scoped per-builder.
     6  --
     7  -- Input parameters:
     8  --   project (string): Name of the LUCI project to query data from.
     9  --   data_window_days (int): Number of days' worth of data to query.
    10  --
    11  -- Output columns:
    12  --   Name (string): Name of the test.
    13  --   Runs (int): Number of runs of this test found in the window.
    14  --   MedianDurationMS (int): Median duration of the test.
    15  --   Builder (string): Name of the builder from which the data comes.
    16  
    17  WITH
    18    resultdb AS (
    19      SELECT
    20        IF(
    21          exported.id LIKE 'build-%', CAST(SUBSTR(exported.id, 7) AS INT64), NULL)
    22          AS buildid,
    23        *
    24      FROM
    25        (
    26          SELECT * FROM `fuchsia-infra.resultdb.dev`
    27          UNION ALL
    28          SELECT * FROM `fuchsia-infra.resultdb.try`
    29          UNION ALL
    30          SELECT * FROM `fuchsia-infra.resultdb.ci`
    31        )
    32      WHERE partition_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @data_window_days DAY)
    33      -- Including only tests with `gn_label` set ensures that we ignore
    34      -- tefmocheck tests as well as test cases, whose durations we don't care
    35      -- about because they can't be scheduled independently.
    36      AND EXISTS(SELECT * FROM UNNEST(tags) WHERE key = "gn_label")
    37    ),
    38    buildbucket AS (
    39      SELECT *
    40      FROM `cr-buildbucket.fuchsia.builds`
    41      WHERE builder.project = @project
    42    ),
    43    test_durations AS (
    44      (
    45        SELECT
    46          r.test_id AS name,
    47          CAST(duration * 1000 AS INT64) AS duration_ms,
    48          b.builder.builder AS builder,
    49        FROM resultdb AS r
    50        JOIN buildbucket AS b ON r.buildid = b.id
    51        WHERE
    52          r.status = 'PASS'
    53          AND r.duration > 0
    54      )
    55    )
    56  SELECT
    57    name AS Name,
    58    COUNT(*) AS Runs,
    59    APPROX_QUANTILES(duration_ms, 100)[OFFSET(50)] AS MedianDurationMS,
    60    builder AS Builder,
    61  FROM test_durations
    62  GROUP BY name, builder
    63  ORDER BY name DESC;