You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@devlake.apache.org by "roberioguedelho (via GitHub)" <gi...@apache.org> on 2023/10/26 13:53:42 UTC
[I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
roberioguedelho opened a new issue, #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/incubator-devlake/issues?q=is%3Aissue) and found no similar issues.
### What happened
We note that the value for the median is incorrect in cases where there are only 2 or 1 incidents in that month.
### What do you expect to happen
the median value is correct.
### How to reproduce
data taken directly from the devlake database:
We can see that for the month 23/04 we have only two non-zero lead times 315 and 670, as we only have two values, the median is the arithmetic average of this value, which results in 492.5 but the value presented in DevLake for that month is 0.
<img width="739" alt="Captura de Tela 2023-10-26 às 09 24 16" src="https://github.com/apache/incubator-devlake/assets/68553100/a0ca014f-4dc0-4ac7-a11f-2f00d5a074e1">
this also occurs for other months that have 2 or 1 incidents.
### Anything else
_No response_
### Version
v0.19.0-beta5
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
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-unsubscribe@devlake.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "roberioguedelho (via GitHub)" <gi...@apache.org>.
roberioguedelho commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1799124185
But, in this query we did not analyze the incident with lead_time_minutos equal to 0 (and i.lead_time_minutos is not null), so we only had the values 315 and 670 to obtain its median, which would not result in 0.
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #6346: [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median
URL: https://github.com/apache/incubator-devlake/issues/6346
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "roberioguedelho (via GitHub)" <gi...@apache.org>.
roberioguedelho commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1788906339
Any updates guys?
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "klesh (via GitHub)" <gi...@apache.org>.
klesh commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1782637464
@Startrekzky Please help with the issue
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "roberioguedelho (via GitHub)" <gi...@apache.org>.
roberioguedelho commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1801783366
Hi @Startrekzky. Ok, I got it now.
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "Startrekzky (via GitHub)" <gi...@apache.org>.
Startrekzky commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1801775035
Hi @roberioguedelho , the condition `i.lead_time_minutos is not null` will not exclude the values that equal 0, as 0 is not a NULL value. You can print the incident details and see if those incidents are taken into account.
You may use `i.lead_time_minutos != 0` instead.
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1880241396
This issue has been automatically marked as stale because it has been inactive for 60 days. It will be closed in next 7 days if no further activity occurs.
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "Startrekzky (via GitHub)" <gi...@apache.org>.
Startrekzky commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1790481479
Hi @roberioguedelho , the definition of median is the 50th percentile value of a value set.
### DevLake's algorithm of calculating median
Since MySQL doesn't support the `median` function, we wrote our own way to measure median.
Below is the SQL to measure MTTR. Check the full doc here: https://devlake.apache.org/docs/Metrics/MTTR#how-is-it-calculated
```
-- Metric 3: median time to restore service - MTTR
with _incidents as (
-- get the number of incidents created each month
SELECT
distinct i.id,
date_format(i.created_date,'%y/%m') as month,
cast(lead_time_minutes as signed) as lead_time_minutes
FROM
issues i
join board_issues bi on i.id = bi.issue_id
join boards b on bi.board_id = b.id
join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'
WHERE
pm.project_name in ($project)
and i.type = 'INCIDENT'
and i.lead_time_minutes is not null
),
_find_median_mttr_each_month_ranks as(
SELECT *, percent_rank() over(PARTITION BY month order by lead_time_minutes) as ranks
FROM _incidents
),
_mttr as(
SELECT month, max(lead_time_minutes) as median_time_to_resolve
FROM _find_median_mttr_each_month_ranks
WHERE ranks <= 0.5
GROUP BY month
)
SELECT
cm.month,
case
when m.median_time_to_resolve is null then 0
else m.median_time_to_resolve/60 end as median_time_to_resolve_in_hour
FROM
calendar_months cm
LEFT JOIN _mttr m on cm.month = m.month
WHERE $__timeFilter(cm.month_timestamp)
```
In the above SQL, the median value is the `MAX value` with `ranks` equal or less than 0.5.
```
_find_median_mttr_each_month_ranks as(
SELECT *, percent_rank() over(PARTITION BY month order by lead_time_minutes) as ranks
FROM _incidents
),
_mttr as(
SELECT month, max(lead_time_minutes) as median_time_to_resolve
FROM _find_median_mttr_each_month_ranks
WHERE ranks <= 0.5
GROUP BY month
)
```
### Why the median value in your case is 0?
You have a value set with 4 values.
|value | rank|
|------| ----|
| 0|0.25|
| **0**|0.5|
| 315|0.75|
| 670|1|
According to the above algorithm, the median value is 0.
### How to change the algorithm?
Normally, if you have a large dataset, this won't be a problem. However, if you find the above algorithm doesn't make sense on your existing data set, you'll have to manually update the SQL.
One easy way is to use `mean` to replace `median`.
The way you proposed might be OK but it you have a dataset like
|value | rank|
|------| ----|
| 0|0.2|
| **0**|0.4|
| **0**|0.6|
| 315|0.8|
| 670|1|
The median value would be (0+0)/2 = 0. Not sure if this works for you.
To sum up, all algorithms of calculating `median` or using `mean` to replace `median` all have constraints when they apply to different datasets.
I hope it makes sense.
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1891126973
This issue has been closed because it has been inactive for a long time. You can reopen it if you encounter the similar problem in the future.
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][Dora] [Median Lead time for changes] wrong value when calculating the median [incubator-devlake]
Posted by "Startrekzky (via GitHub)" <gi...@apache.org>.
Startrekzky commented on issue #6346:
URL: https://github.com/apache/incubator-devlake/issues/6346#issuecomment-1782636977
Taking a look.
--
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-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org