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
commit b2382ef6f912663a34ac8162d97551bd9eb31d1c Author: Startrekzky <[email protected]> AuthorDate: Fri Oct 14 18:11:23 2022 +0800 docs: update SQLs for CLT --- docs/Metrics/LeadTimeForChanges.md | 66 +++++++++------------ static/img/Metrics/lead-time-for-changes-text.jpeg | Bin 40507 -> 20549 bytes 2 files changed, 28 insertions(+), 38 deletions(-) diff --git a/docs/Metrics/LeadTimeForChanges.md b/docs/Metrics/LeadTimeForChanges.md index 7f6140f8e..5a38dd1c4 100644 --- a/docs/Metrics/LeadTimeForChanges.md +++ b/docs/Metrics/LeadTimeForChanges.md @@ -64,37 +64,31 @@ If you want to measure the monthly trend of median lead time for changes as the  ``` -with _deployment_change_lead_time as ( --- to get each deployment's change lead time +with _pr_stats as ( +-- get PRs' cycle lead time in each month SELECT - ct.id as deployment_id, - ct.name as deployment_name, - date_format(ct.finished_date,'%y/%m') as month, - avg(pr.change_timespan) as change_lead_time_of_a_deployment + pr.id, + date_format(pr.merged_date,'%y/%m') as month, + pr.change_timespan as pr_cycle_time FROM - cicd_tasks ct - join cicd_pipeline_commits cpc on ct.pipeline_id = cpc.pipeline_id - join pull_requests pr on cpc.commit_sha = pr.merge_commit_sha + pull_requests pr WHERE - ct.type = 'DEPLOYMENT' - and ct.result = 'success' - and $__timeFilter(ct.finished_date) - GROUP BY 1,2,3 + pr.merged_date is not null + and pr.change_timespan is not null + and $__timeFilter(pr.merged_date) ), _find_median_clt_each_month as ( - SELECT - x.month, x.change_lead_time_of_a_deployment - from _deployment_change_lead_time x join _deployment_change_lead_time y on x.month = y.month - WHERE x.change_lead_time_of_a_deployment is not null and y.change_lead_time_of_a_deployment is not null - GROUP BY x.month, x.change_lead_time_of_a_deployment - HAVING SUM(SIGN(1-SIGN(y.change_lead_time_of_a_deployment-x.change_lead_time_of_a_deployment)))/COUNT(*) > 0.5 + 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_clt_rank_each_month as ( SELECT *, - rank() over(PARTITION BY month ORDER BY change_lead_time_of_a_deployment) as _rank + rank() over(PARTITION BY month ORDER BY med_change_lead_time) as _rank FROM _find_median_clt_each_month ), @@ -102,7 +96,7 @@ _find_clt_rank_each_month as ( _clt as ( SELECT month, - change_lead_time_of_a_deployment as med_change_lead_time + med_change_lead_time from _find_clt_rank_each_month WHERE _rank = 1 ), @@ -135,29 +129,25 @@ If you want to measure in which category your team falls into as the picture sho  ``` -with _deployment_change_lead_time as ( --- get one deployment's change lead time +-- Metric 2: median change lead time +with _pr_stats as ( +-- get PRs' cycle time in the selected period SELECT - ct.id as deployment_id, - ct.name as deployment_name, - ct.finished_date as deployed_at, - avg(pr.change_timespan) as change_lead_time_of_a_deployment + pr.id, + pr.change_timespan as pr_cycle_time FROM - cicd_tasks ct - join cicd_pipeline_commits cpc on ct.pipeline_id = cpc.pipeline_id - join pull_requests pr on cpc.commit_sha = pr.merge_commit_sha + pull_requests pr WHERE - ct.type = 'DEPLOYMENT' - and ct.result = 'success' - and $__timeFilter(ct.finished_date) - GROUP BY 1,2,3 + pr.merged_date is not null + and pr.change_timespan is not null + and $__timeFilter(pr.merged_date) ), _median_change_lead_time as ( - SELECT x.change_lead_time_of_a_deployment as median_change_lead_time from _deployment_change_lead_time x, _deployment_change_lead_time y - WHERE x.change_lead_time_of_a_deployment is not null and y.change_lead_time_of_a_deployment is not null - GROUP BY x.change_lead_time_of_a_deployment - HAVING SUM(SIGN(1-SIGN(y.change_lead_time_of_a_deployment-x.change_lead_time_of_a_deployment)))/COUNT(*) > 0.5 +-- 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 ) diff --git a/static/img/Metrics/lead-time-for-changes-text.jpeg b/static/img/Metrics/lead-time-for-changes-text.jpeg index 6e1ae4ef7..5a9e979ae 100644 Binary files a/static/img/Metrics/lead-time-for-changes-text.jpeg and b/static/img/Metrics/lead-time-for-changes-text.jpeg differ
