Discussion :

[ntexcier]

Bonjour à vous,

Je cherche a savoir comment réaliser un regroupement un peu particulier en SQL.
Pour exemple, j'ai une table tel que:

Journée de l'année / température moyenne
120 / 21
121 / 23
122 / 21
123 / 21
124 / 21
125 / 23
126 / 23

Je souhaiterais obtenir le résultat suivant:
JournéeDeb / JournéeFin / Température
120 / 120 / 21
121 / 121 / 23
122 / 124 / 21
125 / 126 / 23

et non pas (comme je l'obtient en regroupant sur les températures et les min / max des journée):
120 / 124 / 21
121 / 126 / 23

J'espère être compréhensible et remercie par avance tous ceux qui voudrons bien m'éclairer.

NT

[lediz]

voilà

j'ai passé mon temps à trouver une solution 'pas très simple'
mais ça fonctionne

NB : En espérant que la table des "JOURS" ne soit pas pas énorme !
REM : A modifier pour le faire par année

Prévenez si c'est OK,sinon bon courage...
A+

SOLUTION ci-dessous :
---------------------------

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
--
-- vue : qui pour l'exemple sert de TABLE
--
CREATE OR REPLACE VIEW v_day (day,deg)
AS
select 120,21 from dual
union select 121,23 from dual
union select 122,21 from dual
union select 123,21 from dual
union select 124,21 from dual
union select 125,23 from dual
union select 126,23 from dual
/

--
-- vue intermédaire : comprenant les couples de jours de même degré
--

Code :

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

1
2
3
4
5
6
7
8
CREATE OR REPLACE VIEW v_day_same_deg 
    (day,deg_idem_day,deg )
AS
select    a.day,b.day,a.deg
    from  v_day a,v_day b
    where a.deg=b.deg and a.day!=b.day
     and a.day<=b.day
/

--
-- Fonction ramenant le jour(max) d'un range complet
-- pour un jour de la "table" (et son "degré")
--

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE
function f_range(P_day number,p_deg number)
return number
is
 cur_day   number := p_day;
 cur_deg   number := p_deg;
 next_day  number := null;
 next_deg  number := null;
begin
loop
begin
    select    v.day,v.deg  into next_day,next_deg
         from v_day v
        where v.day=cur_day+1 and v.deg=cur_deg;
    cur_day:=next_day;
    cur_deg:=next_deg;
    exception when others then null;
    exit when (sql%rowcount=0);
end;
end loop;
return cur_day;
end;
/

--
-- vue qui exécute la fonction pour tous les jours de la 'table'
--

Code :

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

1
2
3
4
5
6
7
8
9
10
11
create or replace view v_day_range 
(
day,deg,day_sup_idem_deg
)
as
 SELECT
    DAY,
    DEG,
    f_range(DAY,DEG)
 FROM v_day
/

--
-- vue finale
--

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
create or replace view v_day_final 
(
text
)
 as
 select
 day||' / '||day_sup_idem_deg||' : '||deg  from v_day_range  where day!=day_sup_idem_deg
 union
 select
 day||' / '||day||' : '||deg from V_DAY
     where day  not in (select deg_idem_day from v_day_same_deg)
/

[Antoun]

Je te propose une solution ensembliste (à partir de la vue proposée par lediz) :

- les débuts de plage sont facile à repérer, ce sont les jours (J) pour lesquels hier (H) avait une température différente (ou bien il n'y a pas d'hier du tout, afin de ne pas éliminer le premier jour de ton calendrier) :

Code :

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

1
2
3
4
SELECT J.day, J.deg
FROM v_day J
  LEFT JOIN v_day H ON J.day = H.day + 1 
WHERE J.deg <> H.deg OR H.day IS NULL;

- les fins de plage sont les jours pour lesquels demain (D) a une température différente... :

Code :

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

1
2
3
4
SELECT J.day, J.deg
FROM v_day J
  LEFT JOIN v_day D ON J.day = D.day - 1
WHERE J.deg <> D.deg OR D.day IS NULL;

Il n'y a plus qu'à tout trier et replacer dans l'ordre :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT deb2.day Debut, fin2.day Fin, deb2.deg Temp
FROM (
  SELECT day, deg, ROWNUM
  FROM (SELECT J.day, J.deg
    FROM v_day J
      LEFT JOIN v_day H ON J.day = H.day + 1 
    WHERE J.deg <> H.deg OR H.day IS NULL
    ORDER BY J.day 
    ) deb1 
  )  deb2
INNER JOIN
  (SELECT day, ROWNUM
  FROM (SELECT J.day
    FROM v_day J
      LEFT JOIN v_day D ON J.day = D.day - 1
    WHERE J.deg <> D.deg OR D.day IS NULL
    ORDER BY J.day  
    ) fin1
  ) fin2
ON deb2.ROWNUM = fin2.ROWNUM

--
Antoun

Guide complet MySQL 5, par Antoine Dinimant, éd. MicroApplication
http://www.microapp.com/livre_mysql_7873.html

[orafrance]

Merci de penser aux balises code à l'avenir

[lalystar]

Bonjour,

On peut procéder de manière plus directe avec les fonctions analytiques lead et lag :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
select 
   start_group, 
   end_group, deg
from
   (
      select   
         day, 
         deg, 
         start_group, 
         case 
            when end_group is null then lead(end_group) over(order by day) 
         else 
            end_group 
         end end_group
      from
         (
            select
               day, 
               deg, 
               -- detection des changements de groupes de jours consecutifs de meme temperature
               case 
                  when nvl(lag(deg) over(order by day), -1) <> deg then day 
               end start_group,
               case 
                  when nvl(lead(deg) over(order by day), -1) <> deg then day 
               end end_group
            from   
               TEST
         )
      where 
         -- filtre pour ne garder
         -- que les jour debuts ou fin de groupe
         start_group is not null or 
         end_group is not null
   )
where 
   start_group is not null and 
   end_group is not null

Ce qui donne :

Code :

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

1
2
3
4
5
6
7
 
START_GROUP  END_GROUP        DEG
----------- ---------- ----------
        120        120         21
        121        121         23
        122        124         21
        125        126         23

Laly.

[ntexcier]

Bonjour,

Merci a tous pour vos propositions!
Je suis plus que satisfait. Il me semble que l'opération que je souhaite réaliser n'est pas prévue dans le cadre d'une simple exploitation de données.
Les deux premiers exemples montrent bien que SQL n'est fait pour ça. En revanche je ne connaissais pas les fonctions analytiques! C'est trés intéressant.
Je vais m'y plonger dés a présent. Cependant j'avais posté ce message dans un forum général SQL et comme j'ai négligé de préciser que j'utilisais SQLSERVER2k je me retrouve ici. En tout cas merci beaucoup d'avoir trouver une solution a mon probleme.

NT