Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How would I go about querying the overall % change (positive of negative) in their average race speed for a given date range, for example

% Change in first 6 months

Joe Smith - 5% increase
Andy James - 4% increase

% Change in first 12 months

Joe Smith - 8% increase
Lewis May - 6% increase

UPDATE: More detail on % Change

The % Change could be calculated using linear regression, (least-squares-fit would be suitable), the average change is effectivly the y-difference on a line-of-best-fit, where each point is a race, x is the race_date and y is the average_speed for that race.

Postgres's regr_slope will give the gradient of the line which is effectivly the same as the %change

SELECT regr_slope(r.average_speed, EXTRACT(EPOCH FROM r.race_date)) as slope
FROM race_table as r, driver_races as dr
WHERE dr.race_id = r.race_id
AND d.driver_id = 1

This gives the figure I want, but I now need to apply is against all users, sorted by 'slope'

Thanks (again) Erwin, your assumption on the avg speed was correct, a mistake on my part creating the example. The query works well, the simple version is an interesting approach I didnt consider
–
DaveBApr 8 '13 at 10:41