You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@devlake.apache.org by ab...@apache.org on 2023/02/03 11:33:26 UTC
[incubator-devlake] branch release-v0.15 updated: fix: optimize the median calculation logic of sql (#4311)
This is an automated email from the ASF dual-hosted git repository.
abeizn pushed a commit to branch release-v0.15
in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
The following commit(s) were added to refs/heads/release-v0.15 by this push:
new ca2a20351 fix: optimize the median calculation logic of sql (#4311)
ca2a20351 is described below
commit ca2a2035187727190d72e66dcb38c273362613dd
Author: abeizn <zi...@merico.dev>
AuthorDate: Fri Feb 3 19:19:18 2023 +0800
fix: optimize the median calculation logic of sql (#4311)
* fix: optimize the median calculation logic of sql
* fix: optimize the median calculation logic of sql
---
grafana/dashboards/DORA.json | 18 +++++++++---------
1 file changed, 9 insertions(+), 9 deletions(-)
diff --git a/grafana/dashboards/DORA.json b/grafana/dashboards/DORA.json
index 412a423cb..832e43715 100644
--- a/grafana/dashboards/DORA.json
+++ b/grafana/dashboards/DORA.json
@@ -15,8 +15,8 @@
"editable": true,
"gnetId": null,
"graphTooltip": 0,
- "id": 27,
- "iteration": 1671764269365,
+ "id": 28,
+ "iteration": 1675408051556,
"links": [],
"panels": [
{
@@ -143,7 +143,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- get the last few months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELE [...]
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- get the last few months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELE [...]
"refId": "A",
"select": [
[
@@ -242,7 +242,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- get the last few months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELE [...]
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- get the last few months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELE [...]
"refId": "A",
"select": [
[
@@ -341,7 +341,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 2: median change lead time\nwith _pr_stats as (\n-- get PRs' cycle time in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tprm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics prm on prm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project) \n\t\tand pr.merged_date is not null\n\t\tand prm.pr_cycle_time is not null\n\t\tand $__timeFilter(pr.merged_date)\n),\n\n_med [...]
+ "rawSql": "-- Metric 2: median change lead time\nwith _pr_stats as (\n-- get PRs' cycle time in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tprm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics prm on prm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project) \n\t\tand pr.merged_date is not null\n\t\tand prm.pr_cycle_time is not null\n\t\tand $__timeFilter(pr.merged_date)\n),\n\n_med [...]
"refId": "A",
"select": [
[
@@ -444,7 +444,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type = 'INCIDENT'\n\t\tand $__tim [...]
+ "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type = 'INCID [...]
"refId": "A",
"select": [
[
@@ -727,7 +727,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Metric 2: median change lead time per month\nwith _pr_stats as (\n-- get PRs' cycle lead time in each month\n\tSELECT\n\t\tdistinct pr.id,\n\t\tdate_format(pr.merged_date,'%y/%m') as month,\n\t\tprm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin project_pr_metrics prm on prm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\tWHERE\n\t\tpr.merged_date is not null\n\t\tand prm.pr_cycle_time is not null\n\t\tand $__timeFilter(pr.merged_d [...]
+ "rawSql": "-- Metric 2: median change lead time per month\nwith _pr_stats as (\n-- get PRs' cycle lead time in each month\n\tSELECT\n\t\tdistinct pr.id,\n\t\tdate_format(pr.merged_date,'%y/%m') as month,\n\t\tprm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin project_pr_metrics prm on prm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\tWHERE\n\t\tpr.merged_date is not null\n\t\tand prm.pr_cycle_time is not null\n\t\tand $__timeFilter(pr.merged_d [...]
"refId": "A",
"select": [
[
@@ -841,7 +841,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Metric 3: median time to restore service - MTTR\nwith _incidents as (\n-- get the incident count each month\n\tSELECT\n\t\tdate_format(i.created_date,'%y/%m') as month,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type = 'INCIDENT'\n),\n\ [...]
+ "rawSql": "-- Metric 3: median time to restore service - MTTR\nwith _incidents as (\n-- get the incident count each month\n\tSELECT\n\t distinct i.id,\n\t\tdate_format(i.created_date,'%y/%m') as month,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type [...]
"refId": "A",
"select": [
[
@@ -1029,5 +1029,5 @@
"timezone": "",
"title": "DORA",
"uid": "qNo8_0M4z",
- "version": 8
+ "version": 7
}
\ No newline at end of file