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 <[email protected]>
AuthorDate: Thu May 4 15:36:44 2023 +0800
fix: update dora metrics (#508)
Co-authored-by: Startrekzky <[email protected]>
---
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.
