Discussion :

[fatsora]

Bonjour la liste

J'ai une requete
qui permet de sortir la liste des opérations financieres dans une journée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
  select time,id_stock, low,high, open,close ,volume from (
 select distinct
  trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60 time,id_stock,
 min(price) over (partition by trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60) low,
 max(price) over (partition by trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60) high,
 first_value(price) over( partition by  trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60) open,
last_value(price) over( partition by  trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60) close,
sum(volume) over( partition by  trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60) volume
 from my_table
 where id_stock='BLA BLA')
  order by 1;

TIME|ID_STOCK|LOW|HIGH|OPEN|CLOSE|VOLUME
02/01/2008 08:39:00|BLA BLA|57.09|57.11|57.11|57.09|4605569
02/01/2008 08:40:00|BLA BLA|57.08|57.12|57.11|57.12|6669834
02/01/2008 08:41:00|BLA BLA|57.09|57.18|57.17|57.13|12981560
02/01/2008 08:42:00|BLA BLA|57.13|57.19|57.17|57.19|9727827
02/01/2008 08:43:00|BLA BLA|57.18|57.2|57.19|57.2|15615137
02/01/2008 08:44:00|BLA BLA|57.15|57.19|57.17|57.17|9296694
02/01/2008 08:45:00|BLA BLA|57.13|57.18|57.16|57.16|8093429
02/01/2008 08:46:00|BLA BLA|57.08|57.16|57.13|57.16|5929953
02/01/2008 08:47:00|BLA BLA|57.08|57.12|57.08|57.1|4146734
02/01/2008 08:48:00|BLA BLA|57.1|57.15|57.14|57.14|11747030
02/01/2008 08:49:00|BLA BLA|57.13|57.16|57.15|57.15|4789455
02/01/2008 08:50:00|BLA BLA|57.14|57.15|57.15|57.15|7741145
02/01/2008 08:51:00|BLA BLA|57.13|57.15|57.14|57.13|8406266
02/01/2008 08:52:00|BLA BLA|57.12|57.15|57.14|57.13|11522643
02/01/2008 08:53:00|BLA BLA|57.13|57.16|57.13|57.13|7600871
02/01/2008 08:54:00|BLA BLA|57.14|57.17|57.16|57.14|4611490
02/01/2008 08:55:00|BLA BLA|57.16|57.21|57.18|57.19|21415943
02/01/2008 08:56:00|BLA BLA|57.21|57.23|57.21|57.22|11145800
02/01/2008 08:57:00|BLA BLA|57.23|57.29|57.25|57.25|32524707
02/01/2008 08:58:00|BLA BLA|57.29|57.35|57.32|57.32|25366504
02/01/2008 08:59:00|BLA BLA|57.34|57.36|57.36|57.35|19789321
02/01/2008 09:00:00|BLA BLA|57.33|57.39|57.38|57.39|36752011
02/01/2008 09:01:00|BLA BLA|57.32|57.35|57.32|57.33|3719427
02/01/2008 09:02:00|BLA BLA|57.31|57.36|57.31|57.32|15623210
02/01/2008 09:03:00|BLA BLA|57.33|57.43|57.41|57.4|29558331
02/01/2008 09:04:00|BLA BLA|57.38|57.45|57.4|57.45|21418958
02/01/2008 09:05:00|BLA BLA|57.43|57.46|57.43|57.43|9565821
02/01/2008 09:06:00|BLA BLA|57.43|57.48|57.48|57.47|18814019
02/01/2008 09:07:00|BLA BLA|57.46|57.48|57.47|57.47|10627780
02/01/2008 09:08:00|BLA BLA|57.44|57.47|57.45|57.45|9301355
02/01/2008 09:09:00|BLA BLA|57.45|57.47|57.47|57.46|12970678
02/01/2008 09:10:00|BLA BLA|57.45|57.47|57.47|57.47|26259772
02/01/2008 09:11:00|BLA BLA|57.46|57.47|57.47|57.46|16341541
02/01/2008 09:12:00|BLA BLA|57.43|57.46|57.43|57.43|22142860
02/01/2008 09:13:00|BLA BLA|57.32|57.45|57.45|57.45|37338665

en fait je voulais savoir

est ce que les intervalles de temps que je veux obtenir sont bien
de la forme [09:00:00-09:01:00[ et non [09:00:00-09:01:00]

car j'aimerais avoir un resultat du type [09:00:00-09:01:00]

sinon comment l'obtenir

[orafrance]

ça dépend comment tu coderas l'intervalle... j'ai du rater une subtilité là

Et pourquoi tu fais :
trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60

Pour ignorer les secondes tu peux pas faire :
TO_CHAR(server_time,'HH24:MI') tout simplement ?

[fatsora]

je m'exprime peut etre mal ;-)

En fait je voudrais avoir dans le resultat

TIME|ID_STOCK|LOW|HIGH|OPEN|CLOSE|VOLUME
02/01/2008 08:39:00|BLA BLA|57.09|57.11|57.11|57.09|4605569
02/01/2008 08:40:00|BLA BLA|57.08|57.12|57.11|57.12|6669834

toutes les données entre [08:39:00 et 08:40:00[

et ainsi de suite ie [08:40:00 et 08:41:00[
et a la fin [09:12:00 et 09:13:00]
mais est-ce possible

merci