Better Average Runtime Calculation to see what to expect for the next builds

[jbglaw]

While recognizing the page count calculation, I also thought about the expected runtime. Right now, we're using an average of all builds. That however takes into account successful and failed jobs, which might have a considerably different run time.

I suggest to just look at the most recent SUCCESSFUL or FAILED build and average over the consecutive list of builds with the same final state.

This isn't beautiful, but working (and executing quickly):

SELECT  AVG (completedAt - startedAt)
FROM    builds
WHERE           name = 'gcc-mipsel-elf'
        AND     result IN (4, 5)
        AND     number BETWEEN
                        (       -- Newest build that's the OPPOSITE state
                                -- of the most recent SUCCESSFUL or FAILED build
                                SELECT  number
                                FROM    builds
                                WHERE           name = 'gcc-mipsel-elf'
                                        AND     result IN (4, 5)
                                        AND     result != (     -- Result of most recent SUCCESSFUL or FAILED build
                                                        SELECT  result
                                                        FROM    builds
                                                        WHERE           name = 'gcc-mipsel-elf'
                                                                AND     result in (4, 5)
                                                        ORDER BY number DESC
                                                        LIMIT 1
                                                )
                                ORDER BY number DESC
                                LIMIT 1
                        ) + 1
                        AND
                        (       -- Newest build that's FAILED or SUCCESSFUL
                                SELECT  number
                                FROM    builds
                                WHERE           name = 'gcc-mipsel-elf'
                                        AND     result IN (4, 5)
                                ORDER BY number DESC
                                LIMIT   1
                        )
                ;

[ohwgiles]

I'm not sure this is an improvement, and it's significantly more complex. You could also argue that the expected runtime should only include successful runs, and that it shouldn't average so far in the past. Hard to say what is most meaningful for the user.