Un coalesce en colonnes

Ça faisait longtemps que l'on avait pas fait de SQL, non? Alors, petit exercice. Soit une table contenant pour chaque date le nom d'un champ qui change de valeur, et sa nouvelle valeur. Comment transformer cette approche historique en approche par état, où l'on voit à chaque instant la valeur de chaque champ?

Tout d'abord, créons la table.

create table history(t serial, field text, value integer);
insert into history values(default, 'a', 1);
insert into history values(default, 'b', 12);
insert into history values(default, 'a', 3);
insert into history values(default, 'c', 101);
insert into history values(default, 'b', 12);
insert into history values(default, 'b', 14);
insert into history values(default, 'a', 3);
insert into history values(default, 'a', 1);
insert into history values(default, 'c', 102);
insert into history values(default, 'a', 2);
insert into history values(default, 'b', 13);

Pour visualiser la valeur de chaque champ, on donnera à chaque champ sa colonne, pour l'associer à l'ensemble des moments qui nous intéressent. Une jointure gauche s'impose donc:

select m.t, a.value as a, b.value as b, c.value as c
from history m
left outer join history a on m.t = a.t and a.field = 'a'
left outer join history b on m.t = b.t and b.field = 'b'
left outer join history c on m.t = c.t and c.field = 'c'
order by m.t


t
a
b
c
1
1

2
12

3
3

4
101
5
12

6
14

7
3

8
1

9
102
10
2

11
13

C'est à peu près ça, sauf qu'il faut maintenant remplir les vides avec la dernière valeur disponible. Une sorte de coalesce, mais vertical.

Les sous-requêtes viennent rapidement à notre aide: pour chaque champ, l'on veut récupérer la dernière valeur en ordonnant par le temps, ce qui est facile grâce à limit. Voici donc:

select
 m.t,
 (select value from history h 
  where h.t <= m.t 
  and field = 'a' 
  and value is not null 
  order by h.t 
  desc limit 1) as a,
 (select value from history h 
  where h.t <= m.t 
  and field = 'b' 
  and value is not null 
  order by h.t desc 
  limit 1) as b,
 (select value from history h 
  where h.t <= m.t 
  and field = 'c' 
  and value is not null 
  order by h.t desc 
  limit 1) as c
from history m


t
a
b
c
1
1

2
1
12

3
3
12

4
3
12
101
5
3
12
101
6
3
14
101
7
3
14
101
8
1
14
101
9
1
14
102
10
2
14
102
11
2
13
102

Byzance! Mais arrêtons nous un instant: n'est-ce pas terriblement inefficace d'avoir des sous-requêtes dans le select? Peut-on trouver une autre solution, en utilisant par exemple des window functions? Ça vaut la peine d'essayer. Je ne crois pas qu'il existe de window function qui remplisse exactement ce rôle, mais une approche possible consiste à commencer par écrire la requête qui a un t donné donne le dernier t disponible pour le champ, en demandant à la window function d'aller chercher le max(t) pour un champ donné. La fenêtre par défaut allant jusqu'à la ligne courante, et les entrées nulles de notre jointure gauche étant par définition plus petites que le temps précédent, ça marche plutôt bien:

select 
 m.t, 
 max(a.t) over (order by m.t) as a,
 max(b.t) over (order by m.t) as b,
 max(c.t) over (order by m.t) as c
from history m
left outer join history a on m.t = a.t and a.field = 'a'
left outer join history b on m.t = b.t and b.field = 'b'
left outer join history c on m.t = c.t and c.field = 'c'
order by m.t


t
a
b
c
1
1

2
1
2

3
3
2

4
3
2
4
5
3
5
4
6
3
6
4
7
7
6
4
8
8
6
4
9
8
6
9
10
10
6
9
11
10
11
9

Une fois que le temps attendu est disponible dans sa colonne, il est trivial d'obtenir la solution en joignant la table ainsi créée avec la table "history", pour chaque champ, afin d'en retrouver la valeur. La requête a pris du poids:

select h.t, a.value, b.value, c.value
from
 (select 
  m.t, 
  max(a.t) over (order by m.t) as a,
  max(b.t) over (order by m.t) as b,
  max(c.t) over (order by m.t) as c
 from history m
 left outer join history a on m.t = a.t and a.field = 'a'
 left outer join history b on m.t = b.t and b.field = 'b'
 left outer join history c on m.t = c.t and c.field = 'c'
 order by m.t) as h
left outer join history a on h.a = a.t and a.field = 'a'
left outer join history b on h.b = b.t and b.field = 'b'
left outer join history c on h.c = c.t and c.field = 'c'
order by t

Alors, est-ce plus rapide que l'approche par sous-requêtes dans le select? Malheureusement, non... Je m'attendais à une grosse différence, mais le planificateur de requête est loin d'être bête, et avec les bons index, les deux approches se valent, avec un léger avantage pour l'approche par sous-requête.

En cadeau bonus, cependant, le plan de notre dernière requête: