Discussion :

[jmclej]

Bonjour,
J'ai une table dont une colonne "Numero" a des valeurs à '' (vide) ou null.
Je veux mettre à jour ces valeurs suivant la règle suivante :
Pour un tuple "Source"/"polNb"/"Ccy", prendre la date minimum du champs "DtDebut" (que l'on nommera min(DtDebut) ci-dessous) et appliquer les règles suivantes :
If DtDebut = min(DtDebut) then Numero = 0
If DtDebut = min(DtDebut) + 1 an then Numero = 1
If DtDebut = min(DtDebut) + 2 an then Numero = 2
If DtDebut = min(DtDebut) + 3 an then Numero = 3
etc...

J'ai essayé notamment le code suivant :

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
UPDATE MyTable t1
SET t1.Numero = (
	SELECT
	CASE
		WHEN t2.DtDebut = MIN(t2.DtDebut) THEN 0
		WHEN t2.DtDebut = DATEADD(year, 1, MIN(t2.DtDebut)) THEN 1
		WHEN t2.DtDebut = DATEADD(year, 2, MIN(t2.DtDebut)) THEN 2
		WHEN t2.DtDebut = DATEADD(year, 3, MIN(t2.DtDebut)) THEN 3
		--potential need for adding cases here
	END
	FROM MyTable AS t2
	WHERE t1.Source = t2.Source
	AND t1.polNb = t2.polNb
	AND t1.Ccy = t2.Ccy
)
WHERE t1.Numero = '' OR t1.Numero IS NULL;

Mais en plus de ne pas être générique, il me donne l'erreur suivante :

Error in SQL statement: AnalysisException: [MISSING_GROUP_BY] The query does not include a GROUP BY clause. Add GROUP BY or turn it into the window functions using OVER clauses.

Je ne comprends pas le message d'erreur car je n'utilise pourtant que le champs "DtDebut", donc il me semble que je pourrais donc bien utiliser la fonction MIN() dessus
Est-ce que vous voyez comment faire s'il vous plaît pour que ce soit générique et que ça compile ?

[Séb.]

If DtDebut = min(DtDebut) then Numero = 0
If DtDebut = min(DtDebut) + 1 an then Numero = 1
If DtDebut = min(DtDebut) + 2 an then Numero = 2
If DtDebut = min(DtDebut) + 3 an then Numero = 3
etc...

L'idée pourrait se résumer en :

TIMESTAMPDIFF(YEAR, MIN(DtDebut), DtDebut)

À voir si ça correspond réellement à ce que tu souhaites.

Pour la requête finale :

WITH aggregate AS (
	SELECT ALL Source, polNb, Ccy, MIN(DtDebut) AS MinDtDebut
	FROM MyTable
	GROUP BY 1, 2, 3
)
UPDATE MyTable AS m
INNER JOIN aggregate AS a ON (m.Source, m.polNb, m.Ccy) = (a.Source, a.polNb, a.Ccy)
SET m.Numero = TIMESTAMPDIFF(YEAR, a.MinDtDebut, m.DtDebut)
WHERE m.Numero = '' OR m.Numero IS NULL
;

Donne ton DDL et un jeu de test si le résultat n'est pas bon.

[jmclej]

L'idée pourrait se résumer en :

TIMESTAMPDIFF(YEAR, MIN(DtDebut), DtDebut)

À voir si ça correspond réellement à ce que tu souhaites.

Pour la requête finale :

WITH aggregate AS (
	SELECT ALL Source, polNb, Ccy, MIN(DtDebut) AS MinDtDebut
	FROM MyTable
	GROUP BY 1, 2, 3
)
UPDATE MyTable AS m
INNER JOIN aggregate AS a ON (m.Source, m.polNb, m.Ccy) = (a.Source, a.polNb, a.Ccy)
SET m.Numero = TIMESTAMPDIFF(YEAR, a.MinDtDebut, m.DtDebut)
WHERE m.Numero = '' OR m.Numero IS NULL
;

Donne ton DDL et un jeu de test si le résultat n'est pas bon.

L'idée me paraît bonne en effet :-)
Par contre la syntaxe de ton code semble avoir un souci car j'ai une erreur (a priori pas possible d'avoir une jointure sur la table à mettre à jour).
Voici la DDL et la DML pour tester :

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
drop table MyTable;
create table MyTable (Source varchar(10), polNb int, Ccy varchar(3), DtDebut datetime, Numero int);
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-03-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-04-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2017-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2022-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-03-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-04-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2018-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2023-06-15', null;
 
WITH aggregate AS (
	SELECT ALL Source, polNb, Ccy, MIN(DtDebut) AS MinDtDebut
	FROM MyTable
	GROUP BY 1, 2, 3
)
UPDATE MyTable AS m
INNER JOIN aggregate AS a ON (m.Source, m.polNb, m.Ccy) = (a.Source, a.polNb, a.Ccy)
SET m.Numero = TIMESTAMPDIFF(YEAR, a.MinDtDebut, m.DtDebut)
WHERE m.Numero = '' OR m.Numero IS NULL;
 
select * from MyTable;

[tatayo]

Bonjour,
La bonne syntaxe pour faire un update avec des jointure est la suivante:

Code sql :

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

1
2
3
4
5
6
 
Update t1
set t1.x = t2.y
from MaTable as t1
inner join AutreTable as t2
on t1.id = t2.idT1

Donc il faut juste ajouter le FROM et remettre les lignes dans le bon ordre:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH aggregate AS (
	SELECT ALL Source, polNb, Ccy, MIN(DtDebut) AS MinDtDebut
	FROM MyTable
	GROUP BY 1, 2, 3
)
UPDATE m
SET m.Numero = TIMESTAMPDIFF(YEAR, a.MinDtDebut, m.DtDebut)
from MyTable AS m
INNER JOIN aggregate AS a ON m.Source = a.source and m.polNb = a.polNb and m.Ccy = a.Ccy
WHERE m.Numero = '' OR m.Numero IS NULL
;

Tatayo.

[jmclej]

Bonjour,
La bonne syntaxe pour faire un update avec des jointure est la suivante:

Code sql :

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

1
2
3
4
5
6
 
Update t1
set t1.x = t2.y
from MaTable as t1
inner join AutreTable as t2
on t1.id = t2.idT1

Donc il faut juste ajouter le FROM et remettre les lignes dans le bon ordre:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH aggregate AS (
	SELECT ALL Source, polNb, Ccy, MIN(DtDebut) AS MinDtDebut
	FROM MyTable
	GROUP BY 1, 2, 3
)
UPDATE m
SET m.Numero = TIMESTAMPDIFF(YEAR, a.MinDtDebut, m.DtDebut)
from MyTable AS m
INNER JOIN aggregate AS a ON m.Source = a.source and m.polNb = a.polNb and m.Ccy = a.Ccy
WHERE m.Numero = '' OR m.Numero IS NULL
;

Tatayo.

Merci mais même en exécutant ce code, j'ai l'erreur "near ".": syntax error"

[Séb.]

L'idée me paraît bonne en effet :-)
Par contre la syntaxe de ton code semble avoir un souci car j'ai une erreur (a priori pas possible d'avoir une jointure sur la table à mettre à jour).
Voici la DDL et la DML pour tester :

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
drop table MyTable;
create table MyTable (Source varchar(10), polNb int, Ccy varchar(3), DtDebut datetime, Numero int);
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-03-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-04-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2017-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2022-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-03-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-04-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2018-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2023-06-15', null;
 
WITH aggregate AS (
	SELECT ALL Source, polNb, Ccy, MIN(DtDebut) AS MinDtDebut
	FROM MyTable
	GROUP BY 1, 2, 3
)
UPDATE MyTable AS m
INNER JOIN aggregate AS a ON (m.Source, m.polNb, m.Ccy) = (a.Source, a.polNb, a.Ccy)
SET m.Numero = TIMESTAMPDIFF(YEAR, a.MinDtDebut, m.DtDebut)
WHERE m.Numero = '' OR m.Numero IS NULL;
 
select * from MyTable;

Après exécution (MySQL 8.0.35), j'obtiens :

Source	polNb	Ccy	DtDebut	Numero
s1	1	EUR	2015-03-15 00:00:00	0
s1	1	EUR	2015-04-15 00:00:00	0
s1	1	EUR	2017-06-15 00:00:00	2
s1	1	EUR	2022-06-15 00:00:00	7
s2	1	EUR	2016-03-15 00:00:00	0
s2	1	EUR	2016-04-15 00:00:00	0
s2	1	EUR	2018-06-15 00:00:00	2
s2	1	EUR	2023-06-15 00:00:00	7

Ma syntaxe est bonne avec MySQL (quel est ton SGBD ?), mais je ne sais pas si le résultat l'est.

[Waldar]

Je mise sur Spark / Databricks quelque chose du genre.

Essayez avec la clause OVER() min(DtDebut) over(partition by Source, polNb, Ccy).

Par contre je rejoins Séb, merci pour le jeu d'essai mais si vous pouviez produire l'attendu ce serait parfait.

[jmclej]

Je mise sur Spark / Databricks quelque chose du genre.

Essayez avec la clause OVER() min(DtDebut) over(partition by Source, polNb, Ccy).

Par contre je rejoins Séb, merci pour le jeu d'essai mais si vous pouviez produire l'attendu ce serait parfait.

Spark SQL sur Databricks, exactement :-)
L'attendu est bien celui présenté par Seb.
Par contre j'ai essayé de lancer le code sur https://sqliteonline.com/ et ça ne fonctionne pas non plus sur SQLite.

Est-ce que l'utilisation de la clause est supposée s'utiliser comme cela (si ce n'est pas le cas, comment l'écrire s'il vous plaît ?) :

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
drop table MyTable;
create table MyTable (Source varchar(10), polNb int, Ccy varchar(3), DtDebut datetime, Numero int);
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-03-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-04-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2017-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2022-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-03-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-04-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2018-06-15', null;
insert into MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2023-06-15', null;
 
UPDATE MyTable
SET Numero = TIMESTAMPDIFF(YEAR, MIN(DtDebut) OVER (PARTITION BY Source, polNb, Ccy), DtDebut)
WHERE Numero IS NULL OR Numero = '';
 
select * from MyTable;

J'ai essayé ça en tout cas et j'ai l'erreur :

Error in SQL statement: AnalysisException: [UNSUPPORTED_EXPR_FOR_OPERATOR] A query operator contains one or more unsupported expressions. Consider to rewrite it to avoid window functions, aggregate functions, and generator functions in the WHERE clause.
Invalid expressions: ["min(DtDebut) OVER (PARTITION BY Source, polNb, Ccy ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)", "min(DtDebut)"];