github.com/abayer/test-infra@v0.0.5/metrics/configs/job-flakes-config.yaml (about)

     1  metric: job-flakes
     2  description: Computes flakiness for all jobs.
     3  query: |
     4    select /* Calculate probability a job flaked this week */
     5      job,
     6      round(sum(if(flaked==1,passed,runs))/sum(runs),3) build_consistency,
     7      round(1-sum(flaked)/count(distinct commit),3) commit_consistency,
     8      sum(flaked) flakes,
     9      sum(runs) runs
    10    from ( /* Determine whether a (job, pr-num, commit) flaked */
    11      select
    12        job,
    13        stamp,
    14        num,
    15        commit,
    16        if(passed == runs or passed == 0, 0, 1) flaked,
    17        passed,
    18        integer(runs) runs
    19      from (
    20        select /* Count the runs and passes for each (job, pr-num, commit) */
    21          max(stamp) stamp,
    22          num,
    23          if(kind = "pull", commit, version) commit,
    24          sum(if(result=='SUCCESS',1,0)) passed,
    25          count(result) runs
    26        from (
    27          SELECT /* all runs of any job for the past week, noting the commit and whether it passed */
    28            job,
    29            regexp_extract(path, r'pull/(\d+)') as num, /* pr number */
    30            if(left(job, 3) == "pr:", "pull", "ci") kind,
    31            version,
    32            regexp_extract(metadata.value, r'[^,]+,\d+:([a-f0-9]+)"') commit, /* extract the first commit id from the repo flag */
    33            date(started) stamp,
    34            date(date_add(date(started), -dayofweek(started), "day")) wk, /* TODO(fejta): remove or use */
    35            result
    36          FROM [k8s-gubernator:build.all]
    37          where
    38            started > date_add(current_timestamp(), -7, "DAY")
    39            and version != "unknown"
    40            and (metadata.key = 'repos' or left(job, 3) == "ci-")
    41        )
    42        group by job, num, commit
    43      )
    44    )
    45    group by job
    46    order by
    47      flakes desc,
    48      build_consistency,
    49      commit_consistency,
    50      job
    51  
    52  jqfilter: |
    53    [(.[] | select(.job | contains("pr:")) | {(.job): {
    54        flakes: (.flakes|tonumber),
    55        runs: (.runs|tonumber),
    56        consistency: (.commit_consistency|tonumber)
    57    }})] | add