You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@devlake.apache.org by yu...@apache.org on 2023/05/04 07:36:48 UTC
[incubator-devlake-website] branch main updated: fix: update dora metrics (#508)
This is an automated email from the ASF dual-hosted git repository.
yumeng pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-devlake-website.git
The following commit(s) were added to refs/heads/main by this push:
new ca3319f8b5 fix: update dora metrics (#508)
ca3319f8b5 is described below
commit ca3319f8b56d8e587e018d90307894b4891bcc0d
Author: Louis.z <lo...@gmail.com>
AuthorDate: Thu May 4 15:36:44 2023 +0800
fix: update dora metrics (#508)
Co-authored-by: Startrekzky <ka...@merico.dev>
---
docs/Metrics/CFR.md | 145 ++++++++++++++++--------------------
docs/Metrics/DeploymentFrequency.md | 105 +++++++++++++++-----------
docs/Metrics/LeadTimeForChanges.md | 102 ++++++++++++-------------
docs/Metrics/MTTR.md | 90 +++++++++++-----------
docs/Metrics/PRCycleTime.md | 4 +-
5 files changed, 227 insertions(+), 219 deletions(-)
diff --git a/docs/Metrics/CFR.md b/docs/Metrics/CFR.md
index b7e40b40da..64cc22e010 100644
--- a/docs/Metrics/CFR.md
+++ b/docs/Metrics/CFR.md
@@ -20,6 +20,8 @@ The number of deployments affected by incidents/total number of deployments. For
+When there are multiple deployments triggered by one pipeline, tools like GitLab and BitBucket will generate more than one deployment. In these cases, DevLake will consider these deployments as ONE deployment and use the last deployment's finished date as the deployment finished date.
+
Below are the benchmarks for different development teams from Google's report. However, it's difficult to tell which group a team falls into when the team's change failure rate is `18%` or `40%`. Therefore, DevLake provides its own benchmarks to address this problem:
| Groups | Benchmarks | DevLake Benchmarks |
@@ -50,38 +52,48 @@ This metric relies on:
<b>SQL Queries</b>
-If you want to measure the monthly trend of change failure rate, run the following SQL in Grafana.
+If you want to measure the monthly trend of Change Failure Rate, run the following SQL in Grafana.
```
with _deployments as (
--- get the deployments in each month
SELECT
- date_format(ct.finished_date,'%y/%m') as month,
- ct.id AS deployment_id
- FROM
- cicd_tasks ct
- join project_mapping pm on ct.cicd_scope_id = pm.row_id
+ cdc.cicd_deployment_id as deployment_id,
+ max(cdc.finished_date) as deployment_finished_date
+ FROM
+ cicd_deployment_commits cdc
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
WHERE
- pm.project_name in ($project)
- and type = 'DEPLOYMENT'
- and result = 'SUCCESS'
- and environment = 'PRODUCTION'
+ pm.project_name in ($project)
+ and cdc.result = 'SUCCESS'
+ and cdc.environment = 'PRODUCTION'
+ GROUP BY 1
+ HAVING $__timeFilter(max(cdc.finished_date))
),
-_incidents as (
--- get the incidents (caused by deployments) that are created within the selected time period in the top-right corner
+_failure_caused_by_deployments as (
+-- calculate the number of incidents caused by each deployment
SELECT
- date_format(i.created_date,'%y/%m') as month,
- i.id AS incident_id,
- pim.deployment_id
+ d.deployment_id,
+ d.deployment_finished_date,
+ count(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident
FROM
- issues i
- join project_issue_metrics pim on i.id = pim.id
- WHERE
- pim.project_name in ($project) and
- i.type = 'INCIDENT'
+ _deployments d
+ left join project_issue_metrics pim on d.deployment_id = pim.deployment_id
+ left join issues i on pim.id = i.id
+ GROUP BY 1,2
+),
+
+_change_failure_rate_for_each_month as (
+ SELECT
+ date_format(deployment_finished_date,'%y/%m') as month,
+ case
+ when count(deployment_id) is null then null
+ else sum(has_incident)/count(deployment_id) end as change_failure_rate
+ FROM
+ _failure_caused_by_deployments
+ GROUP BY 1
),
_calendar_months as(
@@ -94,86 +106,59 @@ _calendar_months as(
UNION ALL SELECT 10 UNION ALL SELECT 11
) month_index
WHERE (SYSDATE()-INTERVAL (month_index) MONTH) > SYSDATE()-INTERVAL 6 MONTH
-),
-
-_deployment_failures as (
--- calculate the number of incidents caused by each deployment
- SELECT
- distinct
- cm.month,
- d.deployment_id,
- count(distinct i.incident_id) as incident_count
- FROM
- _calendar_months cm
- left join _deployments d on cm.month = d.month
- left join _incidents i on d.deployment_id = i.deployment_id
- GROUP BY 1,2
)
-SELECT
- month,
- case when
- count(deployment_id) is null then null
- else count(case when incident_count = 0 then null else incident_count end)/count(deployment_id) end as change_failure_rate
-FROM _deployment_failures
-GROUP BY 1
-ORDER BY 1
+SELECT
+ cm.month,
+ cfr.change_failure_rate
+FROM
+ _calendar_months cm
+ left join _change_failure_rate_for_each_month cfr on cm.month = cfr.month
+GROUP BY 1,2
+ORDER BY 1
```
-If you want to measure in which category your team falls into, run the following SQL in Grafana.
+If you want to measure in which category your team falls, run the following SQL in Grafana.
```
with _deployments as (
--- get the deployment deployed within the selected time period in the top-right corner
+-- When deploying multiple commits in one pipeline, GitLab and BitBucket may generate more than one deployment. However, DevLake consider these deployments as ONE production deployment and use the last one's finished_date as the finished date.
SELECT
- ct.id AS deployment_id,
- ct.finished_date as deployment_finished_date
- FROM
- cicd_tasks ct
- join project_mapping pm on ct.cicd_scope_id = pm.row_id
+ cdc.cicd_deployment_id as deployment_id,
+ max(cdc.finished_date) as deployment_finished_date
+ FROM
+ cicd_deployment_commits cdc
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
WHERE
- pm.project_name in ($project)
- and type = 'DEPLOYMENT'
- and result = 'SUCCESS'
- and environment = 'PRODUCTION'
- and $__timeFilter(finished_date)
+ pm.project_name in ($project)
+ and cdc.result = 'SUCCESS'
+ and cdc.environment = 'PRODUCTION'
+ GROUP BY 1
+ HAVING $__timeFilter(max(cdc.finished_date))
),
-_incident_caused_by_deployments as (
--- get the incidents (caused by deployments) that are created within the selected time period in the top-right corner
+_failure_caused_by_deployments as (
+-- calculate the number of incidents caused by each deployment
SELECT
- i.id AS incident_id,
- pim.deployment_id
+ d.deployment_id,
+ d.deployment_finished_date,
+ count(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident
FROM
- issues i
- join project_issue_metrics pim on i.id = pim.id
- WHERE
- pim.project_name in ($project) and
- i.type = 'INCIDENT'
- and $__timeFilter(i.created_date)
-),
-
-_deployment_failures as (
--- calculate the number of incidents caused by each deployment
- SELECT
- distinct
- d.deployment_id,
- d.deployment_finished_date,
- count(distinct i.incident_id) as incident_count
- FROM
- _deployments d
- left join _incident_caused_by_deployments i on d.deployment_id = i.deployment_id
+ _deployments d
+ left join project_issue_metrics pim on d.deployment_id = pim.deployment_id
+ left join issues i on pim.id = i.id
GROUP BY 1,2
),
_change_failure_rate as (
SELECT
- case when count(deployment_id) is null then null
- else count(case when incident_count = 0 then null else 1 end)/count(deployment_id) end as change_failure_rate
+ case
+ when count(deployment_id) is null then null
+ else sum(has_incident)/count(deployment_id) end as change_failure_rate
FROM
- _deployment_failures
+ _failure_caused_by_deployments
)
SELECT
diff --git a/docs/Metrics/DeploymentFrequency.md b/docs/Metrics/DeploymentFrequency.md
index 9cd3c6cbcb..2aa57db4d2 100644
--- a/docs/Metrics/DeploymentFrequency.md
+++ b/docs/Metrics/DeploymentFrequency.md
@@ -16,7 +16,9 @@ DORA dashboard. See [live demo](https://grafana-lake.demo.devlake.io/grafana/d/q
## How is it calculated?
-Deployment frequency is calculated based on the number of deployment days, not the number of deployments, e.g.,daily, weekly, monthly, yearly.
+Deployment frequency is calculated based on the number of deployment days, not the number of deployments, e.g., daily, weekly, monthly, yearly.
+
+When there are multiple deployments triggered by one pipeline, tools like GitLab and BitBucket will generate more than one deployment. In these cases, DevLake will consider these deployments as ONE deployment and use the last deployment's finished date as the deployment finished date.
Below are the benchmarks for different development teams from Google's report. DevLake uses the same benchmarks.
@@ -48,21 +50,28 @@ If you want to measure the monthly trend of deployment count as the picture show
```
-with _deployments as (
--- get the deployment count each month
- SELECT
- date_format(finished_date,'%y/%m') as month,
- COUNT(distinct id) AS deployment_count
- FROM
- cicd_tasks
- WHERE
- type = 'DEPLOYMENT'
- and result = 'SUCCESS'
+with _deployments as(
+ SELECT
+ date_format(deployment_finished_date,'%y/%m') as month,
+ count(cicd_deployment_id) as deployment_count
+ FROM (
+ SELECT
+ cdc.cicd_deployment_id,
+ max(cdc.finished_date) as deployment_finished_date
+ FROM cicd_deployment_commits cdc
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
+ WHERE
+ pm.project_name in ($project)
+ and cdc.result = 'SUCCESS'
+ and cdc.environment = 'PRODUCTION'
+ GROUP BY 1
+ HAVING $__timeFilter(max(cdc.finished_date))
+ ) _production_deployments
GROUP BY 1
),
_calendar_months as(
--- deal with the month with no deployments
+-- construct the calendar months of last 6 months
SELECT date_format(CAST((SYSDATE()-INTERVAL (month_index) MONTH) AS date), '%y/%m') as month
FROM ( SELECT 0 month_index
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
@@ -82,13 +91,13 @@ FROM
ORDER BY 1
```
-If you want to measure in which category your team falls into as the picture shown below, run the following SQL in Grafana.
+If you want to measure in which category your team falls as in the picture shown below, run the following SQL in Grafana.
```
with last_few_calendar_months as(
--- get the last few months within the selected time period in the top-right corner
+-- construct the last few calendar months within the selected time period in the top-right corner
SELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day
FROM ( SELECT 0 H
UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
@@ -105,53 +114,63 @@ with last_few_calendar_months as(
(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()
),
+_production_deployment_days as(
+-- When deploying multiple commits in one pipeline, GitLab and BitBucket may generate more than one deployment. However, DevLake consider these deployments as ONE production deployment and use the last one's finished_date as the finished date.
+ SELECT
+ cdc.cicd_deployment_id as deployment_id,
+ max(DATE(cdc.finished_date)) as day
+ FROM cicd_deployment_commits cdc
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
+ WHERE
+ pm.project_name in ($project)
+ and cdc.result = 'SUCCESS'
+ and cdc.environment = 'PRODUCTION'
+ GROUP BY 1
+),
+
_days_weeks_deploy as(
+-- calculate the number of deployment days every week
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,
- MAX(if(deployments.day is not null, 1, 0)) as week_deployed,
- COUNT(distinct deployments.day) as days_deployed
+ MAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,
+ COUNT(distinct _production_deployment_days.day) as days_deployed
FROM
last_few_calendar_months
- LEFT JOIN(
- SELECT
- DATE(finished_date) AS day,
- id
- FROM cicd_tasks
- WHERE
- type = 'DEPLOYMENT'
- and result = 'SUCCESS') deployments ON deployments.day = last_few_calendar_months.day
+ LEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day
GROUP BY week
),
_monthly_deploy as(
+-- calculate the number of deployment days every month
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,
- MAX(if(deployments.day is not null, 1, 0)) as months_deployed
+ MAX(if(_production_deployment_days.day is not null, 1, 0)) as months_deployed
FROM
last_few_calendar_months
- LEFT JOIN(
- SELECT
- DATE(finished_date) AS day,
- id
- FROM cicd_tasks
- WHERE
- type = 'DEPLOYMENT'
- and result = 'SUCCESS') deployments ON deployments.day = last_few_calendar_months.day
+ LEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day
GROUP BY month
),
-_median_number_of_deployment_days_per_week as (
- SELECT x.days_deployed as median_number_of_deployment_days_per_week from _days_weeks_deploy x, _days_weeks_deploy y
- GROUP BY x.days_deployed
- HAVING SUM(SIGN(1-SIGN(y.days_deployed-x.days_deployed)))/COUNT(*) > 0.5
- LIMIT 1
+_median_number_of_deployment_days_per_week_ranks as(
+ SELECT *, percent_rank() over(order by days_deployed) as ranks
+ FROM _days_weeks_deploy
+),
+
+_median_number_of_deployment_days_per_week as(
+ SELECT max(days_deployed) as median_number_of_deployment_days_per_week
+ FROM _median_number_of_deployment_days_per_week_ranks
+ WHERE ranks <= 0.5
+),
+
+_median_number_of_deployment_days_per_month_ranks as(
+ SELECT *, percent_rank() over(order by months_deployed) as ranks
+ FROM _monthly_deploy
),
-_median_number_of_deployment_days_per_month as (
- SELECT x.months_deployed as median_number_of_deployment_days_per_month from _monthly_deploy x, _monthly_deploy y
- GROUP BY x.months_deployed
- HAVING SUM(SIGN(1-SIGN(y.months_deployed-x.months_deployed)))/COUNT(*) > 0.5
- LIMIT 1
+_median_number_of_deployment_days_per_month as(
+ SELECT max(months_deployed) as median_number_of_deployment_days_per_month
+ FROM _median_number_of_deployment_days_per_month_ranks
+ WHERE ranks <= 0.5
)
SELECT
diff --git a/docs/Metrics/LeadTimeForChanges.md b/docs/Metrics/LeadTimeForChanges.md
index efd9fe7c55..d96d194861 100644
--- a/docs/Metrics/LeadTimeForChanges.md
+++ b/docs/Metrics/LeadTimeForChanges.md
@@ -6,24 +6,24 @@ sidebar_position: 27
---
## What is this metric?
-The median amount of time for a commit to be deployed into production.
+The median amount of time for a code change to be deployed into production.
## Why is it important?
-This metric measures the time it takes to commit code to the production environment and reflects the speed of software delivery. A lower average change preparation time means that your team is efficient at coding and deploying your project.
+This metric measures the time it takes to a code change to the production environment and reflects the speed of software delivery. A lower average change preparation time means that your team is efficient at coding and deploying your project.
## Which dashboard(s) does it exist in
DORA dashboard. See [live demo](https://grafana-lake.demo.devlake.io/grafana/d/qNo8_0M4z/dora?orgId=1).
## How is it calculated?
-1. Find the deployments whose finished_date falls into the time range that users select
-2. Calculate the commits diff between each deployment by deployments' commit_sha
-3. Find the PRs mapped to the commits in step 2, now we have the relation of Deployment - Deployed_commits - Deployed_PRs.
-4. Calculate PR Deploy Time by using finish_time of deployment minus merge_time of PR
+This metric is quite similar to [PR Cycle Time](PRCycleTime.md). The difference is that 'Lead Time for Changes' uses a different method to filter PRs.
+
+1. Find the PRs' associated deployments whose finished_date falls into the time range that users select
+2. Calculate the PRs' median cycle time. This will be the Median Lead Time for Changes.
-PR cycle time is pre-calculated when dora plugin is triggered. You can connect to DevLake's database and find it in the field `change_timespan` in [table.pull_requests](https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema/#pull_requests).
+PR cycle time is pre-calculated by the `dora` plugin during every data collection. You can find it in `pr_cycle_time` in [table.project_pr_metrics](https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema/#project_pr_metrics) of DevLake's database.
Below are the benchmarks for different development teams from Google's report. However, it's difficult to tell which group a team falls into when the team's median lead time for changes is `between one week and one month`. Therefore, DevLake provides its own benchmarks to address this problem:
@@ -56,44 +56,37 @@ If you want to measure the monthly trend of median lead time for changes as the
```
with _pr_stats as (
--- get PRs' cycle lead time in each month
+-- get the cycle time of PRs deployed by the deployments finished each month
SELECT
- pr.id,
- date_format(pr.merged_date,'%y/%m') as month,
- pr.change_timespan as pr_cycle_time
+ distinct pr.id,
+ date_format(cdc.finished_date,'%y/%m') as month,
+ ppm.pr_cycle_time
FROM
pull_requests pr
+ join project_pr_metrics ppm on ppm.id = pr.id
+ join project_mapping pm on pr.base_repo_id = pm.row_id
+ join cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id
WHERE
- pr.merged_date is not null
- and pr.change_timespan is not null
- and $__timeFilter(pr.merged_date)
+ pm.project_name in ($project)
+ and pr.merged_date is not null
+ and ppm.pr_cycle_time is not null
+ and $__timeFilter(cdc.finished_date)
),
-_find_median_clt_each_month as (
- SELECT x.month, x.pr_cycle_time as med_change_lead_time
- FROM _pr_stats x JOIN _pr_stats y ON x.month = y.month
- GROUP BY x.month, x.pr_cycle_time
- HAVING SUM(SIGN(1-SIGN(y.pr_cycle_time-x.pr_cycle_time)))/COUNT(*) > 0.5
+_find_median_clt_each_month_ranks as(
+ SELECT *, percent_rank() over(PARTITION BY month order by pr_cycle_time) as ranks
+ FROM _pr_stats
),
-_find_clt_rank_each_month as (
- SELECT
- *,
- rank() over(PARTITION BY month ORDER BY med_change_lead_time) as _rank
- FROM
- _find_median_clt_each_month
-),
-
-_clt as (
- SELECT
- month,
- med_change_lead_time
- from _find_clt_rank_each_month
- WHERE _rank = 1
+_clt as(
+ SELECT month, max(pr_cycle_time) as median_change_lead_time
+ FROM _find_median_clt_each_month_ranks
+ WHERE ranks <= 0.5
+ group by month
),
_calendar_months as(
--- to deal with the month with no incidents
+-- to deal with the month with no incidents
SELECT date_format(CAST((SYSDATE()-INTERVAL (month_index) MONTH) AS date), '%y/%m') as month
FROM ( SELECT 0 month_index
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
@@ -107,38 +100,46 @@ _calendar_months as(
SELECT
cm.month,
case
- when _clt.med_change_lead_time is null then 0
- else _clt.med_change_lead_time/60 end as med_change_lead_time_in_hour
+ when _clt.median_change_lead_time is null then 0
+ else _clt.median_change_lead_time/60 end as median_change_lead_time_in_hour
FROM
_calendar_months cm
left join _clt on cm.month = _clt.month
ORDER BY 1
```
-If you want to measure in which category your team falls into as the picture shown below, run the following SQL in Grafana.
+If you want to measure in which category your team falls as in the picture shown below, run the following SQL in Grafana.
```
with _pr_stats as (
--- get PRs' cycle time in the selected period
+-- get the cycle time of PRs deployed by the deployments finished in the selected period
SELECT
- pr.id,
- pr.change_timespan as pr_cycle_time
+ distinct pr.id,
+ ppm.pr_cycle_time
FROM
- pull_requests pr
+ pull_requests pr
+ join project_pr_metrics ppm on ppm.id = pr.id
+ join project_mapping pm on pr.base_repo_id = pm.row_id
+ join cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id
WHERE
- pr.merged_date is not null
- and pr.change_timespan is not null
- and $__timeFilter(pr.merged_date)
+ pm.project_name in ($project)
+ and pr.merged_date is not null
+ and ppm.pr_cycle_time is not null
+ and $__timeFilter(cdc.finished_date)
+),
+
+_median_change_lead_time_ranks as(
+ SELECT *, percent_rank() over(order by pr_cycle_time) as ranks
+ FROM _pr_stats
),
-_median_change_lead_time as (
+_median_change_lead_time as(
-- use median PR cycle time as the median change lead time
- SELECT x.pr_cycle_time as median_change_lead_time from _pr_stats x, _pr_stats y
- GROUP BY x.pr_cycle_time
- HAVING SUM(SIGN(1-SIGN(y.pr_cycle_time-x.pr_cycle_time)))/COUNT(*) > 0.5
- LIMIT 1
+ SELECT max(pr_cycle_time) as median_change_lead_time
+ FROM _median_change_lead_time_ranks
+ WHERE ranks <= 0.5
)
SELECT
@@ -146,7 +147,8 @@ SELECT
WHEN median_change_lead_time < 60 then "Less than one hour"
WHEN median_change_lead_time < 7 * 24 * 60 then "Less than one week"
WHEN median_change_lead_time < 180 * 24 * 60 then "Between one week and six months"
- ELSE "More than six months"
+ WHEN median_change_lead_time >= 180 * 24 * 60 then "More than six months"
+ ELSE "N/A.Please check if you have collected deployments/incidents."
END as median_change_lead_time
FROM _median_change_lead_time
```
diff --git a/docs/Metrics/MTTR.md b/docs/Metrics/MTTR.md
index 536afc11b5..7465a99e2b 100644
--- a/docs/Metrics/MTTR.md
+++ b/docs/Metrics/MTTR.md
@@ -50,45 +50,38 @@ This metric relies on:
<b>SQL Queries</b>
-If you want to measure the monthly trend of median time to restore service as the picture shown below, run the following SQL in Grafana.
+If you want to measure the monthly trend of the Median Time to Restore Service as the picture shown below, run the following SQL in Grafana.
```
with _incidents as (
--- get the incident count each month
+-- get the number of incidents created each month
SELECT
- date_format(created_date,'%y/%m') as month,
+ distinct i.id,
+ date_format(i.created_date,'%y/%m') as month,
cast(lead_time_minutes as signed) as lead_time_minutes
FROM
- issues
+ issues i
+ join board_issues bi on i.id = bi.issue_id
+ join boards b on bi.board_id = b.id
+ join project_mapping pm on b.id = pm.row_id
WHERE
- type = 'INCIDENT'
+ pm.project_name in ($project)
+ and i.type = 'INCIDENT'
+ and i.lead_time_minutes is not null
),
-_find_median_mttr_each_month as (
- SELECT
- x.*
- from _incidents x join _incidents y on x.month = y.month
- WHERE x.lead_time_minutes is not null and y.lead_time_minutes is not null
- GROUP BY x.month, x.lead_time_minutes
- HAVING SUM(SIGN(1-SIGN(y.lead_time_minutes-x.lead_time_minutes)))/COUNT(*) > 0.5
+_find_median_mttr_each_month_ranks as(
+ SELECT *, percent_rank() over(PARTITION BY month order by lead_time_minutes) as ranks
+ FROM _incidents
),
-_find_mttr_rank_each_month as (
- SELECT
- *,
- rank() over(PARTITION BY month ORDER BY lead_time_minutes) as _rank
- FROM
- _find_median_mttr_each_month
-),
-
-_mttr as (
- SELECT
- month,
- lead_time_minutes as med_time_to_resolve
- from _find_mttr_rank_each_month
- WHERE _rank = 1
+_mttr as(
+ SELECT month, max(lead_time_minutes) as median_time_to_resolve
+ FROM _find_median_mttr_each_month_ranks
+ WHERE ranks <= 0.5
+ GROUP BY month
),
_calendar_months as(
@@ -106,15 +99,15 @@ _calendar_months as(
SELECT
cm.month,
case
- when m.med_time_to_resolve is null then 0
- else m.med_time_to_resolve/60 end as med_time_to_resolve_in_hour
+ when m.median_time_to_resolve is null then 0
+ else m.median_time_to_resolve/60 end as median_time_to_resolve_in_hour
FROM
_calendar_months cm
left join _mttr m on cm.month = m.month
ORDER BY 1
```
-If you want to measure in which category your team falls into as the picture shown below, run the following SQL in Grafana.
+If you want to measure in which category your team falls into as in the picture shown below, run the following SQL in Grafana.
@@ -122,31 +115,38 @@ If you want to measure in which category your team falls into as the picture sho
with _incidents as (
-- get the incidents created within the selected time period in the top-right corner
SELECT
+ distinct i.id,
cast(lead_time_minutes as signed) as lead_time_minutes
FROM
- issues
+ issues i
+ join board_issues bi on i.id = bi.issue_id
+ join boards b on bi.board_id = b.id
+ join project_mapping pm on b.id = pm.row_id
WHERE
- type = 'INCIDENT'
- and $__timeFilter(created_date)
+ pm.project_name in ($project)
+ and i.type = 'INCIDENT'
+ and $__timeFilter(i.created_date)
+),
+
+_median_mttr_ranks as(
+ SELECT *, percent_rank() over(order by lead_time_minutes) as ranks
+ FROM _incidents
),
-_median_mttr as (
- SELECT
- x.lead_time_minutes as med_time_to_resolve
- from _incidents x, _incidents y
- WHERE x.lead_time_minutes is not null and y.lead_time_minutes is not null
- GROUP BY x.lead_time_minutes
- HAVING SUM(SIGN(1-SIGN(y.lead_time_minutes-x.lead_time_minutes)))/COUNT(*) > 0.5
- LIMIT 1
+_median_mttr as(
+ SELECT max(lead_time_minutes) as median_time_to_resolve
+ FROM _median_mttr_ranks
+ WHERE ranks <= 0.5
)
SELECT
case
- WHEN med_time_to_resolve < 60 then "Less than one hour"
- WHEN med_time_to_resolve < 24 * 60 then "Less than one Day"
- WHEN med_time_to_resolve < 7 * 24 * 60 then "Between one day and one week"
- ELSE "More than one week"
- END as med_time_to_resolve
+ WHEN median_time_to_resolve < 60 then "Less than one hour"
+ WHEN median_time_to_resolve < 24 * 60 then "Less than one Day"
+ WHEN median_time_to_resolve < 7 * 24 * 60 then "Between one day and one week"
+ WHEN median_time_to_resolve >= 7 * 24 * 60 then "More than one week"
+ ELSE "N/A.Please check if you have collected deployments/incidents."
+ END as median_time_to_resolve
FROM
_median_mttr
```
diff --git a/docs/Metrics/PRCycleTime.md b/docs/Metrics/PRCycleTime.md
index 925ca9a642..f2e00ccc49 100644
--- a/docs/Metrics/PRCycleTime.md
+++ b/docs/Metrics/PRCycleTime.md
@@ -6,7 +6,9 @@ sidebar_position: 14
---
## What is this metric?
-PR Cycle Time is the sum of PR Coding Time, Pickup TIme, Review Time and Deploy Time. It is the total time from the first commit to when the PR is deployed.
+PR Cycle Time is the sum of PR Coding Time, PR Time-to-Merge and PR Deploy Time. It is the total time from the first commit to when the PR is deployed.
+
+The reason why we use PR Time-to-Merge rather than PR Pickup Time + PR Review Time is that a merged PR may not have any review. In this case, PR Pickup Time and PR Review Time will be NULL, while PR Time-to-Merge is not.
## Why is it important?
PR Cycle Time indicates the overall velocity of the delivery progress in terms of PR.