Combien de personnes dans chaque groupe d'âge

Voici un cas réel où j'ai eu à résoudre un problème en classant des individus par groupe d'âge à l'aide d'une base de données Postgres. D'abord, imaginons deux tables : la première "human" contient la liste des personnes avec une propriété "birth" où est stockée la date de naissance qu'on utilisera pour connaître son âge en temps réel. L'autre table, nommée "age_group" contient la définition des groupes d'âges en incluant comme propriétés l'âge minimum et maximum pour faire parti du groupe. Une valeur nulle dans l'âge maximum indique l'infini (pour inclure les immortels comme le Highlander par exemple).

Voici le nécessaire si vous souhaitez reproduire l'exercice :

CREATE TABLE human
(
   human_id serial NOT NULL,
   firstname character varying(50) NOT NULL,
   lastname character varying(50) NOT NULL,
   birth date
);

CREATE TABLE age_group
(
   group_id serial NOT NULL,
   age_min integer NOT NULL,
   age_max integer,
   group_name character varying(50) NOT NULL
);

INSERT INTO age_group(age_min, age_max, group_name)
VALUES  (0, 12, 'Enfant (Fan de Justin Bieber)'),
  (13, 17, 'Adolescent boutonneux'),
  (18, 25, 'Jeune adulte fringant'),
  (26, 54, 'Adulte'),
  (55, 65, 'Retraité Liberté 55'),
  (65, null, 'Bel âge (Les résidences Soleil!)');

INSERT INTO human (firstname, lastname, birth)
VALUES  ('Marge', 'Simpson', '1963-06-08'),
  ('Homer', 'Simpson', '1962-01-31'),
  ('Bart', 'Simpson', '1997-09-04'),
  ('Lisa', 'Simpson', '1999-06-06'),
  ('Maggie', 'Simpson', '2002-07-27'),
  ('Milhouse', 'Van Houten', '1997-08-08'),
  ('Montgomery', 'Burns', '1912-03-13'),
  ('Abraham', 'Simpson', '1914-07-27');

NB. Évidemment, les dates de naissance des personnages des Simpsons sont 100% arbitraires puisqu'ils ne vieillissent pas depuis plus de 20 ans!

À partir de ces données, comment peut-on connaître le nombre de personnes dans chaque groupe d'âge (incluant les groupes vides) ? L'objectif est d'obtenir comme résultat des statistiques comme celles-ci :



À l'aide d'une sous-requête lancée sur chacun des enregistrements, on peut obtenir au mieux une seule information relative au groupe d'âge :

SELECT h.*, (SELECT group_name FROM age_group as tag WHERE extract('year' from age(h.birth)) BETWEEN tag.age_min AND COALESCE(tag.age_max, 200)) as group_name
FROM human as h
ORDER BY group_name

En se basant sur le calcul de l'âge pour déterminer le groupe relié, voici 3 façons d'écrire un énoncé SQL pour arriver exactement au même résultat.

Méthode 1

SELECT ag.group_id, ag.group_name, ag.age_min, ag.age_max, COUNT(t.human_id) as nb
FROM human as h
INNER JOIN (
   SELECT human_id, (SELECT group_id FROM age_group as tag WHERE extract('year' from age(birth)) BETWEEN tag.age_min AND COALESCE(tag.age_max, 1000)) as group_id
   FROM human
) as t ON t.human_id = h.human_id
RIGHT JOIN age_group as ag ON ag.group_id = t.group_id
GROUP BY ag.group_id, ag.group_name, ag.age_min, ag.age_max
ORDER BY ag.age_min

Méthode 2

Il sera possible de simplifier l'énoncé un peu :

-- requête simplifiée
SELECT ag.group_id, ag.group_name, ag.age_min, ag.age_max, COUNT(h.human_id) as nb
FROM human as h
RIGHT JOIN age_group as ag ON ag.group_id = (SELECT group_id FROM age_group as tag WHERE extract('year' from age(h.birth)) BETWEEN tag.age_min AND COALESCE(tag.age_max, 1000))
GROUP BY ag.group_id, ag.group_name, ag.age_min, ag.age_max
ORDER BY ag.age_min

Méthode 3

Ou encore, une méthode alternative en réécrivant la requête SQL complètement différemment :

-- façon alternative
SELECT ag.group_id, ag.group_name, ag.age_min, ag.age_max, COUNT(tmp.human_id) as nb
FROM age_group as ag
LEFT JOIN (
   SELECT tag.group_id, h.human_id
   FROM human as h
   CROSS JOIN age_group as tag
   WHERE extract('year' from age(h.birth)) BETWEEN tag.age_min AND COALESCE(tag.age_max, 1000)
) as tmp ON ag.group_id = tmp.group_id
GROUP BY ag.group_id, ag.group_name, ag.age_min, ag.age_max
ORDER BY ag.age_min

Je serais curieux de savoir laquelle vous préférez et pourquoi. Avec un gros volume de données, il serait possible d'ajouter une colonne à la table "human" pour stocker le groupe d'âge pour éviter le calcul à chaque appel mais il serait nécessaire de maintenir la donnée avec un trigger sur la date de naissance et sur la table des groupes d'âges en cas de changement (où il serait nécessaire de mettre à jour toutes les fiches des personnes). Vous avez d'autres suggestions ?