This is an automated email from the ASF dual-hosted git repository.
hez 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 445eabe4ef docs: update SQL for pr cycle time related metrics (#487)
445eabe4ef is described below
commit 445eabe4ef0a74e5751b3b4e3e38834cd9c462de
Author: Louis.z <[email protected]>
AuthorDate: Tue Apr 18 08:07:57 2023 +0800
docs: update SQL for pr cycle time related metrics (#487)
Co-authored-by: Startrekzky <[email protected]>
---
docs/Metrics/PRCodingTime.md | 14 ++++++++------
docs/Metrics/PRCycleTime.md | 14 ++++++++------
docs/Metrics/PRDeployTime.md | 14 ++++++++------
docs/Metrics/PRPickupTime.md | 14 ++++++++------
docs/Metrics/PRReviewTime.md | 14 ++++++++------
versioned_docs/version-v0.16/Metrics/PRCodingTime.md | 14 ++++++++------
versioned_docs/version-v0.16/Metrics/PRCycleTime.md | 14 ++++++++------
versioned_docs/version-v0.16/Metrics/PRDeployTime.md | 14 ++++++++------
versioned_docs/version-v0.16/Metrics/PRPickupTime.md | 14 ++++++++------
versioned_docs/version-v0.16/Metrics/PRReviewTime.md | 14 ++++++++------
10 files changed, 80 insertions(+), 60 deletions(-)
diff --git a/docs/Metrics/PRCodingTime.md b/docs/Metrics/PRCodingTime.md
index 7f0ac87f9e..8d72194635 100644
--- a/docs/Metrics/PRCodingTime.md
+++ b/docs/Metrics/PRCodingTime.md
@@ -27,13 +27,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `coding time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `coding time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- coding_timespan/60 as 'PR Coding Time(h)'
+ pr_coding_time/60 as 'PR Coding Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -43,9 +43,11 @@ If you want to measure the monthly trend of `PR coding time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(coding_timespan)/60 as 'PR Coding Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_coding_time)/60 as 'PR Coding Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/docs/Metrics/PRCycleTime.md b/docs/Metrics/PRCycleTime.md
index 46c7f0cc61..925ca9a642 100644
--- a/docs/Metrics/PRCycleTime.md
+++ b/docs/Metrics/PRCycleTime.md
@@ -29,13 +29,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `cycle time` of a specific PR. DevLake
pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `cycle time` of a specific PR. DevLake
pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- change_timespan/60 as 'PR Cycle Time(h)'
+ pr_cycle_time/60 as 'PR Cycle Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -45,9 +45,11 @@ If you want to measure the monthly trend of `PR cycle time`
in the screenshot be
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(change_timespan)/60 as 'PR Cycle Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_cycle_time)/60 as 'PR Cycle Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/docs/Metrics/PRDeployTime.md b/docs/Metrics/PRDeployTime.md
index 077535bfe2..5f616f19e4 100644
--- a/docs/Metrics/PRDeployTime.md
+++ b/docs/Metrics/PRDeployTime.md
@@ -42,13 +42,13 @@ This metric relies on two sources:
<b>SQL Queries</b>
-The following SQL shows how to find the `deploy time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `deploy time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- deploy_timespan/60 as 'PR Deploy Time(h)'
+ pr_deploy_time/60 as 'PR Deploy Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -58,9 +58,11 @@ If you want to measure the monthly trend of `PR deploy time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(deploy_timespan)/60 as 'PR Deploy Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_deploy_time)/60 as 'PR Deploy Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/docs/Metrics/PRPickupTime.md b/docs/Metrics/PRPickupTime.md
index d33a9e46db..ea0efde6e2 100644
--- a/docs/Metrics/PRPickupTime.md
+++ b/docs/Metrics/PRPickupTime.md
@@ -27,13 +27,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `pickup time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `pickup time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- review_lag/60 as 'PR Pickup Time(h)'
+ pr_pickup_time/60 as 'PR Pickup Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -43,9 +43,11 @@ If you want to measure the monthly trend of `PR pickup time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(review_lag)/60 as 'PR Pickup Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_pickup_time)/60 as 'PR Pickup Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/docs/Metrics/PRReviewTime.md b/docs/Metrics/PRReviewTime.md
index e7075db7b2..7add75f2ed 100644
--- a/docs/Metrics/PRReviewTime.md
+++ b/docs/Metrics/PRReviewTime.md
@@ -31,13 +31,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `review time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `review time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- review_timespan/60 as 'PR Review Time(h)'
+ pr_review_time/60 as 'PR Review Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -47,9 +47,11 @@ If you want to measure the monthly trend of `PR review time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(review_timespan)/60 as 'PR Review Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_review_time)/60 as 'PR Review Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/versioned_docs/version-v0.16/Metrics/PRCodingTime.md
b/versioned_docs/version-v0.16/Metrics/PRCodingTime.md
index 7f0ac87f9e..8d72194635 100644
--- a/versioned_docs/version-v0.16/Metrics/PRCodingTime.md
+++ b/versioned_docs/version-v0.16/Metrics/PRCodingTime.md
@@ -27,13 +27,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `coding time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `coding time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- coding_timespan/60 as 'PR Coding Time(h)'
+ pr_coding_time/60 as 'PR Coding Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -43,9 +43,11 @@ If you want to measure the monthly trend of `PR coding time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(coding_timespan)/60 as 'PR Coding Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_coding_time)/60 as 'PR Coding Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/versioned_docs/version-v0.16/Metrics/PRCycleTime.md
b/versioned_docs/version-v0.16/Metrics/PRCycleTime.md
index 46c7f0cc61..925ca9a642 100644
--- a/versioned_docs/version-v0.16/Metrics/PRCycleTime.md
+++ b/versioned_docs/version-v0.16/Metrics/PRCycleTime.md
@@ -29,13 +29,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `cycle time` of a specific PR. DevLake
pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `cycle time` of a specific PR. DevLake
pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- change_timespan/60 as 'PR Cycle Time(h)'
+ pr_cycle_time/60 as 'PR Cycle Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -45,9 +45,11 @@ If you want to measure the monthly trend of `PR cycle time`
in the screenshot be
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(change_timespan)/60 as 'PR Cycle Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_cycle_time)/60 as 'PR Cycle Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/versioned_docs/version-v0.16/Metrics/PRDeployTime.md
b/versioned_docs/version-v0.16/Metrics/PRDeployTime.md
index 077535bfe2..5f616f19e4 100644
--- a/versioned_docs/version-v0.16/Metrics/PRDeployTime.md
+++ b/versioned_docs/version-v0.16/Metrics/PRDeployTime.md
@@ -42,13 +42,13 @@ This metric relies on two sources:
<b>SQL Queries</b>
-The following SQL shows how to find the `deploy time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `deploy time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- deploy_timespan/60 as 'PR Deploy Time(h)'
+ pr_deploy_time/60 as 'PR Deploy Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -58,9 +58,11 @@ If you want to measure the monthly trend of `PR deploy time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(deploy_timespan)/60 as 'PR Deploy Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_deploy_time)/60 as 'PR Deploy Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/versioned_docs/version-v0.16/Metrics/PRPickupTime.md
b/versioned_docs/version-v0.16/Metrics/PRPickupTime.md
index d33a9e46db..ea0efde6e2 100644
--- a/versioned_docs/version-v0.16/Metrics/PRPickupTime.md
+++ b/versioned_docs/version-v0.16/Metrics/PRPickupTime.md
@@ -27,13 +27,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `pickup time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `pickup time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- review_lag/60 as 'PR Pickup Time(h)'
+ pr_pickup_time/60 as 'PR Pickup Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -43,9 +43,11 @@ If you want to measure the monthly trend of `PR pickup time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(review_lag)/60 as 'PR Pickup Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_pickup_time)/60 as 'PR Pickup Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
diff --git a/versioned_docs/version-v0.16/Metrics/PRReviewTime.md
b/versioned_docs/version-v0.16/Metrics/PRReviewTime.md
index e7075db7b2..7add75f2ed 100644
--- a/versioned_docs/version-v0.16/Metrics/PRReviewTime.md
+++ b/versioned_docs/version-v0.16/Metrics/PRReviewTime.md
@@ -31,13 +31,13 @@ N/A
<b>SQL Queries</b>
-The following SQL shows how to find the `review time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.pull_requests.
+The following SQL shows how to find the `review time` of a specific PR.
DevLake pre-calculates the metric and stores it in table.project_pr_metrics.
```
SELECT
- review_timespan/60 as 'PR Review Time(h)'
+ pr_review_time/60 as 'PR Review Time(h)'
FROM
- pull_requests
+ project_pr_metrics
```
@@ -47,9 +47,11 @@ If you want to measure the monthly trend of `PR review time`
in the screenshot b
```
SELECT
- DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
- avg(review_timespan)/60 as 'PR Review Time(h)'
-FROM pull_requests
+ DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY)
as time,
+ avg(ppm.pr_review_time)/60 as 'PR Review Time(h)'
+FROM
+ pull_requests pr
+ JOIN project_pr_metrics ppm ON pr.id = ppm.id
GROUP BY 1
ORDER BY 1
```
