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 [. . .]

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)"];

[Waldar]

A priori pas de sous-requête dans le MERGE chez les briquetiers.
Essayez comme ceci:

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
create temporary view MinDtDebut as
select Source, polNb, Ccy, DtDebut
     , timestampdiff(year, min(DtDebut) over (partition by Source, polNb, Ccy), DtDebut) as Numero
  from MyTable;
 
 merge into MyTable tgt
 using MinDtDebut   src
    on tgt.Source  = src.Source
   and tgt.polNb   = src.polNb
   and tgt.Ccy     = src.Ccy
   and tgt.DtDebut = src.DtDebut
  when matched
   and tgt.Numero  = ''
    or tgt.Numero is null
  then update
   set Numero = src.Numero;

C'est probablement plus rapide de recréer une table depuis la vue et de renommer l'objet.

[jmclej]

Alors effectivement ça marche avec mon jeu de test, merci !
Je n'avais jamais utilisé la clause merge, donc pas très à l'aise avec.
Je ne comprends pas pourquoi DtDebut doit être rajouté comme clef dans la clause merge (il ne faudrait pas que ce soit le cas, c'est peut-êter la cause de mon problème ci-après) ?
Quelle serait la méthode que vous imaginez avec une "recréation de table depuis la vue et de renommer l'objet" ?

Quand j'applique le merge avec un doublon dans la table (j'ai modifié la date de la 2ème ligne), comme ci-dessous, ça ne marche pas, comment modifier le code pour que ça fonctionne dans ce cas 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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
drop table if exists rmop.MyTable;
drop view if exists MinDtDebut;
create table rmop.MyTable (Source varchar(10), polNb int, Ccy varchar(3), DtDebut date, Numero int);
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-03-15', null;
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2015-03-15', null;
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2017-06-15', null;
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's1', 1, 'EUR', '2022-06-15', null;
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-03-15', null;
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2016-04-15', null;
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2018-06-15', null;
insert into rmop.MyTable(Source, polNb, Ccy, DtDebut, Numero) select 's2', 1, 'EUR', '2023-06-15', null;
 
create temporary view MinDtDebut as
select Source, polNb, Ccy, DtDebut
     , timestampdiff(year, min(DtDebut) over (partition by Source, polNb, Ccy), DtDebut) as Numero
  from rmop.MyTable;
 
 merge into rmop.MyTable tgt
 using MinDtDebut   src
    on tgt.Source  = src.Source
   and tgt.polNb   = src.polNb
   and tgt.Ccy     = src.Ccy
   and tgt.DtDebut = src.DtDebut
  when matched
   and tgt.Numero  = ''
    or tgt.Numero is null
  then update
   set Numero = src.Numero;
 
select * from rmop.MyTable;

J'ai l'erreur :

Error in SQL statement: DeltaUnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same
target row in the Delta table in possibly conflicting ways. By SQL semantics of Merge,
when multiple source rows match on the same target row, the result may be ambiguous
as it is unclear which source row should be used to update or delete the matching
target row. You can preprocess the source table to eliminate the possibility of
multiple matches. Please refer to
https://docs.microsoft.com/azure/dat...ge#merge-error

[jmclej]

Bonjour,
Je suis toujours intéressé par une réponse si vous avez le temps de regarder de nouveau.
Merci beaucoup

[tatayo]

Bonjour,
Si je traduis bien le message d'erreur, le problème ici est que plusieurs lignes de la source vont se "retrouver" sur la même ligne de la table cible.
Donc en première approche je verrais bien un SELECT DISTINCT pour éliminer les doublons lors de la création de la table temporaire.
Mais pour que ce soit pérenne, il faut dédoublonner sur les colonnes qui correspondent à la clé primaire de la table cible.
Ainsi une ligne de la source ne correspondra qu'au au plus une ligne de la cible.

Tatayo.

[Waldar]

Tatayo a parfaitement raison, ce qu'il vous manque ici c'est une clef d'unicité.
En en ajoutant une, tout fonctionne bien :

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
create or replace table MyTable
( Id      bigint generated always as identity
, Source  varchar(10)
, polNb   int
, Ccy     varchar(3)
, DtDebut date
, Numero  int
);
 
insert into MyTable (Source, polNb, Ccy, DtDebut) values
('s1', 1, 'EUR', date '2015-03-15'),
('s1', 1, 'EUR', date '2015-03-15'),
('s1', 1, 'EUR', date '2017-06-15'),
('s1', 1, 'EUR', date '2022-06-15'),
('s2', 1, 'EUR', date '2016-03-15'),
('s2', 1, 'EUR', date '2016-04-15'),
('s2', 1, 'EUR', date '2018-06-15'),
('s2', 1, 'EUR', date '2023-06-15');
 
create temporary view MinDtDebut as
select Id
     , timestampdiff(year, min(DtDebut) over (partition by Source, polNb, Ccy), DtDebut) as Numero
  from MyTable;
 
 merge into MyTable tgt
 using MinDtDebut   src
    on tgt.Id      = src.Id
   and coalesce(tgt.Numero, '')  = ''
  when matched then update
   set tgt.Numero = src.Numero;
 
select * from MyTable order by Id;
 
Id  Source  polNb  Ccy  DtDebut     Numero
--  ------  -----  ---  ----------  ------
 1  s1          1  EUR  2015-03-15       0
 2  s1          1  EUR  2015-03-15       0
 3  s1          1  EUR  2017-06-15       2
 4  s1          1  EUR  2022-06-15       7
 5  s2          1  EUR  2016-03-15       0
 6  s2          1  EUR  2016-04-15       0
 7  s2          1  EUR  2018-06-15       2
 8  s2          1  EUR  2023-06-15       7