Hi,
I need to find the age for each day ,but I need it for all previous dates in one query. So I used the following query:
select trunc(sysdate) - level + 1 DATE
,trunc(sysdate) - level + 1 - created_date AGE
from items
connect by trunc(sysdate) - level + 1 - created_date > 0
I am getting output(FOR DATE & AGE) which is fine and correct:
DATE AGE
--------- ----------
6-JUL-15 22
5-JUL-15 21
4-JUL-15 20
3-JUL-15 19
2-JUL-15 18
1-JUL-15 17
30-JUN-15 16
29-JUN-15 15
28-JUN-15 14
27-JUN-15 13
26-JUN-15 12
25-JUN-15 11
24-JUN-15 10
Now I need to calculate average age for each day so I added average in the following query:
select trunc(sysdate) - level + 1 DATE ,
avg(trunc(sysdate) - level + 1 - created_date ) AVERAGE_AGE
from items
connect by trunc(sysdate) - level + 1 - created_date > 0
group by trunc(sysdate) - level + 1
is this query correct? When I add aggregate function (avg) to this query, it takes 1 hour to retrieve data .When I remove the average function from query it gives result in 2 seconds?What is the possible solution to calculate average without affecting the performance ?Please help