Requête sur des années consécutives

**Solevita** · 19/01/2022, 14h38

Bonjour,

J'ai une table ou chaque colonne correspond à un résultat par années. Je cherche à réalisé une requête qui permet de vérifier si le phénomène à lieu sur 2 années consécutives, 3 années, 4 années etc jusqu'à 10 années. Par ailleurs il ne faut qu'il y'ait de doublons (la condition pour 3 ans consécutifs ne doit pas retourner les valeurs retournées pour les 2 années consécutives).

il y'a bien la possibilité de faire de la sorte :

Code :

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

1
2
3
4
5
 
select * from table 
where col_2020 != 0 and col_2011 !=0 and col_2012 =0 and col_2013 =0 and col_2014 =0 and col_2015 =0 and col_2016 =0 and col_2017 =0 and col_2018=0 and col_2019 =0 and col_2020 =0
OR 
col_2020 =0 and col_2011 !=0 and col_2012 !=0 and col_2013 =0 ...

mais vu le nombre de combinaisons à tester ... c'est pas du tout adapté.

**Waldar** · 19/01/2022, 15h18

Quel est votre SGBD ?
Pouvez-vous fournir un petit jeu d'essai représentatif - quelques lignes et quelques colonnes suffisent, données "ABC" tant que la logique et les types sont conservés.

**JeitEmgie** · 20/01/2022, 07h55

Avec ORACLE :
UNPIVOT (à moins que vous ayez les données avant la présentation des années en colonne, alors partez de cette version)
suivi de MATCH_RECOGNIZE, il en faudra sans doute 2 imbriqués, le premier pour détecter les conditions de suite consécutive,
le deuxième pour éliminer les suites avec doublons (mais pour cette condition d'autres solutions existent comme comparer le COUNT et le COUNT DISTINCT).
Sans MATCH_RECOGNIZE, il doit être possible d'y arriver avec des fonctions analytiques comme LEAD imbriquées dans un DECODE,
mais dans les 2 cas, c'est beaucoup plus simple de partir des données avant le PIVOT qui a mis les années en colonne.
(Et sur les données non pilotées il pourrait être plus efficace de se débarrasser d'abord des doublons)

Mais comme demandé par Waldar : jeu de données et résultats attendus parce que entre ce que dites et le query que vous présentez il y a plus d'un hiatus.

**escartefigue** · 20/01/2022, 11h43

Attention : la clause WHERE comporte des combinaisons de AND et OR sans parenthèses, il est probable que le résultat obtenu ne soit pas celui attendu !

**Waldar** · 20/01/2022, 14h23

Envoyé par escartefigue

Attention : la clause WHERE comporte des combinaisons de AND et OR sans parenthèses, il est probable que le résultat obtenu ne soit pas celui attendu !

Pour le coup, ça m'a l'air correct !

**JeitEmgie** · 20/01/2022, 16h15

Premier essai (ORACLE 21c)

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
40
41
42
43
44
45
WITH data(id, col_2010, col_2011, col_2012, col_2013, col_2014, col_2015, col_2016, col_2017, col_2018, col_2019, col_2020, col_2021) AS (
    SELECT 1, 0, 1, 2, 3, 0, 1, 2, 0, 4, 5, 0, 0 FROM DUAL UNION ALL
    SELECT 2, 0, 7, 8, 8, 9, 0, 0, 0, 0, 0, 0, 0 FROM DUAL
),
udata AS (
    SELECT * FROM data
    UNPIVOT (
        val
        FOR year IN (
            col_2010 AS 2010,
            col_2011 AS 2011,
            col_2012 AS 2012,
            col_2013 AS 2013,
            col_2014 AS 2014,
            col_2015 AS 2015,
            col_2016 AS 2016,
            col_2017 AS 2017,
            col_2018 AS 2018,
            col_2019 AS 2019,
            col_2020 AS 2020,
            col_2021 AS 2021
        )
    )
),
tabibito AS (
    SELECT id, year, uval, year+1 - row_number() over(order by id, year) AS grpn FROM (
        SELECT id, year, uval FROM (
            SELECT id, year, uval, LAG(uval) OVER(w) AS pval, LEAD(uval) OVER(w) AS nval FROM (
                -- d'après le query exemple, le "phénomène" semble être "val <> 0" : à confirmer
                SELECT id, year, val, NULLIF(val,0) AS uval
                FROM udata
                WINDOW w AS (
                    PARTITION BY id ORDER BY year
                )
            )
        )
        WHERE uval IS NOT NULL AND NOT(pval IS NULL AND nval IS NULL)
        -- NOT(pval IS NULL AND nval IS NULL) élimine les phénomènes d'une seule année
    )
)
SELECT id, MIN(YEAR) AS from_year, (MAX(year) - MIN(year) + 1) as duration, 
    CAST(MULTISET(SELECT uval FROM tabibito t2 WHERE t2.id = t1.id AND t2.grpn = t1.grpn) AS SYS.ODCINUMBERLIST) AS luval
FROM tabibito t1
GROUP BY id, grpn
;

id	from_year	duration	luval
1	2011	3	SYS.ODCINUMBERLIST(1, 2, 3)
1	2015	2	SYS.ODCINUMBERLIST(1, 2)
1	2018	2	SYS.ODCINUMBERLIST(4, 5)
2	2011	4	SYS.ODCINUMBERLIST(7, 8, 8, 9)

Maintenant si vous voulez ne pas avoir le (2015,2) sous prétexte que [1,2] est une sous-liste de [1,2,3] du (1,2011),
cela sera encore plus compliqué.

**Waldar** · 20/01/2022, 17h26

Ma solution sur DBFiddle un peu différente mais un peu similaire à celle de JeitEmgie :

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
40
41
42
43
44
45
with cte_data (id, col_2010, col_2011, col_2012, col_2013, col_2014, col_2015, col_2016, col_2017, col_2018, col_2019, col_2020, col_2021) AS
(
select 1, 0, 1, 2, 3, 0, 1, 2, 0, 4, 5, 0, 0 from dual union all
select 2, 0, 7, 8, 8, 9, 0, 0, 0, 0, 0, 0, 0 from dual
)
  , cte_upvt (id, year, val) as
(
 select id, year, val
   from cte_data
unpivot ( val for year in ( col_2010 as 2010, col_2011 as 2011
                          , col_2012 as 2012, col_2013 as 2013
                          , col_2014 as 2014, col_2015 as 2015
                          , col_2016 as 2016, col_2017 as 2017
                          , col_2018 as 2018, col_2019 as 2019
                          , col_2020 as 2020, col_2021 as 2021
                          )
        )
)
  ,  cte_reset (id, year, val, rst) as
(
select id, year, val
     , count(case val when 0 then 1 end) over(partition by id order by year asc)
  from cte_upvt
)
  ,  cte_grp (id, year, val, rst, grp) as
(
select id, year, val, rst
     , case when count(case when val > 0 then 1 end) over(partition by id, rst order by year asc) > 0 then 1 end
  from cte_reset
)
  select id
       , min(year)                                         as year_deb
       , max(year)                                         as year_fin
       , listagg(val, ',') within group(order by year asc) as lst_val
    from cte_grp
   where grp = 1
group by id, rst
order by id, rst;
 
ID  YEAR_DEB  YEAR_FIN  LST_VAL
--  --------  --------  -------
 1      2011      2013  1,2,3
 1      2015      2016  1,2
 1      2018      2019  4,5
 2      2011      2014  7,8,8,9

**JeitEmgie** · 20/01/2022, 20h09

Et avec LISTAGG on pourra plus facilement exclure des solutions qui ont des patterns de valeurs identiques avec un WHERE NOT EXISTS(... WHERE INSTR(s2.lst_val, s1.lst_val) > 0 AND ...). (si nécessaire...)

**JeitEmgie** · 21/01/2022, 14h08

Avec MATCH_RECOGNIZE, on gagne 1 CTE :

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
40
 
WITH data(id, col_2010, col_2011, col_2012, col_2013, col_2014, col_2015, col_2016, col_2017, col_2018, col_2019, col_2020, col_2021) AS (
	SELECT 1, 0, 1, 2, 3, 0, 1, 2, 0, 4, 5, 0, 0 FROM DUAL UNION ALL
	SELECT 2, 0, 7, 8, 8, 9, 0, 0, 0, 0, 0, 0, 0 FROM DUAL
),
udata AS (
	SELECT * FROM data
	UNPIVOT (
		val
		FOR year IN (
			col_2010 AS 2010,
			col_2011 AS 2011,
			col_2012 AS 2012,
			col_2013 AS 2013,
			col_2014 AS 2014,
			col_2015 AS 2015,
			col_2016 AS 2016,
			col_2017 AS 2017,
			col_2018 AS 2018,
			col_2019 AS 2019,
			col_2020 AS 2020,
			col_2021 AS 2021
		)
	)
)
SELECT id, start_year, last_year, duration, 
	CAST(MULTISET(SELECT val 
	FROM udata t WHERE t.id = t0.id AND year BETWEEN start_year AND last_year) AS SYS.ODCINUMBERLIST) luval  
FROM (
	SELECT * FROM udata
	MATCH_RECOGNIZE(
	PARTITION BY id
	ORDER BY year
	MEASURES first(year) start_year, last(year) last_year, last(year)-first(year)+1 duration
	PATTERN ( succ+ strt )
	DEFINE
		succ AS val <> 0 AND next(val) <> 0 AND year = next(year)-1 
	)
) t0
;

**Solevita** · 31/01/2022, 16h48

Envoyé par Waldar

Quel est votre SGBD ?
Pouvez-vous fournir un petit jeu d'essai représentatif - quelques lignes et quelques colonnes suffisent, données "ABC" tant que la logique et les types sont conservés.

Bonjour ,

Merci pour vos réponse à tous et désolé de ma réaction tardive.
Je suis sur postgres, voici un le jeu d'essai :

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
CREATE TABLE turbi.turbidite (
    id_bdcarth bigint,
    turb10 bigint,
    turb11 bigint,
    turb12 bigint,
    turb13 bigint,
    turb14 bigint,
    turb15 bigint,
    turb16 bigint,
    turb17 bigint,
    turb18 bigint,
    turb19 bigint,
    turb20 bigint
);
 
COPY turbi.turbidite (id_bdcarth, turb10, turb11, turb12, turb13, turb14, turb15, turb16, turb17, turb18, turb19, turb20) FROM stdin;
973028648	0	0	0	0	0	0	0	0	0	0	0
973013491	0	0	0	0	0	0	0	0	0	0	1
973031265	0	0	0	0	0	0	0	0	0	0	0
973092736	0	0	0	0	0	0	0	0	0	0	0
973029641	0	0	0	0	0	0	0	0	0	0	0
973016554	0	0	0	0	0	0	0	0	0	0	0
973056394	0	0	0	0	0	0	0	0	0	0	0
973057107	0	0	0	0	0	0	0	0	0	0	0
973054712	0	0	0	0	0	0	0	0	0	0	0
973020283	0	0	0	0	0	0	0	0	0	0	0
973019554	0	0	0	0	0	0	0	0	0	0	0
973012604	0	0	0	0	0	1	0	0	0	0	3
973055294	0	0	0	0	0	0	0	0	0	0	0
973014344	0	0	0	0	0	0	0	0	0	0	0
973027908	0	0	0	0	0	0	0	0	0	0	1
973013348	0	0	0	2	2	1	0	0	0	3	1
973018200	0	0	0	0	2	2	3	0	5	3	0
973011450	0	0	0	0	0	0	0	0	0	0	0
973031257	0	0	0	0	0	0	0	0	0	0	0
973054572	0	0	0	0	0	0	0	0	0	0	0
973051983	0	0	0	0	0	0	0	0	0	0	0
973132948	0	0	0	0	0	0	0	0	0	0	0
973053978	0	0	0	0	0	7	2	4	1	3	1
973010730	0	0	0	0	0	0	0	0	0	0	0
973028577	0	0	0	0	0	0	0	0	0	0	0
973063499	0	0	0	0	0	0	0	0	0	0	0
973052604	0	0	0	0	0	0	0	0	0	0	0
973027403	0	0	0	0	0	0	0	0	0	0	0
973052418	0	0	0	0	0	0	0	0	0	0	0
973017739	0	0	0	11	11	9	4	11	10	11	7
973063620	0	0	17	51	271	301	444	485	703	505	516
973019336	0	0	0	0	0	2	0	0	0	0	0
973063567	0	0	17	54	282	312	476	509	747	557	560
973009998	0	0	0	0	0	0	0	0	0	0	0
973136667	0	0	0	0	0	0	0	0	5	2	1
973011928	0	0	0	0	0	0	0	0	0	0	0
973011953	0	0	0	0	0	0	0	0	0	0	0
973018678	0	0	0	0	0	0	0	0	0	0	0
973029126	0	0	0	0	0	0	0	0	0	0	0
973055207	0	0	3	3	17	44	25	47	61	42	26
973018704	0	0	0	0	0	0	0	0	0	0	0
\.

je vais de ce pas lire les suggestions.

Requête sur des années consécutives

Langage SQL

Discussions similaires

Partager

Partager