Optimisation de ma requete

Citation:

Envoyé par lety247

Bonjour,

Jai cette requete, sous Oracle 10i

Code:

1
2
3
4
5
6
7
8
9
10
11
12
select path, avg(c)
from (
select path, music_station_id, count(path) c
from (
select nvl2(file_id, nvl(gm.song_filename,'N/A (file)'), nvl(url,'N/A (path)')) path, music_station_id
from playlogs pl, global_music gm
where date_played between sysdate-200 and sysdate
and pl.file_id = gm.music_file_id (+)
)
group by music_station_id, path
)
group by path

Je dois faire un average de count....
est il possible d'avoir le meme resultat sans faire un select de select de select??

Je trouve ca bizarre.

Code:

1
2
3
4
5
6
7
8
9
10
select path, avg(c)
from (
select nvl2(file_id, nvl(gm.song_filename,'N/A (file)'), nvl(url,'N/A (path)')) path, music_station_id, 
count(nvl2(file_id, nvl(gm.song_filename,'N/A (file)'), nvl(url,'N/A (path)')) ) C
from playlogs pl, global_music gm
where date_played between sysdate-200 and sysdate
and pl.file_id = gm.music_file_id (+)
group by music_station_id, nvl2(file_id, nvl(gm.song_filename,'N/A (file)'), nvl(url,'N/A (path)')) 
)
group by path

Aux () près... tu peux enlever un niveau.