|
The bad news is: AVG only works on numbers. If you need to average anything else, the best you can do is map the values to numbers, average the numbers, and, if necessary and possible, map back to the original domain.
The good news is: That's quite easy with dates in any version of Oracle.
Oracle's date arithmetic does it all for you: you don't even have to
use TO_NUMBER or TO_DATE.
SELECT SYSDATE + AVG (predict_dt - SYSDATE)
FROM table_q;
In Oracle, subtracting one date from another results in a number, equal to
the number of days between them. (For example, if predict_dt is midnight
at the beginning of tomorrow, and it's now 6:00 pm, then predict_dt
minus SYSDATE is .25.) When you add a date d to a number n in
Oracle, the result is another date, n days later than d.
(For example, SYSDATE + .25 is the date .25 days in the future,
that is, six hours from now.)
You may want to treat the average as a column in a query, alongside
the predicted date and other values. In that case, join in a subquery:
SELECT q.predict_dt,
s.avg_predict_dt,
...
FROM table_q q,
(
SELECT SYSDATE + AVG (predict_dt - SYSDATE) AS avg_predict_dt
FROM table_q
) s
...;
What's so special about SYSDATE that we can use it as the center of
our time reckoning system? Nothing at all: SYSDATE is completely arbitrary.
You can use any date as point 0.
If you want to, you can use any of these:
- January 1, 4712 BCE
- July 4, 1776
- Your birthday
- January 1, 2000
The only thing that matters is that you use the same value when converting
from a date to a number (predict_dt - x) and back again (x + AVG ...). I chose
SYSDATE because it produces a date
with only seven keystrokes.
Now, what if your query is so complex that it takes several minutes
to run, and you're concerned about getting an average that's accurate
down to the second? Don't worry about SYSDATE changing in the course
of a SELECT statement: Oracle will use the time the query started as
SYSDATE throughout the entire statement, even if it takes a noticeable
amount of time to finish. (Similarly, in long-running INSERT and UPDATE
statements, Oracle uses the ending time of the query as SYSDATE for
all rows affected.)
|
