dosubot[bot] commented on issue #8363:
URL:
https://github.com/apache/incubator-devlake/issues/8363#issuecomment-2755655469
<!-- Answer -->
The issue with the "Change Failure Rate" panel displaying a rate over 100%
when multiple projects are selected is due to the SQL calculation not
accounting for the distinct separation of deployments across different
projects. The current query aggregates incidents and deployments without
distinguishing between projects, leading to an inflated failure rate.
To correct this, the SQL query should be modified to calculate the change
failure rate separately for each project and then aggregate the results. This
can be achieved by including the project name in the `GROUP BY` clause and
adjusting the calculation accordingly. Here is a revised version of the SQL
query:
```sql
-- Metric 3: change failure rate
with _deployments as (
SELECT
cdc.cicd_deployment_id as deployment_id,
max(cdc.finished_date) as deployment_finished_date,
pm.project_name
FROM
cicd_deployment_commits cdc
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
and pm.`table` = 'cicd_scopes'
WHERE
pm.project_name in (${project})
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY
1, 3
HAVING
$__timeFilter(max(cdc.finished_date))
),
_failure_caused_by_deployments as (
SELECT
d.deployment_id,
d.deployment_finished_date,
d.project_name,
count(distinct case when i.id is not null then i.id end) as has_incident
FROM
_deployments d
left join project_incident_deployment_relationships pim on
d.deployment_id = pim.deployment_id
left join incidents i on pim.id = i.id
GROUP BY
1, 2, 3
)
SELECT
d.project_name,
sum(has_incident) / count(deployment_id) as "change_failure_rate"
FROM
_failure_caused_by_deployments d
GROUP BY
d.project_name
```
This modification ensures that the change failure rate is calculated per
project, preventing the rate from exceeding 100% when multiple projects are
selected
<sup>[[1]](https://github.com/apache/incubator-devlake/blob/main/grafana/dashboards/DORADetails-ChangeFailureRate.json)</sup><sup>[[2]](https://github.com/apache/incubator-devlake/blob/main/grafana/dashboards/DORADebug.json)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
---
<sup>Help Dosu learn! Give it feedback: [Great
Response](https://app.dosu.dev/response-feedback/a2f91fbc-ea2a-4609-bf2e-39963cb351e9?feedback_type=great_response)
| [Irrelevant
Answer](https://app.dosu.dev/response-feedback/a2f91fbc-ea2a-4609-bf2e-39963cb351e9?feedback_type=irrelevant_answer)
| [Incorrect
Sources](https://app.dosu.dev/response-feedback/a2f91fbc-ea2a-4609-bf2e-39963cb351e9?feedback_type=incorrect_sources)
| [Too
Verbose](https://app.dosu.dev/response-feedback/a2f91fbc-ea2a-4609-bf2e-39963cb351e9?feedback_type=too_verbose)
|
[Hallucination](https://app.dosu.dev/response-feedback/a2f91fbc-ea2a-4609-bf2e-39963cb351e9?feedback_type=hallucination)
| [Bug
Report](https://app.dosu.dev/response-feedback/a2f91fbc-ea2a-4609-bf2e-39963cb351e9?feedback_type=bug_report)
|
[Other](https://app.dosu.dev/response-feedback/a2f91fbc-ea2a-4609-bf2e-39963cb351e9?feedback_type=other)</sup>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscr...@devlake.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
