Startrekzky commented on issue #8363:
URL:
https://github.com/apache/incubator-devlake/issues/8363#issuecomment-3166379979
Hi @jeffschaper, I found that there's something wrong with the above SQL.
Could you try this one out, please?
```
-- Metric 3: change failure rate
with _deployments 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(cdc.finished_date) as deployment_finished_date
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
HAVING
$__timeFilter(max(cdc.finished_date))
),
_failure_caused_by_deployments as (
-- calculate the number of incidents caused by each deployment
SELECT
d.deployment_id,
d.deployment_finished_date,
-- count(distinct i.id) as has_incident
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
)
SELECT
sum(has_incident) / count(deployment_id) as "change_failure_rate"
FROM
_failure_caused_by_deployments
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
